how to groupby by date and custom range of time pandas dataframe

user_11855531 0
Viewed 39

I have a table like this:

          value   time         date         
0           1   06:30:04    2021-01-01
1           1   07:02:37    2021-01-01
2           1   08:22:45    2021-01-01
3           1   08:33:39    2021-01-01
4           1   09:02:41    2021-01-01

I want to group by the date but in custom range. if the time is exactly after 12:30:00 p.m., I want to count that as the next day. or in other words, the wanted range starts from 12:31 p.m. to 12:30 p.m. of day after. for example, if the date is 2021-01-01 and the time is 12:30:01 p.m. the row goes to the 2021-01-02 element. how can I do that?

pandas group-by python
1 Answers

Pandas has a datetime rounding function, which you could use to round to the day. Here I've edited your last record to occur after 12:30

df = pd.DataFrame({'value': [1, 1, 1, 1, 1],
 'time': ['06:30:04', '07:02:37', '08:22:45', '08:33:39', '12:45:41'],
 'date': ['2021-01-01',
  '2021-01-01',
  '2021-01-01',
  '2021-01-01',
  '2021-01-01']})

output = df.groupby(pd.to_datetime(df['date'] + ' ' + df['time']).round('D'))['value'].sum().reset_index()

Output

       index  value
0 2021-01-01      4
1 2021-01-02      1
user_47183500
Related