[FIXED] Average day of a month on minute precision

Issue

I have data with datetime index using minute resolution. I want to see what is the average 'profile' of one day in a month using minute resolution.

The dataset format is like this:

                               Power
2019-01-01 11:43:01+02:00   9.223261
2019-01-01 11:44:01+02:00  14.304057
2019-01-01 11:45:01+02:00  28.678970
2019-01-01 11:46:01+02:00  35.143512
2019-01-01 11:47:01+02:00  24.431278
...                              ...
2019-12-31 15:05:14+02:00  -0.075000
2019-12-31 15:06:14+02:00  -0.075000
2019-12-31 15:07:14+02:00  -0.075000
2019-12-31 15:08:14+02:00  -0.075000
2019-12-31 15:09:14+02:00  -0.075000

To plot the average day of a month power profile on hourly basis I did the following

plt.plot(df_jul.groupby(df_jul.index.hour)[['Power']].mean(), label=('July'))

where df_jul is a subset of the data above, including only data from July.

                              Power
2019-07-01 05:28:15+03:00  2.561204
2019-07-01 05:29:15+03:00  2.749837
2019-07-01 05:30:15+03:00  2.963823
2019-07-01 05:31:15+03:00  3.190177
2019-07-01 05:32:15+03:00  3.374277
...                             ...
2019-07-31 21:12:02+03:00  2.311575
2019-07-31 21:13:02+03:00  2.310808
2019-07-31 21:14:02+03:00  2.415743
2019-07-31 21:15:02+03:00  2.485820
2019-07-31 21:16:02+03:00  1.874091

The resulting figure is like this:

So, what is the best method to get the same profile plot as in the figure above, but using minute resolution?

I tried to group it by minutes but that results in an average hour of the month. I also think I could just iterate through the dataframe and do the average calculations, but I feel like there is an easier way that I am missing.

Here is the correct result plot

Solution

You should use resample with the desired frequency.

If you want to plot an average day of the month, you can transform all days to the end of month (with pd.offsets.MonthEnd), then resample:

(df_jul.set_axis(df_jul.index + pd.offsets.MonthEnd(0))
       .resample('2min')['Power'].mean()
       .plot(marker='o')
)

Or with a variant of your original groupby:

df_jul.groupby(df_jul.index.floor('1min').time)['Power'].mean().plot()

If you want to plot the full month:

df_jul.resample('1min')['Power'].mean().plot(marker='o')

Output:

Answered By - mozway