'Creating Time Bucket and analyze them with GroupBy Python
I came up with a new question while I'm dealing with my code. I would like to ceil the data to 15mins. interval and calculate the difference of each max and min, now there is another problem for the day changes and the zero values.
Raw data would be as follows,
A Date Time Cumulative
0 A-1 23.10.2021 23:05:31 12
1 A-1 23.10.2021 23:11:13 17
2 A-1 23.10.2021 23:19:22 19
3 A-1 23.10.2021 23:25:03 23
4 A-1 23.10.2021 23:48:05 38
5 A-1 24.10.2021 00:09:32 56
Here are the problems:
- Since there isn't data for the time bucket of 23:45:00 (no record between 23:30:00 and 23:44:59), it should be shown as 0
- For the records whose time >= 23:45:00, it should be ceiled as 24.10.2021 00:00:00 (not to ceil as 23.10.2021 00:00:00)
after creating the time buckets and calculating the difference of the time buckets, the result that I'd like to have is as follows,
Date TimeBuckets A Cumulative
1 23.10.2021 23:15:00 A-1 5
2 23.10.2021 23:30:00 A-1 4
3 23.10.2021 23:45:00 A-1 0
0 24.10.2021 00:00:00 A-1 15
4 24.10.2021 00:15:00 A-1 18
But the result that I have is,
Date TimeBuckets A Cumulative
0 23.10.2021 00:00:00 A-1 0
1 23.10.2021 23:15:00 A-1 5
2 23.10.2021 23:30:00 A-1 4
3 23.10.2021 23:45:00 A-1 0
4 24.10.2021 00:15:00 A-1 0
The sample code is as follows,
import pandas as pd
import numpy as np
df = pd.DataFrame({'A': 'A-1 A-1 A-1 A-1 A-1 A-1 A-1'.split(),
'Date':'23.10.2021 23.10.2021 23.10.2021 23.10.2021 23.10.2021 23.10.2021 24.10.2021'.split(),
'Time': '23:05:31 23:11:13 23:19:22 23:25:03 23:33:12 23:48:05 00:09:32'.split(),
'Cumulative': '12 17 19 23 29 38 56'.split()})
df1 = (df.assign(Cumulative=df['Cumulative'].astype(int),
TimeBuckets=pd.to_datetime(df['Time']).dt.ceil('15min').dt.time
)
.groupby(['Date','TimeBuckets'], as_index=False)
.agg({'A': 'first', 'Cumulative': lambda x: x.max()-x.min()})
)
print(df1)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
