CODEWITHSUNDEEP
pythonpandas
kevin
kevin

Reputation: 2014

Pandas add monthly average to a column

Here is my dataframe.

   index     y
6/17/2018   100
6/24/2018   200
7/1/2018    100
7/8/2018    200
7/15/2018   300
   …         …

The desired dataframe would be:

index        y  MonthlyAverage
6/17/2018   100 150
6/24/2018   200 150
7/1/2018    100 200
7/8/2018    200 200
7/15/2018   300 200
…            …  …

Any suggestions?

Upvotes: 1

Views: 495

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150825

I would use to_period('M') to transform the dates into months and group by those

df['index'] = pd.to_datetime(df['index'])
df['MonthlyAverage'] = (df.groupby(df['index'].dt.to_period('M'))
                          ['y'].transform('mean')
                       )

Output:

       index    y  MonthlyAverage
0 2018-06-17  100             150
1 2018-06-24  200             150
2 2018-07-01  100             200
3 2018-07-08  200             200
4 2018-07-15  300             200

Upvotes: 3

BENY
BENY

Reputation: 323396

First we need convert the index to datetime by to_datetime, then we do transform 

df['index']=pd.to_datetime(df.index,format='%m/%d/%Y')
df['mean']=df.groupby(df['index'].dt.strftime('%Y%d')).y.transform('mean')
df

Upvotes: 0

Related Questions