<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Maks Pazuniak on Medium]]></title>
        <description><![CDATA[Stories by Maks Pazuniak on Medium]]></description>
        <link>https://medium.com/@maks_p?source=rss-fad42e2d78db------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*M1Rovlfp0WKwkVI4y1OrfA.jpeg</url>
            <title>Stories by Maks Pazuniak on Medium</title>
            <link>https://medium.com/@maks_p?source=rss-fad42e2d78db------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 13:59:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@maks_p/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[the hidden cost of cocktails]]></title>
            <link>https://medium.com/data-science/the-hidden-cost-of-cocktails-8763340c7db7?source=rss-fad42e2d78db------2</link>
            <guid isPermaLink="false">https://medium.com/p/8763340c7db7</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Maks Pazuniak]]></dc:creator>
            <pubDate>Thu, 17 Oct 2019 18:40:46 GMT</pubDate>
            <atom:updated>2019-10-18T03:22:43.672Z</atom:updated>
            <content:encoded><![CDATA[<p>Cocktails are more than high-end spirits, fresh juices and exotic ingredients — time and opportunity cost matters too.</p><p><em>In previous blog posts, I’ve discussed the process of automating some of our daily workflows at </em><a href="http://www.jupiterdisco.com/"><em>Jupiter Disco</em></a><em>, specifically </em><a href="https://medium.com/swlh/automating-workflows-pos-data-to-google-sheets-using-an-apps-script-62f98c878a86"><em>parsing our daily sales reports</em></a><em> and </em><a href="https://medium.com/better-programming/coding-interview-questions-applied-14c3a37e58ae"><em>product mix reports</em></a><em> to Google Sheets. I’ve since added an integration in Google Apps Scripts that loads the data into a MySQL database living in a Google CloudSQL instance and is replicated in </em><a href="https://cloud.google.com/bigquery/"><em>BigQuery</em></a><em> via </em><a href="https://www.stitchdata.com/"><em>Stitch</em></a><em>. While the data pipeline and architecture are still a work in progress, there’s enough there at this point that I can actually dig in and start mining for some insights using SQL, Python and statistics.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/550/1*5CJqizJ7YAFmJdKRAXYPig.png" /></figure><h3>regarding bars</h3><p>I want to state upfront that bars are magical and essential. They are one of the last bastions of unscripted life, and my intention with this ongoing project is to help my bar and others thrive in the digital age. Technology is as transformative as it is disruptive — if we can apply a tiny fraction of the technical bandwidth that Netflix and Fortnite consume to our small brick and mortar spaces maybe we’ll have a fighting chance against these behemoths of late-stage capitalism, at least until we’re all just trying to get into the <a href="https://en.wikipedia.org/wiki/Snow_Crash">Black Sun</a>.</p><h3>and on cocktails</h3><p><em>“Why serve cocktails when I can sell ten vodka sodas in the same amount of time at a higher margin” —</em> Annoying Bar Owner</p><p>Annoying Bar Owner is technically right of course, in the same sense that most economists are technically right about the machinations of the economy. But bars, like the economies they exist within, are built, operated and consumed by people, and people, in all their unpredictable, creative, and eccentric glory don’t always want vodka sodas, and sometimes they don’t know what they want, and a lot of times what they’re drinking doesn’t actually matter, but they do appreciate the option of a finely crafted cocktail served on a big chunk of ice. Just not too sweet.</p><p>And cocktails rule. The truly great cocktail bars offer menus that are at once whimsical, gonzo, retro, innovative, and timeless - a physical, consumable manifestation of what Dune (2020) would look like if <a href="https://en.wikipedia.org/wiki/Yorgos_Lanthimos">Yorgos Lanthimos</a> had been tapped to direct it and <a href="https://soundcloud.com/resident-advisor/ra610-dj-bus-replacement-service?in=deejaybrs/sets/bus-replacement-services">DJ Bus Replacement Service</a> was in charge of music supervision. I don’t believe any cocktails have broken through into the realm of “real art” yet, but some have at least achieved Dune (1984) status, which isn’t nothing. So let’s dive into this data and tease out some “actionable insights”.</p><h3>set up</h3><p>This is Jupiter Disco’s product mix for the YTD period — this first chart is based on sales:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/980/1*3s6uEvNXEe_z5X_lKAxe0Q.png" /><figcaption>Jupiter Disco Product Mix YTD — Sales</figcaption></figure><p>So at a high level, 40.8% of Jupiter Disco’s sales YTD are in the liquor category, which represents highballs (the aforementioned vodka sodas, gin &amp; tonics, whiskey gingers, etc), shots, and straight pours. Another 26.8% are cocktails, both classics and house, and 25.2% of our sales have been beers, both draft and in cans.</p><p>The product mix looks a bit different based on quantities sold — note that cocktails went from 26.8% as a percentage of dollar revenue to 17.8% as a percentage of quantity sold.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*DUh-vR7gHixFY2oI3q0aGQ.png" /><figcaption>Jupiter Disco Product Mix YTD — Quantities</figcaption></figure><p>We sell a lot of $5 Narragansetts. And not quite as many cocktails for $13, but enough that overall our cocktail revenue is slightly higher than our beer revenue.</p><p>Before we move on: <em>Bars come in a lot of different forms and formats. Jupiter Disco is a 1,000 square foot cocktail bar / dance club that routinely hits its limited standing-allowed capacity on weekends. It’s an entirely different format from the seated-only cocktail bar, which is itself an entirely different format from the “neighborhood bar that does really good cocktails” format. We’re all different, so your mileage may vary here.</em></p><h3>question</h3><p>So what is the impact of running a cocktail bar wrapped in a night club? Well for one, we have to spend a lot more money maintaining our sound system:</p><h3>maks on Twitter</h3><p>Someone good with budgets please help me my bar is dying</p><p>But the other is that we’re offering proper cocktails in a high volume setting, and those cocktails take time to make. Can we quantify the cost of that time?</p><h3>time to code</h3><p>My DataFrame after querying our database and doing a little clean-up looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/978/1*Wb_hLoJGKf6Ji0HCflQaRA.png" /></figure><p>So each row represents quantity and total sales per product per day. This dataset has 12,341 rows (essentially 2019 YTD) that I can aggregate and flip to percentages using the df.pivot_table() and .apply() methods, add a column with the day of the week, consolidate into “Cocktails” and “Non-Cocktails”, and filter the DataFrame down to weekends only:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0OWC_pyRJwgkgEZyoY6lQQ.png" /></figure><p>At this point, I’ll merge this with a DataFrame containing daily net sales and have a complete DataFrame ready to explore, containing 84 total rows (every Friday &amp; Saturday night in 2019).</p><p>To set the stage for this next step of the analysis, let’s look at the mean, standard deviation, range and quartiles for cocktails percentage of total by quantity over the year:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/804/1*pjE4lU35lHPM1bV5c38PPw.png" /></figure><p>So on weekends, our mean cocktails percentage is 16.0%, which you’ll note is slightly lower than overall (inclusive of all days) of 17.8%.</p><h3>correlations</h3><p>Let’s look at the correlation between cocktail and non-cocktail percentages with net sales:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/732/1*ueVWGoojNIUcXmK0lY7fcg.png" /></figure><p>So that’s interesting. It looks like our cocktails percentage is negatively correlated with net sales, with a correlation coefficient of -0.38. One interpretation of this is that when we sell a higher ratio of cocktails to non-cocktails on a weekend, our sales are lower. And while correlation does not imply causation, the fact that we objectively know a cocktail takes longer to make than pulling a canned beer, we can wonder if the price premium for a cocktail isn’t quite capturing the opportunity cost of the time it takes to make one.</p><p>Our Saturdays are busier than Fridays and the relationship is even stronger then:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/848/1*6WAj99yIXXjr2ouwXqrrNw.png" /></figure><p>Let’s plot weekends:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UBWSYI_2pN1lq5DadltdUw.png" /><figcaption>Net Sales on the Y-Axis, Cocktails as a Percentage of Total by Quantity on the X-Axis</figcaption></figure><p>-0.38 isn’t a strong correlation, but it’s fair-to-moderate, and the pattern is discernible on the above scatterplot. We can also look at the relationship over the year:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uoEyMYpC0Pr7NJLIT45PVA.png" /><figcaption>Net Sales (Blue) versus Cocktails Percentage of Total by Quantity (Red)</figcaption></figure><p>I’m obviously obscuring the left Y-Axis because the intention of this post is not to blast Jupiter Disco’s sales all over the Internet, hopefully, y’all understand.</p><h3>statistical significance</h3><p>To try to quantify the impact, I broke this dataset into two sample sets: (1) Low Cocktails Percentage, representing the 50% of the data below the median cocktail percentage of 15.9%, and (2) High Cocktails Percentage, representing the 50% of the data above.</p><p>Looking at the statistics for each sample set, the Low Cocktails Percentage group had mean sales 5.3% higher than the overall mean, and the High Cocktails Percentage Group had mean sales 5.3% lower than the overall mean, which feels like a fairly large swing in either direction based on what percentage of cocktails we sell.</p><p>Is it statistically significant?</p><p>Before we run the test, let’s frame the question properly. Our Null Hypothesis is that a high percentage of cocktails <strong>does not</strong> have a negative impact on sales. Our Alternative Hypothesis is that a high percentage of cocktails <strong>does</strong> have a negative impact on sales. Let’s set our significance level to 0.05 and run a t-test to evaluate whether we can reject or fail to reject our Null Hypothesis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9ZTiEVDoMPSc_oghfeWxcg.png" /></figure><p>So, with a p-value of 0.01, it does look like the difference between these two means is statistically significant, and we can <strong>reject</strong> the Null Hypothesis.</p><h3>now what</h3><p>Quantifying the cost of running a cocktail program is difficult. There is undoubtedly a lot noise in the data. The crowds change, the total amount of people over the course of a night varies over time, and there will be staff turnover. The cocktails themselves will change.</p><p>But it does look like there’s a bit of signal in this data, implying that the cocktail price premium isn’t quite capturing the opportunity cost of the time it takes to produce them (the opportunity cost here is the time bartenders could be selling vodka sodas). And if we don’t want to raise prices (which we don’t), it means we’ll have to revisit the builds and processes for making cocktails to see if we can shave some seconds off and get them out faster.</p><p>On a high-level, I hope this post helps you understand one of the reasons why cocktails are more expensive than a highball. It’s not just the ingredients in the glass. Every minute spent stirring a cocktail is a minute not spent slinging beers and shots, and on busy nights that time can add up. And that’s before we start accounting for the research &amp; development cost for each cocktail, the training time, and all the other factors that go into their real cost, explicitly stated or not.</p><p>Hope you enjoyed.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8763340c7db7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/the-hidden-cost-of-cocktails-8763340c7db7">the hidden cost of cocktails</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Coding Interview Questions Applied]]></title>
            <link>https://medium.com/better-programming/coding-interview-questions-applied-14c3a37e58ae?source=rss-fad42e2d78db------2</link>
            <guid isPermaLink="false">https://medium.com/p/14c3a37e58ae</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[algorithms]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Maks Pazuniak]]></dc:creator>
            <pubDate>Fri, 20 Sep 2019 14:30:56 GMT</pubDate>
            <atom:updated>2019-09-24T00:32:07.530Z</atom:updated>
            <content:encoded><![CDATA[<h4>Applying problem-solving techniques in the real world</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hpiMRYONt1Q8EQB7ZckeKw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@freshhconnection?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Freshh Connection</a> on <a href="https://unsplash.com/s/photos/interview?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Last week, I used a Google Apps script to automate part of our workflow at Jupiter Disco — specifically parsing a daily sales report that our point of sales system (‘POS’) sends every day and populating our primary sales spreadsheet with the data.</p><p>The script works great — no more copying and pasting data from one place to another— but it’s also only saving us five to ten minutes a day and doesn’t build up a new stream of data that we can use to improve the business.</p><p>What I really wanted to explore was building a similar automation with the product mix (“PMix”) that comes attached with that email as an <a href="https://fileinfo.com/extension/htm">.htm file</a>. We currently don’t track our PMix on a daily basis, mostly because it would be a super annoying, time-consuming, and repetitive daily task.</p><p>In attempting to set this automated process up, I encountered a challenge that was oddly familiar.</p><p>If you, like me, are looking for a job as a data scientist, software engineer, web developer, data engineer, whatever, you’ve probably spent a good amount of time practicing potential interview questions and generally trying to improve your coding skills on <a href="https://www.hackerrank.com/">HackerRank</a> or a similar site — <a href="https://www.codewars.com/">Codewars</a>, <a href="https://leetcode.com/">LeetCode</a>, <a href="https://www.coderbyte.com/">Coderbyte</a>, <a href="https://projecteuler.net/">Project Euler</a>, etc.</p><p>The problems can veer towards the abstract, at least for non-computer science people like myself, and will often require <a href="https://www.hackerrank.com/challenges/minimum-swaps-2/problem">navigating or transforming an array</a>, which is where I found myself today.</p><h3>Problem</h3><p>As I discussed last week, the libraries at our disposal to parse HTML in a Google Apps script are fairly limited, so my hacked-together solution was to make an array with each line as an element and then pull the appropriate value into a Google Sheet by index.</p><p>This works with a highly structured page like a daily sales report, but won’t for a PMix report, as guests will order different items night to night, meaning different products will appear on different lines.</p><p>Using an index isn’t dynamic enough, and what we really want to do is recreate the table format the report is delivered in. The report is sent as an attachment, and looks like this (this is just a screenshot):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fo32SlU4Ta6I4TD_Q5fRVA.png" /></figure><p>And the HTML for that same attachment looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vZfJHd0W54SS8Os5UFH0rQ.png" /></figure><p>Without getting too into the weeds for this post, as I definitely did last week, the process for automating the daily upload of this Gmail attachment to a Google Sheet is more or less as follows:</p><ul><li>Grab the product mix attachment from the daily sales report.</li><li>Convert the HTML into a String that we can parse.</li><li>Split the String into an array of strings — each element is a separate line of HTML — and strip out all the HTML tags.</li><li>Structure the array into tabular format that resembles the original document.</li><li>Populate a Google Sheet with the structured data.</li></ul><p>As it turns out, all the work on arrays and algorithms on HackerRank really helped when it came to the fourth step. I’ll provide my code in a minute, but if you’d like to try it, the question can be framed as follows:</p><p>Given an array of elements following a consistent pattern (for example, every third element is the product, every fifth element is the quantity and every seventh element is the price, up to an <em>nth</em> element that represents the last element of an observation), create a new array that is arranged such that all the <em>nth</em> elements line up with each other column-wise and can be appended row-by-row into a spreadsheet.</p><p>To put a finer point on it, you need to create an array of arrays, and each nested array will hold x elements, where x is the total number of data points for each observation.</p><h3>Solution</h3><p>This problem would probably be categorized as <em>easy </em>in HackerRank, but it’s also a fun, real-world example of needing to use the very toolkit we’re practicing every day.</p><p>On the 2019–09–18 product mix report at Jupiter Disco, the original array<em> </em>had 288 elements that repeated itself every nine elements (so the final array would contain 288 / 9 = 32 arrays).</p><p>How would you solve it?</p><p>My function was:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4ae901f1e933ef61eea94598093b7cc1/href">https://medium.com/media/4ae901f1e933ef61eea94598093b7cc1/href</a></iframe><p>I’m sure there’s a more efficient way of solving this, share yours below if you have one!</p><p>The rest of the function is similar to last week‘s— all I have to do is append each of those nested arrays to a Google Sheets spreadsheet. The result looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U9qiVek9yZSizH2bU_gGbw.png" /></figure><p>I’ll be able to use an expanded version of this script to automatically populate a database with our daily product mix, which is super cool.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14c3a37e58ae" width="1" height="1" alt=""><hr><p><a href="https://medium.com/better-programming/coding-interview-questions-applied-14c3a37e58ae">Coding Interview Questions Applied</a> was originally published in <a href="https://betterprogramming.pub/">Better Programming</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automating Workflows >POS Data to Google Sheets using an Apps Script]]></title>
            <link>https://medium.com/swlh/automating-workflows-pos-data-to-google-sheets-using-an-apps-script-62f98c878a86?source=rss-fad42e2d78db------2</link>
            <guid isPermaLink="false">https://medium.com/p/62f98c878a86</guid>
            <category><![CDATA[point-of-sale]]></category>
            <category><![CDATA[javascript]]></category>
            <category><![CDATA[productivity]]></category>
            <category><![CDATA[google]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Maks Pazuniak]]></dc:creator>
            <pubDate>Fri, 13 Sep 2019 19:28:06 GMT</pubDate>
            <atom:updated>2019-09-14T14:47:07.790Z</atom:updated>
            <content:encoded><![CDATA[<h3>Automating Workflows &gt; POS Data to Google Sheets using an Apps Script</h3><p>Point of Sale systems are ubiquitous in bars and restaurants, but don’t always integrate neatly with your workflow. Using a Google Apps Script, we can automatically populate a Google Sheets spreadsheet from the daily email our POS sends.</p><p>This will be Part One in this series. In Part Two, I’ll demonstrate how you can use Zapier to perform the same action with a lot less code (but with a monthly fee). This is geared towards bar &amp; restaurant owners, so I’ll try to be as detailed as possible. Fair warning that this is a long post.</p><p>The complete code for this blog is available on my GitHub <a href="https://github.com/maks-p/gmail-google-sheets">here</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/706/1*B82r1PfzwE0S4UdO--zEgg.jpeg" /></figure><h3><strong>Why though?</strong></h3><p>At <a href="http://www.jupiterdisco.com/">Jupiter Disco</a> we use a <a href="https://smarttab.com/">SmartTab</a> POS system that we’re generally happy with because it’s highly efficient during service and intuitive to use. Opening and closing tabs is a breeze and the built-in search function for products and tabs is really well executed. Like any POS system, it has some drawbacks, one being that it doesn’t integrate with any of our back office workflow, namely <a href="https://www.google.com/sheets/about/">Google Sheets</a>.</p><p>Every day we have to pull up the report from the previous night and copy and paste the individual values into a Google Sheet in another tab in our browser (or worse, just type it in, leading to input errors). And we’re certainly not the only ones performing this archaic workflow, as many, if not most, independent bars and restaurants will execute some form of it on a daily basis.</p><p>It doesn’t take long, but it can lead to errors that later have to be tracked down. And it just feels unnecessary. So let’s automate this simple task first, and then we can tackle some more complicated automations later.</p><h3><strong>Are we sure about this?</strong></h3><p>No. Look, every POS system is different. They all run different reports, name the same value something different (“Net Sales” or “Net (Taxable) Sales”), send different emails with different layouts if they send emails at all, so there is no one size fits all solution here. I’ll do my best to work through the process, so you can apply to your own POS and workflow as necessary.</p><p>JavaScript, which Google Apps Script is built on, is not my native language, so buyer beware. This whole post is essentially information <a href="https://www.reddit.com/r/TheSimpsons/comments/4c1juc/bart_so_how_do_you_know_so_much_about_american/">I pieced together from sugar packets</a>.</p><p>I’ll walk through the process of setting up a Gmail-to-Google Sheets-spreadsheet automation from a <a href="https://squareup.com/us/en">Square</a> Daily Sales Report Email (which we use as a back-up payment processor in case our primary merchant services provider goes down). The code for a SmartTab implementation is available <a href="https://github.com/maks-p/gmail-google-sheets/blob/master/script_smart_tab.js">here</a> from my GitHub.</p><h3><strong>Technical Requirements</strong></h3><p>I’m assuming the following:</p><ul><li>You get a Daily Sales Report email from your POS system.</li><li>That email is sent to a Gmail account.</li><li>That same Gmail account is associated with a Google Shared Drive where you keep all your bar / restaurant related work.</li></ul><h3><strong>Step_1 :: Set up a google sheets file</strong></h3><p>You probably already have a spreadsheet to track sales, so our first step will be to create an intermediate spreadsheet that we’ll dump the parsed data into. My recommendation is to <strong>(a )</strong> Create a new folder in your share drive — call it “pos_sales_data” or something, <strong>(b )</strong> Create a new Google Sheets file in that folder, <strong>(c ) </strong>Name the first worksheet “sales_data” and <strong>(d )</strong> Add a second worksheet called “test_data”. Finally, you’ll want to <strong>(e )</strong> add your header row to the “sales_data” worksheet — these are the columns you’ll want to populate from your POS: Date, Gross Sales, Discounts, Sales Tax, Net Sales, Etc. Assuming that you’re following along, your spreadsheet will look something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9ejn1Zl2j-eNG_RaHs1URg.png" /></figure><h3><strong>Step_2 :: Connect &amp; create a Google Apps Script</strong></h3><p>You most likely haven’t already connected Google Apps Script, so the next step will be to navigate to the “+ New” button in your Google Drive, go down to “More” and then click “Connect More Apps.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*89SbfUXuaFVigujAjfhrJw.png" /></figure><p>Once there, search for “google apps” and click on the connect button for “Google Apps Script”:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z31TpGrazMicoU110votZw.png" /></figure><p>Next, create a new Google Apps Script by click on the “+ New” button again and navigating to the application. It will open in a new tab and should look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HCUb2pOj0dYfV2Wx3zfhYQ.png" /></figure><p>Name your project whatever you want and let’s get to the fun part.</p><h3><strong>Step_3 :: Identify relevant emails</strong></h3><p>Navigate to Gmail and enter a search term that finds <strong>only</strong> the Daily Sales Reports your POS sends. For example, it may look something like this:</p><blockquote>“from:<a href="/service/mailto:noreply@messaging.squareup.com">noreply@messaging.squareup.com</a> AND Daily Sales Summary Report”</blockquote><p>Copy the complete search term and navigate back to your Google Apps Script.</p><h3><strong>Step_4 :: Start coding!</strong></h3><p>First, name your function. Instead of myFunction let’s call it parseEmailPOS. Next, create a variable searchTerm and assign it the string of the search term we used above to identify the relevant emails with the following code block:</p><p>var searchTerm = &quot;&lt;INSERT_YOUR_SEARCH TERM&gt;&quot;</p><p>Then create a variable thread that will hold the first thread (which we identify with the [0] index) in the array of threads returned by the built-in GmailAppsearch method.</p><p>After that we can find the first message in the first thread and assign it to the variable message. To test our integration, pass the argument message.getSubject() into Google Apps’ built-in Logger method. Your code should look like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e736107e6991cb608dcd41f5a83f785b/href">https://medium.com/media/e736107e6991cb608dcd41f5a83f785b/href</a></iframe><p>Next, go to <strong>Run -&gt; Run function -&gt; parseEmailPOS</strong> and click.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*90t1q2t4EaQZleoS0D58qA.png" /></figure><p>If this is your first time running this script, you’ll likely get the following pop-up:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f7W3yn0wLxX8EzyrZzRTYQ.png" /></figure><p>Click <strong>Review Permissions</strong> and follow the instructions to allow the connection to your Gmail. You want to make sure you’re adding the correct user here — the user that receives the Daily Sales Report from your POS system.</p><p>Once this is done you can run the script, and assuming there are no errors, navigate to <strong>View -&gt; Logs</strong> to confirm that your script was able to find and log the Subject Line of the most recent email sent by your POS:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E624yK5-kQDbPs6jLH_2Tw.png" /></figure><p>If you see something similar to this it means your script worked! Nice.</p><h3><strong>Step_5 :: Connect to your google sheets spreadsheet</strong></h3><p>The goal here is to parse that email and send the data to a spreadsheet, so next we need to connect to the Google Sheets spreadsheet we made earlier. The process is similar to what we did for Gmail except now we’re using the SpreadsheetApp class in Google Apps Script. The code will look like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/83b54faad508676c15c0ba5a05d2b6b0/href">https://medium.com/media/83b54faad508676c15c0ba5a05d2b6b0/href</a></iframe><p>Copy and paste the above inside of the parseEmailPOS function (copy it inline with the previous code, inside the { }. Inside of the var url string, replace &lt;YOUR_KEY_HERE&gt; with the key from your <strong>pos_sales_data </strong>Google Sheets spreadsheet that you made earlier (or just copy and past the entire url and replace the string if it’s easier).</p><p>This code instructs the script to open the spreadsheet, find the appropriate sheet, and then append a new row to that sheet consisting of our Subject Line. Don’t forget the [] surrounding message.getSubject() in your appendRow argument, as the method is expecting an array.</p><p>Same as before, run this function by navigating to <strong>Run -&gt; Run function -&gt; parseEmailPOS</strong>. You’ll be asked to authorize the connection to the Google Sheets spreadsheet. This time, if the script ran successfully, you should be able to go to the spreadsheet in another tab, click on the <strong>test_data</strong> <strong>worksheet </strong>and see the subject line:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ph09X-eui94CJKcOHfKyXg.png" /></figure><p>Taking a step back, our little script has now successfully searched for and returned the most recent email from our POS system, and input the subject line into a Google Sheets spreadsheet. Not bad!</p><h3><strong>Step_6 :: Prep the email</strong></h3><p>These next couple of steps are difficult, but we can break them down into small steps and work through it fairly easily by running the script often and debugging as we go.</p><p>First, let’s comment out testData.appendRow([message.getSubject()]) by adding // in front of the line. Then let’s add some code inside our function that will create an array of lines of HTML:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/69f01f3b94e862d67c933651b04d0085/href">https://medium.com/media/69f01f3b94e862d67c933651b04d0085/href</a></iframe><p>messageBody is a String of HTML. We run the .split(&quot;\n&quot;) method on it to create an array of lines of HTML that we assign to line_array. Then we loop through line_array and append each line of HTML to a new row in our <strong>test_data worksheet. </strong>Why are we doing this? So we can easily identify where the pertinent information is buried in the HTML of this email.</p><p>As far as I could find, there is no good way to parse HTML in a Google Apps Script. We can’t import <a href="https://www.crummy.com/software/BeautifulSoup/bs4/doc/">BeautifulSoup</a> and cleanly work our way through the document. So we’re basically going to hack together a solution that works for each specific use case, that being the exact email that your POS system sends you. Since they’re sending a structured document, and not some random email, as long the format of the email stays the same, this hack will work. If it changes, you’ll just have to retrace some of the steps we’re about to take.</p><p>Before you run the above code, clear your<strong> test_data worksheet</strong>. It should be completely blank. Then run your parseEmailPOS function and navigate to that worksheet. It should look like this once the function finishes running (it could actually take a minute or so):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*06paWM1RI81DMXEPBbGZfQ.png" /></figure><p>Neat. What we’re looking for next are the actual data points we want to pull from the HTML, identified by which row in the spreadsheet it appears. Every POS will actually vary wildly at this point, so let’s take this line item by line item.</p><h3><strong>Step_7 :: Parse the email</strong></h3><p>We’re going to create a new variable for each data point that we want to extract from the email and input into our Google Sheets spreadsheet.</p><p>To get the full <strong>Date</strong> from the Square email, we actually want to parse the Subject Line, so we can knock that out with the following code:</p><p>var reportDate = message.getSubject().split(“ for “)[1]</p><p>Again, this is a bit hacked-together — the code is *very* hard-coded. But as long as the subject line doesn’t change, we now have a variable that holds a value in a format Google Sheets will interpret as a date.</p><p>If you want to see this in action, comment-out the “Identify Relevant Line Number” section so we don’t have to wait for that to run again, and add a line inside the function to append a row to the main <strong>sales_data worksheet:</strong></p><p>salesData.appendRow([reportDate])</p><p>The full function up until this point should look like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1a678d0331e75990c67d4efc4ee690f8/href">https://medium.com/media/1a678d0331e75990c67d4efc4ee690f8/href</a></iframe><p>And if you run it, and navigate to your spreadsheet’s <strong>sales_data</strong> <strong>worksheet, </strong>it should look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uTuo34JoNJG27H4k-RObWQ.png" /></figure><p>Next we’ll work through the rest of the data points:</p><p><strong>business_day</strong> is easy, and a little redundant, but we can pull it from line 213 in our <strong>test_data worksheet</strong>, which is number 212 in our line_array index:</p><p>var BusinessDay = line_array[212].split(“&gt;”)[1].split(‘ ‘)[0]</p><p>.split works by separating the String on the string that we pass as an argument (e.g. “&gt;”). In the above code block we actually split it twice, first on “&gt;”, then on a space.</p><p>Now we can populate all of the values. For the Square email, this is actually pretty easy since they’re not buried inside of HTML tags, but we do need to delve fairly deep to find them all. For example, the <strong>Gross Sales dollar amount </strong>is found here on line 300 (index 299 in our array).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-kDTBxRkVskTr6-z6LSy-Q.png" /></figure><p>So our variable will be:</p><p>var adjustedGrossSales = line_array[299].split(‘$’)[1]</p><p>By now you should have a good sense of the methodology for finding and extracting each value:</p><ul><li>Identify value in <strong>test_data worksheet </strong>and note row number (e.g. 300)</li><li>Create a variable following this format: var <strong>posValue</strong> = line_array[<strong>row_num</strong> — 1].split(&#39;$&#39;)[1]</li><li>If your posValue isn’t a standalone value, you’ll have to add some additional code to break it out. Try adding another .split(&#39;&lt;whatever_comes_next&gt;&#39;)[0] to the variable.</li><li>If you have whitespace around the value, append .trim() to the end.</li></ul><p>The full set of variables, along with our appendRow, should look like the following for our Square email:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/30f3bb73db019ddb782ae66c0c070d29/href">https://medium.com/media/30f3bb73db019ddb782ae66c0c070d29/href</a></iframe><p>Run this function and your <strong>sales_data worksheet </strong>should fully populate, depending on which columns you included. If you have additional columns you’d like to include you’ll have to add the variable. Note that appendRow populates the columns in order of the variables in the array, so keep your order straight.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4AAqWILZQTq961IhmtPUUQ.png" /></figure><p>Some quick notes on the Google Sheets component of this:</p><ul><li>appendRow appends new data under the last populated row in the spreadsheet, so you can copy and paste all your historical data into this spreadsheet if you want to create a sort of primary sales worksheet that you pull data from in all your other spreadsheets.</li><li>You can also just feed this worksheet into one in another spreadsheet file using <strong>IMPORTRANGE()</strong> and then using a <strong>VLOOKUP()</strong> or another function to automatically populate your target spreadsheet.</li></ul><h3><strong>Step_8 :: Add index validation</strong></h3><p>We want to include a basic test to give us some comfort that the values we’re extracting from the HTML are the correct ones and haven’t changed, so using my limited knowledge of JavaScript, let’s wrap the above variables and appendRow method in an <strong>if / else statement</strong> that confirms that a couple rows are where they’re supposed to be, and throws up an error otherwise:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b099288019c83351a96daef9e203bca6/href">https://medium.com/media/b099288019c83351a96daef9e203bca6/href</a></iframe><h3><strong>Step_8 :: Refactor</strong></h3><p>I’ve kept everything in our one function parseEmailPOS up until now, but it’s bad practice to wrap all this action in one function, so let’s refactor the code, and add some enhanced functionality along the way, namely a check that the date doesn’t already exist in our Spreadsheet (this is especially important for a use-case like Jupiter Disco’s with Square — we only get reports from Square when we need to use it as a back-up, so when we run it on a daily basis it would just keep repeating the same data from the same email).</p><p>If you’re following along, I won’t get into the details of every element here — it’s mostly copy and pasting our existing code into new functions and then passing the return from those functions into our existing ones.</p><p><strong>getMessage()</strong> will pull the most recent email from our POS system:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4db6faa766579b7ba321fcb6c4384e4c/href">https://medium.com/media/4db6faa766579b7ba321fcb6c4384e4c/href</a></iframe><p><strong>connectSpreadsheet() </strong>will connect to our Google Sheets file and return a Spreadsheet object.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/cc3204fe839bd87854c06e11300c01e7/href">https://medium.com/media/cc3204fe839bd87854c06e11300c01e7/href</a></iframe><p><strong>appendHTMLarray() </strong>isolates our HTML dump into the<strong> test_data worksheet</strong>, so we only have to call that function when necessary.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a520caf953ef58516abaab99b307f85b/href">https://medium.com/media/a520caf953ef58516abaab99b307f85b/href</a></iframe><p><strong>getLastDate()</strong> is an annoyingly complicated function that returns the most recent Date in our Spreadsheet (assumes <strong>Date</strong> is Column ‘A’). Adapted from <a href="https://stackoverflow.com/questions/4169914/selecting-the-last-value-of-a-column">this Stack Overflow post</a>.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9f87dc150decc59ab52e702b7073477b/href">https://medium.com/media/9f87dc150decc59ab52e702b7073477b/href</a></iframe><p><strong>parseEmailPOS(), </strong>our main function, checks the <strong>getLastDate()</strong>, then checks that the HTML elements are all where they’re supposed to be, before appending the new row. If the date already exists, the script will log it via the built-in Logger method. I’ve also moved all the variable assignments outside of the nested if/else blocks.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ed5f4269660c1c05a851f395192bb84e/href">https://medium.com/media/ed5f4269660c1c05a851f395192bb84e/href</a></iframe><p>Our POS specific code — where you’ll use the appendHTMLarray() function to identify where the relevant HTML elements are located and assign to variables, is commented, and contained to this function.</p><p>If anything in your code looks off, please refer to the code on my <a href="https://github.com/maks-p/gmail-google-sheets">GitHub page for this project</a>.</p><h3><strong>Step_9 :: Add a time-based trigger</strong></h3><p>Right now the function runs when we navigate to <strong>Run -&gt; Run function, </strong>but we want it to run automatically every day when this email hits your inbox (for example, our SmartTab email hits at 7:00 AM EST). This is super easy to set up in Google Apps Script.</p><p>For reference, we’re just going to follow the “Managing triggers manually” Google Apps instructions <a href="https://developers.google.com/apps-script/guides/triggers/installable">here</a>:</p><ul><li>Navigate to <strong>Edit -&gt; Current project’s triggers</strong></li><li>Click <strong>+Add Trigger </strong>in the bottom right</li></ul><p>You should get a screen like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5vZ9YMRst_lDPpGmOwgmFg.png" /></figure><p>Next, choose which function to run: <strong>parseEmailPOS.</strong></p><p>Leave the deployment on <strong>Head, </strong>and event source as <strong>Time-driven.</strong> Set the type of time based trigger to <strong>Day timer</strong> and then select the <strong>hour interval</strong> appropriate for your email (I have our SmartTab interval set for 7am to 8am). Click <strong>Save</strong> and you’ll be all set.</p><h3><strong>The End</strong></h3><p>Let me know in the comments if you have any questions or issues implementing this system. I’ll try to add additional scripts to my GitHub for other POS systems as well. Good luck!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=62f98c878a86" width="1" height="1" alt=""><hr><p><a href="https://medium.com/swlh/automating-workflows-pos-data-to-google-sheets-using-an-apps-script-62f98c878a86">Automating Workflows &gt;POS Data to Google Sheets using an Apps Script</a> was originally published in <a href="https://medium.com/swlh">The Startup</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unpacking R²]]></title>
            <link>https://medium.com/data-science/unpacking-r%C2%B2-a2a2ffcd27db?source=rss-fad42e2d78db------2</link>
            <guid isPermaLink="false">https://medium.com/p/a2a2ffcd27db</guid>
            <category><![CDATA[linear-regression]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[xgboost]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Maks Pazuniak]]></dc:creator>
            <pubDate>Mon, 19 Aug 2019 16:53:50 GMT</pubDate>
            <atom:updated>2019-08-22T01:16:08.925Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>R² is a popular statistic for evaluating linear regression models, but as I discovered, it can be easily misapplied.</em></h4><p>Data science is an iterative process, which is especially true for new practitioners. We can spend weeks on a project, only to later discover a fundamental flaw in the analysis. This post explores a basic mistake I made on a project, and working through how it happened and why it matters.</p><p>My <a href="https://github.com/maks-p/restaurant_sales_forecasting">capstone project</a> was a predictive analytics tool that utilized historical sales data and weather data to forecast nightly and weekly sales for an award-winning restaurant in Brooklyn. Historical sales data were pulled directly from the restaurant’s point of sale system (two separate systems that had to be aggregated, actually), and while the project was fairly straightforward, certain elements did present challenges. Holidays, closed days, outside seating that was only open on a seasonal basis and was subject to unexpected closures, room rental fees, inaccurate guest count data, and special events all had to be understood and handled appropriately.</p><p>One decision that I had to make was how to deal with holidays / closed days, when sales were $0. I considered two options: (1) Include an encoded boolean “Closed” feature, or (2) Drop closed days entirely from the dataset (a 3rd option would have been to treat the days as outliers — more on this later). While I was experimenting with the two options, I noticed that regardless of which regression model I used, the R² value varied significantly depending on how I treated the closed days.</p><p>I ended up using the boolean “Closed” feature as it resulted in a higher R² value, but that decision has never really sat well with me. The purpose of this post will be to revisit that decision and understand why my original decision was the incorrect one.</p><h3><strong>Set Up</strong></h3><p>To provide some context, the following graph shows nightly average sales in the restaurant’s dining room, bar, and private dining room areas, excluding the outside area.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/1*a7CMQbLrfB7gaLO1DTRkEg.png" /><figcaption>Nightly Average Sales, Jan-2017 — June-2019</figcaption></figure><p>The restaurant averaged indoor sales of $14,575, with a standard deviation of $1,832 over the period January 2017 through June 2019 (this is a particularly successful restaurant, I should note).</p><h3>Comparing Approaches</h3><p>The following table shows how three different evaluation metrics differed between the two options for handling closed days — in addition to R²,<a href="https://en.wikipedia.org/wiki/Root-mean-square_deviation"> Root Mean Square Error</a> (RMSE) and <a href="https://en.wikipedia.org/wiki/Mean_absolute_error">Mean Absolute Error</a> (MAE):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0zPJdybbOMLXEOR0nQdsjw.png" /></figure><p>So, while RMSE &amp; MAE stayed fairly consistent, R² dropped significantly when I dropped the observations with closed days. Why is this? Time to unpack R² and better understand what’s happening.</p><p>Before we move on, it’s instructive to look at the residuals under both scenarios — this will provide some intuition into what is happening here:</p><p><strong>Residuals with an Encoded, Boolean Closed Feature:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/735/1*z9g_Z5pCRL4peEP2-UnndQ.png" /></figure><p><strong>Residuals when Closed Days are Dropped:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/733/1*XAF3Am4eKDIe6iiYhwCCgg.png" /></figure><h3>R²</h3><p>R² represents the proportion of the total variation in a dependent variable (the target variable, in this case, sales) that is explained by the variation in the independent variables (our feature variables, such as day-of-week, month, sales trend, and temperature).</p><p>One of the simplest expressions for R² in a modeling context is the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/642/1*zWUYEDe0CFtPfHMMxz7DSA.png" /></figure><p>The <a href="https://en.wikipedia.org/wiki/Residual_sum_of_squares">Residual Sum of Squares </a>is the summation of the squared errors — the differences between an actual data point and the data point predicted by an estimation model. The <a href="https://en.wikipedia.org/wiki/Total_sum_of_squares">Total Sum of Squares</a> represents the sum of the deviation of each target observation from the mean of all target observations.</p><p>The code to calculate R² looks like the following (y_hat is the predicted target variable — this particular code block calculates the test set’s R²):</p><pre>sum_squares_residual = sum((y_test - y_hat)**2)<br>sum_squares_total = sum((y_test - np.mean(y_test))**2)</pre><pre>r_squared = 1 - (float(sum_squares_residual))/sum_squares_total</pre><p>We’re beginning to see why including the closed days was a mistake, but let’s take the next step in the calculation to really understand it:</p><pre>Boolean Closed Feature:</pre><pre>sum_squares_residual: 225_779_046<br>sum_squares_total: 921_523_793</pre><pre>sum_squares_residual / sum_squares_total = 0.245<br>r-squared = 0.755</pre><pre>Dropped Closed Days:</pre><pre>sum_squares_residual: 223_557_101<br>sum_squares_total: 484_108_603</pre><pre>sum_squares_residual / sum_squares_total = 0.46<br>r-square = 0.538</pre><p>Look at the difference in the denominator! The RSS is actually lower when the closed days are dropped, which should indicate a higher R², but the denominator (TSS) is significantly lower, leading to a higher RSS / TSS ratio (and thus lower R²).</p><p>So what’s going on?</p><p>Since I included the data points from closed days <strong>and specifically excluded them from my outlier adjustment</strong>, for each of the 12 closed data points in the dataset the R² formula is subtracting $0 from $14,575 (the mean for the entire dataset — in practice the train R² is using the training mean and the test R² is using the test mean), <strong>and then squaring the result </strong>($14,575² is $212,430,625!).<strong> This an entirely artificially constructed variance that is unnecessary to include, but does have the side benefit of boosting my R² value.</strong> A closed day does not represent natural variance — we know why sales are $0 on those days and can actually predict $0 sales for them with 100% accuracy. They should have been dropped.</p><h3>Lessons</h3><p>My first mistake was improperly treating closed days — it would have made more sense to drop them from the dataset. And as I dug into R², I also realized that Root Mean Squared Error or Mean Absolute Error would have been better model evaluation metrics for this particular application (and by the time you read this, my project will reflect as much). If you really want to dive into why R² is a flawed metric for a lot of reasons please see these <a href="http://www.stat.cmu.edu/~cshalizi/mreg/15/lectures/10/lecture-10.pdf">lecture notes</a> from Cosma Shalizi at Carnegie Mellon.</p><p>This exercise also further illustrates the importance of properly handling outliers — a lot of the statistical assumptions that machine learning is built on simply don’t work if you aren’t properly account for them. These closed days were effectively outliers — engineering a feature for them was the wrong approach.</p><p>Lastly, and most importantly and obviously, this is why <strong>understanding statistics and the underlying math is so important for Data Science.</strong> This was a simple mistake on my part, and theoretically a forgivable one given that I’m just starting out in the field, but also highly instructive. Nobody is an expert overnight, and when something doesn’t look right, you can learn a lot from digging into why, and not just assuming that your model knows best.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a2a2ffcd27db" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/unpacking-r%C2%B2-a2a2ffcd27db">Unpacking R²</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Clustering Models + Land Use // Exploring the Atlas of Urban Expansion]]></title>
            <link>https://medium.com/@maks_p/clustering-models-land-use-exploring-the-atlas-of-urban-expansion-b39098c756d7?source=rss-fad42e2d78db------2</link>
            <guid isPermaLink="false">https://medium.com/p/b39098c756d7</guid>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[cities]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[urbanism]]></category>
            <dc:creator><![CDATA[Maks Pazuniak]]></dc:creator>
            <pubDate>Fri, 24 May 2019 02:21:13 GMT</pubDate>
            <atom:updated>2019-05-25T16:39:34.490Z</atom:updated>
            <content:encoded><![CDATA[<p>The <a href="http://atlasofurbanexpansion.org/">Atlas of Urban Expansion — 2016 Edition</a> is a “multi-phase research effort to monitor the quantitative and qualitative aspects of global urban expansion” from New York University, UN-Habitat and the Lincoln Institute of Land Policy. The study focuses on a sample of 200 cities from a universe of 4,231 cities that had a population of 100,000 or more as of 2010, and uses “state-of-the-art research, satellite imagery and novel analytical techniques” to produce urban indicators and metrics, including boundaries, populations, densities, and other land use phenomena. The data was gathered for three dates: ~1990, ~2000, and ~2014, which helped the researchers identify the scale of urbanization on our planet and how it is changing over time.</p><p>The data is available for download from the <em>Atlas</em> <a href="http://atlasofurbanexpansion.org/data">website</a> . I love cities and urbanism, so let’s dive in.</p><h3>The Data</h3><p>The researchers selected sample cities at random based on world region, city population size, and number of cities the country.</p><p>The maps of the urban extent of the cities were created using <a href="https://www.usgs.gov/land-resources/nli/landsat">Landsat</a> imagery, which with the help of human-assisted algorithms allow for classification as (1) built-up, (2) open area, or (3) water. The researchers were then able to classify areas as urban, suburban or rural based on pixel counts within a certain radius of a point. The below map shows how Addis Ababa, in Ethiopia, was classified (based on 1986 data).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/978/1*Eo897VgsH79S2oR-JwWLXw.png" /><figcaption>The built-up area within the study area of Addis Ababa, Ethiopia, in 1986 differentiated into urban (dark red), suburban (red) and rural (ochre) pixels.</figcaption></figure><p>Researchers used this data along with census data to measure <em>Density, Fragmentation</em> and C<em>ompactness:</em></p><ul><li><strong>Density</strong> // Ratio of total population and its urban extent, measured in persons per hectare.</li><li><strong>Fragmentation </strong>//<strong> </strong>The degree to which the built-up area of urban extents is fragmented by open space, measured by <em>Saturation </em>(Ratio of built-up area within the urban extent of the city to its urban extent) and the <em>Openness Index</em> (Average share of open space “pixels” in a given area within the urban extent).</li><li><strong>Compactness</strong> // A general measure of the two-dimensional shape of an urban extent. A city is considered to be more compact the closer it is to being a perfect circle. The study utilizes two measures, the <em>Proximity Index </em>and the <em>Cohesion Index</em>, both of which rely on comparing the urban extent to the shape of a circle with the area of the urban extent centered on city hall.</li></ul><h3>Data Exploration</h3><p><a href="http://atlasofurbanexpansion.org/cities/view/Tokyo">Tokyo</a> is the largest city in the sample, with a 2014 population of 34.8 million. The 50-most populous cities are below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*Mk-alKavwzuIgw7Iv30-fg.png" /><figcaption>Urban Populations</figcaption></figure><p><a href="http://atlasofurbanexpansion.org/cities/view/Dhaka">Dhaka</a> is the most dense city in the sample, with a population density of 372 persons per hectare. The 50-most dense cities are below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/947/1*9s8IMQH5FVjE2klDm23_Pg.png" /><figcaption>Urban Densities</figcaption></figure><p>Urban population is on the rise:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ePq-iFm9Wq2cqMfU6wezaA.png" /></figure><p>But density is actually decreasing, as cities sprawl outwards:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i7H8RI0N6m33Q6_GwJQysA.png" /></figure><h3>K-Means Clustering</h3><p>Clustering is an unsupervised machine learning technique that groups unlabeled observations together based on their underlying data. The goal of clustering algorithms is to classify observations with similar properties (or “features”) as one group, while classifying observations with dissimilar properties as another. The technique is widely used by data scientists to uncover hidden relationships in data.</p><p>K-Means is a popular algorithm for this task. It effectively identifies the ‘centroid’ of an estimated cluster and then measures the distance between that centroid and each observation. Clusters are formed by assigning observations to a single cluster if the observation is closer to that centroid than any other. More details <a href="https://stanford.edu/~cpiech/cs221/handouts/kmeans.html">here</a>.</p><p>Some basic, very unsophisticated code is below:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f82c9f900361c557500a69af3b6ca2cd/href">https://medium.com/media/f82c9f900361c557500a69af3b6ca2cd/href</a></iframe><p>I used nine features on the first run:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c5b2295f5ce4a97943eb7820041c2ac6/href">https://medium.com/media/c5b2295f5ce4a97943eb7820041c2ac6/href</a></iframe><p>I was curious which cities the model would evaluate as closest to New York City. With n_clusters of 4, the output looked like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2fcf0ea7e00dc31e47ac9bc54d957d6f/href">https://medium.com/media/2fcf0ea7e00dc31e47ac9bc54d957d6f/href</a></iframe><p>This cluster largest consists of cities in United States &amp; China, which share large land areas and large populations. This intuition is borne out when we plot some of the features. While we can’t map nine dimensions, we can still experiment with some simple visual expressions of the clusters:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6CEBMydKmD0R0xbg23IcCA.png" /><figcaption>x- and y-axis have been log-transformed for this chart; Marker size represents scaled Population; 9 features + 4 clusters.</figcaption></figure><p>New York City is in cluster c2, which represents cities with large urban extent areas, high populations, but relatively low densities compared to other large cities.</p><p>Another look at the same chart, with one more dimension added on the z-axis (Compactness, which generally measures how well the city looks like a perfect circle):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Cm2cy3G5oELNePob3K0VWQ.png" /><figcaption>x-, y- and z-axis have been log-transformed; 9 features + 4 clusters. Size of the marker represents scaled Population.</figcaption></figure><p>There is a bit of a pattern in the geography of the clusters as well. European and African cities nearly all fall in clusters c1 and c2, as show in this map:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4h8LPJIonVF_eDom0QK5Hw.png" /><figcaption>Size of the marker represents scaled Population.</figcaption></figure><p>This elbow curve, which should indicate the optimal number of clusters for this model, isn’t incredibly clean:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/714/1*yEwksb_UszobzjQZ-iqseQ.png" /><figcaption>Elbow curve with 9 features.</figcaption></figure><p>Let’s see what the same model looks like with six clusters instead of four:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/36a156ed3a3bd50cfe1c22cc985aa70f/href">https://medium.com/media/36a156ed3a3bd50cfe1c22cc985aa70f/href</a></iframe><p>New York only has 12 friends now. While we can see some fairly well defined clusters in the plot, these are largely the same as the original model with some additional splits that are less well-defined. The additional clusters don’t appear to add a lot of value, but as noted, we’re not able to map all nine features that the algorithm used.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lx4NoLD1T436H2vKnwPrgg.png" /><figcaption>x- and y-axis have been log-transformed; 9 features + 6 clusters. Size of the marker represents scaled Population.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rbOUy6zOdiHspREqLl4aWA.png" /><figcaption>x-, y- and z-axis have been log-transformed; 9 features + 6 clusters. Size of the marker represents scaled Population.</figcaption></figure><h4>Clustering with Spatial Features Only</h4><p>Clustering can benefit from dimensionality reduction and I was curious how the algorithm would generate clusters without some of the more obvious features such as Population and Density.</p><p>The following model is run on solely spatial features, with no population elements. I’ve also removed several area features, resulting in only five total features:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e5275f899bc96257a0dc3eb7d76cc823/href">https://medium.com/media/e5275f899bc96257a0dc3eb7d76cc823/href</a></iframe><p>This model has a similar elbow curve. Six clusters does look like the best option:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/1*W_aYxVz8elE1sSoM4Wjt6A.png" /><figcaption>5 features</figcaption></figure><p>With six total clusters and only five features, New York is down to only four friends:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a4e1e9e38fed3113d89ef4a2ddd9e1f6/href">https://medium.com/media/a4e1e9e38fed3113d89ef4a2ddd9e1f6/href</a></iframe><p>It’s interesting to look at the original scatter plots of the clusters utilizing the original axes after running this model, since only Urban Extent Area was included as a feature in the model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*80hYrpwq7OmFJCKlVVlxhg.png" /><figcaption>x- and y-axis have been log-transformed for the chart; 5 features + 6 clusters. Size of the marker represents scaled Population.</figcaption></figure><p>The structure of the 3D plot actually looks pretty good:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7wsj39rjyAtjCHn7ocnvrQ.png" /></figure><p>Let’s see what these scatter plots look like using different axes — Compactness and Fragmentation, which were both included as features in this model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OQPgL0GSbH4JV3kfmsabEg.png" /><figcaption>x- and y-axis were log-transformed; Size of marker represents scaled urban extent area.</figcaption></figure><p>Fragmentation (Openness Index) is on the x-axis and Compactness is on the y-axis. The size of the marker represents scaled Urban Extent Area. The plot doesn’t look bad, though there is some overlap.</p><h4>Conclusion</h4><p>Based on some unsophisticated clustering models, Chicago, Los Angeles and Tokyo are most similar to New York, at least from the perspective of an urbanist that cares about land use patterns. The inclusion of Los Angeles may raise some eyebrows among New Yorkers given its sprawl-ridden reputation, but the underlying data shows that on a global scale, we’re more similar than we are different.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d9v6mMnBu-xz0NhcMNGKvw.png" /><figcaption>New York and friends. 5 features + 6 clusters.</figcaption></figure><h3>Next Steps</h3><p>I’d like to explore some other clustering algorithms to see how they differ and run a set of models that take into account temporal features (percent change in Population, Density, etc. between 1990 and 2014).</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b39098c756d7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploratory Data Analysis :: NYC Liquor Licenses ::]]></title>
            <link>https://medium.com/@maks_p/exploratory-data-analysis-nyc-liquor-licenses-6c261ec1c1b1?source=rss-fad42e2d78db------2</link>
            <guid isPermaLink="false">https://medium.com/p/6c261ec1c1b1</guid>
            <category><![CDATA[liquor-license]]></category>
            <category><![CDATA[new-york-city]]></category>
            <category><![CDATA[bars]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Maks Pazuniak]]></dc:creator>
            <pubDate>Wed, 10 Apr 2019 18:45:14 GMT</pubDate>
            <atom:updated>2019-05-22T15:41:25.053Z</atom:updated>
            <content:encoded><![CDATA[<h3>Exploratory Data Analysis // NYC Liquor Licenses</h3><p><em>A bar industry veteran, Maks is a bar owner + aspiring Data Scientist currently studying at The Flatiron School.</em></p><p>The New York State Liquor Authority maintains a <a href="https://data.ny.gov/Economic-Development/Liquor-Authority-Quarterly-List-of-Active-Licenses/hrvs-fxs2">quarterly list of all active licensees</a> that is an effective starting point for exploring the breadth and distribution of the industry in New York City.</p><p>As of the 1Q 2019 report, there were 51,200 active licensees in New York State, of which 30,650 (60%) were located in New York City. While there are 89 types of unique License Types in the dataset, spanning everything from Liquor Store to Farm Winery to various seasonal &amp; temporary licenses, the majority of licenses fall into one of five categories, which cumulatively make up 82% of the active licenses in NYC: (1) On Premise, (2) Grocery Beer &amp; Wine, (3) Restaurant Wine, (4) Liquor Store, and (5) Grocery Store Beer.</p><p>Our focus here will be on the 6,900 active New York City <a href="https://www1.nyc.gov/nycbusiness/description/onpremises-liquor-license">On-Premise</a> licenses, the standard License Type for establishments that the public is most familiar with, such as bars, taverns, restaurants, nightclubs, which allows the licensee to sell liquor, beer, wine, cider for consumption. This is fairly consistent with a <a href="https://www.nytimes.com/2018/08/15/nyregion/liquor-licenses-bars-nyc.html">New York Times report from August 2018</a> that found 7,616 bars, clubs, restaurants and hotels had a license to sell liquor.</p><h3>Active On-Premise Liquor Licenses by Borough</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9oGd7MKUqxgvXvTEdMOr1g.png" /></figure><p>The majority of active on-premise liquor licenses are established in Manhattan, despite ranking 3rd in total population. There are over two liquor licenses per 1,000 Manhattan residents, well above the other boroughs.</p><h3>Liquor License Location + Population</h3><p>The SLA data includes addresses for each active on-premise license, making it fairly simple to aggregate their location by zip code. Unfortunately, demographic data compiled by zip code is less precise, as zip codes are ultimately designed to effectively deliver mail, not measure population. There are several types of crosswalks available to map equivalent US Census Bureau elements to zip codes, and here I used Public Use Microdata Areas (PUMAs), which roughly approximate New York City’s Community Districts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dtkgiNnpczWa3iZ6gDHTsA.png" /><figcaption>Top 10 PUMAs by Number of Liquor Licenses</figcaption></figure><p>A tidy 62% of all active, on-premise liquor licenses in New York City are located in one of ten PUMAs, representing just 19% of the total population. The Chelsea, Clinton &amp; the Midtown Business District PUMA has nearly 1,000 total licenses, 6.6 per 1,000 residents.</p><p>Where are there the least on-premise liquor licenses per 1k people?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U8ZC-fl3qWKqPOR7dVwMkg.png" /></figure><p>These neighborhoods have nearly as much population as the top-10 PUMAs, but a fraction of the active liquor licenses.</p><p>On a more granular level, the Zip Code with the most liquor licenses is 10019 (Midtown West), with 273:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1016/1*LgzDOjGoLuPoB0uyoFOHPg.png" /><figcaption>Top 10 Zip Codes by Number of Liquor Licenses</figcaption></figure><h3>Liquor License Heat Map</h3><p>The following heat maps show where active liquor licenses are most dense, and pretty much tells the story one would expect:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5KtNuNzj-qH30XN53uS6SQ.png" /><figcaption>New York City Active Liquor Licenses</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pHs8R4uCGNSwsjOKR4CPPw.png" /><figcaption>Lower Manhattan and Brooklyn</figcaption></figure><h3>Licenses by Original Issue Date</h3><p>From our set of 6,900 active, on-premise New York City liquor licenses, the SLA has original issue date data for 6,400 licensees, which break down as follow:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/772/1*5Al1JD9XcsYiuj4WAqxUSw.png" /></figure><p>23.0% of all active on-premise NYC liquor licenses were issued between 2017 and March 2019, and only 5.1% predate 2000. Running bars and restaurants in NYC is a tough business, and one would expect a lot of turnover, which is reflected in these statistics.</p><p>The chart below shows which year current active on-premise liquor licenses were issued in New York City:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3VrjFRXEk5cUfEY4ACtc7g.png" /></figure><p>Using Python, Pandas and Folium, we explored some high level NYC liquor license data. In the future, I’d like to build a database to track NYC Liquor License activity, import past “Active License Reports”, and build a more robust picture of the bar and restaurant industry vis-a-vis liquor licenses in New York City.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6c261ec1c1b1" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>