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?

Erfan · Accepted Answer · 2021-01-09T16:20:44.890

2

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

edited Jan 09 '21 at 16:20

answered Jan 09 '21 at 15:37

Erfan

40,971
8
66
78

thank you for your response, but what if I don't have the Time Column? Can I still convert them into the desired output? – Vesper Jan 09 '21 at 16:18
No, I mean what if I don't have any Time column? Just Gen1 and Gen2 column. – Vesper Jan 09 '21 at 16:24
That makes the question completely different.. but see my answer [here](https://stackoverflow.com/a/65370558/9081267). – Erfan Jan 09 '21 at 16:25
Can you please do that for this question? – Vesper Jan 09 '21 at 16:27

antoine · Answer 2 · 2021-01-09T15:34:21.820

1

you can try create a column hourand then groupby('hour').mean().

df['date_time'] = pd.to_datetime(df['Time'], format="%H:%M:%S") 
df['hour'] = df['date_time'].apply(lambda x: x.strftime("%H:00:00"))
gr_df = df.groupby('hour').mean()
gr_df.index.name = 'Time'
print(gr_df.reset_index())

       Time       Gen1  Gen2
0  00:00:00  12.666667  23.0
1  01:00:00  45.000000  28.5
2  02:00:00  40.000000  61.0

edited Jan 09 '21 at 15:34

answered Jan 09 '21 at 15:10

antoine

662
4
10

how to convert 15 mins data to hourly in pandas?

2 Answers2