python - 根据日期范围和值匹配合并熊猫数据框
问题描述
我希望自动对银行交易进行对账。有两张表,银行表和系统表,其中系统表中的交易延迟了几天。这些表的长度各不相同,并且没有 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
解决方案
使用DataFrame.merge
和删除不符合规则的行:
df = bankdf.merge(systemdf)
mask = (df['SystemCreditDate']-df['BankDate']).abs().dt.days<6
df = df.loc[mask, :]
print(df)
BankDate Amount SystemCreditDate
0 2018-12-31 107 2019-01-04
2 2019-01-03 132 2019-01-07
6 2019-01-21 132 2019-01-25
8 2019-01-09 75 2019-01-13
9 2019-01-12 152 2019-01-16
10 2019-01-15 88 2019-01-19
或删除负数:
df = bankdf.merge(systemdf)
mask = (df['SystemCreditDate']-df['BankDate']).dt.days
mask = mask.le(6) & ~mask.lt(0)
df = df.loc[mask, :]
推荐阅读
- python - 使用 Python 根据两个 CSV 文件的列值过滤 CSV 文件中的行
- android - 如何使用数据绑定调用静态方法?
- networking - 查找collectd客户端的IP地址
- azure - Bot Builder:如何在 sharepoint 中使用 OAuth 2 隐式授权流方法创建机器人
- r - R:如何随机创建相等的变量块?
- html - 左侧和右侧具有固定宽度列的引导行
- sql - SQL SERVER:我想在删除第一列之前将我的数据传输到同一张表的另一列
- tablesorter - Tablesorter 计算数据上的自定义方程
- jquery - 再一:TypeError: Cannot read path of undefined
- sql - Oracle查询显示计数和开始,序列结束