基于列值删除Pandas DataFrame中相互抵消的行

当前DataFrame状态 #

Index   PN      PI  PostingDate Batch   LDC     AB      MT  Qty Price
11015   4105409 1   12/30/2022  57424   50322   None    22  1   0.05
11016   4105409 1   12/30/2022  57424   50322   None    11  1   0.05
11017   4105157 1   12/29/2022  11619   19436   None    11  4   0.4
11018   None    N   12/29/2022  18424   None    None    11  1   NaN
11019   4104995 5   12/29/2022  14834   None    None    11  2   0.5
11020   4104758 1   12/29/2022  16899   2020    None    11  3   51.07
11021   None    N   12/29/2022  17602   2130    None    11  1   NaN
11022   4104360 16  12/29/2022  16682   2013    None    11  2   44.82
11023   None    N   12/29/2022  18806   None    None    11  1   NaN
11024   4104941 1   12/29/2022  19038   None    None    11  5   15.61
11025   4104459 1   12/28/2022  17652   2020    None    11  2   15.99
11026   4104380 2   12/28/2022  92993   2037    None    11  1   10900
11027   4104901 1   12/28/2022  96084   None    None    11  70  0.07
11028   4105415 1   12/28/2022  69987   None    None    11  1   324.55
11029   4104430 2   12/28/2022  69293   2028    None    11  4   14.34
11030   4104547 6   12/28/2022  17469   None    None    11  2   0.61
11031   None    N   12/28/2022  89600   1822    None    11  1   NaN
11032   4104019 2   12/28/2022  77134   None    None    11  2   1.25
11033   4104430 3   12/28/2022  46079   2028    None    11  2   18.82
11034   4105002 5   12/28/2022  13798   None    None    11  1   8.9
11035   4104187 2   12/28/2022  31287   None    2039    11  4   417.02
11036   4104434 4   12/28/2022  84718   None    None    11  35  0.08
11037   None    N   12/28/2022  24255   None    None    11  1   NaN
11038   4104893 1   12/28/2022  92590   None    None    11  2   0.02
11039   4104327 1   12/28/2022  67448   NONE    None    11  1   1.23

预期结果 #

计算后需保留除前两行外的所有数据，这两行属于相互抵消的交易。判断依据需结合Price、Batch、Qty字段，同时要考虑MT字段的特殊性——即使MT唯一，只要其他数值和金额字段重复，也需判定为可抵消的交易。

无效脚本 #

以下代码无法实现需求：

cols = ["Batch", "LDC", "Qty"]
out = (df.assign(n=df.groupby(cols+["Price"]).cumcount(), abs=df["Price"].abs())
   .drop_duplicates(subset=cols+['n'], keep=False)
   .drop(columns=['abs', 'n']))

问题说明：cumcount()会给组内第一条记录赋值0，后续重复项赋值1，无法通过这种方式精准识别并删除需要抵消的行。

可行解决方案 #

方案思路 #

要识别相互抵消的交易，需先按Batch、LDC、Qty、Price分组（这些字段是核心匹配项），然后筛选出组内记录数为2且MT为不同值的组（对应前两行的情况），最后保留不在这些组内的所有记录。

代码实现 #

# 定义分组键：核心匹配字段
group_keys = ["Batch", "LDC", "Qty", "Price"]

# 1. 计算每组的记录数，以及组内MT的唯一值数量
group_stats = df.groupby(group_keys).agg(
    count=('MT', 'size'),
    mt_unique=('MT', 'nunique')
).reset_index()

# 2. 筛选出需要删除的组：记录数为2，且MT有2个不同值（即相互抵消的交易对）
to_remove = group_stats[(group_stats['count'] == 2) & (group_stats['mt_unique'] == 2)][group_keys]

# 3. 从原DataFrame中排除这些组的记录
out = df.merge(to_remove, on=group_keys, how='left', indicator=True)
out = out[out['_merge'] != 'both'].drop(columns=['_merge'])

代码说明 #

• 首先通过分组统计，找出那些刚好有2条记录且MT不同的组，这些就是需要抵消的交易对；
• 使用merge配合indicator参数，标记出属于这些组的记录，然后排除它们，剩下的就是符合预期的结果。

内容的提问来源于stack exchange，提问作者bay