如何使用pandas实现多列数值范围匹配的两个DataFrame关联查询

需求说明 #

我们需要基于两个字段的区间匹配规则，关联两个DataFrame并映射指定字段值，具体场景如下：

输入数据示例 #

df1（区间规则表） #

price_start  price_end  year_start  year_end  score
         10         50        2001      2005     20
         60        100        2001      2005     50
         10         50        2006      2010     30

df2（待匹配数据表） #

Price  year
   10  2001
   70  2002
   50  2010

匹配规则 #

• df2的Price值落在df1的[price_start, price_end]闭区间内
• df2的year值落在df1的[year_start, year_end]闭区间内
• 同时满足以上两个条件的，将df1对应的score字段映射到df2中

预期输出 #

price  year  score
   10  2001     20
   70  2002     50
   50  2010     30

实现代码 #

提供两种常用实现方式，适配不同数据量场景：

方式1：交叉连接后过滤（适合小数据量场景） #

import pandas as pd

# 构造示例数据
df1 = pd.DataFrame({
    'price_start': [10, 60, 10],
    'price_end': [50, 100, 50],
    'year_start': [2001, 2001, 2006],
    'year_end': [2005, 2005, 2010],
    'score': [20, 50, 30]
})
df2 = pd.DataFrame({
    'Price': [10, 70, 50],
    'year': [2001, 2002, 2010]
})

# 交叉连接两个表
df_cross = df2.merge(df1, how='cross')

# 过滤符合区间条件的记录
result = df_cross[
    (df_cross['Price'].between(df_cross['price_start'], df_cross['price_end'])) &
    (df_cross['year'].between(df_cross['year_start'], df_cross['year_end']))
]
# 保留需要的字段并重命名
result = result[['Price', 'year', 'score']].rename(columns={'Price':'price'})
print(result)

方式2：apply逐行匹配（适合df2数据量不大的场景） #

def match_score(row):
    mask = (
        (df1['price_start'] <= row['Price']) & (df1['price_end'] >= row['Price']) &
        (df1['year_start'] <= row['year']) & (df1['year_end'] >= row['year'])
    )
    # 取第一个匹配的score值，多匹配场景可根据业务需求调整逻辑
    return df1.loc[mask, 'score'].iloc[0] if mask.any() else None

df2['score'] = df2.apply(match_score, axis=1)
df2 = df2.rename(columns={'Price':'price'})
print(df2)

补充说明 #

如果数据量达到十万级以上，可使用numpy广播机制或者pandasql库通过SQL的between条件关联优化性能；如果存在多匹配结果的情况，需要根据业务需求增加排序、取最值等去重逻辑。

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