如何用Python Pandas根据日期匹配订单对应实际成本价

我在分析电商平台销售数据时，需要根据成本价的变更日期，为每笔订单匹配对应的实际成本价，规则如下：

• 订单日期早于成本价变更日期时，使用变更前的成本价；订单日期晚于或等于变更日期时，使用变更后的成本价
• 若成本价表中price_change_date列为空，直接使用cost_price列的值
• 可按需重构成本价表

示例说明 #

• 3月12日收到A商品订单，当时成本价为100美元；
• 3月20日将A商品成本价上调10美元，变为110美元；
• 3月25日再次收到A商品订单；
• 制作3月销售报表时，3月12日的订单成本价应为100美元，3月25日的订单成本价应为110美元。

现有数据表结构 #

订单表

import pandas as pd

orders_test = {
    'order_id': ['82514913-0085-1', '82514913-0085-2', '82514913-0090-3', '82514913-0090-4'],
    'article_id': ['99823WH2MO11093_34', '99823WH2MO11093_34', '99823WH2MO11093_34', '99823WH2MO11093_34'],
    'profit': [1042.91, 1042.91, 2000, 2000],
    'order_date': ['01-15-2024', '01-21-2024', '02-15-2024', '02-20-2024']
}
orders_test = pd.DataFrame(orders_test)
orders_test['order_date'] = pd.to_datetime(orders_test['order_date'])

成本价表

cost_price_test = {
    'article_id': ['99823WH2MO11093_34', '99823WH2MO11093_34', '99823WH2MO11093_34'],
    'cost_price': [100, 110, 120],
    'price_change_date': ['01-10-2024', '01-20-2024', '02-10-2024'],
    'new_cost_price': [110, 120, 100]
}
cost_price_test = pd.DataFrame(cost_price_test)
cost_price_test['price_change_date'] = pd.to_datetime(cost_price_test['price_change_date'])

期望结果 #

order_id          article_id   profit order_date  cost_price
0  82514913-0085-1  99823WH2MO11093_34  1042.91 2024-01-15         100
1  82514913-0085-2  99823WH2MO11093_34  1042.91 2024-01-21         120
2  82514913-0090-3  99823WH2MO11093_34  2000.00 2024-02-15         100
3  82514913-0090-4  99823WH2MO11093_34  2000.00 2024-02-20         100

解决方案 #

核心思路是先重构成本价表，生成每个成本价的生效时间区间，再通过订单日期与区间的匹配来获取对应成本价。

步骤1：重构成本价表，生成生效区间

# 按商品ID和变更日期排序
cost_price_sorted = cost_price_test.sort_values(['article_id', 'price_change_date']).reset_index(drop=True)

price_ranges = []
for idx, row in cost_price_sorted.iterrows():
    article_id = row['article_id']
    
    # 添加初始价格区间：变更前的价格，生效至变更日期前一天
    if idx == 0:
        price_ranges.append({
            'article_id': article_id,
            'start_date': pd.Timestamp('1900-01-01'),
            'end_date': row['price_change_date'] - pd.Timedelta(days=1),
            'effective_cost': row['cost_price']
        })
    
    # 添加变更后价格的生效区间：从变更日期开始，至下一次变更前一天（最后一次则设为远期日期）
    next_end_date = (cost_price_sorted.loc[idx+1, 'price_change_date'] - pd.Timedelta(days=1)) if idx < len(cost_price_sorted)-1 else pd.Timestamp('2100-01-01')
    price_ranges.append({
        'article_id': article_id,
        'start_date': row['price_change_date'],
        'end_date': next_end_date,
        'effective_cost': row['new_cost_price']
    })

# 处理price_change_date为空的情况（永久生效的价格）
null_change_records = cost_price_test[cost_price_test['price_change_date'].isna()]
for _, row in null_change_records.iterrows():
    price_ranges.append({
        'article_id': row['article_id'],
        'start_date': pd.Timestamp('1900-01-01'),
        'end_date': pd.Timestamp('2100-01-01'),
        'effective_cost': row['cost_price']
    })

# 转换为DataFrame
price_ranges_df = pd.DataFrame(price_ranges)

步骤2：匹配订单与成本价

# 按商品ID合并订单表和价格区间表
merged = orders_test.merge(price_ranges_df, on='article_id', how='left')

# 筛选订单日期落在对应价格区间内的记录
result = merged[(merged['order_date'] >= merged['start_date']) & (merged['order_date'] <= merged['end_date'])]

# 整理结果列并重置索引
result = result[['order_id', 'article_id', 'profit', 'order_date', 'effective_cost']].rename(columns={'effective_cost': 'cost_price'})
result = result.reset_index(drop=True)

# 输出结果
print(result)

运行上述代码后，即可得到与期望一致的结果。

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