Pandas处理FIFO库存订单匹配效率低下，如何优化代码性能？

问题背景 #

现有两张业务表：

• packing：库存表，字段包括Item_number（物料编号）、Box_number（箱号）、Inventory_quantity（库存数量）
• orders：订单需求表，字段包括Item_number、Order_number（订单号）、Order_quantity（订单数量）

需通过Item_number关联两张表，按FIFO先进先出规则匹配库存满足订单需求，覆盖以下场景：

• 场景1：订单物料A、数量100，匹配库存后剩余900
• 场景2：订单物料B、数量100，拆分两个箱的库存满足需求，扣减后库存为0
• 场景3：订单物料B、数量50，库存不足时提示短缺
• 场景4：订单物料C、数量100，无对应库存时提示不在清单

原实现代码在数据规模达1万行时速度极慢甚至无响应，需优化性能。

原代码性能瓶颈 #

原代码核心问题：

1. 双重iterrows()逐行迭代：逐行遍历订单和库存，时间复杂度为O(n*m)，数据量大时效率暴跌
2. packing.at[pidx, ...]逐行修改：频繁修改DataFrame单一行数据，触发多次内存重排，严重拖慢速度
3. 重复分组操作：每次处理订单都重新获取分组数据，未复用状态

优化后的实现代码 #

import pandas as pd
import numpy as np

def optimize_fifo_match(orders, packing):
    # 复制原数据避免修改输入
    packing_copy = packing.copy().sort_values(['Item_number', 'Box_number']).reset_index(drop=True)
    orders_copy = orders.copy().reset_index(drop=True)
    
    # 计算库存的累计数量（FIFO顺序）
    packing_copy['cum_inv'] = packing_copy.groupby('Item_number')['Inventory_quantity'].cumsum()
    # 计算每个物料的总库存
    total_inv = packing_copy.groupby('Item_number')['Inventory_quantity'].sum().reset_index(name='total_inv')
    
    # 标记无库存的订单
    orders_with_inv = orders_copy.merge(total_inv, on='Item_number', how='left')
    orders_with_inv['Remark'] = np.where(
        orders_with_inv['total_inv'].isna(),
        'not in the packing list',
        None
    )
    
    # 处理有库存的订单，计算累计需求区间
    has_inv_orders = orders_with_inv[orders_with_inv['total_inv'].notna()].copy()
    has_inv_orders['cum_order'] = has_inv_orders.groupby('Item_number')['Order_quantity'].cumsum()
    has_inv_orders['prev_cum_order'] = has_inv_orders.groupby('Item_number')['cum_order'].shift(fill_value=0)
    
    # 用merge_asof匹配订单需求区间对应的库存箱
    merged = pd.merge_asof(
        has_inv_orders.sort_values(['Item_number', 'cum_order']),
        packing_copy.sort_values(['Item_number', 'cum_inv']),
        by='Item_number',
        left_on='cum_order',
        right_on='cum_inv',
        direction='backward'
    )
    merged = pd.merge_asof(
        merged.sort_values(['Item_number', 'prev_cum_order']),
        packing_copy.sort_values(['Item_number', 'cum_inv']),
        by='Item_number',
        left_on='prev_cum_order',
        right_on='cum_inv',
        direction='forward',
        suffixes=('', '_next')
    )
    
    # 计算每个订单从库存箱中扣减的数量
    def calculate_deduction(row):
        if row['Box_number'] == row['Box_number_next']:
            # 订单完全匹配单个库存箱
            used_qty = min(row['Order_quantity'], row['Inventory_quantity'])
            remaining_qty = row['Inventory_quantity'] - used_qty
            return pd.Series([row['Box_number'], used_qty, remaining_qty])
        else:
            # 订单跨多个库存箱
            first_box_used = row['cum_inv'] - row['prev_cum_order']
            first_box_remaining = row['Inventory_quantity'] - first_box_used
            last_box_used = row['cum_order'] - row['cum_inv_next']
            last_box_remaining = row['Inventory_quantity_next'] - last_box_used
            return pd.Series([
                f"{row['Box_number']},{row['Box_number_next']}",
                f"{first_box_used},{last_box_used}",
                f"{first_box_remaining},{last_box_remaining}"
            ])
    
    merged[['Box_number', 'Used_quantity', 'Remaining_inventory']] = merged.apply(calculate_deduction, axis=1)
    
    # 标记库存不足的订单
    merged['Remark'] = np.where(
        merged['cum_order'] > merged['total_inv'],
        f"Insufficient inventory quantity：{(merged['cum_order'] - merged['total_inv']).astype(int)}",
        merged['Remark']
    )
    
    # 整合最终结果
    result = pd.concat([
        merged[['Item_number', 'Order_number', 'Box_number', 'Used_quantity', 'Remark']],
        orders_with_inv[orders_with_inv['total_inv'].isna()][['Item_number', 'Order_number', 'Box_number', 'Used_quantity', 'Remark']]
    ]).sort_index()
    
    # 批量更新库存表
    update_rows = []
    for _, row in merged.iterrows():
        if pd.notna(row['Box_number']):
            boxes = str(row['Box_number']).split(',')
            remainings = str(row['Remaining_inventory']).split(',')
            for box, remaining in zip(boxes, remainings):
                update_rows.append({
                    'Item_number': row['Item_number'],
                    'Box_number': int(box),
                    'Inventory_quantity': int(remaining)
                })
    update_df = pd.DataFrame(update_rows)
    packing_copy = packing_copy.merge(update_df, on=['Item_number', 'Box_number'], how='left')
    packing_copy['Inventory_quantity'] = packing_copy['Inventory_quantity_y'].fillna(packing_copy['Inventory_quantity_x'])
    packing_copy = packing_copy[['Item_number', 'Box_number', 'Inventory_quantity']]
    
    return result, packing_copy

# 测试数据初始化
orders = pd.DataFrame({
    "Item_number": {0: 'A', 1: 'B',2: 'B',3: 'C'},
    "Order_number": {0: 10001, 1: 10002, 2: 10003, 3: 10004}, 
    "Order_quantity": {0: 100, 1: 100, 2: 50, 3: 100}
})

packing = pd.DataFrame({
    "Item_number": {0: 'A', 1: 'B', 2: 'B'}, 
    "Box_number": {0: 1, 1: 2, 2: 3}, 
    "Inventory_quantity": {0: 1000, 1: 50, 2: 50}
})

# 放大数据规模
orders = pd.concat([orders] * 1500).reset_index(drop=True)
packing = pd.concat([packing] * 1500).reset_index(drop=True)

# 执行匹配
result, updated_packing = optimize_fifo_match(orders, packing)

print("更新后的库存表：")
print(updated_packing.head())
print("\n匹配结果：")
print(result.head())

核心优化说明 #

1. 向量化计算替代逐行迭代：使用cumsum计算累计库存/订单量，结合merge_asof匹配需求区间，将时间复杂度降至O(n log n + m log m)，大幅提升处理速度
2. 批量更新库存：先计算所有库存更新行，再一次性合并更新，避免频繁修改DataFrame单一行的开销
3. 复用分组状态：提前计算物料的累计库存和总库存，避免重复分组操作
4. 高效区间匹配：利用merge_asof的有序匹配特性，精准定位FIFO规则下的库存箱

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