CODEWITHSUNDEEP
pythonpandas
IRK
IRK

Reputation: 95

Pandas average values for the same hour for each day

The post Pandas Dataframe, getting average value for each monday 1 Am described a similar problem as what I have right now, so I borrowed their dataframe. However, mine is a little bit harder and that solution didn't really work for me.

time                    value
2016-03-21 00:00:00     0.613014
2016-03-21 01:00:00     0.596383
2016-03-21 02:00:00     0.623570
2016-03-21 03:00:00     0.663350
2016-03-21 04:00:00     0.677817
2016-03-21 05:00:00     0.727116
2016-03-21 06:00:00     0.920279
2016-03-21 07:00:00     1.205863
2016-03-21 08:00:00     0.880946
2016-03-21 09:00:00     0.186947
2016-03-21 10:00:00     -0.563276
2016-03-21 11:00:00     -1.249595
2016-03-21 12:00:00     -1.596035
2016-03-21 13:00:00     -1.886954
2016-03-21 14:00:00     -1.912325
2016-03-21 15:00:00     -1.750623
...     
2016-06-20 23:00:00     2.125791

What I need to do is to get average value for each day at a specific time. Say, I have to get the average for every day at 1AM, and then 2AM, and then 3AM. I would like to do this in a groupby way which might make the rolling avg I have do after that easier, but every method counts, thanks!

Upvotes: 2

Views: 2001

Answers (2)

Adam Conrad
Adam Conrad

Reputation: 176

You could create a temporary column that holds hours and group on that, eg: df['hour'] = df['time'].dt.hour and then hour_avg = df.groupby(['hour']).mean() note this approach aggregates hours over all years, months and days.

Upvotes: 1

Shivam Roy
Shivam Roy

Reputation: 2061

You can extract the hours in a separate column and groupby() by it:

df['hour'] = df.time.dt.hour
result_df = df.groupby(['hour']).mean()

Upvotes: 3

Related Questions