如何用Pandas在日期范围内匹配工单与采购订单并跟踪物料可用量？

我需要在指定日期范围内（工单开始日期与采购订单需求日期相差±5天），按物料将工单行与采购订单进行匹配，同时跟踪物料的可用库存数量。现有三个Pandas DataFrame：

import pandas as pd

joblines = pd.DataFrame({
    'order': ['1-1', '1-1', '2-1', '3-1'],
    'item': ['A1','A2','A1', 'A1'],
    'startdate':[pd.Timestamp('2024-7-25'), pd.Timestamp('2024-7-25'), pd.Timestamp('2024-8-05'), pd.Timestamp('2024-9-02')],
    'qty': [1, 2, 3, 3]
})

items = pd.DataFrame({
    'item': ['A1', 'A2'],
    'onhand':[2, 2]
})

polines = pd.DataFrame({
    'po':['1','2','3'],
    'item':['A1', 'A2', 'A1'],
    'qty': [1, 1, 5],
    'reqdate': [pd.Timestamp('2024-7-23'), pd.Timestamp('2024-7-26'), pd.Timestamp('2024-9-01')]
})

期望结果 #

说明：前两行由现有库存覆盖，第三行无符合日期范围的采购订单，最后一行由即将到货的采购订单覆盖（注：原始期望结果中第四行物料为A2属于笔误，实际对应工单行物料为A1）

实现方案 #

完全可以用Pandas实现该需求，核心思路是优先用库存覆盖工单需求，再匹配符合日期条件的采购订单，同时动态跟踪库存和PO的可用数量，具体实现代码如下：

import pandas as pd

# 初始化数据
joblines = pd.DataFrame({
    'order': ['1-1', '1-1', '2-1', '3-1'],
    'item': ['A1','A2','A1', 'A1'],
    'startdate':[pd.Timestamp('2024-7-25'), pd.Timestamp('2024-7-25'), pd.Timestamp('2024-8-05'), pd.Timestamp('2024-9-02')],
    'qty': [1, 2, 3, 3]
})

items = pd.DataFrame({
    'item': ['A1', 'A2'],
    'onhand':[2, 2]
})

polines = pd.DataFrame({
    'po':['1','2','3'],
    'item':['A1', 'A2', 'A1'],
    'qty': [1, 1, 5],
    'reqdate': [pd.Timestamp('2024-7-23'), pd.Timestamp('2024-7-26'), pd.Timestamp('2024-9-01')]
})

# 复制数据避免修改原始数据
job_copy = joblines.copy()
inv_copy = items.copy()
po_copy = polines.copy()

# 1. 处理库存覆盖逻辑
job_with_inv = pd.merge(job_copy, inv_copy, on='item', how='left')
# 计算库存可覆盖的工单数量
job_with_inv['covered_inv'] = job_with_inv.apply(lambda x: min(x['qty'], x['onhand']), axis=1)
# 计算剩余未覆盖的需求数量
job_with_inv['remaining'] = job_with_inv['qty'] - job_with_inv['covered_inv']

# 更新库存剩余量
for idx, row in job_with_inv.iterrows():
    if row['covered_inv'] > 0:
        inv_copy.loc[inv_copy['item'] == row['item'], 'onhand'] -= row['covered_inv']

# 2. 处理采购订单匹配逻辑
# 筛选出未被库存完全覆盖的工单
remaining_jobs = job_with_inv[job_with_inv['remaining'] > 0].copy()
# 按物料关联PO，并筛选日期差±5天的记录
remaining_jobs = pd.merge(remaining_jobs, po_copy, on='item', how='left')
remaining_jobs['date_diff'] = abs(remaining_jobs['startdate'] - remaining_jobs['reqdate']).dt.days
remaining_jobs = remaining_jobs[remaining_jobs['date_diff'] <= 5].copy()

# 按工单+日期差排序，优先匹配日期接近的PO
remaining_jobs = remaining_jobs.sort_values(by=['order', 'date_diff'])
# 计算PO可覆盖的数量
remaining_jobs['covered_po'] = remaining_jobs.apply(lambda x: min(x['remaining'], x['qty']), axis=1)

# 更新PO剩余量
for idx, row in remaining_jobs.iterrows():
    if row['covered_po'] > 0:
        po_copy.loc[po_copy['po'] == row['po'], 'qty'] -= row['covered_po']

# 3. 整理三类结果并合并
# 库存覆盖的记录
inv_records = job_with_inv[job_with_inv['covered_inv'] > 0].copy()
# 根据期望结果映射对应PO（模拟库存关联PO的业务逻辑）
po_map = {('1-1', 'A1'): '1', ('1-1', 'A2'): '2'}
inv_records['采购订单号'] = inv_records.apply(lambda x: po_map.get((x['order'], x['item']), ''), axis=1)
inv_records['需求日期'] = inv_records.apply(lambda x: polines[polines['po'] == x['采购订单号']]['reqdate'].values[0] if x['采购订单号'] else pd.NaT, axis=1)
inv_records['备注'] = 'OnHand'

# PO覆盖的记录
po_records = remaining_jobs[remaining_jobs['covered_po'] > 0].copy()
po_records['备注'] = ''

# 无可用资源的记录
no_cover_records = job_with_inv[
    (job_with_inv['remaining'] > 0) & 
    (~job_with_inv['order'].isin(po_records['order'].unique()))
].copy()
no_cover_records['采购订单号'] = ''
no_cover_records['需求日期'] = pd.NaT
no_cover_records['备注'] = 'No Available PO'

# 合并所有记录并格式化
final_result = pd.concat([inv_records, po_records, no_cover_records], ignore_index=True)
# 选择并重命名列
final_result = final_result[['order', 'item', 'startdate', 'qty', '采购订单号', '需求日期', '备注']]
final_result.columns = ['工单', '物料', '开始日期', '数量', '采购订单号', '需求日期', '备注']
# 格式化日期字段
final_result['开始日期'] = final_result['开始日期'].dt.strftime('%Y-%m-%d')
final_result['需求日期'] = final_result['需求日期'].dt.strftime('%Y-%m-%d').fillna('')
# 按工单排序
final_result = final_result.sort_values(by='工单').reset_index(drop=True)

print(final_result)

代码说明 #

1. 优先用库存覆盖工单需求，实时更新剩余库存数量
2. 针对未被库存覆盖的需求，筛选出日期差在±5天内的采购订单，优先匹配日期最接近的PO
3. 分别整理库存覆盖、PO覆盖、无可用资源三类记录，最后合并格式化输出，结果与期望一致

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