pandas: compare sum over two time periods?
<p>I have a dataframe that looks like this:</p>

<pre><code>         prod_code      month  items      cost
0  040201060AAAIAI 2016-05-01      5    572.20   
1  040201060AAAKAK 2016-05-01    164  14805.19    
2  040201060AAALAL 2016-05-01  13465  14486.07  
</code></pre>

<p>I would like to first group by the first four characters of <code>prod_code</code>, then sum the total cost of each group from Jan-Feb 2016, then compare this with the total cost from Mar-Apr 2016, then find the groups with the biggest percentage increase over the two time periods.</p>

<p>What's the best way to go about this?</p>

<p>Here is my code so far:</p>

<pre><code>d = { 'prod_code': ['040201060AAAIAI', '040201060AAAIAJ', '040201060AAAIAI', '040201060AAAIAI', '040201060AAAIAI', '040201060AAAIAI', '040301060AAAKAG', '040301060AAAKAK', '040301060AAAKAK', '040301060AAAKAX', '040301060AAAKAK', '040301060AAAKAK'], 'month': ['2016-01-01', '2016-02-01', '2016-03-01', '2016-01-01', '2016-02-01', '2016-03-01', '2016-01-01', '2016-02-01', '2016-03-01', '2016-01-01', '2016-02-01', '2016-03-01'], 'cost': [43, 45, 46, 41, 48, 59, 8, 9, 10, 12, 15, 13] }
df = pd.DataFrame.from_dict(d)
df['para'] = df.prod_code.str[:4]
df_para = df.groupby(['para', 'month']).sum()
</code></pre>

<p>This gives me <code>df_para</code> which looks like this:</p>

<pre><code>                 cost
para month
0402 2016-01-01    84
     2016-02-01    93
     2016-03-01   105
0403 2016-01-01    20
     2016-02-01    24
     2016-03-01    23
</code></pre>

<p>Now I need to calculate the sum for each group for Jan-Feb and for Apr-Mar, then the difference between those two groups, and finally sort by the difference between those two groups. What is the best way to do this?</p>

<p>You can create a month group variable based on whether the months are <code>Jan-Feb</code> or <code>Mar-Apr</code> and then group by the code and month group variable, summarize the cost and calculate the difference:</p>

<pre><code>import numpy as np
import pandas as pd
df['month_period'] = np.where(pd.to_datetime(df.month).dt.month.isin([1,2]), 1, 2)
# creation of the month group variable could be adjusted based on how you want to cut 
# your time, this is a simplified example which assumes you only have data from Jan-Apr

(df.groupby([df.prod_code.str[:4], df.month_period]).sum().groupby(level = 0).pct_change()
   .dropna().sort('cost', ascending=False))
</code></pre>

<p><a href="https://i.stack.imgur.com/7Urvn.png" rel="nofollow"><img src="https://i.stack.imgur.com/7Urvn.png" alt="enter image description here"></a></p>

python pandas