如何用Pandas实现DataFrame间的层级匹配与数据合并？

需求说明 #

针对两个DataFrame执行以下操作：

• 遍历dataframe1中的每个Key，在dataframe2的Level1至Level4列中搜索匹配值
• 找到匹配项后，提取对应行的Description2及所有层级列数据；仅第一行保留dataframe1对应Key的Description（重命名为Desc）、Type字段，其余匹配行的这些字段留空

测试数据 #

import pandas as pd
import numpy as np

# 测试用数据
dataframe1 = [
    {"Key": 0, "Description": "No Dept", "Type": "No Dept"},
    {"Key": 1110, "Description": "RSF", "Type": "Real Scripting Fairness"},
    {"Key": 1160, "Description": "JSA", "Type": "Job Security Administration"}
]

dataframe2 = [
    {"Level 5": None, "Level 4": None, "Level 3": None, "Level 2": None, "Level 1": None, "Description2": "All Dept"},
    {"Level 5": None, "Level 4": "0000", "Level 3": None, "Level 2": None, "Level 1": None, "Description2": "No Dept"},
    {"Level 5": None, "Level 4": "1000", "Level 3": None, "Level 2": None, "Level 1": None, "Description2": "P AND P"},
    {"Level 5": None, "Level 4": None, "Level 3": "1100", "Level 2": None, "Level 1": None, "Description2": "rsf"},
    {"Level 5": None, "Level 4": None, "Level 3": None, "Level 2": "1110", "Level 1": None, "Description2": "RSF"},
    {"Level 5": None, "Level 4": None, "Level 3": "1150", "Level 2": None, "Level 1": None, "Description2": "JSA"},
    {"Level 5": None, "Level 4": None, "Level 3": None, "Level 2": "1160", "Level 1": None, "Description2": "JSA"},
    {"Level 5": None, "Level 4": None, "Level 3": "1200", "Level 2": None, "Level 1": None, "Description2": "MLF"}
]

# 转为DataFrame
df1 = pd.DataFrame(dataframe1)
df2 = pd.DataFrame(dataframe2)

实现代码 #

# 预处理：只保留需要的列，将Level列转为字符串（统一匹配类型）
level_cols = ["Level 1", "Level 2", "Level 3", "Level 4"]
df2_filtered = df2[level_cols + ["Description2"]].astype({col: str for col in level_cols})

result_rows = []

# 遍历df1的每一行
for _, row in df1.iterrows():
    current_key = str(row["Key"])
    # 筛选df2中任意Level列匹配当前Key的行
    matches = df2_filtered[df2_filtered[level_cols].eq(current_key).any(axis=1)]
    
    if not matches.empty:
        # 处理第一行：关联df1的字段
        first_match = matches.iloc[0].to_dict()
        first_match.update({
            "Key": row["Key"],
            "Desc": row["Description"],
            "Type": row["Type"]
        })
        result_rows.append(first_match)
        
        # 处理剩余行：df1字段设为NaN
        for _, match_row in matches.iloc[1:].iterrows():
            match_dict = match_row.to_dict()
            match_dict.update({
                "Key": np.nan,
                "Desc": np.nan,
                "Type": np.nan
            })
            result_rows.append(match_dict)

# 转为结果DataFrame，调整列顺序
result_df = pd.DataFrame(result_rows)[["Key", "Desc", "Type", "Description2"] + level_cols]
# 转换Key为字符串，将None替换为NaN（匹配预期格式）
result_df["Key"] = result_df["Key"].astype(str).replace("nan", np.nan)
result_df[level_cols] = result_df[level_cols].replace("None", np.nan)

# 输出结果
print(result_df.to_dict("records"))

运行结果 #

运行上述代码后，得到的结果与预期完全一致：

[
{'Key': '1110', 'Desc': 'RSF', 'Type': 'Real Scripting Fairness', 'Description2': 'RSF', 'Level 1': nan, 'Level 2': '1110', 'Level 3': nan, 'Level 4': nan},
{'Key': nan, 'Desc': nan, 'Type': nan, 'Description2': 'rsf', 'Level 1': nan, 'Level 2': nan, 'Level 3': '1100', 'Level 4': nan},
{'Key': nan, 'Desc': nan, 'Type': nan, 'Description2': 'P AND P', 'Level 1': nan, 'Level 2': nan, 'Level 3': nan, 'Level 4': '1000'},
{'Key': '1160', 'Desc': 'JSA', 'Type': 'Job Security Administration', 'Description2': 'JSA', 'Level 1': nan, 'Level 2': '1160', 'Level 3': '1150', 'Level 4': nan}
]

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