事件时长计算优化：修正Pandas代码以实现预期输出

我有一个事件列表（注：列表无需按日期排序，可包含多个ID），需要计算每个事件的持续时长。计算规则如下：

事件1的时长 = 同一ID下（事件2的时间戳 - 事件1的时间戳）

示例：

Cleaning = ID1234 (06.11.2023 14:29 - 06.11.2023 14:19) = 10分钟

End of Work代表结束，无需计算其时长。我尝试了以下Pandas代码，但计算结果不符合预期，如何修改以得到期望输出？

示例DataFrame代码 #

import pandas as pd

# Sample DataFrame
data = {
    'Timestamp': ['06.11.2023 14:19', '06.11.2023 14:29', '06.11.2023 14:37', '06.11.2023 14:41', '06.11.2023 15:00'],
    'Event Date': ['06.11.2023', '06.11.2023', '06.11.2023', '06.11.2023', '06.11.2023'],
    'ID': ['1234', '1234', '1234', '1234', '1234'],
    'Event Category': ['Working Event', 'Working Event', 'Working Event', 'Failure Event', 'Working Event'],
    'What did you do?': ['Cleaning', 'Recording', 'Insert', pd.NA, 'End of Work']
}


df = pd.DataFrame(data)

df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='%d.%m.%Y %H:%M')

df = df.sort_values(by=['ID', 'Event Category', 'Timestamp'])

df['Duration'] = df.groupby(['ID', 'Event Category'])['Timestamp'].diff().dt.seconds.div(60, fill_value=0)

print(df)

原始数据 #

Timestamp  Event Date    ID Event Category What did you do?
0  06.11.2023 14:19  06.11.2023  1234  Working Event         Cleaning
1  06.11.2023 14:29  06.11.2023  1234  Working Event        Recording
2  06.11.2023 14:37  06.11.2023  1234  Working Event           Insert
3  06.11.2023 14:41  06.11.2023  1234  Failure Event             <NA>
4  06.11.2023 15:00  06.11.2023  1234  Working Event      End of Work

当前输出 #

Timestamp  Event Date    ID Event Category What did you do?  Duration
3 2023-11-06 14:41:00  06.11.2023  1234  Failure Event             <NA>       0.0
0 2023-11-06 14:19:00  06.11.2023  1234  Working Event         Cleaning       0.0
1 2023-11-06 14:29:00  06.11.2023  1234  Working Event        Recording      10.0
2 2023-11-06 14:37:00  06.11.2023  1234  Working Event           Insert       8.0
4 2023-11-06 15:00:00  06.11.2023  1234  Working Event      End of Work      23.0

期望输出 #

Timestamp  Event Date    ID Event Category What did you do?   Duration
0  06.11.2023 14:19  06.11.2023  1234  Working Event         Cleaning   10
1  06.11.2023 14:29  06.11.2023  1234  Working Event        Recording   8
2  06.11.2023 14:37  06.11.2023  1234  Working Event           Insert   4
3  06.11.2023 14:41  06.11.2023  1234  Failure Event             <NA>   19
4  06.11.2023 15:00  06.11.2023  1234  Working Event      End of Work   -

原代码核心问题：

• 排序逻辑错误：按Event Category排序打乱了事件的时间顺序，导致时长计算基准混乱
• 分组计算错误：按ID+Event Category分组，无法跨类别计算事件时长；且diff()取的是当前行与上一行的差值，不符合“下一行减当前行”的规则

修正后的代码如下：

import pandas as pd

# Sample DataFrame
data = {
    'Timestamp': ['06.11.2023 14:19', '06.11.2023 14:29', '06.11.2023 14:37', '06.11.2023 14:41', '06.11.2023 15:00'],
    'Event Date': ['06.11.2023', '06.11.2023', '06.11.2023', '06.11.2023', '06.11.2023'],
    'ID': ['1234', '1234', '1234', '1234', '1234'],
    'Event Category': ['Working Event', 'Working Event', 'Working Event', 'Failure Event', 'Working Event'],
    'What did you do?': ['Cleaning', 'Recording', 'Insert', pd.NA, 'End of Work']
}

df = pd.DataFrame(data)

# 转换时间戳为datetime类型
df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='%d.%m.%Y %H:%M')

# 保存原始索引，用于恢复输出顺序
df['original_index'] = df.index

# 按ID和时间戳排序，确保同一ID下事件按时间先后排列
df_sorted = df.sort_values(by=['ID', 'Timestamp'])

# 计算时长：同一ID下，下一个事件时间戳减当前时间戳，转成分钟
df_sorted['Duration'] = (df_sorted.groupby('ID')['Timestamp'].shift(-1) - df_sorted['Timestamp']).dt.total_seconds() / 60

# 将End of Work的时长设为'-'
df_sorted.loc[df_sorted['What did you do?'] == 'End of Work', 'Duration'] = '-'

# 恢复原始顺序并删除临时索引列
df = df_sorted.sort_values('original_index').drop('original_index', axis=1)

# 将浮点型时长转为整数，匹配期望输出格式
df['Duration'] = df['Duration'].apply(lambda x: int(x) if isinstance(x, float) else x)

print(df)

代码关键说明 #

1. 保存原始索引：避免排序后打乱用户期望的输出顺序，计算完成后可恢复原顺序
2. 正确排序：仅按ID和Timestamp排序，保证同一ID内事件的时间连续性
3. 时长计算逻辑：用shift(-1)获取同一ID下的下一个事件时间戳，减去当前时间戳后转成分钟（total_seconds()可兼容跨小时/天的场景）
4. 结束事件处理：直接将End of Work对应的时长设为'-'
5. 格式统一：将浮点型时长转为整数，与期望输出格式一致

运行结果 #

Timestamp Event Date    ID Event Category What did you do? Duration
0 2023-11-06 14:19:00  06.11.2023  1234  Working Event         Cleaning       10
1 2023-11-06 14:29:00  06.11.2023  1234  Working Event        Recording        8
2 2023-11-06 14:37:00  06.11.2023  1234  Working Event           Insert        4
3 2023-11-06 14:41:00  06.11.2023  1234  Failure Event             <NA>       19
4 2023-11-06 15:00:00  06.11.2023  1234  Working Event      End of Work        -

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