如何用Pandas/Python识别并删除配对的原始订单与取消订单

一、Pandas高效实现方案 #

核心思路：精准匹配同一用户下，商品、单价一致且数量互为相反数的取消订单与原始订单，解决之前宽泛匹配导致的误匹配问题。

代码实现 #

import pandas as pd

# 假设数据集已读入为df，未读入可执行：df = pd.read_csv('your_dataset.csv')

# 1. 标记并分离取消订单与正常订单
df['is_cancel'] = df['InvoiceNo'].str.startswith('C', na=False)
cancel_orders = df[df['is_cancel']].copy()
normal_orders = df[~df['is_cancel']].copy()

# 2. 构造取消订单的匹配键：取Quantity绝对值，确保和原始订单数量匹配
cancel_orders['match_qty'] = cancel_orders['Quantity'].abs()
match_columns = ['CustomerID', 'StockCode', 'UnitPrice', 'match_qty']

# 3. 精准匹配对应的原始订单
matched_origin_orders = pd.merge(
    normal_orders,
    cancel_orders[match_columns + ['InvoiceNo']],
    left_on=['CustomerID', 'StockCode', 'UnitPrice', 'Quantity'],
    right_on=match_columns,
    how='inner',
    suffixes=('_origin', '_cancel')
)

# 4. 收集所有需删除的订单编号（取消订单+匹配到的原始订单）
to_delete_invoices = set(cancel_orders['InvoiceNo']).union(set(matched_origin_orders['InvoiceNo_origin']))

# 5. 过滤得到有效订单
valid_orders = df[~df['InvoiceNo'].isin(to_delete_invoices)]

# 验证结果
print(f"原记录数：{len(df)} | 删除记录数：{len(df)-len(valid_orders)} | 剩余取消订单数：{valid_orders['is_cancel'].sum()}")

关键说明 #

• 用str.startswith('C')精准标记取消订单，避免误判非取消订单
• 匹配时严格绑定CustomerID，确保是同一用户的订单配对
• 使用集合存储待删除订单编号，大幅提升isin操作的查询效率（适配50w+数据量）
• 仅匹配商品、单价、数量绝对值完全一致的记录，缩小匹配范围，解决之前匹配过多的问题

二、备选优化方案 #

1. 处理重复匹配场景 #

若同一用户同一商品存在多笔订单，可按订单时间匹配最近的原始订单：

# 转换时间字段（假设存在InvoiceDate）
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])

# 按用户、商品、时间排序
normal_sorted = normal_orders.sort_values(['CustomerID', 'StockCode', 'InvoiceDate'])
cancel_sorted = cancel_orders.sort_values(['CustomerID', 'StockCode', 'InvoiceDate'])

# 按时间匹配最近的前置原始订单
matched_origin = pd.merge_asof(
    cancel_sorted,
    normal_sorted,
    on='InvoiceDate',
    by=['CustomerID', 'StockCode', 'UnitPrice'],
    direction='backward',
    tolerance=pd.Timedelta(days=30)  # 限制匹配的时间范围，避免无效配对
)

# 后续收集InvoiceNo并过滤的步骤同上

2. 数据库层面处理（超大数据量场景） #

若数据存储在数据库中，直接用SQL处理可避免内存压力：

WITH cancel_orders AS (
    SELECT 
        CustomerID,
        StockCode,
        UnitPrice,
        ABS(Quantity) AS abs_qty,
        InvoiceNo AS cancel_invoice
    FROM sales_data
    WHERE InvoiceNo LIKE 'C%'
),
matched_origin AS (
    SELECT s.InvoiceNo AS origin_invoice
    FROM sales_data s
    JOIN cancel_orders c
        ON s.CustomerID = c.CustomerID
        AND s.StockCode = c.StockCode
        AND s.UnitPrice = c.UnitPrice
        AND s.Quantity = c.abs_qty
        AND s.InvoiceNo NOT LIKE 'C%'
)
SELECT *
FROM sales_data
WHERE InvoiceNo NOT IN (
    SELECT cancel_invoice FROM cancel_orders
    UNION
    SELECT origin_invoice FROM matched_origin
);

3. 预处理优化 #

• 先过滤缺失CustomerID的记录：取消订单必然对应同一用户，无CustomerID的无法配对，可直接保留或提前过滤
• 对UnitPrice做精度处理：df['UnitPrice'] = df['UnitPrice'].round(2)，避免浮点精度问题导致匹配失败

三、结果验证建议 #

• 检查剩余数据中取消订单数量应为0，确保所有取消订单都已配对删除
• 随机抽取删除的订单对，验证是否为同一用户、同一商品的正负数量订单
• 对比删除前后的记录数，确认删除数量符合预期（约为3679*2，若所有取消订单都找到对应原订单）

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