Pandas合并排序行程起止时间列 动态统计园区车辆数量

问题说明 #

现有100辆车的行程起止时间数据，包含司机、行程开始时间、行程结束时间字段，示例数据集构造代码如下：

import pandas as pd

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，处理规则如下：

• 将行程start、end时间合并为单列并按时间升序排序
• 逐行追踪园区内车辆数量变化：司机触发start（行程开始）事件时车辆数减1，触发end（行程结束）事件时车辆数加1
• 最终输出包含日期、司机、事件类型、车辆计数的结果表

期望输出格式：

----------------------------------------------------
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

实现方案 #

用宽表转长表+累计求和的思路即可快速实现，代码如下：

# 拆分出发、返程两类事件，分别打标、设置计数变化量
start_df = df[['driver', 'start']].rename(columns={'start': 'date'})
start_df['type'] = 'start'
start_df['delta'] = -1

end_df = df[['driver', 'end']].rename(columns={'end': 'date'})
end_df['type'] = 'end'
end_df['delta'] = 1

# 合并事件表、按时间升序排列
event_df = pd.concat([start_df, end_df]).sort_values('date').reset_index(drop=True)

# 从初始车辆数100开始，累计计算实时园区车辆数
event_df['count'] = 100 + event_df['delta'].cumsum()

# 筛选需要的列得到最终结果
result = event_df[['date', 'driver', 'type', 'count']]

运行代码后得到的result与期望输出完全匹配。

内容的提问来源于stack exchange，提问作者maxime