Pandas如何按日期规则筛选保留每个ID分组的目标行

前置处理 #

首先确保两列日期为datetime类型，避免字符串日期比较逻辑错误：

import pandas as pd

# 构造示例数据
df = pd.DataFrame([
    ["A", "2022-01-01", "2022-01-31", 1],
    ["A", "2022-01-15", "2022-01-31", 2],
    ["A", "2022-01-28", "2022-01-31", 3],
    ["B", "2022-01-15", "2022-01-31", 1],
    ["B", "2022-01-28", "2022-01-31", 2],
    ["B", "2022-01-31", "2022-01-31", 3],
    ["C", "2022-01-01", "2022-01-31", 1],
    ["C", "2022-01-27", "2022-01-31", 2],
    ["C", "2022-02-01", "2022-01-31", 3],
    ["C", "2022-02-03", "2022-01-31", 4],
    ["D", "2022-06-03", "2022-01-31", 1],
], columns=["ID", "Record Date", "Cutoff Date", "Value"])

# 转换日期格式
df["Record Date"] = pd.to_datetime(df["Record Date"])
df["Cutoff Date"] = pd.to_datetime(df["Cutoff Date"])

实现逻辑 #

按照规则优先级逐组判断即可，单条记录的分组会自动适配，无需单独处理：

1. 优先匹配Record Date与Cutoff Date相等的行
2. 其次筛选所有晚于Cutoff Date的记录，取其中日期最早的行
3. 剩余场景为所有记录早于Cutoff Date，取其中日期最晚（距离截止日最近）的行

对应实现代码：

def pick_target_row(group):
    cutoff = group["Cutoff Date"].iloc[0]
    # 匹配等于截止日的行
    equal_match = group[group["Record Date"] == cutoff]
    if not equal_match.empty:
        return equal_match.iloc[0]
    # 匹配晚于截止日的最早记录
    later_match = group[group["Record Date"] > cutoff]
    if not later_match.empty:
        return later_match.sort_values("Record Date").iloc[0]
    # 剩余为全早于截止日的场景，取最晚记录
    return group.sort_values("Record Date").iloc[-1]

result = df.groupby("ID", as_index=False).apply(pick_target_row)

结果验证 #

运行后得到的result完全符合预期输出：

注：如果存在同一ID对应多个不同Cutoff Date的场景，只需要将分组键从"ID"改为["ID", "Cutoff Date"]即可直接适配。

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