如何在Pandas中匹配ID并按日期条件从另一DataFrame添加列

示例数据 #

首先创建并初始化两个DataFrame，同时将日期列转换为datetime类型（这是后续匹配的前提）：

import pandas as pd

# 产品价格历史表
price_history = pd.DataFrame({
    'product_id': ['A001', 'A001', 'A002', 'A002', 'A003'],
    'updated': ['2023-01-01', '2023-03-15', '2023-02-01', '2023-04-20', '2023-01-05'],
    'price': [100, 120, 80, 85, 200]
})

# 交易发票表
invoices = pd.DataFrame({
    'invoice_id': ['INV001', 'INV002', 'INV003', 'INV004'],
    'product_id': ['A001', 'A001', 'A002', 'A003'],
    'transaction_date': ['2023-02-01', '2023-04-01', '2023-03-10', '2023-01-01']
})

# 转换日期列为datetime类型
price_history['updated'] = pd.to_datetime(price_history['updated'])
invoices['transaction_date'] = pd.to_datetime(invoices['transaction_date'])

解决方案 #

map方法仅能基于单一键做简单匹配，无法处理日期筛选逻辑。这里推荐使用pd.merge_asof，它专门用于按分组键+最近日期的规则匹配数据，正好符合需求：

1. 先对两个表按product_id和日期列排序（merge_asof要求输入表已按匹配的日期列排序）：

price_history_sorted = price_history.sort_values(by=['product_id', 'updated'])
invoices_sorted = invoices.sort_values(by=['product_id', 'transaction_date'])

2. 执行匹配操作：

result = pd.merge_asof(
    invoices_sorted,
    price_history_sorted,
    by='product_id',  # 按产品ID分组匹配
    left_on='transaction_date',  # 发票表的交易日期
    right_on='updated',  # 价格历史表的更新日期
    direction='backward'  # 筛选规则：找小于等于交易日期的最近更新记录，此为默认值可省略
)

# 整理结果，保留所需字段并按发票ID排序
result = result[['invoice_id', 'product_id', 'transaction_date', 'price']].sort_values('invoice_id').reset_index(drop=True)

预期结果 #

invoice_id product_id transaction_date  price
0     INV001       A001       2023-02-01    100
1     INV002       A001       2023-04-01    120
2     INV003       A002       2023-03-10     80
3     INV004       A003       2023-01-01     NaN

说明：INV004的交易日期早于A003的最早价格更新日期，因此无匹配价格，显示为NaN。若需要填充默认值，可使用result['price'] = result['price'].fillna(0)等方式处理。

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