如何基于指定列合并Pandas DataFrame并覆盖其余列？

需求：基于指定列（如Name、Gender、Age）匹配两个DataFrame，保留匹配列，用第二个DataFrame的值覆盖第一个DataFrame中匹配行的其余所有列，且无需手动指定所有待覆盖列名。

示例数据 #

import pandas as pd

df1 = pd.DataFrame(columns=["Name", "Gender", "Age", "LastLogin", "LastPurchase"])
df1.loc[0] = ["Bob", "Male", "21", "2023-01-01", "2023-01-01"]
df1.loc[1] = ["Frank", "Male", "22", "2023-02-01", "2023-02-01"]
df1.loc[2] = ["Steve", "Male", "23", "2023-03-01", "2023-03-01"]
df1.loc[3] = ["John", "Male", "24", "2023-04-01", "2023-04-01"]

df2 = pd.DataFrame(columns=["Name", "Gender", "Age", "LastLogin", "LastPurchase"])
df2.loc[0] = ["Steve", "Male", "23", "2022-11-01", "2022-11-02"]
df2.loc[1] = ["Simon", "Male", "23", "2023-03-01", "2023-03-02"]
df2.loc[2] = ["Gary", "Male", "24", "2023-04-01", "2023-04-02"]
df2.loc[3] = ["Bob", "Male", "21", "2022-12-01", "2022-12-01"]

期望结果 #

匹配Name、Gender、Age的行，用df2的非匹配列值覆盖df1，最终输出：

Name Gender Age   LastLogin LastPurchase
0    Bob   Male  21  2022-12-01   2022-12-01
1  Frank   Male  22  2023-02-01   2023-02-01
2  Steve   Male  23  2022-11-01   2022-11-02
3   John   Male  24  2023-04-01   2023-04-01

方法1：利用merge批量处理列（通用且保留原结构） #

通过左连接合并后，批量处理带后缀的临时列，无需手动指定每一列：

# 定义匹配列
match_cols = ["Name", "Gender", "Age"]

# 左连接合并，给重复列添加后缀区分
merged_df = df1.merge(df2, on=match_cols, how='left', suffixes=('_x', '_y'))

# 筛选出所有非匹配列（即需要覆盖的列）
non_match_cols = [col for col in df1.columns if col not in match_cols]

# 批量更新并清理临时列
for col in non_match_cols:
    # 优先用df2的值，无匹配则保留df1原数据
    merged_df[col] = merged_df[f"{col}_y"].fillna(merged_df[f"{col}_x"])
    # 删除合并产生的临时列
    merged_df.drop([f"{col}_x", f"{col}_y"], axis=1, inplace=True)

print(merged_df)

方法2：设置索引后用update（简洁高效，原地修改） #

update方法会直接用df2中匹配索引的行覆盖df1，适合只需要覆盖已有列的场景：

match_cols = ["Name", "Gender", "Age"]

# 将匹配列设为索引，用于对齐数据
df1.set_index(match_cols, inplace=True)
df2.set_index(match_cols, inplace=True)

# 用df2匹配的行更新df1，无匹配则保留原数据
df1.update(df2)

# 重置索引恢复原结构
df1.reset_index(inplace=True)

print(df1)

内容的提问来源于Stack Exchange，提问作者Jak