CODEWITHSUNDEEP
pythonpandasdatetime-formatdata-cleaning
Sakib Shahriar
Sakib Shahriar

Reputation: 121

Aggregate 10 minute interval data to hourly

I have a df with Temperature and Humidity readings in 10-minute interval like:

Time                   
1/2/2017 13:00
1/2/2017 13:10
1/2/2017 13:20
1/2/2017 13:30
1/2/2017 13:40
1/2/2017 13:50
1/2/2017 14:00
1/2/2017 14:10
1/2/2017 14:20

I want to convert the df to hourly by taking the average within an hour:

Time
1/2/2017 13:00
1/2/2017 14:00

I tried groupby after converting to datetime:

times = pd.to_datetime(df.Time)
df.groupby([times.hour, times.minute])

I got the error: AttributeError: 'Series' object has no attribute 'hour'

I tried

df.groupby(pd.DatetimeIndex(df['Time']).hour).mean()

but this grouped everything based on 24 hours of the day.

Upvotes: 0

Views: 1477

Answers (2)

Sakib Shahriar
Sakib Shahriar

Reputation: 121

This worked perfectly: df.resample('60T').mean()

Upvotes: 1

Nicolas Gervais
Nicolas Gervais

Reputation: 36624

You can do it as following:

import pandas as pd
import numpy as np

dates = ['1/2/2017 13:00', '1/2/2017 13:10', '1/2/2017 13:20', 
         '1/2/2017 13:30', '1/2/2017 13:40', '1/2/2017 13:50',
         '1/2/2017 14:00', '1/2/2017 14:10', '1/2/2017 14:20']

numbers = np.random.randint(0, 11, 9)

df = pd.DataFrame(numbers, index=dates)

df

1/2/2017 13:00 5
1/2/2017 13:10 8
1/2/2017 13:20 10
1/2/2017 13:30 1
1/2/2017 13:40 6
1/2/2017 13:50 7
1/2/2017 14:00 10
1/2/2017 14:10 7
1/2/2017 14:20 8 

times = pd.to_datetime(df.index)
df.groupby(times.hour).mean()

13 6.166667
14 8.333333

Here 13 and 14 represent the hourly aggregate, i.e., mean of 13h, mean of 14h.

Upvotes: 0

Related Questions