How should I find mean quarterly sales by store using pandas

March 1, 2022

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
How can this be achieved?

Expected Output:

        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

Thanks in advanced.

>Solution :

Convert values to quarter by DatetimeIndex.quarterand 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