Pandas group by datetime

Question

I have a pandas dataframe that looks like this:

df = pd.DataFrame({})
df['Date']  = pd.to_datetime(np.arange(0,11), unit='h', origin='2018-08-01 00:00:00')

                 Date
0 2018-08-01 00:00:00
1 2018-08-01 01:00:00
2 2018-08-01 02:00:00
3 2018-08-01 03:00:00
4 2018-08-01 04:00:00
5 2018-08-01 05:00:00
6 2018-08-01 06:00:00
7 2018-08-01 07:00:00
8 2018-08-01 08:00:00
9 2018-08-01 09:00:00
10 2018-08-01 10:00:00

I want to create a new column 'Trip', which is an integer number which tells me which trip it is. All records within 4 hour of the start of the trip should be part of the trip. The result has to look like:

                 Date   Trip
0 2018-08-01 00:00:00   1
1 2018-08-01 01:00:00   1
2 2018-08-01 02:00:00   1
3 2018-08-01 03:00:00   1
4 2018-08-01 04:00:00   1
5 2018-08-01 05:00:00   2
6 2018-08-01 06:00:00   2
7 2018-08-01 07:00:00   2
8 2018-08-01 08:00:00   2
9 2018-08-01 09:00:00   2
10 2018-08-01 10:00:00  3

Any idea how to do this?

Answer 1

Here you go. Enjoy!

import pandas as pd
import numpy as np

df = pd.DataFrame({})
df['Date']  = pd.to_datetime(np.arange(0,11), unit='h', origin='2018-08-01 00:00:00')

def get_cum_time(x):

    diff_time = x.Date - x.prev_date

    return diff_time / np.timedelta64(1, 'h')

df['prev_date'] = df.Date.shift(1)

df['diff_time'] = df.apply(get_cum_time, axis=1)
df['cum_time'] = df.diff_time.cumsum()

df.cum_time.fillna(1.0, inplace=True)

def get_trip(x):
    return x // 4 + 1

df['Trip'] = df.cum_time.apply(get_trip)

print(df)

Output:

                      Date           prev_date  diff_time  cum_time  Trip
0  2018-08-01 00:00:00                 NaT        NaN       1.0   1.0
1  2018-08-01 01:00:00 2018-08-01 00:00:00        1.0       1.0   1.0
2  2018-08-01 02:00:00 2018-08-01 01:00:00        1.0       2.0   1.0
3  2018-08-01 03:00:00 2018-08-01 02:00:00        1.0       3.0   1.0
4  2018-08-01 04:00:00 2018-08-01 03:00:00        1.0       4.0   2.0
5  2018-08-01 05:00:00 2018-08-01 04:00:00        1.0       5.0   2.0
6  2018-08-01 06:00:00 2018-08-01 05:00:00        1.0       6.0   2.0
7  2018-08-01 07:00:00 2018-08-01 06:00:00        1.0       7.0   2.0
8  2018-08-01 08:00:00 2018-08-01 07:00:00        1.0       8.0   3.0
9  2018-08-01 09:00:00 2018-08-01 08:00:00        1.0       9.0   3.0
10 2018-08-01 10:00:00 2018-08-01 09:00:00        1.0      10.0   3.0