根据日期范围和值匹配合并熊猫数据框

Question

我希望自动对银行交易进行对账。有两张表，银行表和系统表，其中系统表中的交易延迟了几天。这些表的长度各不相同，并且没有 1:1 匹配的事务。

问题是找到一种一致的方法来识别和分组协调和非协调事务。我遇到的第一个挑战是根据日期范围和数量加入/合并表格。Pandas.merge_asof 适合基于日期范围连接，但仅限于 1 个基于列的连接。

请参见下面的示例表：

bankdf = pd.DataFrame({'BankDate': pd.date_range('2018-12-28', periods=10, freq='3D'), 'Amount': np.array([140,107,132,188,75,152,88,159,132,107])})

systemdf = pd.DataFrame({'SystemCreditDate': pd.date_range('2019-01-04', periods=9, freq='3D'), 'Amount': np.array([107,132,190,75,152,88,110,132,132])})

bankdf

Out[119]: 

   Amount   BankDate

0     140 2018-12-28

1     107 2018-12-31

2     132 2019-01-03

3     188 2019-01-06

4      75 2019-01-09

5     152 2019-01-12

6      88 2019-01-15

7     159 2019-01-18

8     132 2019-01-21

9     107 2019-01-24

systemdf

Out[120]: 

   Amount SystemCreditDate

0     107       2019-01-04

1     132       2019-01-07

2     190       2019-01-10

3      75       2019-01-13

4     152       2019-01-16

5      88       2019-01-19

6     110       2019-01-22

7     132       2019-01-25

8     132       2019-01-28

需要根据“金额”匹配且日期差异小于 6 天（SystemCreditDate - BankDate）< 6）来连接 2 个表。

最终结果应如下所示：

   Amount   BankDate SystemCreditDate

1     107 2018-12-31 2019-01-04

2     132 2019-01-03 2019-01-07

3      75 2019-01-09 2019-01-13

4     152 2019-01-12 2019-01-16

5      88 2019-01-15 2019-01-19

6     132 2019-01-21 2019-01-25

Answer 1

使用DataFrame.merge和删除不符合规则的行：df = bankdf.merge(systemdf)mask = (df['SystemCreditDate']-df['BankDate']).abs().dt.days<6df = df.loc[mask, :]print(df)&nbsp; &nbsp; &nbsp;BankDate&nbsp; Amount SystemCreditDate0&nbsp; 2018-12-31&nbsp; &nbsp; &nbsp;107&nbsp; &nbsp; &nbsp; &nbsp;2019-01-042&nbsp; 2019-01-03&nbsp; &nbsp; &nbsp;132&nbsp; &nbsp; &nbsp; &nbsp;2019-01-076&nbsp; 2019-01-21&nbsp; &nbsp; &nbsp;132&nbsp; &nbsp; &nbsp; &nbsp;2019-01-258&nbsp; 2019-01-09&nbsp; &nbsp; &nbsp; 75&nbsp; &nbsp; &nbsp; &nbsp;2019-01-139&nbsp; 2019-01-12&nbsp; &nbsp; &nbsp;152&nbsp; &nbsp; &nbsp; &nbsp;2019-01-1610 2019-01-15&nbsp; &nbsp; &nbsp; 88&nbsp; &nbsp; &nbsp; &nbsp;2019-01-19或删除负数：df = bankdf.merge(systemdf)mask = (df['SystemCreditDate']-df['BankDate']).dt.daysmask = mask.le(6) & ~mask.lt(0)df = df.loc[mask, :]