Group by a certain column in dataframe and aggregate last 12 month data

user_10033320 0
Viewed 296

I am trying to aggregate the data (value) for the last 12 months and group by id in a dataframe. Here is the dataframe that I have . I want to add another column - last 12 month and aggregate the value column for the last 12 month grouped by id.

enter image description here

And I want to add a last column to have the data as represented below

enter image description here

id  month   value   Last_12_month
1   Jan-19  2    2 
1   Feb-19  32   34 
1   Mar-19  4    38 
1   Apr-19  6    44 
1   May-19  7    51 
1   Jun-19  83   134 
1   Jul-19  2    136 
1   Aug-19  3    139 
1   Sep-19  4    143 
1   Oct-19  6    149 
1   Nov-19  7    156 
1   Dec-19  8    164 
1   Jan-20  44   206 
1   Feb-20  664  838 
1   Mar-20  77   911 
1   Apr-20  4    909 
1   May-20  2    904 
1   Jun-20  567  1,388 
1   Jul-20  888  2,274 
1   Aug-20  43   2,314 
2   Apr-19  24   24 
2   May-19  5    29 
2   Jun-19  67   96 
2   Jul-19  88   184 
2   Aug-19  5    189 
2   Sep-19  2    191 
2   Oct-19  235  426 
2   Nov-19  6    432 
2   Dec-19  788  1,220 
2   Jan-20  96   1,316 
2   Feb-20  4    1,320 
2   Mar-20  3    1,323 
2   Apr-20  25   1,324 
2   May-20  56   1,375 
2   Jun-20  7    1,315 
2   Jul-20  88   1,315 
2   Aug-20  88   1,398 
2   Sep-20  89   1,485 
2   Oct-20  87   1,337 
2   Nov-20  6    1,337

pandas-groupby pandas dataframe python
1 Answers

This should work (simplifying your example):

import numpy as np
import pandas as pd

idx = [i for idx in [[1] * 13, [2] * 13] for i in idx ]
df = pd.DataFrame({'month': ['month'] * 26,
                   'value': np.arange(1, 27),
                   'id': idx}).set_index('id')

df['last_12_months'] = (df.groupby('id')['value']
                          .apply(lambda x: x.rolling(12, min_periods=1).sum()))

which gives:

    month  value  last_12_months
id                              
1   month      1             1.0
1   month      2             3.0
1   month      3             6.0
1   month      4            10.0
1   month      5            15.0
1   month      6            21.0
1   month      7            28.0
1   month      8            36.0
1   month      9            45.0
1   month     10            55.0
1   month     11            66.0
1   month     12            78.0
1   month     13            90.0
2   month     14            14.0
2   month     15            29.0
2   month     16            45.0
2   month     17            62.0
2   month     18            80.0
2   month     19            99.0
2   month     20           119.0
2   month     21           140.0
2   month     22           162.0
2   month     23           185.0
2   month     24           209.0
2   month     25           234.0
2   month     26           246.0

you need min_periods to not obtain NaN in the first 12 values

user_139591660
Related