如何用Pandas分组计算指定日期组合的小时级时间差？

原始数据 #

DataFrame 代码 #

import pandas as pd

df = pd.DataFrame([[1,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM','1/2/22 12:00:00 AM'],
[1,'A','X','1/4/22 12:00:00AM','1/3/22 12:00:00 AM','1/3/22 12:00:00 AM'],
[1,'A','Y','1/3/22 12:00:00AM','1/2/22 12:00:00 AM','1/1/22 12:00:00 AM'],
[1,'B','X','1/3/22 12:00:00AM','1/2/22 12:00:00 AM','1/3/22 12:00:00 AM'],
[2,'A','X','1/5/22 12:00:00AM','1/3/22 12:00:00 AM','1/4/22 12:00:00 AM'],
[2,'A','X','1/6/22 12:00:00AM','1/4/22 12:00:00 AM','1/5/22 12:00:00 AM']],
columns = ['ID','Category','Site','Task Completed','Access Completed', 'Upload Completed'])

数据表格 #

需求说明 #

按ID、Category、Site分组，计算小时级时间差，逻辑公式为：

(max(Access Completed) 和 min(Upload Completed) 中的较晚日期) - (首个Task Completed日期)

同时需保留分组后的First Task Completed、Max Access Date、Min Upload Date字段。

预期结果 #

已实现代码及输出 #

目前仅完成了Access Completed最大值与首个Task Completed日期的差值计算，代码如下：

out = (df
   .groupby(['ID', 'Category', 'Site'], as_index=False)
   .agg({'Task Completed': 'first', 'Access Completed': 'max'})
   .assign(**{'Time Difference': lambda d: d['Task Completed']
              .sub(d['Access Completed'])
              .dt.total_seconds().floordiv(3600)})
)

输出结果：

ID Category Site      Task Completed Access Completed  Time Difference
0   1        A    X 2022-01-03 00:00:00       2022-01-02             24.0
1   1        A    Y 2022-01-01 01:00:00       2022-01-01              1.0
2   1        B    X 2022-01-01 01:00:00       2022-01-01              1.0
3   2        A    X 2022-01-03 00:00:00       2022-01-02             24.0

完整解决方案 #

需先将日期列转为datetime类型，再扩展聚合字段，最后按逻辑计算时间差：

# 转换日期列为datetime类型
df[['Task Completed', 'Access Completed', 'Upload Completed']] = df[['Task Completed', 'Access Completed', 'Upload Completed']].apply(pd.to_datetime)

# 分组聚合+计算时间差
out = (df
       .groupby(['ID', 'Category', 'Site'], as_index=False)
       .agg(
           First_Task_Completed=('Task Completed', 'first'),
           Max_Access_Date=('Access Completed', 'max'),
           Min_Upload_Date=('Upload Completed', 'min')
       )
       # 取max(Access)和min(Upload)中的较晚日期
       .assign(
           Compare_Date=lambda x: x[['Max_Access_Date', 'Min_Upload_Date']].max(axis=1),
           Time_Difference=lambda x: (x['First_Task_Completed'] - x['Compare_Date']).dt.total_seconds().floordiv(3600).abs()
       )
       # 调整列顺序匹配预期结果
       [['ID', 'Category', 'Site', 'Time_Difference', 'First_Task_Completed', 'Max_Access_Date', 'Min_Upload_Date']]
)

# 格式化日期显示
out['First_Task_Completed'] = out['First_Task_Completed'].dt.strftime('%m/%d/%y %I:%M:%S%p')
out['Max_Access_Date'] = out['Max_Access_Date'].dt.strftime('%m/%d/%y %I:%M:%S %p')
out['Min_Upload_Date'] = out['Min_Upload_Date'].dt.strftime('%m/%d/%y %I:%M:%S %p')

print(out)

输出结果 #

ID Category Site  Time_Difference First_Task_Completed   Max_Access_Date     Min_Upload_Date
0   1        A    X                0  01/03/22 12:00:00AM  01/03/22 12:00:00 AM  01/02/22 12:00:00 AM
1   1        A    Y               24  01/03/22 12:00:00AM  01/02/22 12:00:00 AM  01/01/22 12:00:00 AM
2   1        B    X                0  01/03/22 12:00:00AM  01/02/22 12:00:00 AM  01/03/22 12:00:00 AM
3   2        A    X               24  01/05/22 12:00:00AM  01/04/22 12:00:00 AM  01/04/22 12:00:00 AM

关键步骤说明 #

1. 日期类型转换：字符串日期必须转为datetime类型，否则无法进行时间运算。
2. 扩展聚合：分组时新增Upload Completed的最小值聚合，获取Min_Upload_Date。
3. 取较晚日期：用max(axis=1)对每行的两个日期字段取最大值（即较晚的日期）。
4. 时间差计算：用首个任务完成日期减去较晚日期，转成总秒数后除以3600取整，取绝对值得到小时级差值。
5. 日期格式化：将datetime类型转回字符串，匹配预期结果的显示格式。

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