python - how to convert 15 mins data to hourly in pandas?

Question

I have two generator's generation data which is 15 min time block, I want to convert it hourly. Here is an example:

 Time      Gen1  Gen2
00:15:00    10    21
00:30:00    12    22
00:45:00    16    26
01:00:00    20    11
01:15:00    60    51
01:30:00    30    31
01:45:00    70    21    
02:00:00    40    61

I want to take the average of the first 4 values( basically the 15 min block to the hourly block) and put them in place of a 1-hour block. Expected output:

Time      Gen1   Gen2
01:00:00   14.5    20
02:00:00    50     41

I know I can use the pandas' grourpby function to get the expected output but I don't know its proper syntex. So can anyone please help?

question from:https://stackoverflow.com/questions/65644126/how-to-convert-15-mins-data-to-hourly-in-pandas

Answer 1

Use resample with closed='right'. But first we convert your Time column to datetime type:

df['Time'] = pd.to_datetime(df['Time'])
df.resample('H', on='Time', closed='right').mean().reset_index()

                 Time  Gen1  Gen2
0 2021-01-09 00:00:00  14.5  20.0
1 2021-01-09 01:00:00  50.0  41.0

---

To convert the Time column back to time format, use:

df['Time'] = df['Time'].dt.time

       Time  Gen1  Gen2
0  00:00:00  14.5  20.0
1  01:00:00  50.0  41.0