基于重复键及日期列条件合并Pandas DataFrame

需求规则 #

• 数据集A的Fact date不能晚于数据集B的Action date
• 为每条A数据匹配最接近其Fact date的B数据
• 若A的Fact date晚于B中所有Action date，左合并后对应B表字段留空

示例数据集 #

数据集A #

数据集B #

期望合并结果 #

特殊情况说明 #

若A中Depression的Fact date改为2024-02-18（晚于B中所有Action date），合并后该行的User和Action date字段为空。

解决代码 #

直接用Pandas的merge_asof方法就能实现这个逻辑，它专门用于按最近日期匹配的场景，步骤如下：

import pandas as pd

# 创建示例数据
df_a = pd.DataFrame({
    'ID': [1, 1, 1],
    'Fact': ['Alcohol abuse', 'Anxiety disorder', 'Depression'],
    'Level': ['Specialist', 'Specialist', 'Advisor'],
    'Fact date': ['2024-01-23', '2024-01-23', '2024-02-15']
})

df_b = pd.DataFrame({
    'ID': [1, 1],
    'User': ['AA', 'BB'],
    'Action date': ['2024-01-23', '2024-02-17']
})

# 转换日期列为datetime类型（必须步骤）
df_a['Fact date'] = pd.to_datetime(df_a['Fact date'])
df_b['Action date'] = pd.to_datetime(df_b['Action date'])

# 按分组键和日期排序（merge_asof要求的前置条件）
df_a_sorted = df_a.sort_values(['ID', 'Fact date'])
df_b_sorted = df_b.sort_values(['ID', 'Action date'])

# 执行按日期匹配的合并
merged_df = pd.merge_asof(
    df_a_sorted,
    df_b_sorted,
    left_on='Fact date',
    right_on='Action date',
    by='ID',
    direction='forward',
    allow_exact_matches=True
)

# 恢复原数据的排序（可选，根据需求调整）
merged_df = merged_df.sort_index()

print(merged_df)

代码关键说明 #

1. 日期类型转换：必须把日期列转为datetime格式，否则无法识别日期顺序。
2. 排序要求：merge_asof要求左右数据集都按分组键（ID）和日期列排序，否则匹配逻辑会混乱。
3. 参数作用：
   • by='ID'：限定只在同一ID内匹配数据，避免跨ID关联
   • direction='forward'：查找大于等于左表Fact date的最近Action date，完美契合需求规则
   • 当左表日期晚于右表所有日期时，merge_asof会自动将右表字段设为NaN，无需额外处理特殊情况

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