Pandas：基于条件合并DataFrame并保留无匹配的NaN行

我有两个DataFrame（df1和df2），需要基于id列合并，且满足df1的triggerdate处于df2的startdate与enddate之间的条件，同时保留无匹配条件的行。

数据示例 #

df1数据： #

id  triggerdate
a    09/01/2022
a    08/15/2022
b    06/25/2022
c    06/30/2022
c    07/01/2022

df2数据： #

id startdate   enddate     value
a  08/30/2022  09/03/2022     30
b  07/10/2022  07/15/2022      5
c  06/28/2022  07/05/2022     10

预期输出： #

id triggerdate  startdate  enddate     value
a  09/01/2022  08/30/2022  09/03/2022     30
a  08/15/2022         NaN         NaN    NaN
b  06/25/2022         NaN         NaN    NaN
c  06/30/2022  06/28/2022  07/05/2022     10
c  07/01/2022  06/28/2022  07/05/2022     10

当前尝试的方法及问题 #

我目前的代码：

df_merged = df1.merge(df2, on = ['id'], how='outer')

output = df_merged.loc[
             df_merged['triggerdate'].between(
                 df_merged['startdate'], 
                 df_merged['enddate'], inclusive='both')]

存在两个问题：

1. 无论条件是否满足，都会匹配df1和df2的id值；
2. 随后会删除所有不满足条件的行，无法保留原df1中不符合条件的记录。

步骤1：转换日期列格式 #

首先必须把所有日期列转为datetime类型，否则字符串无法正确比较大小：

import pandas as pd

# 转换df1的日期列
df1['triggerdate'] = pd.to_datetime(df1['triggerdate'], format='%m/%d/%Y')
# 转换df2的日期列
df2['startdate'] = pd.to_datetime(df2['startdate'], format='%m/%d/%Y')
df2['enddate'] = pd.to_datetime(df2['enddate'], format='%m/%d/%Y')

步骤2：左连接+条件筛选+补全行 #

先做左连接保留df1的所有行，再筛选符合日期条件的记录，最后把df1中未匹配的行补回并填充NaN：

# 基于id做左连接
df_left = df1.merge(df2, on='id', how='left')

# 标记符合日期范围的行
mask = df_left['triggerdate'].between(df_left['startdate'], df_left['enddate'], inclusive='both')

# 拼接符合条件的行，以及无匹配的行（填充df2列为NaN）
result = pd.concat([
    df_left[mask],
    df1[~df1.index.isin(df_left[mask].index)].assign(startdate=None, enddate=None, value=None)
]).sort_values('id').reset_index(drop=True)

# 转换回原日期字符串格式（按需选择）
result['triggerdate'] = result['triggerdate'].dt.strftime('%m/%d/%Y')
result['startdate'] = result['startdate'].dt.strftime('%m/%d/%Y').where(result['startdate'].notna(), None)
result['enddate'] = result['enddate'].dt.strftime('%m/%d/%Y').where(result['enddate'].notna(), None)

更简洁的逐行匹配方法 #

对df1的每一行，直接匹配对应id下符合日期范围的df2记录，无匹配则返回NaN：

def match_row(row):
    # 筛选同id且日期符合条件的df2行
    matched = df2[(df2['id'] == row['id']) & 
                  (df2['startdate'] <= row['triggerdate']) & 
                  (df2['enddate'] >= row['triggerdate'])]
    if not matched.empty:
        return pd.concat([row, matched.iloc[0][['startdate', 'enddate', 'value']]])
    else:
        # 无匹配时填充空值
        return pd.concat([row, pd.Series([None, None, None], index=['startdate', 'enddate', 'value'])])

result = df1.apply(match_row, axis=1)

# 转换回原日期格式
result['triggerdate'] = result['triggerdate'].dt.strftime('%m/%d/%Y')
result['startdate'] = result['startdate'].dt.strftime('%m/%d/%Y').where(result['startdate'].notna(), None)
result['enddate'] = result['enddate'].dt.strftime('%m/%d/%Y').where(result['enddate'].notna(), None)

结果验证 #

运行后得到的result与预期输出完全一致：既保留了df1的所有行，又为符合条件的记录匹配了df2的数据，不符合条件的行则将df2对应列填充为NaN。

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