pythonpandasdataframesumcumsum

Rolling sum of groups by period


I have got this dataframe:

lst=[['01012021','A',10],['01012021','B',20],['02012021','A',12],['02012021','B',23]]
df2=pd.DataFrame(lst,columns=['Date','FN','AuM'])

I would like to get the rolling sum by date and FN. The desired result looks like this:

lst=[['01012021','A',10,''],['01012021','B',20,''],['02012021','A',12,22],['02012021','B',23,33]]
df2=pd.DataFrame(lst,columns=['Date','FN','AuM','Roll2PeriodSum'])

Would you please help me?

Thank you


Solution

  • Use groupby.rolling.sum:

    df2['Roll2PeriodSum'] = (
        df2.assign(Date=pd.to_datetime(df2['Date'], format='%d%m%Y'))
           .groupby('FN').rolling(2)['AuM'].sum().droplevel(0)
    )
    print(df2)
    
    # Output
           Date FN  AuM  Roll2PeriodSum
    0  01012021  A   10             NaN
    1  01012021  B   20             NaN
    2  02012021  A   12            22.0
    3  02012021  B   23            43.0