Pandas按id提取最早open和最晚closed状态记录的实现方法

结论 #

完全可以通过pandas实现该需求，无需依赖SQL的rank子句即可完成分组取极值的逻辑。

前置准备 #

首先确保你的数据已经完成两项预处理：

• 已将结构化表格加载为pandas DataFrame对象，变量名设为df
• timestamp字段已转换为pandas datetime类型（避免字符串排序出现误差），转换代码参考：

import pandas as pd
df['timestamp'] = pd.to_datetime(df['timestamp'])

实现步骤 #

1. 提取每个id对应的最早open记录

# 筛选open状态数据，按时间升序排序后分组取每组第一行，即为每个id最早的open记录
df_open = df[df['status'] == 'open'].sort_values('timestamp').groupby('id', as_index=False).first()

2. 提取每个id对应的最晚closed记录

# 筛选closed状态数据，按时间降序排序后分组取每组第一行，即为每个id最晚的closed记录
df_closed = df[df['status'] == 'closed'].sort_values('timestamp', ascending=False).groupby('id', as_index=False).first()

3. 合并两部分结果得到最终输出

# 合并两个结果集，按id、时间排序后重置索引即可
result = pd.concat([df_open, df_closed], ignore_index=True).sort_values(['id', 'timestamp']).reset_index(drop=True)

结果验证 #

上述代码运行后完全匹配你给出的示例场景：

• id=abc：匹配到1条open+1条closed记录，共返回2条
• id=xyz：匹配到最早open+唯一closed记录，共返回2条
• id=ijk：匹配到最早open+最晚closed记录，共返回2条
• id=suv：仅匹配到open记录，仅返回1条

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