如何用Pandas按多列匹配合并两个Excel文件的DataFrame

嘿，我明白你遇到的麻烦了——三次分别合并确实容易导致列错位、重复数据这类问题，毕竟每次合并都会引入新的列，处理起来很繁琐。咱们换个更优雅的思路，利用**数据重塑（melt）**来简化匹配逻辑，一步到位实现你想要的合并效果。

核心思路 #

既然Data Frame 2的Col_Z是唯一值，且只会匹配Data Frame 1中Col_A/Col_B/Col_C的某一个，那我们可以先把Data Frame 1的这三列“拉长”成一列，和Col_Z做一次匹配，之后再把数据“转宽”回去，和原Data Frame 1合并即可。这样既避免了多次合并的混乱，又能完美保留原索引顺序。

完整代码示例 #

先创建模拟数据方便你复现：

import pandas as pd

# 创建Data Frame 1
df1 = pd.DataFrame({
    'Index': range(1, 101),
    'Col_A': [f'A{i}' for i in range(1, 101)],
    'Col_B': [f'B{i}' for i in range(1, 101)],
    'Col_C': [f'C{i}' for i in range(1, 101)],
    'Col_Q': [f'Q{i}' for i in range(1, 101)]
})

# 创建Data Frame 2（N=5，示例数据）
df2 = pd.DataFrame({
    'Index': range(1, 6),
    'Col_X': [f'XData{i}' for i in range(1, 6)],
    'Col_Y': [f'YData{i}' for i in range(1, 6)],
    'Col_Z': ['B2', 'A5', 'C10', 'B15', 'A20']  # 分别匹配df1的B/A/C/B/A列
})

接下来执行合并步骤：

# 步骤1：将df1的Col_A/Col_B/Col_C转成长格式，保留原Index和Col_Q
df1_melted = df1.melt(
    id_vars=['Index', 'Col_Q'],
    value_vars=['Col_A', 'Col_B', 'Col_C'],
    var_name='Matched_Column',  # 可选：记录匹配到的是哪一列，不需要可以删除
    value_name='Col_Z'  # 把A/B/C的值统一放到Col_Z列，方便和df2匹配
)

# 步骤2：和df2合并，基于Col_Z匹配
merged_temp = pd.merge(
    df1_melted,
    df2.drop(columns=['Index']),  # 去掉df2的Index，避免和df1的Index冲突
    on='Col_Z',
    how='left'  # 保留df1的所有行，匹配不到的用NaN填充
)

# 步骤3：转回宽格式，和原df1合并（完美保留原索引顺序）
df3 = pd.merge(
    df1,
    merged_temp.drop(columns=['Matched_Column', 'Col_Z']),
    on=['Index', 'Col_Q'],
    how='left'
)

# 查看匹配到的结果示例
print(df3.loc[df3['Col_X'].notna()])

代码解释 #

1. melt重塑数据：把df1的三列匹配列转成一列，这样我们只需要做一次合并，而不是三次。同时保留原Index和Col_Q，确保后续能准确合并回去。
2. 单次合并匹配：用Col_Z作为唯一匹配键，和df2合并，因为你提到每个Col_Z仅匹配一个值，所以不会出现重复行或错位问题。
3. 合并回原数据：把临时合并结果和原df1合并，完美保留原df1的索引、列顺序和所有数据，同时把df2的对应数据追加到右侧。

为什么之前的方法出问题？ #

三次分别合并会生成多组Col_X/Col_Y/Col_Z重复列（比如Col_x_x、Col_x_y这类后缀列），而且如果某一行意外匹配了多个列（哪怕你说不会，代码没做限制），会导致行重复、数据错位。而用melt的方法从根源上避免了这个问题，逻辑更清晰，代码也更简洁。

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