Calculate MRR in Python Pandas dataframe
<p>I have a Pandas dataframe with the following columns</p>

<p><code>date | months | price</code></p>

<p>I calculate some basic BI metrics. I did the Net Revenue by grouping the dataframe on date and sum the price:</p>

<p><code>df = df[["Date", "Price"]].groupby(df['Date'])["Price"].sum().reset_index()</code></p>

<p>Now, I want to find the MRR, which is similar to the Net Revenue, but in case the column months have more than 1 month, the price should be "moved" equally to the next months. And also, it is grouped by month and not day.</p>

<p>For example, if I am on January 2016 and I have a row with 3 months and price 30$, I should add 10$ to January, 10$ to February and 10$ to March.</p>

<p>My first idea was to iterate through the dataframe, keep track of the months and the amount of price I should "move" on next months and create a new dataframe manually.</p>

<p>But, first, is there any Pythonic way in Pandas to do it?</p>

<p><strong>Data to reproduce a dataframe:</strong></p>

<pre><code>import pandas as pd
df = pd.DataFrame({'date': ['01-01-2016', '05-01-2016', '10-01-2016','04-02-2016'], 
                   'months': [1, 3, 1, 6],
                   'price': [40, 60, 20, 60]})
</code></pre>

<p>Desired result:</p>

<pre><code>Date         | MRR
January 2016 | 80
February 2016| 30
March 2016   | 10
April 2016   | 10
May 2016     | 10
June 2016    | 10
July 2016    | 10
</code></pre>

<p>And the results calculated like this for each row</p>

<pre><code>January 2016 = 40 + 20 + 20 + 0
February 2016 = 0 + 20 + 0 + 10
March 2016 = 0 + 0 + 0 + 10
April 2016 = 0 + 0 + 0 + 10
May 2016 = 0 + 0 + 0 + 10
June 2016 = 0 + 0 + 0 + 10
July 2016 = 0 + 0 + 0 + 10
</code></pre>

<p>I don't know any way around using a loop. However, I can suggest a way to make the code pretty clean and efficient.</p>

<p>First, let's load the example data you supplied in the question text:</p>

<pre><code>df = pd.DataFrame({'date': ['01-01-2016', '05-01-2016', '10-01-2016','04-02-2016'], 
                   'months': [1, 3, 1, 6],
                   'price': [40, 60, 20, 60]})
</code></pre>

<p>In order to use Panda's date functionality (e.g. grouping by month), we will use the <code>date</code> column as index. A <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DatetimeIndex.html" rel="nofollow"><code>DateTimeIndex</code></a> in fact:</p>

<pre><code>df['date'] = pd.to_datetime(df['date'], format='%d-%m-%Y')
df = df.set_index('date')
</code></pre>

<p>Now, it's really easy to, for example, view a month-by-month summary, by using the <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.resample.html" rel="nofollow">resample</a> function that works like the <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow">groupby</a> function you already know, but uses time-periods:</p>

<pre><code>df.resample('M').sum()
</code></pre>

<p>Now to "spread out" rows where the <code>months</code> column is > 1 over multiple months. My approach here is to generate a new <code>DataFrame</code> for each row:</p>

<pre><code>dfs = []
for date, values in df.iterrows():
    months, price = values
    dfs.append(
        pd.DataFrame(
            # Compute the price for each month, and repeat this value
            data={'price': [price / months] * months},
            # The index is a date range for the requested number of months
            index=pd.date_range(date, periods=months, freq='M')
        )
    )
</code></pre>

<p>Now we can just concatenate the list of <code>DataFrame</code>s, resample to months and take the sum:</p>

<pre><code>pd.concat(dfs).resample('M').sum()
</code></pre>

<p>Output:</p>

<pre><code>            price
2016-01-31     80
2016-02-29     30
2016-03-31     30
2016-04-30     10
2016-05-31     10
2016-06-30     10
2016-07-31     10
</code></pre>

<p>See <a href="http://pandas.pydata.org/pandas-docs/stable/timeseries.html" rel="nofollow">http://pandas.pydata.org/pandas-docs/stable/timeseries.html</a> for all the cool things Panda's can do regarding time. For example, to exactly produce your desired output you could do this:</p>

<pre><code>output.index = output.index.strftime('%B %Y')
</code></pre>

<p>Which results in this:</p>

<pre><code>               price
January 2016      80
February 2016     30
March 2016        30
April 2016        10
May 2016          10
June 2016         10
July 2016         10
</code></pre>

python pandas dataframe business-intelligence