This code below calculate moving average for every row within a group. However I only interested in the moving average of the last 2 rows for each group of id.
Since my data is quite large, this code takes too much time to run.
The desired output is column avg having NaN for all rows except for time = 4 and 5.
Thank you so much for your help. HC
import pandas as pd
df = {'id':[1,1,1,1,1,1,2,2,2,2],
'time':[1,2,3,4,5,5,1,2,3,4],
'value': [1, 2, 3, 4,2 ,16, 26, 50, 10, 30],
}
df = pd.DataFrame(data=df)
df.sort_values(by=['id','time'], ascending=[True, True] , inplace=True)
df['avg'] = df['value'].groupby(df['id']).apply(lambda g: g.rolling( 3 ).mean())
df