如何在Pandas DataFrame中移除退款(Chargebacks)及其对应正向记录？

我有一个存储会计数据的Pandas DataFrame，不同数据分组里存在负值（也就是退款Chargebacks），需要把这些负值记录和它们对应的正向记录一起从DataFrame里删掉。

示例原始数据 #

import pandas as pd

df = pd.DataFrame({
    "ID": [1,2,3,3,3,4,5,5], 
    "Date": [44958,44958,44959,44959,44959,44959,44988,44988],
    "Amount": [39.99,24.15,15.5,-15.5,15.5,8.99,12.99,-12.99], 
    "Type" : ["Sale","Sale","Sale","Sale","Sale","Sale","Sale","Sale"],
    "Product": ["Pencil","Pencil","Book","Book","Book","Ruler","Pen","Pen"],
    "Tax": [2.7993,1.6905,1.085,-1.085,1.085,0.6293,0.9093,-0.9093],
    "Fee": [0.03999,0.02415,0.0155,-0.0155,0.0155,0.00899,0.01299,-0.01299]
})

期望处理后结果 #

ID  Date  Amount  Type Product     Tax      Fee
0   1  44958   39.99  Sale  Pencil  2.7993  0.03999
1   2  44958   24.15  Sale  Pencil  1.6905  0.02415
2   3  44959   15.50  Sale    Book  1.0850  0.01550
3   4  44959    8.99  Sale   Ruler  0.6293  0.00899

解决方法 #

核心逻辑是：同一笔交易的正向记录和退款记录会共享相同的ID、Date、Product，且金额绝对值一致。我们可以按这些字段分组统计数量，偶数条的分组说明正负刚好抵消（要全删），奇数条的分组说明剩一条未被抵消的正向记录（要保留）。

直接用groupby + transform就能快速实现：

# 按标识字段和金额绝对值分组，计算每组记录数
df['record_count'] = df.groupby(['ID', 'Date', 'Product', df['Amount'].abs()])['Amount'].transform('count')
# 筛选出记录数为奇数的行，删除临时列并重置索引
result = df[df['record_count'] % 2 != 0].drop('record_count', axis=1).reset_index(drop=True)

运行这段代码后，就能得到和期望一致的结果。

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