I have the below dataframe
Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec
store_id
S_1 8.0 20.0 13.0 21.0 17.0 20.0 24.0 17.0 16.0 9.0 7.0 6.0
S_10 14.0 23.0 20.0 11.0 12.0 13.0 19.0 6.0 5.0 22.0 17.0 16.0
and I want to calculate the mean of each store per quarter:
Q1 Q2 Q3 Q4
store_id
S_1 13.67 19.33 15.67 7.33
S_10 19.0 12.0 10.0 18.33
How can this be achieved?
Convert values to quarter by DatetimeIndex.quarter and aggregate, it working correct also if changed order of columns:
#if necessary
df = df.rename(columns={'July':'Jul'})
df = (df.groupby(pd.to_datetime(df.columns, format='%b').quarter, axis=1)
.mean()
.add_prefix('Q')
.round(2))
print(df)
Q1 Q2 Q3 Q4
store_id
S_1 13.67 19.33 19.0 7.33
S_10 19.00 12.00 10.0 18.33