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
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