如何基于Name和日期范围合并两个Pandas DataFrame？

原始数据 #

我有两个Pandas DataFrame：

import pandas as pd

df = pd.DataFrame({'orderID': [10, 11, 12, 13, 14], 'Sales': [100, 110, 120, 140, 150], 'Name': ['John', "Maria", "Maria", "John", "Cesar"],
                   'Date':['2022-01-08', '2022-02-10', '2022-02-15', '2022-02-05', '2022-05-07']})

df2 = pd.DataFrame({'Negotiation': [100, 110, 121, 134, 141], 'Sales': [100, 110, 120, 140, 150], 'Name': ['John', "Maria", "Maria", "John", "Ricardo"],
                   'Date':['2022-01-01', '2022-01-20', '2022-01-30', '2022-02-01', '2022-09-01']})

需求 #

需要基于Name和日期范围（谈判日期早于等于订单日期）合并两个DataFrame，得到如下目标结果：

df_m = pd.DataFrame({'orderID': [10, 11, 12, 13, 14], 'Sales': [100, 110, 120, 140, 150], 'Name_x': ['John', "Maria", "Maria", "John", "Cesar"],
                   'Date_X':['2022-01-08', '2022-02-10', '2022-02-15', '2022-02-05', '2022-05-07'], 'Negotiation': [100, 110, 121, 134, 'Null'], 'Sales': [100, 110, 120, 140, 'Null'], 'Name_y': ['John', "Maria", "Maria", 'John',  "Null"], 'Date_y':['2022-01-01', '2022-01-20', '2022-01-30', '2022-02-01', 'Null']})

同时要避免出现错误的日期匹配（比如将John的订单匹配到2022-02-01的谈判记录）：

df_m_wrong_date = pd.DataFrame({'orderID': [10, 11, 12, 13, 14], 'Sales': [100, 110, 120, 140, 150], 'Name_x': ['John', "Maria", "Maria", "John", "Cesar"],
                   'Date_X':['2022-01-08', '2022-02-10', '2022-02-15', '2022-02-05', '2022-05-07'], 'Negotiation': [100, 110, 121, 134, 141], 'Sales': [100, 110, 120, 140, 150], 'Name_y': ['John', "Maria", "Maria", 'John',  "Null"], 'Date_y':['2022-02-01', '2022-01-30', '2022-01-20', '2022-01-01', 'Null']})

解决方案 #

步骤1：转换日期类型 #

先将两个DataFrame的Date列转为datetime类型，确保日期比较逻辑正确：

df['Date'] = pd.to_datetime(df['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

步骤2：按Name左连接 #

基于Name进行左连接，保留所有订单记录：

merged = df.merge(df2, on='Name', how='left', suffixes=('_x', '_y'))

步骤3：筛选符合日期范围的记录并保留最近的谈判记录 #

筛选出谈判日期早于等于订单日期的记录，再对每个orderID保留最近的谈判记录（即最大的Date_y）：

# 筛选日期符合条件的记录
filtered = merged[merged['Date_x'] >= merged['Date_y']]

# 按订单ID分组，保留每个订单最近的谈判记录
filtered = filtered.sort_values('Date_y').groupby('orderID').last().reset_index()

步骤4：补全缺失记录并整理格式 #

将筛选结果与原始订单DataFrame合并，补全无匹配谈判记录的条目，最后整理列名并替换缺失值为Null：

# 合并回原始订单数据，补全无匹配的记录
final_df = df.merge(filtered, on=['orderID', 'Sales', 'Name', 'Date'], how='left')

# 整理列名
final_df.columns = ['orderID', 'Sales', 'Name_x', 'Date_X', 'Negotiation', 'Sales_y', 'Name_y', 'Date_y']

# 将NaN替换为'Null'
final_df = final_df.fillna('Null')

最终结果 #

执行上述代码后，得到的final_df即为目标DataFrame，可避免错误的日期匹配。

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