I have a dataframe df with one column only. The data is monthly. The data type is float. The index is dates with the 'start of month' format. Sample data is here
np.random.seed(167)
rng = pd.date_range('2000-01-01', periods=200, freq='MS')
df = pd.DataFrame(
{"x": np.cumsum([np.random.uniform(-0.01, 0.01) for _ in range(200)])
}, index=rng
)
I can obtain a dataframe with the monthly averages (i.e. twelve values, one for each month of the year) with this:
df.groupby(df.index.to_series().dt.month).mean().
But I am trying to obtain the monthly averages across the last few years, for example the last three years. I am trying this:
df.groupby(df.index.to_series().dt.month).apply(lambda x: x.iloc[-3:]).mean()
but it returns one single value rather than the desired dataframe with twelve values, one for each month of the year.