How to take the mean of each hour for several days independently-CodePudding

I have data taken every 15 minutes each day for a month. I want to find the mean of every hour within each day independently of the other days (so I can't group them by hours and take the mean because it will combine all the days together). Here is a sample of the data for one day So I want to take the mean for the first hour on the first day, then the mean for the next hour, and so on.

CodePudding user response：

It sounds like you want to resample to hourly. Something like df.resample('H').mean() should work.

CodePudding user response：

I think your problem can be solved by using pandas.Grouper. It provides functionality to group and aggregate data according to a selected interval.

Note the example, the data is similar to what you described (for simplicity only from two consecutive days and from two hours however the solution is general).

                  date  value
0  2022-02-11 13:12:00      2
1  2022-02-11 13:27:00      4
2  2022-02-11 13:43:00      7
3  2022-02-11 13:58:00      6
4  2022-02-11 14:13:00     11
5  2022-02-11 14:28:00      3
6  2022-02-11 14:44:00      5
7  2022-02-11 14:59:00      4
8  2022-02-12 13:12:00      1
9  2022-02-12 13:27:00      6
10 2022-02-12 13:43:00      7
11 2022-02-12 13:58:00      3
12 2022-02-12 14:13:00      1
13 2022-02-12 14:28:00     13
14 2022-02-12 14:44:00     11
15 2022-02-12 14:59:00      2

df.groupby(pd.Grouper(freq='60min', key='date')).mean().dropna()

                     value
date                      
2022-02-11 13:00:00   4.75
2022-02-11 14:00:00   5.75
2022-02-12 13:00:00   4.25
2022-02-12 14:00:00   6.75

The mean value for 2022-02-11 13:00:00 is taken from values: 2, 4, 6, 7 which all belongs to the interval between 13:00 - 13:59 that day. In result you get mean value per day per hour independently.