如何用df1作为查找表为df2填充RESULT列（保留所有行）

我有两个DataFrame：df1（查找表）和df2（主表），数据如下：

df1（查找表） #

group_id       date     value
0    105716  1/30/2019    Soccer
1    105717  1/30/2019  Football
2    105718  1/30/2019      Rest
3    105719  1/30/2019    Soccer
4    105716  1/31/2019      Rest
5    105717  1/31/2019      Rest
6    105718  02/01/2019  Football
7    105719  02/01/2019    Soccer
8    105719  02/02/2019    Tennis
8    105722  02/03/2019    Tennis

df2（主表） #

GROUP_ID  STARTDATE    ENDDATE
0     105716  1/30/2019  1/30/2019
1     105717  1/30/2019  1/30/2019
2     105718  1/30/2019  1/30/2019
3     105719  1/30/2019  1/30/2019
4     105716  1/30/2019  1/31/2019
5     105717  1/31/2019  1/31/2019
6     105718  1/31/2019  1/31/2019
7     105719  1/31/2019  1/31/2019
8     105716  1/31/2019  1/31/2019
9     105717  1/31/2019  1/31/2019
10    105718  1/31/2019  1/31/2019
11    105719  1/31/2019   2/1/2019
12    105716   2/1/2019   2/1/2019
13    105717   2/1/2019   2/1/2019
14    105718   2/1/2019   2/1/2019
15    105719   2/1/2019   2/1/2019
16    105716   2/1/2019   2/1/2019
17    105717   2/1/2019   2/1/2019
18    105718   2/1/2019   2/1/2019
19    105719   2/1/2019   2/1/2019
20    105716   2/1/2019   2/2/2019
21    105717   2/2/2019   2/2/2019
22    105718   2/2/2019   2/2/2019
23    105719   2/2/2019   2/2/2019
24    105716   2/2/2019   2/2/2019
25    105717   2/2/2019   2/2/2019
26    105718   2/2/2019   2/2/2019
27    105719   2/2/2019   2/3/2019
28    105716   2/3/2019   2/3/2019
29    105722   2/3/2019   2/3/2019

目标输出 #

给df2添加RESULT列，满足：

• 当df2.GROUP_ID等于df1.group_id，且df1.date落在df2.STARTDATE和df2.ENDDATE之间时，填充df1.value
• 保留df2所有行，不满足条件的填充'None'

预期输出示例：

GROUP_ID  STARTDATE    ENDDATE    VALUE
0     105716  1/30/2019  1/30/2019    Soccer
1     105717  1/30/2019  1/30/2019    Football
2     105718  1/30/2019  1/30/2019    Rest
3     105719  1/30/2019  1/30/2019    Soccer
4     105716  1/30/2019  1/31/2019    Rest
5     105717  1/31/2019  1/31/2019    Rest
6     105718  1/31/2019  1/31/2019    None
...
29    105722   2/3/2019   2/3/2019    Tennis

尝试过的方法及问题 #

1. numpy.where方法：

df2['RESULT'] = 'None'
df2.result = np.where(((df1.group_id==df2.GROUP_ID)&((df1.date>=df2.STARTDATE)&(df1.date>=df2.ENDDATE))), df1.value, 'None')

报错：ValueError: Can only compare identically-labeled DataFrame objects

2. 向量化索引：

df2.result = df1.value[(df1.group_id==df2.GROUP_ID)&((df1.date>=df2.STARTDATE)&(df1.date>=df2.ENDDATE))]

同样报上述错误。

3. merge方法：

df_activity = pd.merge(df2, df1, left_on='GROUP_ID', right_on='group_id')[((pd.merge(df2, df1, left_on='GROUP_ID', right_on='group_id')['STARTDATE'] <= pd.merge(df2, df1, left_on='GROUP_ID', right_on='group_id').date)&(pd.merge(df2, df1, left_on='GROUP_ID', right_on='group_id')['ENDDATE'] >= pd.merge(df2, df1, left_on='GROUP_ID', right_on='group_id')['date']))]

可行但会丢失不匹配的行，虽可二次merge修复，但希望更高效简洁的实现。

步骤1：统一日期格式为datetime类型 #

先把所有日期列转为datetime，避免字符串比较的逻辑错误：

import pandas as pd

# 转换日期列
df1['date'] = pd.to_datetime(df1['date'])
df2['STARTDATE'] = pd.to_datetime(df2['STARTDATE'])
df2['ENDDATE'] = pd.to_datetime(df2['ENDDATE'])

步骤2：左连接+条件过滤+空值填充 #

通过左连接保留df2所有行，再筛选符合日期条件的记录，最后填充空值：

# 左连接保留df2所有行
merged = pd.merge(df2, df1, left_on='GROUP_ID', right_on='group_id', how='left')

# 筛选日期在区间内的记录，不符合的设为NaN
merged['RESULT'] = merged['value'].where(
    (merged['date'] >= merged['STARTDATE']) & (merged['date'] <= merged['ENDDATE']),
    None
)

# 把NaN替换为'None'，清理多余列
df2['RESULT'] = merged['RESULT'].fillna('None')
df2 = df2.drop(columns=['group_id', 'date', 'value'], errors='ignore')

高效优化方案：使用merge_asof（适用于有序日期） #

如果df1和df2的日期是有序的，用merge_asof能大幅提升大数据集的处理效率：

# 先按group_id和日期排序
df1_sorted = df1.sort_values(['group_id', 'date'])
df2_sorted = df2.sort_values(['GROUP_ID', 'STARTDATE'])

# 按group_id匹配，取df1.date <= df2.ENDDATE的最近记录
merged = pd.merge_asof(
    df2_sorted,
    df1_sorted,
    left_on='ENDDATE',
    right_on='date',
    by='GROUP_ID',
    direction='backward'
)

# 过滤date >= STARTDATE的有效匹配，其余设为'None'
merged['RESULT'] = merged['value'].where(merged['date'] >= merged['STARTDATE'], 'None')

# 恢复原df2的顺序
df2['RESULT'] = merged.set_index(df2.index)['RESULT'].fillna('None')

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