Create hourly buckets from time series data points

user_514149 0
Viewed 247

I have a list of DB records (i.e. class instances), which include a datetime object as well as a boolean flag. There may be such a record for every few seconds, but there may also be some hours (or even days) without any such record.

Now I want to count the number of these records per hour in which the boolean flag is True. But these buckets should include also those hours where either no record with a True flag is or where no record is at all.

So for example see this list of records:

2021-07-05T00:00:32 --> False 
2021-07-05T00:01:32 --> False
2021-07-05T00:01:45 --> True
2021-07-05T00:02:01 --> False
2021-07-05T00:05:12 --> False
2021-07-05T00:05:24 --> True
2021-07-05T00:05:37 --> True
2021-07-05T00:08:11 --> True
2021-07-05T00:09:12 --> False
2021-07-05T00:10:15 --> False
2021-07-05T00:11:23 --> False

So for these first twelve hours of day 2021-07-05 I would need the following buckets:

hour  count
  00      0
  01      1
  02      0
  03      0
  04      0
  05      2
  06      0
  07      0
  08      1
  09      0
  10      0
  11      0

I guess I would find some "manual" solution to this problem - but I also have the feeling there is a more elegant, Pythonic solution, with some list magic or something. Or maybe based on the pandas library? I am not very familiar with that one though ...

Thanks in advance for any ideas!

algorithm python
2 Answers

You can use collections.defaultdict:

from collections import defaultdict
import datetime
records = [[datetime.datetime(2021, 7, 5, 0, 0, 32), False], [datetime.datetime(2021, 7, 5, 0, 1, 32), False], [datetime.datetime(2021, 7, 5, 0, 1, 45), True], [datetime.datetime(2021, 7, 5, 0, 2, 1), False], [datetime.datetime(2021, 7, 5, 0, 5, 12), False], [datetime.datetime(2021, 7, 5, 0, 5, 24), True], [datetime.datetime(2021, 7, 5, 0, 5, 37), True], [datetime.datetime(2021, 7, 5, 0, 8, 11), True], [datetime.datetime(2021, 7, 5, 0, 9, 12), False], [datetime.datetime(2021, 7, 5, 0, 10, 15), False], [datetime.datetime(2021, 7, 5, 0, 11, 23), False]]
d = defaultdict(int)
for a, b in records:
   d[tuple(getattr(a, i) for i in ['year', 'month', 'day', 'hour', 'minute'])] += b

result = [[str(datetime.datetime(*(j:=(2021, 7, 5, 0, i)))), d.get(j, 0)] for i in range(0, 13)]

Output:

[['2021-07-05 00:00:00', 0],    
 ['2021-07-05 00:01:00', 1], 
 ['2021-07-05 00:02:00', 0], 
 ['2021-07-05 00:03:00', 0], 
 ['2021-07-05 00:04:00', 0], 
 ['2021-07-05 00:05:00', 2], 
 ['2021-07-05 00:06:00', 0], 
 ['2021-07-05 00:07:00', 0], 
 ['2021-07-05 00:08:00', 1], 
 ['2021-07-05 00:09:00', 0], 
 ['2021-07-05 00:10:00', 0],  
 ['2021-07-05 00:11:00', 0], 
 ['2021-07-05 00:12:00', 0]]
user_73267380

Here is my solution based on pandas

import pandas as pd

df = pd.DataFrame(
    {"time": [
        "2021-07-05T00:00:32",
        "2021-07-05T00:01:32",
        "2021-07-05T00:01:45",
        "2021-07-05T00:02:01",
        "2021-07-05T00:05:12",
        "2021-07-05T00:05:24",
        "2021-07-05T00:05:37",
        "2021-07-05T00:08:11",
        "2021-07-05T00:09:12",
        "2021-07-05T00:10:15",
        "2021-07-05T00:11:23"],
    "is_anomaly": [
            False,
            False,
            True,
            False,
            False,
            True,
            True,
            True,
            False,
            False,
            False]})

# preprocessing
df.time = pd.to_datetime(df.time)
df.set_index("time", inplace=True)
df['is_anomaly'] = df['is_anomaly'].astype(int)

# bucket by minute
df_minute = df.resample("1min").sum()

# print
print(df_minute)
user_101535740
Related