Python实现原材料库存分析：双数据集匹配逻辑与代码实现求助

核心逻辑说明 #

按MATERIAL/PURCHASE_ORDER/ITEM_NUMBER共同字段分组，每组内：

• 计划交付记录按SCHEDULE_DATE升序排序
• 实际收货记录按RECEIPT_DATE升序排序
• 用双指针法逐笔匹配抵扣：优先耗尽当前计划的剩余量，再处理下一条计划；若当前收货量耗尽，则切换到下一笔收货，最终生成包含抵扣明细、剩余量的输出表格。

完整代码实现 #

1. 导入依赖与示例数据

import pandas as pd

# 模拟计划交付数据集
schedule_data = [
    ["A", "PO001", "ITEM01", "2024-01-05", 100],
    ["A", "PO001", "ITEM01", "2024-01-10", 150],
    ["B", "PO002", "ITEM02", "2024-01-07", 80]
]
df_schedule = pd.DataFrame(
    schedule_data,
    columns=["MATERIAL", "PURCHASE_ORDER", "ITEM_NUMBER", "SCHEDULE_DATE", "QUANTITY"]
)
df_schedule["SCHEDULE_DATE"] = pd.to_datetime(df_schedule["SCHEDULE_DATE"])

# 模拟实际收货数据集
receipt_data = [
    ["A", "PO001", "ITEM01", "2024-01-06", 80],
    ["A", "PO001", "ITEM01", "2024-01-08", 50],
    ["A", "PO001", "ITEM01", "2024-01-12", 120],
    ["B", "PO002", "ITEM02", "2024-01-06", 90]
]
df_receipt = pd.DataFrame(
    receipt_data,
    columns=["MATERIAL", "PURCHASE_ORDER", "ITEM_NUMBER", "RECEIPT_DATE", "QUANTITY"]
)
df_receipt["RECEIPT_DATE"] = pd.to_datetime(df_receipt["RECEIPT_DATE"])

2. 定义分组处理函数

def process_group(schedule_group, receipt_group):
    # 按日期排序计划与收货记录
    schedule_sorted = schedule_group.sort_values("SCHEDULE_DATE").reset_index(drop=True)
    receipt_sorted = receipt_group.sort_values("RECEIPT_DATE").reset_index(drop=True)
    
    output_rows = []
    s_idx = 0  # 计划记录指针
    r_idx = 0  # 收货记录指针
    
    # 复制数量列，避免修改原数据
    schedule_remaining = schedule_sorted["QUANTITY"].copy().tolist()
    receipt_remaining = receipt_sorted["QUANTITY"].copy().tolist()
    
    # 逐笔抵扣核心逻辑
    while s_idx < len(schedule_sorted) and r_idx < len(receipt_sorted):
        current_sched = schedule_sorted.iloc[s_idx]
        current_receipt = receipt_sorted.iloc[r_idx]
        
        # 计算本次可抵扣的数量（取剩余计划量与剩余收货量的最小值）
        deduct_qty = min(schedule_remaining[s_idx], receipt_remaining[r_idx])
        
        # 生成输出行
        output_row = {
            "MATERIAL": current_sched["MATERIAL"],
            "PURCHASE_ORDER": current_sched["PURCHASE_ORDER"],
            "ITEM_NUMBER": current_sched["ITEM_NUMBER"],
            "SCHEDULE_DATE": current_sched["SCHEDULE_DATE"],
            "PLANNED_QTY": current_sched["QUANTITY"],
            "RECEIPT_DATE": current_receipt["RECEIPT_DATE"],
            "RECEIVED_QTY": current_receipt["QUANTITY"],
            "DEDUCT_QTY": deduct_qty,
            "REM_QTY": schedule_remaining[s_idx] - deduct_qty
        }
        output_rows.append(output_row)
        
        # 更新剩余量
        schedule_remaining[s_idx] -= deduct_qty
        receipt_remaining[r_idx] -= deduct_qty
        
        # 指针移动规则：耗尽则切换到下一条记录
        if schedule_remaining[s_idx] == 0:
            s_idx += 1
        if receipt_remaining[r_idx] == 0:
            r_idx += 1
    
    # 处理未完全抵扣的计划记录（收货已耗尽）
    while s_idx < len(schedule_sorted):
        current_sched = schedule_sorted.iloc[s_idx]
        output_rows.append({
            "MATERIAL": current_sched["MATERIAL"],
            "PURCHASE_ORDER": current_sched["PURCHASE_ORDER"],
            "ITEM_NUMBER": current_sched["ITEM_NUMBER"],
            "SCHEDULE_DATE": current_sched["SCHEDULE_DATE"],
            "PLANNED_QTY": current_sched["QUANTITY"],
            "RECEIPT_DATE": None,
            "RECEIVED_QTY": 0,
            "DEDUCT_QTY": 0,
            "REM_QTY": schedule_remaining[s_idx]
        })
        s_idx += 1
    
    # 处理未匹配的收货记录（计划已耗尽）
    while r_idx < len(receipt_sorted):
        current_receipt = receipt_sorted.iloc[r_idx]
        output_rows.append({
            "MATERIAL": current_receipt["MATERIAL"],
            "PURCHASE_ORDER": current_receipt["PURCHASE_ORDER"],
            "ITEM_NUMBER": current_receipt["ITEM_NUMBER"],
            "SCHEDULE_DATE": None,
            "PLANNED_QTY": 0,
            "RECEIPT_DATE": current_receipt["RECEIPT_DATE"],
            "RECEIVED_QTY": current_receipt["QUANTITY"],
            "DEDUCT_QTY": 0,
            "REM_QTY": 0
        })
        r_idx += 1
    
    return pd.DataFrame(output_rows)

3. 分组应用并生成最终结果

# 按共同字段分组
schedule_groups = df_schedule.groupby(["MATERIAL", "PURCHASE_ORDER", "ITEM_NUMBER"])
receipt_groups = df_receipt.groupby(["MATERIAL", "PURCHASE_ORDER", "ITEM_NUMBER"])

# 合并所有分组的处理结果
final_result = []
for key in schedule_groups.groups.keys():
    s_group = schedule_groups.get_group(key)
    # 处理无对应收货记录的分组
    r_group = receipt_groups.get_group(key) if key in receipt_groups.groups else pd.DataFrame(columns=df_receipt.columns)
    processed_df = process_group(s_group, r_group)
    final_result.append(processed_df)

final_df = pd.concat(final_result, ignore_index=True)

# 格式化日期显示（可选）
final_df["SCHEDULE_DATE"] = final_df["SCHEDULE_DATE"].dt.strftime("%Y-%m-%d").fillna("N/A")
final_df["RECEIPT_DATE"] = final_df["RECEIPT_DATE"].dt.strftime("%Y-%m-%d").fillna("N/A")

# 查看结果
print(final_df)

关键细节说明 #

• 双指针法：避免嵌套循环，提升处理效率，适合大规模数据集
• 剩余量维护：通过独立列表实时更新剩余量，不修改原始数据
• 边界处理：覆盖计划未完全抵扣、收货无对应计划等极端场景
• 分组隔离：确保每个物料-订单-物料号的组独立处理，不会跨组混淆

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