Python中合并两个DataFrame并替换指定列值的技术实现

需求说明 #

保留数据集1的所有条目，仅用数据集2中匹配条目的Winner和Outcome Decided列值替换数据集1的对应列。

实现步骤 #

用pandas完成操作，核心是左连接两个数据集，用数据集2的目标列值覆盖数据集1的对应列（仅匹配到条目时生效）。

1. 导入依赖并创建示例数据集

先将你提供的数据集转换成pandas.DataFrame（修正了数据集1的列名排版错误，确保数据结构合法）：

import pandas as pd

# 数据集1
df1 = pd.DataFrame({
    "Date": ["11/30/1872", "03/08/1873", "03/07/1874", "03/06/1875", "03/04/1876"],
    "Home": ["Scotland", "England", "Scotland", "England", "Scotland"],
    "Away": ["England", "Scotland", "England", "Scotland", "England"],
    "Home Score": [0.0, 4.0, 2.0, 2.0, 3.0],
    "Away Score": [0.0, 2.0, 1.0, 2.0, 0.0],
    "Tournament": ["Friendly", "Friendly", "Friendly", "Friendly", "Friendly"],
    "City": ["Glasgow", "London", "Glasgow", "London", "Glasgow"],
    "Country": ["Scotland", "England", "Scotland", "England", "Scotland"],
    "Winner": ["Draw", "England", "Scotland", "Draw", "Scotland"],
    "Outcome Decided": ["Normally", "Normally", "Normally", "Normally", "Normally"]
})

# 数据集2
df2 = pd.DataFrame({
    "Date": ["08/22/1967", "11/14/1971", "05/17/1972", "05/19/1972", "04/21/1973"],
    "Home Team": ["India", "South Korea", "Thailand", "Thailand", "Senegal"],
    "Away Team": ["Taiwan", "Vietnam", "South Korea", "Cambodia", "Ghana"],
    "Winner": ["Taiwan", "Republic of South Korea", "South Korea", "Thailand", "Ghana"],
    "Outcome Decided": ["Penalty Shootouts", "Penalty Shootouts", "Penalty Shootouts", "Penalty Shootouts", "Penalty Shootouts"]
})

2. 确定匹配键

以比赛唯一标识为匹配依据：用Date+主客场队伍（数据集1的Home对应数据集2的Home Team，Away对应Away Team）作为连接键，确保匹配准确。

3. 左连接并更新指定列

# 左连接两个数据集，指定匹配键，给重复列添加后缀区分
merged = df1.merge(
    df2[["Date", "Home Team", "Away Team", "Winner", "Outcome Decided"]],
    left_on=["Date", "Home", "Away"],
    right_on=["Date", "Home Team", "Away Team"],
    how="left",
    suffixes=("", "_new")
)

# 用数据集2的非空值覆盖原列，无匹配则保留原数据
merged["Winner"] = merged["Winner_new"].combine_first(merged["Winner"])
merged["Outcome Decided"] = merged["Outcome Decided_new"].combine_first(merged["Outcome Decided"])

# 删除临时列，得到最终结果
final_df = merged.drop(["Home Team", "Away Team", "Winner_new", "Outcome Decided_new"], axis=1)

# 查看结果
print(final_df)

代码说明

• how="left"确保保留df1的所有行；
• combine_first()优先使用df2的匹配值，无匹配时保留df1原值；
• 最后清理连接产生的临时列，输出干净结果。

注意事项 #

如果仅通过Date匹配，只需修改left_on和right_on为["Date"]即可，实际使用时请根据真实数据的匹配规则调整连接键。

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