python - 使用唯一键和条件合并两个 Pandas 数据框

Question

我有两个数据框，第一个:

KEY DATE               VALUE
A   01/01/2020         1
A   05/01/2020         3
B   01/02/2020         1
C   05/02/2020         1

第二个数据框:

KEY    START_DATE     END_DATE     VALUE2
A      01/01/2020     03/01/2020   3
B      01/03/2020     06/03/2020   4
C      01/02/2020     07/02/2020   5

我想通过 KEY 合并这两个，但条件是第一个数据帧的 DATE 包含在第二个数据帧的窗口 START_DATE - END_DATE 中。

预期结果:

KEY   DATE          START_DATE    END_DATE     VALUE     VALUE2
A     01/01/2020    01/01/2020    03/01/2020   1         3
A     05/01/2020    NAT           NAT          1         NAN
B     01/02/2020    NAT           NAT          1         NAN
C     05/02/2020    01/02/2020    07/01/2020   1         5

我试过这个，但是如果 KEY 在第二个数据帧中有多个条目，这个方法会非常消耗内存:

df = df1.merge(df2, on="KEY", how="left")
idx = (df.DATE >= df.START_DATE) & (df.DATE <= df.END_DATE)
df = df[idx] 

Answer 1

如果所有 3 个“DATE...”列都是 string 类型，则从将它们转换为日期时间:

df1.DATE = pd.to_datetime(df1.DATE, dayfirst=True)
df2.START_DATE = pd.to_datetime(df2.START_DATE, dayfirst=True)
df2.END_DATE = pd.to_datetime(df2.END_DATE, dayfirst=True)

那么，一种可能的解决方案是:

• 合并 A 列上的两个 DataFrame，现在忽略第二个要求(和重新排序列)，
• 在 DATE 超出要求范围的行中，设置 NaN 三感兴趣的列。

实现的代码是:

res = df1.merge(df2, on='KEY', how="left")\
    [['KEY', 'DATE', 'START_DATE', 'END_DATE', 'VALUE', 'VALUE2']]
msk = res.DATE.between(res.START_DATE, res.END_DATE)
res[['START_DATE', 'END_DATE', 'VALUE2']] = res[['START_DATE', 'END_DATE', 'VALUE2']]\
   .where(msk)