基于多条件合并Pandas大型DataFrame的高效解决方案求助

问题需求 #

需要基于以下条件合并两个大型Pandas DataFrame：

• df1.year ≤ df2.year（即df2的生产年份与df1相同或更晚）
• df1.make = df2.make 且 df1.location = df2.location

模拟数据 #

第一个DataFrame（df1） #

import numpy as np
import pandas as pd

data = np.array([[2014,"toyota","california","corolla"], 
                 [2015,"honda"," california", "civic"], 
                 [2020,"hyndai","florida","accent"], 
                 [2017,"nissan","NaN", "sentra"]]) 

df1 = pd.DataFrame(data, columns = ['year', 'make','location','model'])

第二个DataFrame（df2） #

data2 = np.array([[2012,"toyota","california","airbag"], 
                 [2017,"toyota","california", "wheel"], 
                 [2022,"hyndai","newyork","seat"], 
                 [2017,"nissan","london", "light"]]) 

df2 = pd.DataFrame(data2, columns = ['year', 'make','location','id'])

期望输出 #

data3 = np.array([[2017,"toyota",'corolla',"california", "wheel"]]) 

df3 = pd.DataFrame(data3, columns = ['year', 'make','model','location','id'])

过往低效尝试 #

之前采用全量合并后过滤的方式，不仅效率低下，准确性也不足：

df4= pd.merge(df1,df2, on=['location','make'], how='outer')
df4=df4.dropna()
df4['year'] = df4.apply(lambda x : x['year_y'] if x['year_y'] >= x['year_x'] else "0", axis=1)

高效解决方案 #

针对大型DataFrame，全量合并会产生大量冗余数据，推荐以下几种更高效的方案：

方法1：内连接+条件过滤 #

先通过make和location做内连接缩小数据范围，再筛选年份符合要求的行：

# 内连接匹配共同键，减少数据量
merged = pd.merge(df1, df2, on=['make', 'location'], suffixes=('_x', '_y'))
# 过滤年份条件
filtered = merged[merged['year_y'] >= merged['year_x']]
# 提取目标列并重命名
result = filtered[['year_y', 'make', 'model', 'location', 'id']].rename(columns={'year_y': 'year'})

方法2：merge_asof（最优效率方案） #

如果数据可按年份排序，merge_asof能避免笛卡尔积，直接为每行匹配符合条件的最近记录，大幅提升效率：

# 按分组键和年份排序
df1_sorted = df1.sort_values(['make', 'location', 'year'])
df2_sorted = df2.sort_values(['make', 'location', 'year'])

# 按make、location分组，匹配year_y >= year_x的记录
result = pd.merge_asof(
    df1_sorted,
    df2_sorted,
    on='year',
    by=['make', 'location'],
    direction='forward'  # 选取第一个符合年份条件的记录
)

# 过滤未匹配项并整理列
result = result.dropna(subset=['id'])[['year_y', 'make', 'model', 'location', 'id']].rename(columns={'year_y': 'year'})

方法3：query优化过滤 #

若内连接后数据量仍较大，query方法比普通布尔索引的执行效率更高：

merged = pd.merge(df1, df2, on=['make', 'location'], suffixes=('_x', '_y'))
result = merged.query('year_y >= year_x')[['year_y', 'make', 'model', 'location', 'id']].rename(columns={'year_y': 'year'})

额外注意事项 #

• 示例中存在location列带空格、缺失值的情况，会导致匹配失败，建议先做数据清洗：

# 去除空格、替换缺失值标记
df1['location'] = df1['location'].str.strip().replace('NaN', pd.NA)
df2['location'] = df2['location'].str.strip().replace('NaN', pd.NA)

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