基于两列匹配行为状态对并计算活动总时长的技术需求

核心逻辑 #

• 按behaviour字段分组：空白值统一归为一组，"其他浏览器"单独成组，组内仅匹配同类型的START与STOP记录
• 解决无序问题：对每组内的START、STOP分别按时间排序后一一配对；若存在同一行为多次启停的嵌套场景，用计数法标记配对序号（每出现一次START计数+1，STOP计数-1，相同计数的START和STOP为一对）

Excel实现步骤 #

假设数据列：A=时间，B=behaviour，C=status

1. 统一分组标识：添加辅助列D，公式=IF(ISBLANK(B2),"空白组",B2)，将空白behaviour归为统一组
2. 标记配对序号：添加辅助列E，公式=COUNTIFS($D$2:D2,D2,$C$2:C2,"START")，统计同组内当前行之前的START数量，同序号的START和STOP为一对
3. 匹配对应STOP时间：添加辅助列F，公式=XLOOKUP(1,($D$2:$D$100=D2)*($C$2:$C$100="STOP")*($E$2:$E$100=E2),$A$2:$A$100,"")，定位同组同序号的STOP时间
4. 计算时长：添加辅助列G，公式=IF(F2<>"",F2-A2,"")，得到单条活动的时长

Python（Pandas）实现代码 #

import pandas as pd

# 读取数据（替换为你的数据源路径）
df = pd.read_excel("your_data.xlsx")
# 统一处理空白behaviour
df["behaviour"] = df["behaviour"].fillna("空白组")
# 确保时间列为可计算的日期时间格式
df["time"] = pd.to_datetime(df["time"])

result_list = []
# 按behaviour分组处理
for behaviour, group_df in df.groupby("behaviour"):
    # 分离START和STOP并按时间排序
    start_records = group_df[group_df["status"] == "START"].sort_values("time").reset_index(drop=True)
    stop_records = group_df[group_df["status"] == "STOP"].sort_values("time").reset_index(drop=True)
    # 配对并计算时长
    paired_df = pd.concat([start_records, stop_records.add_suffix("_stop")], axis=1)
    paired_df["duration"] = paired_df["time_stop"] - paired_df["time"]
    result_list.append(paired_df)

# 合并最终结果
final_result = pd.concat(result_list, ignore_index=True)
# 输出关键信息列
print(final_result[["behaviour", "time", "time_stop", "duration"]])

注意事项 #

• 若存在START与STOP数量不匹配的情况，需额外处理未配对的孤立记录
• 时间列必须转为日期时间格式，否则无法生成有效的时长差值

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