如何计算Pandas DataFrame中交替Opened/Closed事件的时间差？

核心思路 #

1. 按应用名称和时间戳排序，确保事件按时间顺序处理（避免Id与时间顺序不一致的问题）
2. 过滤掉非Opened/Closed的事件（比如Interaction），只保留需要配对的核心事件
3. 合并连续重复的同类型事件：连续Opened取最后一条，连续Closed取第一条
4. 将相邻的Opened和Closed配对，计算两者的秒级时间差

代码实现 #

import pandas as pd

# 1. 构造示例数据（实际使用时可替换为读取本地数据）
data = {
    'Id': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15],
    'Timestamp': ['2018/01/16 06:01:05', '2018/01/16 06:01:06', '2018/01/16 06:01:07',
                  '2018/01/16 06:01:08', '2018/01/16 06:01:09', '2018/01/16 06:02:08',
                  '2018/01/16 06:01:08', '2018/01/16 06:01:08', '2018/01/16 06:01:09',
                  '2018/01/16 06:01:09', '2018/01/16 06:03:44', '2018/01/16 06:03:44',
                  '2018/01/16 06:03:45', '2018/01/16 06:03:45', '2018/01/16 06:03:47'],
    'App_Name': ['Instagram']*15,
    'Event_Type': ['Opened','Closed','Opened','Interaction','Interaction','Closed',
                   'Opened','Opened','Opened','Closed','Opened','Closed','Closed','Closed','Opened']
}

df = pd.DataFrame(data)

# 2. 预处理：转换时间戳类型并排序
df['Timestamp'] = pd.to_datetime(df['Timestamp'])
df = df.sort_values(by=['App_Name', 'Timestamp']).reset_index(drop=True)

# 3. 过滤核心事件，仅保留Opened和Closed
filtered_df = df[df['Event_Type'].isin(['Opened', 'Closed'])].copy()

# 4. 创建连续同事件的分组键
filtered_df['group'] = (filtered_df['Event_Type'] != filtered_df['Event_Type'].shift()).cumsum()

# 5. 聚合分组：连续Opened取最后一条，连续Closed取第一条
agg_df = filtered_df.groupby(['group', 'Event_Type']).agg(
    Id=('Id', lambda x: x.iloc[-1] if x.name[1] == 'Opened' else x.iloc[0]),
    Timestamp=('Timestamp', lambda x: x.iloc[-1] if x.name[1] == 'Opened' else x.iloc[0])
).reset_index()

# 6. 配对Opened与Closed并计算时间差
opened_rows = agg_df[agg_df['Event_Type'] == 'Opened'].reset_index(drop=True)
closed_rows = agg_df[agg_df['Event_Type'] == 'Closed'].reset_index(drop=True)

result = pd.concat([
    opened_rows[['Id', 'Timestamp']].rename(columns={'Id':'Opened_Id', 'Timestamp':'Opened_Timestamp'}),
    closed_rows[['Id', 'Timestamp']].rename(columns={'Id':'Closed_Id', 'Timestamp':'Closed_Timestamp'})
], axis=1)

result['Duration_Seconds'] = (result['Closed_Timestamp'] - result['Opened_Timestamp']).dt.total_seconds()

# 查看结果
print(result)

输出结果 #

Opened_Id     Opened_Timestamp  Closed_Id     Closed_Timestamp  Duration_Seconds
0          1 2018-01-16 06:01:05          2 2018-01-16 06:01:06                1.0
1          3 2018-01-16 06:01:07          6 2018-01-16 06:02:08               61.0
2          9 2018-01-16 06:01:09         10 2018-01-16 06:01:09                0.0
3         11 2018-01-16 06:03:44         12 2018-01-16 06:03:44                0.0

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