使用Pandas计算不同里程碑间的时间差（含重复记录处理）

输入DataFrame #

需求 #

将上述DataFrame转换为事务透视表，为每个id生成A-B, min和A-C, min两列：

• 时间差基于每个里程碑的最早时间计算，单位为分钟
• 若id缺少对应里程碑序列（如没有C里程碑），则标记为None

目标输出 #

用户尝试过的方法 #

• 通过df.groupby(['id','milestone'])['tm'].min().to_frame('tm').reset_index()获取每个里程碑的最早时间
• 使用.sort_values(['id','tm']).groupby('id')['tm'].diff()计算时间差，但无法生成目标列
• 考虑过.pivot方法，但不清楚如何对里程碑进行聚合

解决方案 #

步骤1：预处理时间列 #

先将tm列转换为datetime类型，确保能正确计算时间差：

import pandas as pd

# 加载用户提供的示例数据
df = pd.DataFrame({'id':['00335c06f96a21e4089c49a5da','00335c06f96a21e4089c49a5da','00335c06f96a21e4089c49a5da','00335c06f96a21e4089c49a5da','00335c06f96a21e4089c49a5da','0043545f6b9112c7e471d5cc81','0043545f6b9112c7e471d5cc81','0043545f6b9112c7e471d5cc81','0043545f6b9112c7e471d5cc81','ffe92ae6b0962e800dbdbdf00a','ffe92ae6b0962e800dbdbdf00a','ffe92ae6b0962e800dbdbdf00a','ffe92ae6b0962e800dbdbdf00a'],
             'tm':['2023-02-01 18:13:42.307543','2023-02-01 18:14:42.307543','2023-02-01 18:15:42.307543','2023-02-01 18:19:10.307543','2023-02-01 18:21:05.307543',
                  '2023-02-02 08:06:42.307543','2023-02-02 08:07:42.307543','2023-02-02 09:05:42.307543','2023-02-02 09:05:42.307543',
                  '2023-02-12 19:05:42.307543','2023-02-12 19:05:42.307543','2023-02-12 19:07:42.307543','2023-02-13 21:03:42.307543'],
              'milestone':['A','A','A','B','C','A','A','B','B','A','B','B','C']})

# 转换时间列为datetime类型
df['tm'] = pd.to_datetime(df['tm'])

步骤2：获取每个id+里程碑的最早时间 #

用groupby聚合得到每个id下各里程碑的最早时间，再转成宽表：

# 聚合最早时间并透视成宽表
milestone_min = df.groupby(['id', 'milestone'])['tm'].min().unstack()

步骤3：计算时间差并格式化 #

基于宽表计算A到B、A到C的时间差，转换为分钟并处理缺失值：

# 计算时间差（转换为分钟）
milestone_min['A-B, min'] = (milestone_min['B'] - milestone_min['A']).dt.total_seconds() / 60
milestone_min['A-C, min'] = (milestone_min['C'] - milestone_min['A']).dt.total_seconds() / 60

# 处理缺失值为None，保留两位小数匹配示例
milestone_min['A-B, min'] = milestone_min['A-B, min'].round(2).where(milestone_min['A-B, min'].notna(), None)
milestone_min['A-C, min'] = milestone_min['A-C, min'].round(2).where(milestone_min['A-C, min'].notna(), None)

# 保留目标列并重置索引
result = milestone_min[['A-B, min', 'A-C, min']].reset_index()

查看结果 #

运行后result即为目标透视表：

print(result)

输出结果：

id  A-B, min  A-C, min
0  00335c06f96a21e4089c49a5da      5.47      7.38
1  0043545f6b9112c7e471d5cc81     59.00       None
2  ffe92ae6b0962e800dbdbdf00a      0.00    1558.00

关键说明 #

• unstack()将聚合后的长表转为宽表，每个里程碑作为列，方便直接计算时间差
• dt.total_seconds() / 60将时间差精确转换为分钟单位
• where()方法将缺失的时间差替换为None，符合需求
• round(2)用于保留两位小数，和示例输出格式一致

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