Python: Grouping by time interval

Question

I have a dataframe that looks like this:

I'm using python 3.6.5 and a datetime.time object for the index

print(sum_by_time)

           Trips
  Time

00:00:00    10
01:00:00    10
02:00:00    10
03:00:00    10
04:00:00    20
05:00:00    20
06:00:00    20
07:00:00    20
08:00:00    30
09:00:00    30
10:00:00    30
11:00:00    30

How can I group this dataframe by time interval to get something like this:

                         Trips
       Time    

00:00:00 - 03:00:00        40
04:00:00 - 07:00:00        80
08:00:00 - 11:00:00       120

jezrael · Accepted Answer · 2018-05-02 12:50:06Z

4

I think need convert index values to timedeltas by to_timedelta and then resample:

df.index = pd.to_timedelta(df.index.astype(str))

df = df.resample('4H').sum()
print (df)
          Trips
00:00:00     40
04:00:00     80
08:00:00    120

EDIT:

For your format need:

df['d'] = pd.to_datetime(df.index.astype(str))

df = df.groupby(pd.Grouper(freq='4H', key='d')).agg({'Trips':'sum', 'd':['first','last']})
df.columns = df.columns.map('_'.join)
df = df.set_index(df['d_first'].dt.strftime('%H:%M:%S') + ' - ' + df['d_last'].dt.strftime('%H:%M:%S'))[['Trips_sum']]
print (df)
                     Trips_sum
00:00:00 - 03:00:00         40
04:00:00 - 07:00:00         80
08:00:00 - 11:00:00        120

edited May 2, 2018 at 12:50

answered May 2, 2018 at 11:30

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tda Over a year ago

You beat me to it! This is what I came up with! Just for future visitors, you can use 'H' for hour, 'T' for minute and 'M' for month time bins.

Jad Over a year ago

Awesome thanks! Any idea on how to make the index reflect the time intervals? i.e. instead of 00:00:00 write 00:00:00 - 03:00:00

jezrael Over a year ago

@Jad - Added solution, please check it.

Quickbeam2k1 Over a year ago

a different way to extract the information for the time spans, could be to use to_period on the index and use start_timeand end_time. Then one doesn't need to parse the time string again.

jezrael Over a year ago

@Quickbeam2k1 - Good idea, thanks. I try implement it.

|

Collectives™ on Stack Overflow

Python: Grouping by time interval

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related