<?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 Gleb Mikhaylov on Medium]]></title>
        <description><![CDATA[Stories by Gleb Mikhaylov on Medium]]></description>
        <link>https://medium.com/@glebmikhaylov?source=rss-587d84031a1a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*SyYZ23V96gBuU9Z9TUnpeg.png</url>
            <title>Stories by Gleb Mikhaylov on Medium</title>
            <link>https://medium.com/@glebmikhaylov?source=rss-587d84031a1a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 12:51:00 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@glebmikhaylov/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[Churn Report in Pandas]]></title>
            <link>https://medium.com/analytics-vidhya/churn-report-in-pandas-94a461e50014?source=rss-587d84031a1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/94a461e50014</guid>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Gleb Mikhaylov]]></dc:creator>
            <pubDate>Fri, 27 Nov 2020 10:39:42 GMT</pubDate>
            <atom:updated>2020-11-27T16:39:54.126Z</atom:updated>
            <content:encoded><![CDATA[<p>This is a continuation of my <a href="https://medium.com/analytics-vidhya/sales-report-in-pandas-c4ef777cc490">Sales Report in Pandas</a> post and it begins with the final table from it. Here it is for one random customer.</p><pre>report[report[&#39;CustomerID&#39;] == 12347.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2FvFmvo8Mm8U95Gzx0LHeQ.png" /></figure><p>It represents an activity of this particular customer for the whole available date range aggregated by month. You can see that this activity is not regular.</p><p>First, I want to add a flag whether or not customer was active in the previous month.</p><pre>report[&#39;active_prev&#39;] = (report.sort_values(by=[&#39;month&#39;], ascending=True)<br>.groupby([&#39;CustomerID&#39;])[&#39;active&#39;].shift(1))</pre><p>This hard stuff is something like a window function in sql. Because I have many customers I have to somehow say to pandas that they should be treated separately, so that’s what groupby for. And also I want months to be in order, that’s what sort_values for. Finally, shift gives the value from the previous row (month in this case). Check it.</p><pre>report[report[&#39;CustomerID&#39;] == 12347.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mQwVZNUfxycykFyONKvrKg.png" /></figure><p>Correct. Compare the active column with active_prev. Now I want to understand when customer go from active to inactive and vice versa.</p><pre>report[&#39;change_status&#39;] = (report[&#39;active&#39;] != report[&#39;active_prev&#39;]) * 1</pre><pre>report[report[&#39;CustomerID&#39;] == 12347.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yodCsmux7c89UD9HXLoXcA.png" /></figure><p>Looks good. Next I want to assign some kind of an id to each status change. And here is a very neat trick with cumulative sum. Look at the change_status column. If I perform a cumulative sum on this column I will get an increment each time the user changes status. That’s what I want. And again I use this scary construction because I want pandas to treat each user separately.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0yoHu2nt_RnTai-npkuUmw.png" /></figure><p>It’s perfect. Find a session with id of 2. Can you see that it is the session where the customer was inactive for two months? Now I can sum up the number of inactive months per user and per each session.</p><pre>report[&#39;inactive&#39;] = (report[&#39;active&#39;] == 0) * 1</pre><pre>report[&#39;month_inactive&#39;] = (report.sort_values(by=[&#39;month&#39;], ascending=True)<br>.groupby([&#39;CustomerID&#39;,&#39;session_id&#39;])[&#39;inactive&#39;].cumsum())</pre><pre>report[report[&#39;CustomerID&#39;] == 12347.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wUd5T8duhBQi4lZALPXQ6g.png" /></figure><p>Look at row number 4. In the month_inactive column we get the correct number of months the customer was inactive. Check it with your eyes. And it’s correct for the whole table).</p><p>Now let’s calculate the metric. Say’s number of users who has been in churn for two or more months.</p><pre>report[&#39;churn_2m&#39;] = (report[&#39;month_inactive&#39;] &gt;= 2) * 1</pre><pre>report[report[&#39;CustomerID&#39;] == 12347.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9wLXzLlGwOM4si62y74I3A.png" /></figure><p>Looks good. Now groupby to get info for all customers per month.</p><pre>t = report.groupby(&#39;month&#39;)[[&#39;revenue&#39;,&#39;user&#39;,&#39;new&#39;,&#39;active&#39;,&#39;churn_2m&#39;]].agg(&#39;sum&#39;)</pre><pre>t</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*_XrUMg5D8GVjo8UjUhRiKw.png" /></figure><p>You also may want to get it in %. Choice of denominator depends on your methodology of churn. Here I will just use current user count for each month. But it might be user count for the previous month or even for month-2, because we are measuring two month churn.</p><pre>t[&#39;churn_2m%&#39;] = t[&#39;churn_2m&#39;] / t[&#39;user&#39;]</pre><pre>t</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U5ZUWt3yT3gY75D5gfMVCQ.png" /></figure><p>How cool is that?)</p><p>Here is the Colab notebook with the whole project (sales + churn) <a href="https://colab.research.google.com/drive/1TwivVdUbavTO2aIWBBDlFEUPvF74PwU-#scrollTo=2sC3zrxjRj8c">https://colab.research.google.com/drive/1TwivVdUbavTO2aIWBBDlFEUPvF74PwU-#scrollTo=2sC3zrxjRj8c</a></p><p>Thank you for reading. Find me also at <a href="https://www.glebmikhaylov.com/">https://www.glebmikhaylov.com/</a> and at my YouTube channel <a href="https://www.youtube.com/channel/UCLdAnxmoGVySnh691CwDz9Q">https://www.youtube.com/channel/UCLdAnxmoGVySnh691CwDz9Q</a> .</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=94a461e50014" width="1" height="1" alt=""><hr><p><a href="https://medium.com/analytics-vidhya/churn-report-in-pandas-94a461e50014">Churn Report in Pandas</a> was originally published in <a href="https://medium.com/analytics-vidhya">Analytics Vidhya</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Sales Report in Pandas]]></title>
            <link>https://medium.com/analytics-vidhya/sales-report-in-pandas-c4ef777cc490?source=rss-587d84031a1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/c4ef777cc490</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[sales-reports]]></category>
            <category><![CDATA[pandas]]></category>
            <dc:creator><![CDATA[Gleb Mikhaylov]]></dc:creator>
            <pubDate>Tue, 17 Nov 2020 13:36:46 GMT</pubDate>
            <atom:updated>2020-11-19T17:17:51.170Z</atom:updated>
            <content:encoded><![CDATA[<p>I will use <a href="https://www.kaggle.com/carrie1/ecommerce-data">https://www.kaggle.com/carrie1/ecommerce-data</a>. I work in Colab and store data on my Google Drive.</p><pre>import pandas as pd</pre><pre>df = pd.read_csv(&#39;drive/My Drive/data/ecommerce-data.zip&#39;,encoding = &quot;cp1252&quot;)</pre><pre>df.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_dFWnQHWQmUXTDGwTF6t1Q.png" /></figure><p>Look at missing values.</p><pre>df.isna().mean()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/428/1*61Q_QPRTrRUhQWaFCubQJw.png" /></figure><p>Fill missing CustomerID with some number in order have these customers in groupbys: by default groupby ignores NaN. I fill it with negative number to preserve numeric type.</p><pre>df[&#39;CustomerID&#39;].fillna(-999,inplace=True)</pre><p>Convert text date to python date.</p><pre>df[&#39;InvoiceDate&#39;] = pd.to_datetime(df[&#39;InvoiceDate&#39;],format=&#39;%m/%d/%Y %H:%M&#39;)</pre><p>Let’s see what time period do we have.</p><pre>df[&#39;InvoiceDate&#39;].min()</pre><p>Timestamp(‘2010–12–01 08:26:00’)</p><pre>df[&#39;InvoiceDate&#39;].max()</pre><p>Timestamp(‘2011–12–09 12:50:00’)</p><p>It’s about a year. Ok. Now let’s create a monthly sales report.</p><h3>1. Create a table with unique users</h3><p>In real life there is always such a table in the database. But in this case we do not have it. I also add a registration date in this table, which is just minimum invoice date for each customer.</p><pre>user = df.groupby([&#39;CustomerID&#39;])[&#39;InvoiceDate&#39;].min().reset_index()<br>user.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/566/1*qEFrpSFo2Wdc9Lgg5q97ww.png" /></figure><p>Rename columns.</p><pre>user.columns = [&#39;CustomerID&#39;,&#39;reg_date&#39;]</pre><p>Add registration month.</p><pre>user[&#39;reg_month&#39;] = user[&#39;reg_date&#39;].values.astype(&#39;datetime64[M]&#39;)</pre><p>No worries). It’s just the first link from google on “pandas how to get first day of month” <a href="https://stackoverflow.com/questions/45304531/extracting-the-first-day-of-month-of-a-datetime-type-column-in-pandas">https://stackoverflow.com/questions/45304531/extracting-the-first-day-of-month-of-a-datetime-type-column-in-pandas</a></p><pre>user</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/788/1*KRM8mD9Ae3vjpnyKGYOmGA.png" /></figure><h3>2. Generate a template table with each possible month for each user.</h3><pre>min_month = df[&#39;InvoiceDate&#39;].values.astype(&#39;datetime64[M]&#39;).min()<br>max_month = df[&#39;InvoiceDate&#39;].values.astype(&#39;datetime64[M]&#39;).max()<br>dr = pd.DataFrame(pd.date_range(min_month,max_month,freq=&#39;MS&#39;))<br>dr.columns = [&#39;month&#39;]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/260/1*GDkIIdmvp1RLB21pfhfNKQ.png" /></figure><p>Now we have to perform cross join with user table and get</p><pre>len(user)*len(dr)</pre><p>rows. In pandas it could be performed like this.</p><pre>dr[&#39;key&#39;] = 1<br>user[&#39;key&#39;] = 1<br>report = dr.merge(user,on=&#39;key&#39;)<br>report.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/998/1*-jsXNitG1yZdFE7JmWYvoA.png" /></figure><pre>len(report)</pre><p>56849</p><p>Like expected. But we have some extra rows: some users came long after minimal date (2010–12) and it’s stupid to have records for a user before he came to us).</p><pre>report = report[report[&#39;month&#39;]&gt;=report[&#39;reg_month&#39;]]</pre><p>Let’s look at some specific users just to make a sanity check.</p><pre>report[report[&#39;CustomerID&#39;] == 12346.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_3nFL1No3-h4bu0XL-NMQA.png" /></figure><pre>report[report[&#39;CustomerID&#39;] == 12448.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SEfGA5Hi_JM4OaV7upAYdQ.png" /></figure><p>Now we can join sales information to this template. But before doing it we should make some preparations.</p><h3>3. Pre-aggregate data before joining with template.</h3><p>Calculate total sales for each month for per customer.</p><pre>df[&#39;month&#39;] = df[&#39;InvoiceDate&#39;].values.astype(&#39;datetime64[M]&#39;)<br>df[&#39;revenue&#39;] = df[&#39;UnitPrice&#39;] * df[&#39;Quantity&#39;]<br>sales_month = df.groupby([&#39;CustomerID&#39;,&#39;month&#39;])[[&#39;revenue&#39;]].agg(&#39;sum&#39;).reset_index()<br>sales_month.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/608/1*to1G5c_o9TsrIe8nmm7biw.png" /></figure><p>When you create new tables you should always make sure that your totals agree.</p><pre>df[&#39;revenue&#39;].sum()</pre><p>9747747.933999998</p><pre>sales_month[&#39;revenue&#39;].sum()</pre><p>9747747.934000025</p><p>Fine. If you consern about small decimals please read this <a href="https://stackoverflow.com/a/11873114/4527289">https://stackoverflow.com/a/11873114/4527289</a></p><h3>4. Join to template</h3><p>It is very important to use the left join! Using the left join we are keeping all rows from the report table — that’s how we understand gaps in customer activity.</p><pre>report = report.merge(sales_month,how=&#39;left&#39;,on=[&#39;CustomerID&#39;,&#39;month&#39;])<br>report.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GyoAKlXx3smOih1IGow1xA.png" /></figure><p>Check totals.</p><pre>df[&#39;revenue&#39;].sum()</pre><p>9747747.933999998</p><pre>report[&#39;revenue&#39;].sum()</pre><p>9747747.934000023</p><p>Cool. Now another sanity check on one customer.</p><pre>report[report[&#39;CustomerID&#39;] == 12347.0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OxvARs1nSsU9qViTrZEUvg.png" /></figure><p>We can see that activity of this customer is not regular! For example, there is pause in purchases after 2011–01–01 for two months. And then purchases start again.</p><h3>5. Basic metrics</h3><p>Add total, active and new users counts.</p><pre>report[&#39;user&#39;] = 1<br>report[&#39;new&#39;] = (report[&#39;reg_month&#39;] == report[&#39;month&#39;]) * 1<br>report[&#39;active&#39;] = (report[&#39;revenue&#39;] &gt; 0) * 1<br>report.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ykPfO4O0T6fWtAoj7VYDgw.png" /></figure><p>Now you can create monthly report from this table.</p><pre>report.groupby(&#39;month&#39;)[[&#39;revenue&#39;,&#39;user&#39;,&#39;new&#39;,&#39;active&#39;]].agg(&#39;sum&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/1*_Kow8AHMbxXM9ANtc3QDBg.png" /></figure><p>How cool is that?</p><p>This is a very robust way to create all kinds of reports: daily, weekly, monthly. If you generate all possible dates you will never miss a thing. But keep in mind that this method is not scalable because of the cross join: if you have a lot of customers you will end with enormous template table. But from my experience it work very well with middle sized enterprise with say 200k customers: monty report work well. But don’t try daily please)).</p><p>And of course you should better do this in sql. Sql much more powerful with doing such operations.</p><p>I think that there are tons of more smarter way of creating sales reports without such huge sparse tables, but I like this method for it simplicity. It saves you a lot of pain because it’s very simple. But it has its price.</p><p>Here is the colab notebook: <a href="https://colab.research.google.com/drive/1WP3Mtj_8MvroC4ucVlh0a_ChuBzeNe8f?usp=sharing">https://colab.research.google.com/drive/1WP3Mtj_8MvroC4ucVlh0a_ChuBzeNe8f?usp=sharing</a></p><p>Thank you for reading. Find me also at <a href="https://www.glebmikhaylov.com/">https://www.glebmikhaylov.com/</a> and my YouTube channel <a href="https://www.youtube.com/channel/UCLdAnxmoGVySnh691CwDz9Q">https://www.youtube.com/channel/UCLdAnxmoGVySnh691CwDz9Q</a> .</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c4ef777cc490" width="1" height="1" alt=""><hr><p><a href="https://medium.com/analytics-vidhya/sales-report-in-pandas-c4ef777cc490">Sales Report in Pandas</a> was originally published in <a href="https://medium.com/analytics-vidhya">Analytics Vidhya</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>