get average monthly value by divide from its monthly row count-CodePudding

i have following datframe

created_time    shares_count
2021-07-01          250.0
2021-07-31          501.0
2021-08-02          48.0
2021-08-05          300.0
2021-08-07          200.0
2021-09-06          28.0
2021-09-08          100.0
2021-09-25          100.0
2021-09-30          200.0

did the grouping as monthly like this

df_groupby_monthly = df.groupby(pd.Grouper(key='created_time',freq='M')).sum()
df_groupby_monthly

Now how to get the average of these 'shares_count's by dividing from a sum of monthly rows?

ex: if the 07th month has 2 rows average should be 751.0/2 = 375.5, and the 08th month has 3 rows average should be 548.0/3 = 182.666, and the 09th month has 4 rows average should be 428.0/4 = 142.66

how to get like this final output

created_time   shares_count
2021-07-31       375.5
2021-08-31       182.666
2021-09-30       142.66

I have tried following

df.groupby(pd.Grouper(key='created_time',freq='M')).apply(lambda x: x['shares_count'].sum()/len(x))

this is working if only one column, multiple ones hard to get

CodePudding user response：

df['created_time'] = pd.to_datetime(df['created_time'])
output = df.groupby(df['created_time'].dt.to_period('M')).mean().round(2).reset_index()
output
###
  created_time  shares_count
0      2021-07        375.50
1      2021-08        182.67
2      2021-09        107.00

CodePudding user response：

Use this code:

df=df.groupby(pd.Grouper(key='created_time',freq='M')).agg({'shares_count':['sum', 'count']}).reset_index()
df['ss']=df[('shares_count','sum')]/df[('shares_count','count')]