Python Pandas: Group Datetime Column into Hour and Minute Aggregations

pandas groupby time of day with 15 minute bins

You can first take the floor of the timestamps at a certain frequency, and then access their time to group the data:

>>> df.groupby(df.index.floor('15T').time).sum()

          data
00:00:00    30
00:15:00    30
00:30:00    30
00:45:00    30
01:00:00    30
       ...
22:45:00    45
23:00:00    45
23:15:00    45
23:30:00    45
23:45:00    45

[96 rows x 1 columns]

This should work for all other minute frequencies that evenly divide the hour (1, 2, 3, 4, 5, 6, 10, 12, 15, 20, 30, or 60 minutes).

Pandas group by time interval (5min, 10min, 1day, 1year) and count amount of entries

Are you looking for something like this, for minute intervals:

df.groupby(['point',df.timestamp_local.dt.floor('5Min')]).size()

and this, for month/year

df.groupby(['point', df.timestamp_local.dt.to_period('M')]).size()

How do I aggregate data by hour?

The tool you need is panasonic resample method.

To use it, Date column must be of datetime type, so if it is kept
as a text, start from:

df.Date = pd.to_datetime(df.Date)

To compute the resampled DataFrame, run:

result = df.resample('30T', on="Date").mean()

In the above example 30T is the resample frequency - 30 mins.
If you change your mind, set another value.

The result, for your source data sample, is:

                     Temperature  Humidity     Light       CO2
Date                                                          
2015-02-04 17:30:00     0.986975  0.468231  0.273730  0.181391
2015-02-04 18:00:00     0.960853  0.467565  0.172402  0.169187

using Python, How to group a column in Dataframe by the hour?

Using @jezrael setup.

df.resample(rule='H', how='count').rename(columns = {'time':'count'})

                      count
2016-08-24 00:00:00      1
2016-08-24 01:00:00      3
2016-08-24 02:00:00      1

Aggregate 15 min data to 1 hour data based on another column in python?

You can extract the hour value from the timestamp and then groupby -

df['hour'] = pd.to_datetime(df['Timestamp']).dt.hour
df.groupby(['hour', 'Computer no.']).agg('mean').reset_index()

Output

   hour  Computer no.  Memory Usage in %
0     8             1              24.25
1     8             2              47.50
2     8             3              43.75
3     9             1              43.00
4     9             2              26.00

How I can group timestamp column to hourly and aggregate the rows in pandas dataframe

Try pd.Grouper and specify the freq parameter:

df.groupby([pd.Grouper(key='date', freq='1H')]).sum()

Full code:

import pandas as pd
from datetime import datetime
import numpy as np

date_rng = pd.date_range(start='1/1/2018', end='1/08/2018', freq='T')
df = pd.DataFrame(date_rng, columns=['date'])
df['data'] = np.random.randint(0, 100, size=(len(date_rng)))

print(df.groupby([pd.Grouper(key='date', freq='1H')]).sum())
#                      data
# date
# 2018-01-01 00:00:00  2958
# 2018-01-01 01:00:00  3084
# 2018-01-01 02:00:00  2991
# 2018-01-01 03:00:00  3021
# 2018-01-01 04:00:00  2894
# ...                   ...
# 2018-01-07 20:00:00  2863
# 2018-01-07 21:00:00  2850
# 2018-01-07 22:00:00  2823
# 2018-01-07 23:00:00  2805
# 2018-01-08 00:00:00    25

# [169 rows x 1 columns]

Hope that helps !

How to group dataframe by hour using timestamp with Pandas

I came across this gem, pd.DataFrame.resample, after I posted my round-to-hour solution.

# Construct example dataframe
times = pd.date_range('1/1/2018', periods=5, freq='25min')
values = [4,8,3,4,1]
df = pd.DataFrame({'val':values}, index=times)

# Resample by hour and calculate medians
df.resample('H').median()

Or you can use groupby with Grouper if you don't want times as index:

df = pd.DataFrame({'val':values, 'times':times})
df.groupby(pd.Grouper(level='times', freq='H')).median()

Group Datetime in panda into three hourly intervals

You haven't provided much detail, but you can use the 'TimeGrouper':

df.groupby(pd.TimeGrouper(key='your_time_column', freq='3H')).count()

The key parameter is optional if your time is the index.

---

Related Topics