CODEWITHSUNDEEP
pythonpandas
Vesper
Vesper

Reputation: 845

how to convert 15 mins data to hourly in pandas?

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?

Upvotes: 0

Views: 2738

Answers (2)

Erfan
Erfan

Reputation: 42886

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

Upvotes: 2

antoine
antoine

Reputation: 672

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

Upvotes: 1

Related Questions