Combine and sort datetime columns

June 2, 2022

Assume that I have data of trip start and end datetime info and I have 100 vehicles.

I want to combine and sort trip start and end date in one column and track the number of vehicles in the park area. If a driver start a trip the number of vehicles decreases by 1 and increases by 1 if the trip ends.

trips = {
    'driver':['Tom', 'Tom', 'Max'],
         
    'start': ['2022-01-03 13:56:03',
              '2022-01-04 10:21:06',
              '2022-01-04 11:38:39'],
         
     'end':  ['2022-01-03 15:39:14',
              '2022-01-04 17:07:38',
              '2022-01-04 16:06:42'],
 }

df = pd.DataFrame(trips)

vehicle count = 100

output

----------------------------------------------------
date                 |  driver |  type   |  count
----------------------------------------------------
2022-01-03 13:56:03  |   Tom   |  start  |   99
2022-01-03 15:39:14  |   Tom   |  end    |   100
2022-01-04 10:21:06  |   Tom   |  start  |   99
2022-01-04 11:38:39  |   Max   |  start  |   98
2022-01-04 16:06:42  |   Max   |  end    |   99
2022-01-04 17:07:38  |   Tom   |  end    |   100

>Solution :

You can melt and cumsum ±1 depending on the start/end type:

out = (df
 .melt(id_vars='driver', var_name='type', value_name='date')
 .sort_values(by='date', ignore_index=True)
 .assign(count=lambda d: 100+d['type'].map({'start': -1, 'end': 1}).cumsum())
)

Output:

  driver   type                 date  count
0    Tom  start  2022-01-03 13:56:03     99
1    Tom    end  2022-01-03 15:39:14    100
2    Tom  start  2022-01-04 10:21:06     99
3    Max  start  2022-01-04 11:38:39     98
4    Max    end  2022-01-04 16:06:42     99
5    Tom    end  2022-01-04 17:07:38    100