基于退款金额抵消规则移除DataFrame对应行的实现需求

原始数据生成代码 #

import pandas as pd

test = {"number": ['1333','1444','1555','1666','1777', '1444', '1999', '2000', '2000'],
        "order_amount": ['1000.00','-500.00','100.00','200.00','-200.00','-500.00','150.00','-100.00','-20.00'],
        "number_of_refund": ['','1333','', '', '1666', '1333', '','1999','1999']
        }
df = pd.DataFrame(test)

原始DataFrame #

number order_amount number_of_refund
0   1333      1000.00                 
1   1444      -500.00             1333
2   1555       100.00                 
3   1666       200.00                 
4   1777      -200.00             1666
5   1444      -500.00             1333
6   1999       150.00                 
7   2000      -100.00             1999
8   2000       -20.00             1999

需求 #

若某订单的number值出现在number_of_refund列中，且对应所有退款记录的金额总和恰好抵消该订单的金额，则移除该订单及对应退款记录，保留未被完全抵消的记录。

解决方案代码 #

# 将金额列转为数值类型，便于计算
df['order_amount'] = df['order_amount'].astype(float)

# 获取所有被退款的订单编号
refund_targets = df[df['number_of_refund'] != '']['number_of_refund'].unique()

# 计算每个被退款订单的总退款金额（取绝对值，因为退款为负数）
refund_totals = df[df['number_of_refund'].isin(refund_targets)]\
                .groupby('number_of_refund')['order_amount'].sum().abs()

# 筛选出需要移除的订单及对应退款记录的索引
to_remove = []
for target in refund_targets:
    # 获取原订单的金额
    original_amount = df[df['number'] == target]['order_amount'].iloc[0]
    # 若原订单金额等于总退款额，说明完全抵消，需要移除
    if original_amount == refund_totals[target]:
        # 添加原订单行索引
        to_remove.extend(df[df['number'] == target].index.tolist())
        # 添加对应退款行索引
        to_remove.extend(df[df['number_of_refund'] == target].index.tolist())

# 去重后移除对应行，并重置索引
final_df = df.drop(list(set(to_remove))).reset_index(drop=True)

print(final_df)

最终结果 #

number  order_amount number_of_refund
0   1555         100.0                 
1   1999         150.0                 
2   2000        -100.0             1999
3   2000         -20.0             1999

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