Pandas合并日期范围匹配DataFrame：保留不匹配行并填充Null

这是某日期区间匹配问题的后续需求：当交易DataFrame中存在费率DataFrame里无匹配Agentname的行，或交易日期不在对应Agentname+ProductType的费率日期区间内时，需保留这些行并将Agentname_rates列设为Null/NA值。

输入数据 #

费率表（Rates table） #

Agentname ProductType  OldRate  NewRate StartDate   EndDate
0    VSFAAL       SPORTS      0.0     10.0 2020-11-05 2021-01-18
1    VSFAAL      APPAREL      0.0     35.0 2020-11-05 2022-05-03
2    VSFAAL       SPORTS     10.0     15.0 2021-01-18 2022-05-03
3  VSFAALJS       SPORTS      0.0     10.0 2020-11-07 2022-05-03
4  VSFAALJS      APPAREL      0.0     15.0 2020-11-07 2021-11-09
5  VSFAALJS      APPAREL     15.0      5.0 2021-11-09 2022-05-03

交易表（Transactions table） #

Date  Sales Agentname ProductType
0 2020-12-01 08:00:02  100.0    VSFAAL       SPORTS
1 2022-03-01 08:00:09   99.0    VSFAAL      APPAREL
2 2022-03-01 08:00:14   75.0    VSFAAL       SPORTS
3 2021-05-01 08:00:39   67.0  VSFAALJS       SPORTS
4 2020-05-01 08:00:56  160.0  VSFAALJS      APPAREL
5 2021-05-01 08:00:56   65.0  VSFAALJS      APPAREL
6 2021-06-03 09:07:33   55.0  VSRANDOM       SPORTS

原代码问题 #

你提供的代码在合并后直接筛选符合日期条件的行，导致不符合条件（无匹配Agent、日期不在区间内）的行被丢弃，而非保留并设为Null。

解决方案 #

方法1：高效合并筛选法（适合大数据量） #

先统一日期类型，通过左连接保留所有交易行，再筛选日期匹配的费率并映射回原表：

import pandas as pd

# 1. 加载并转换日期格式
# 费率表
rates_data = {
    'Agentname': ['VSFAAL', 'VSFAAL', 'VSFAAL', 'VSFAALJS', 'VSFAALJS', 'VSFAALJS'],
    'ProductType': ['SPORTS', 'APPAREL', 'SPORTS', 'SPORTS', 'APPAREL', 'APPAREL'],
    'OldRate': [0.0, 0.0, 10.0, 0.0, 0.0, 15.0],
    'NewRate': [10.0, 35.0, 15.0, 10.0, 15.0, 5.0],
    'StartDate': ['2020-11-05', '2020-11-05', '2021-01-18', '2020-11-07', '2020-11-07', '2021-11-09'],
    'EndDate': ['2021-01-18', '2022-05-03', '2022-05-03', '2022-05-03', '2021-11-09', '2022-05-03']
}
df_rates = pd.DataFrame(rates_data)
df_rates['StartDate'] = pd.to_datetime(df_rates['StartDate'])
df_rates['EndDate'] = pd.to_datetime(df_rates['EndDate'])

# 交易表
trans_data = {
    'Date': ['2020-12-01 08:00:02', '2022-03-01 08:00:09', '2022-03-01 08:00:14', '2021-05-01 08:00:39', '2020-05-01 08:00:56', '2021-05-01 08:00:56', '2021-06-03 09:07:33'],
    'Sales': [100.0, 99.0, 75.0, 67.0, 160.0, 65.0, 55.0],
    'Agentname': ['VSFAAL', 'VSFAAL', 'VSFAAL', 'VSFAALJS', 'VSFAALJS', 'VSFAALJS', 'VSRANDOM'],
    'ProductType': ['SPORTS', 'APPAREL', 'SPORTS', 'SPORTS', 'APPAREL', 'APPAREL', 'SPORTS']
}
df_trans = pd.DataFrame(trans_data)
df_trans['Date'] = pd.to_datetime(df_trans['Date'])

# 2. 左连接合并，保留所有交易行
merged = df_trans.merge(df_rates, on=['Agentname', 'ProductType'], how='left')

# 3. 筛选交易日期在费率区间内的行
mask = (merged['Date'] >= merged['StartDate']) & (merged['Date'] <= merged['EndDate'])

# 4. 将匹配的费率映射回原交易表，无匹配则为NaN
df_trans['Agentname_rates'] = merged[mask].groupby(merged[mask].index)['NewRate'].first()

# 查看结果
print(df_trans)

方法2：逐行匹配法（适合小数据量，逻辑直观） #

使用apply逐行查找对应Agent和Product的匹配费率：

import pandas as pd

# （省略数据加载和日期转换步骤，同上）

def get_matching_rate(row):
    # 筛选当前行对应的Agent和Product的费率记录
    filtered_rates = df_rates[(df_rates['Agentname'] == row['Agentname']) & 
                              (df_rates['ProductType'] == row['ProductType'])]
    # 找到日期在区间内的费率
    match = filtered_rates[(filtered_rates['StartDate'] <= row['Date']) & 
                           (filtered_rates['EndDate'] >= row['Date'])]
    # 返回匹配的NewRate，无匹配则返回None（自动转为NaN）
    return match['NewRate'].iloc[0] if not match.empty else None

# 生成Agentname_rates列
df_trans['Agentname_rates'] = df_trans.apply(get_matching_rate, axis=1)

# 查看结果
print(df_trans)

期望输出 #

执行后将得到符合要求的结果：

Date  Sales Agentname ProductType  Agentname_rates
0 2020-12-01 08:00:02  100.0    VSFAAL       SPORTS             10.0
1 2022-03-01 08:00:09   99.0    VSFAAL      APPAREL             35.0
2 2022-03-01 08:00:14   75.0    VSFAAL       SPORTS             15.0
3 2021-05-01 08:00:39   67.0  VSFAALJS       SPORTS             10.0
4 2020-05-01 08:00:56  160.0  VSFAALJS      APPAREL              NaN
5 2021-05-01 08:00:56   65.0  VSFAALJS      APPAREL             15.0
6 2021-06-03 09:07:33   55.0  VSRANDOM       SPORTS              NaN

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