所以我有这两个表，我想在其中执行 left join 并过滤 df1 中的 date 列介于两者之间的行df2 中的 from 和 to 列。
注意 第 6 行，它没有最终会导致问题的 ClockInDate。
df1:
Company Resource ClockInDate
0 A ResA 2019-02-09
1 A ResB 2019-02-09
2 A ResC 2019-02-09
3 B ResD 2019-02-09
4 B ResE 2019-02-09
5 B ResF 2019-02-09
6 B ResG NaT
df2:
Company Resource EffectiveFrom EffectiveTo
0 A ResA 2018-01-01 2018-12-31
1 A ResA 2019-01-01 2099-12-31
2 A ResB 2018-01-01 2018-12-31
3 A ResB 2019-01-01 2099-12-31
4 B ResE 2018-01-01 2018-12-31
5 B ResE 2019-01-01 2099-12-31
6 B ResF 2018-01-01 2018-12-31
7 B ResF 2019-01-01 2099-12-31
8 B ResG 2018-01-01 2018-12-31
9 B ResG 2019-01-01 2099-12-31
我想我可以在 pandas 中使用 left merge 来做到这一点，然后应用过滤器。
但它给出了不同的输出。
因此，在 SQL 中，您可以像这样在 ON 子句中包含此过滤器，但这与在 WHERE 子句中加入后包含此过滤器不同:
SELECT t1.company,
t1.resource,
t2.company,
t2.resource,
t1.ClockInDate,
t2.EffectiveFrom,
t2.EffectiveTo
FROM table1 t1
LEFT JOIN table2 t2 ON t1.resource = t2.resource
AND t1.company = t2.company
AND t1.ClockInDate BETWEEN t2.EffectiveFrom AND t2.EffectiveTo
注意部分:AND t1.ClockInDate BETWEEN t2.EffectiveFrom AND t2.EffectiveTo
注意:SQL代码中df1为t1，df2为t2
SQL 输出(这是我的预期输出):
t1.Company t1.Resource t1.ClockInDate t2.EffectiveFrom t2.EffectiveTo
0 A ResA 2019-02-09 2019-01-01 2099-12-31
1 A ResB 2019-02-09 2019-01-01 2099-12-31
2 A ResC NaT NaT NaT
3 B ResD NaT NaT NaT
4 B ResE 2019-02-09 2019-01-01 2099-12-31
5 B ResF 2019-02-09 2019-01-01 2099-12-31
6 B ResG NaT NaT NaT
所以这是我在 Python 中的代码:
Python 输出
df_merge = pd.merge(df1, df2, on=['Company', 'Resource'], how='left')
df_final = df_merge[df_merge.ClockInDate.between(df_merge.EffectiveFrom, df_merge.EffectiveTo) | df_merge.EffectiveFrom.isnull()]
#Output:
Company Resource ClockInDate EffectiveFrom EffectiveTo
1 A ResA 2019-02-09 2019-01-01 2099-12-31
3 A ResB 2019-02-09 2019-01-01 2099-12-31
4 A ResC 2019-02-09 NaT NaT
5 B ResD 2019-02-09 NaT NaT
7 B ResE 2019-02-09 2019-01-01 2099-12-31
9 B ResF 2019-02-09 2019-01-01 2099-12-31
请注意，我的 Python 输出中没有包含资源 ResG 的最后一行。
复制并粘贴代码以重现DataFrames
df1 = pd.DataFrame({'Company':['A', 'A', 'A', 'B', 'B', 'B', 'B'],
'Resource':['ResA', 'ResB','ResC', 'ResD', 'ResE', 'ResF', 'ResG'],
'ClockInDate':['2019-02-09', '2019-02-09', '2019-02-09', '2019-02-09', '2019-02-09', '2019-02-09', '']})
df1['ClockInDate'] = pd.to_datetime(df1.ClockInDate)
df2 = pd.DataFrame({'Company':['A','A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B'],
'Resource':['ResA', 'ResA', 'ResB', 'ResB', 'ResE', 'ResE', 'ResF', 'ResF', 'ResG', 'ResG'],
'EffectiveFrom':['2018-01-01', '2019-01-01', '2018-01-01', '2019-01-01', '2018-01-01', '2019-01-01', '2018-01-01', '2019-01-01', '2018-01-01', '2019-01-01'],
'EffectiveTo':['2018-12-31', '2099-12-31', '2018-12-31', '2099-12-31', '2018-12-31', '2099-12-31', '2018-12-31', '2099-12-31', '2018-12-31', '2099-12-31']})
df2['EffectiveFrom'] = pd.to_datetime(df2.EffectiveFrom)
df2['EffectiveTo'] = pd.to_datetime(df2.EffectiveTo)
请您参考如下方法:
因此，在完成这个项目后，我获得了更多见解。我找到了一个解决方案，但希望有一个更干净 的解决方案。但这可行:我们可以连接原始数据帧中具有 ClockIndate.isnull 的行:
df_merge = pd.merge(df1, df2, on=['Company', 'Resource'], how='left')
df_filter = df_merge[df_merge.ClockInDate.between(df_merge.EffectiveFrom, df_merge.EffectiveTo) | df_merge.EffectiveFrom.isnull()]
df_final = pd.concat([df_filter, df1[df1.ClockInDate.isnull()]], sort=True)
print(df_final)
ClockInDate Company EffectiveFrom EffectiveTo Resource
1 2019-02-09 A 2019-01-01 2099-12-31 ResA
3 2019-02-09 A 2019-01-01 2099-12-31 ResB
4 2019-02-09 A NaT NaT ResC
5 2019-02-09 B NaT NaT ResD
7 2019-02-09 B 2019-01-01 2099-12-31 ResE
9 2019-02-09 B 2019-01-01 2099-12-31 ResF
6 NaT B NaT NaT ResG
