Dataframe from Series grouped by weekday and hour of day-CodePudding

I have a Series with a DatetimeIndex, as such :

time                   my_values
2017-12-20 09:00:00    0.005611
2017-12-20 10:00:00   -0.004704
2017-12-20 11:00:00    0.002980
2017-12-20 12:00:00    0.001497
                         ...   
2021-08-20 13:00:00   -0.001084
2021-08-20 14:00:00   -0.001608
2021-08-20 15:00:00   -0.002182
2021-08-20 16:00:00   -0.012891
2021-08-20 17:00:00    0.002711

I would like to create a dataframe of average values with the weekdays as columns names and hour of the day as index, resulting in this :

hour  Monday      Tuesday    ...   Sunday
0     0.005611   -0.001083        -0.003467
1    -0.004704    0.003362        -0.002357
2     0.002980    0.019443         0.009814
3     0.001497   -0.002967        -0.003466
        ...    
19    -0.001084   0.009822         0.003362
20    -0.001608  -0.002967        -0.003567
21    -0.002182   0.035600        -0.003865
22    -0.012891   0.002945        -0.002345
23     0.002711  -0.002458         0.006467

How can do this in Python ?

CodePudding user response：

Since you asked for a solution that returns the average values, I propose this groupby solution

df["weekday"] = df.time.dt.strftime('%A')
df["hour"] = df.time.dt.strftime('%H')

df = df.drop(["time"], axis=1)

# calculate averages by weekday and hour
df2 = df.groupby(["hour", "weekday"]).mean()

# put it in the right format
df2.unstack()

CodePudding user response：

Do as follows

# Coerce time to datetime
df['time'] = pd.to_datetime(df['time'])

# Extract day and hour
df = df.assign(day=df['time'].dt.strftime('%A'), hour=df['time'].dt.hour)

# Pivot
pd.pivot_table(df, values='my_values', index=['hour'],
                columns=['day'], aggfunc=np.mean)