Calculate the average value for each day of the week in time series using pandas

Question

Current DataFrame is as follows-

import datetime
#Adding Day,Weekday Field in dataframe
df1['day']=df1['timestamp'].dt.weekday
df1['DOW'] = df1['timestamp'].dt.day_name()
df1.info()
df1.head()

I want to add Avg Value for "perf_data.load5" with Day of Week in current dataframe as new column as Avg_DOW.

DataFrame

I tried adding new column but it is showing NAN values.

df1['avg_dow']=df1[['perf_data.load5','DOW']].groupby(('DOW'),sort=False).mean()
df1

enter image description here

But While using code in which I don't add new column to dataframe it is working fine.

Please Suggest how to add Avg_DOW column in the existing dataframe.

Thank You in Advance.

df1[['perf_data.load5','DOW']].groupby(('DOW'),sort=False).mean()

enter image description here

Answer 1

This code working fine and suffice the need.

df.join(df.groupby(['hour', 'weekday'])['value'].mean(), on=['hour','weekday'], rsuffix='_avg')

For more details regarding this,pls visit-enter link description here