How should I find mean quarterly sales by store using pandas-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Assuming you have the columns in order, use groupby on axis=1:

import numpy as np
out = df.groupby([np.arange(df.shape[1])//3 1], axis=1).mean().add_prefix('Q')

output:

                 Q1         Q2    Q3         Q4
store_id                                       
S_1       13.666667  19.333333  19.0   7.333333
S_10      19.000000  12.000000  10.0  18.333333