基于起止日期及匹配字段合并两个DataFrame的问题

需要合并列车出发详情表（data_df）与票价表（fares_df），合并规则如下：

• 精确匹配Origin、Dest、j_type、bucket四个字段
• 列车的dep_date必须落在票价的start_date与end_date区间内（包含两端）

数据源 #

列车出发详情表：

import pandas as pd

data_df = pd.DataFrame(columns=['Train', 'Origin', 'Dest', 'j_type', 'bucket', 'dep_date'],
             data = [['AB001', 'NZM', 'JBP', 'OP', 'S1', '2022-12-27'],
                     ['AB001', 'NZM', 'JBP', 'SP', 'S1', '2023-01-02'],
                     ['AB001', 'NZM', 'JBP', 'OP', 'S1', '2023-01-05'],
                     ['AB002', 'NZM', 'JBP', 'SP', 'S1', '2022-12-21'],
                     ['AB002', 'NZM', 'JBP', 'SP', 'S1', '2023-05-21'],
                     ['AB003', 'NZM', 'RKP', 'OP', 'S2', '2023-01-07'],
                     ['AB012', 'NZM', 'JBP', 'OP', 'S2', '2023-02-07'],
                    ]
            )

票价表：

fares_df = pd.DataFrame(columns=['Origin', 'Dest', 'j_type', 'bucket', 'start_date', 'end_date', 'fare'],
             data = [['NZM', 'JBP', 'OP', 'S1', '2022-01-01', '2022-12-31', 200],
                     ['NZM', 'JBP', 'SP', 'S1', '2023-01-01', '2023-12-31', 400],
                     ['NZM', 'JBP', 'OP', 'S1', '2023-01-01', '2022-01-31', 205],
                     ['NZM', 'JBP', 'OP', 'S1', '2023-01-31', '2023-12-31', 210],
                     ['NZM', 'JBP', 'OP', 'S2', '2023-01-31', '2023-12-31', 215]]
            )

原代码问题分析 #

你尝试的方法存在以下问题：

1. 未将日期字段转换为datetime类型，直接用字符串比较日期可能出现逻辑错误
2. 先单独匹配日期区间，再合并其他字段，会导致日期匹配的结果可能对应到字段不匹配的票价记录
3. 未过滤票价表中无效的日期区间（第三行start_date > end_date），这部分记录永远无法匹配成功

正确实现方式 #

方法一：Merge+布尔过滤（小数据量首选）

先按匹配字段做左连接，再过滤符合日期区间的行，逻辑清晰易维护：

# 转换所有日期字段为datetime类型
data_df['dep_date'] = pd.to_datetime(data_df['dep_date'])
fares_df['start_date'] = pd.to_datetime(fares_df['start_date'])
fares_df['end_date'] = pd.to_datetime(fares_df['end_date'])

# 提前过滤票价表中无效的日期区间
fares_df = fares_df[fares_df['start_date'] <= fares_df['end_date']]

# 按匹配字段合并，再筛选日期条件
merged_df = pd.merge(data_df, fares_df, on=['Origin', 'Dest', 'j_type', 'bucket'], how='left')
merged_df = merged_df[(merged_df['dep_date'] >= merged_df['start_date']) & 
                      (merged_df['dep_date'] <= merged_df['end_date'])]

# 查看结果
print(merged_df)

方法二：Numpy广播匹配（大数据量更高效）

利用numpy的广播特性直接匹配所有条件，减少中间计算步骤：

import numpy as np

# 日期转换与无效区间过滤（同方法一）
data_df['dep_date'] = pd.to_datetime(data_df['dep_date'])
fares_df['start_date'] = pd.to_datetime(fares_df['start_date'])
fares_df['end_date'] = pd.to_datetime(fares_df['end_date'])
fares_df = fares_df[fares_df['start_date'] <= fares_df['end_date']]

# 将匹配字段转换为元组数组，用于快速比较
data_keys = data_df[['Origin', 'Dest', 'j_type', 'bucket']].apply(tuple, axis=1).values
fare_keys = fares_df[['Origin', 'Dest', 'j_type', 'bucket']].apply(tuple, axis=1).values

# 广播生成匹配掩码：字段匹配 + 日期在区间内
match_mask = (data_keys[:, None] == fare_keys) & \
             (data_df['dep_date'].values[:, None] >= fares_df['start_date'].values) & \
             (data_df['dep_date'].values[:, None] <= fares_df['end_date'].values)

# 获取所有匹配的索引对
i, j = np.where(match_mask)

# 拼接匹配后的结果
merged_df = pd.concat([data_df.iloc[i].reset_index(drop=True), 
                       fares_df.iloc[j].reset_index(drop=True)], axis=1)

# 查看结果
print(merged_df)

输出结果说明 #

两种方法都会得到正确的合并结果：

• AB001 2022-12-27匹配到票价200
• AB001 2023-01-02匹配到票价400
• AB002 2023-05-21匹配到票价400
• AB012 2023-02-07匹配到票价215
• 其余记录无匹配（字段不匹配或日期不在有效区间）

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