0

Python- aggregation on timestamp.

Hi, I have a python dataframe similar to the below for 24 hours, with an interval of 15 min. I want to group the data with 3 hours window and sum up the 'value' field. Any suggestions?

index   name     m_timestamp            value
    1   A        2019-01-27 00:30:00    10
    2   A        2019-01-27 00:45:00    20
    3   A        2019-01-27 01:00:00    30
    4   A        2019-01-27 01:15:00    40
.
.
.
Nihal
  • 5,262
  • 7
  • 23
  • 41
Raghava kattamudi
  • 67
  • 1
  • 1
  • 7

1 Answers1

0

This should split the timestamp into 3 hour blocks and output the sum within that time period. Docs on pd.Grouper

import pandas as pd

data = [{'name': 'A', 'm_timestamp': '2019-01-27 00:30:00', 'value': 10}, {'name': 'A', 'm_timestamp': '2019-01-27 00:45:00', 'value': 20}, {'name': 'A', 'm_timestamp': '2019-01-27 01:00:00', 'value': 30}, {'name': 'A', 'm_timestamp': '2019-01-27 01:15:00', 'value': 40}, {'name': 'A', 'm_timestamp': '2019-01-27 04:15:00', 'value': 100}, {'name': 'A', 'm_timestamp': '2019-01-27 19:15:00', 'value': 12}]

df = pd.DataFrame(data)
df['m_timestamp'] = pd.to_datetime(df['m_timestamp'])  # Makes sure your timestamp is in datetime format
df.groupby(pd.Grouper(key='m_timestamp', freq='180Min')).sum()

Output:

m_timestamp         value            
2019-01-27 00:00:00    100
2019-01-27 03:00:00    100
2019-01-27 06:00:00      0
2019-01-27 09:00:00      0
2019-01-27 12:00:00      0
2019-01-27 15:00:00      0
2019-01-27 18:00:00     12
cullzie
  • 2,705
  • 2
  • 16
  • 21