Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python: Grouping by time interval

Tags:

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
like image 847
Jad Avatar asked May 02 '18 10:05

Jad


1 Answers

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
like image 154
jezrael Avatar answered Sep 28 '22 18:09

jezrael