Python Pandas中如何基于单列匹配多列实现DataFrame合并

核心逻辑 #

按照ID1→ID2→ID3的优先级逐次匹配，每轮只保留未匹配成功的ID进入下一轮匹配，最后合并所有匹配结果即可。

完整实现代码 #

import pandas as pd
import numpy as np

1. 构造示例数据 #

# 构造table1
table1 = pd.DataFrame({'ID': [11, 22, 33, 44]})
# 构造table2示例，可根据实际业务数据替换
table2 = pd.DataFrame({
    'ID1': [11, 55, 66, 77],
    'ID2': [88, 22, 99, 100],
    'ID3': [101, 102, 33, 103],
    'VAL': ['val1', 'val2', 'val3', 'val4']
})

2. 逐轮匹配实现 #

# 初始化结果存储列表和待匹配ID集合
result_list = []
unmatched = table1.copy()

# 第一轮：优先匹配ID1
merge1 = pd.merge(unmatched, table2, left_on='ID', right_on='ID1', how='left')
matched1 = merge1[merge1['ID1'].notna()].copy()
unmatched = merge1[merge1['ID1'].isna()][['ID']].copy()
result_list.append(matched1)

# 第二轮：ID1未匹配的部分匹配ID2
if not unmatched.empty:
    merge2 = pd.merge(unmatched, table2, left_on='ID', right_on='ID2', how='left')
    matched2 = merge2[merge2['ID2'].notna()].copy()
    unmatched = merge2[merge2['ID2'].isna()][['ID']].copy()
    result_list.append(matched2)

# 第三轮：ID2未匹配的部分匹配ID3
if not unmatched.empty:
    merge3 = pd.merge(unmatched, table2, left_on='ID', right_on='ID3', how='left')
    matched3 = merge3[merge3['ID3'].notna()].copy()
    unmatched = merge3[merge3['ID3'].isna()][['ID']].copy()
    result_list.append(matched3)

# 加入全部未匹配的ID，对应字段补NaN
if not unmatched.empty:
    unmatched[['ID1', 'ID2', 'ID3', 'VAL']] = np.nan
    result_list.append(unmatched)

# 合并所有结果得到最终表
final_df = pd.concat(result_list, ignore_index=True)

3. 结果验证 #

上述示例数据运行后得到的final_df输出如下，完全符合需求规则：

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