Pandas分组计算：获取分组最早任务与最晚访问完成时间差

原始数据集 #

代码定义 #

import pandas as pd

cols = ['ID','Category','Site','Task Completed','Access Completed']

df = pd.DataFrame([
    [1,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM'],
    [1,'A','X','1/4/22 1:00:00AM','1/2/22 12:00:00 AM'],
    [1,'A','Y','1/1/22 1:00:00AM','1/1/22 12:00:00 AM'],
    [1,'B','X','1/1/22 1:00:00AM','1/1/22 12:00:00 AM'],
    [2,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM'],
    [2,'A','X','1/4/22 12:00:00AM','1/2/22 12:00:00 AM']
], columns = cols)

表格展示 #

需求说明 #

针对数据集中每个ID/Category/Site组合，计算最晚Access Completed日期与最早Task Completed日期的时间差（以小时为单位），同时在结果中包含这两个日期。

现有问题 #

现有代码能获取每组最早的Task Completed日期，但无法正确获取每组最晚的Access Completed日期，直接对Access Completed调用.max()方法会计算全局最大值而非分组最大值，不符合需求。

现有代码 #

import pandas as pd

cols = ['ID','Category','Site','Task Completed','Access Completed']

df = pd.DataFrame([
    [1,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM'],
    [1,'A','X','1/4/22 1:00:00AM','1/2/22 12:00:00 AM'],
    [1,'A','Y','1/1/22 1:00:00AM','1/1/22 12:00:00 AM'],
    [1,'B','X','1/1/22 1:00:00AM','1/1/22 12:00:00 AM'],
    [2,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM'],
    [2,'A','X','1/4/22 12:00:00AM','1/2/22 12:00:00 AM']
], columns = cols)

# 转换为日期时间格式
df[['Task Completed','Access Completed']] = df[['Task Completed','Access Completed']].apply(lambda x: pd.to_datetime(x))

# 去重保留每组最早的Task Completed
res = df.sort_values('Task Completed')\
    .drop_duplicates(subset=["ID", "Category", 'Site'], keep='first')\
    .sort_index()

# 计算时间差
res['Time Difference'] = res['Task Completed'].sub(res['Access Completed']).dt.total_seconds().div(3600)

# 调整列顺序和名称
cols.insert(3,'Time Difference')
res = res[cols].rename(columns={"Task Completed": "First Task Completed"})

# 转换日期格式
res["First Task Completed"] = res["First Task Completed"].dt.strftime('%m/%d/%Y %H:%M:%S %p')
res["Access Completed"] = res["Access Completed"].dt.strftime('%m/%d/%Y %H:%M:%S %p')

print(res)

尝试的错误代码 #

res['Time Difference'] = res['Task Completed'].sub(res['Access Completed'].max()).dt.total_seconds().div(3600)

解决方案 #

使用groupby分组聚合操作，一次性获取每组的最早任务完成时间和最晚访问完成时间，再计算时间差。

完整代码 #

import pandas as pd

cols = ['ID','Category','Site','Task Completed','Access Completed']

df = pd.DataFrame([
    [1,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM'],
    [1,'A','X','1/4/22 1:00:00AM','1/2/22 12:00:00 AM'],
    [1,'A','Y','1/1/22 1:00:00AM','1/1/22 12:00:00 AM'],
    [1,'B','X','1/1/22 1:00:00AM','1/1/22 12:00:00 AM'],
    [2,'A','X','1/3/22 12:00:00AM','1/1/22 12:00:00 AM'],
    [2,'A','X','1/4/22 12:00:00AM','1/2/22 12:00:00 AM']
], columns = cols)

# 转换为日期时间格式
df[['Task Completed', 'Access Completed']] = df[['Task Completed', 'Access Completed']].apply(pd.to_datetime)

# 按ID/Category/Site分组，聚合得到最早任务完成时间和最晚访问完成时间
res = df.groupby(['ID', 'Category', 'Site']).agg(
    First_Task_Completed=('Task Completed', 'min'),
    Last_Access_Completed=('Access Completed', 'max')
).reset_index()

# 计算时间差（小时）
res['Time Difference'] = (res['First_Task_Completed'] - res['Last_Access_Completed']).dt.total_seconds() / 3600

# 调整列顺序
res = res[['ID', 'Category', 'Site', 'Time Difference', 'First_Task_Completed', 'Last_Access_Completed']]

# 转换日期为目标格式
res['First_Task_Completed'] = res['First_Task_Completed'].dt.strftime('%m/%d/%Y %I:%M:%S %p')
res['Last_Access_Completed'] = res['Last_Access_Completed'].dt.strftime('%m/%d/%Y %I:%M:%S %p')

# 格式化时间差为整数
res['Time Difference'] = res['Time Difference'].astype(int)

print(res)

代码说明 #

1. 日期转换：将两个日期列转为datetime格式，确保时间计算的有效性。
2. 分组聚合：通过groupby按指定组合分组，使用agg方法一次性获取每组的Task Completed最小值（最早完成）和Access Completed最大值（最晚完成），这是解决问题的核心。
3. 时间差计算：用聚合后的日期列相减，转换为总秒数后除以3600得到小时数。
4. 格式调整：调整列顺序，将日期转为需求的字符串格式，时间差转为整数匹配预期结果。

预期输出 #

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