python - 在左连接中使用类似 SQL 的连接和过滤器(之间)合并表

Question

所以我有这两个表，我想在其中执行 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)

Answer 1

因此，在完成这个项目后，我获得了更多见解。我找到了一个解决方案，但希望有一个更干净 的解决方案。但这可行:我们可以连接原始数据帧中具有 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