如何合并df1与df2生成含特征及对应取值的目标DataFrame？

现有两个pandas DataFrame：df1和df2，需要生成df_new，该DataFrame包含df1中的feat0、feat1、feat2列，以及df2中对应特征的取值（分别命名为value_feat0、value_feat1、value_feat2），预期输出结果已给出。尝试用melt()函数与merge方法实现但未成功，求可行解决方案。

df1的定义：

import pandas as pd

df1 = pd.DataFrame([["Age", "Education-Num", "Sex"],
                    ["Sex","Education-Num","Age"],
                    ["Education-Num","Age","Sex"],
                    ["Age", "Education-Num", "Sex"],
                    ["Age", "Education-Num", "Sex"]],
                   columns=["feat0", "feat1", "feat2"])

df2的定义：

df2 = pd.DataFrame([[39.0, 7, 13.0, 4, 1, 0, 4, 1, 2174.0, 0.0, 40.0, 39],
                    [50.0, 6, 13.0, 2, 4, 4, 4, 1, 0.0, 0.0, 13.0, 39],
                    [38.0, 4, 9.0, 0, 6, 0, 4, 1, 0.0, 0.0, 40.0, 39],
                    [53.0, 4, 7.0, 2, 6, 4, 2, 1, 0.0, 0.0, 40.0, 39],
                    [28.0, 4, 13.0, 2, 10, 5, 2, 0, 0.0, 0.0, 40.0, 5]],
                   columns=["Age", "Workclass", "EducationNum", "MaritalStatus", "Occupation",
                            "Relationship", "Race", "Sex", "CapitalGain", "CapitalLoss",  
                            "Hoursperweek", "Country"])

预期输出df_new：

df_new = pd.DataFrame([["Age", 39, "EducationNum", 13, "Sex", 1],
                       ["Sex",1, "EducationNum",13, "Age", 50],
                       ["EducationNum",9, "Age",38, "Sex", 1],
                       ["Age", 38, "EducationNum", 7, "Sex", 1],
                       ["Age", 28, "EducationNum", 13, "Sex", 0]],
                      columns=["feat0", "value_feat0", "feat1", "value_feat1", "feat2", "value_feat2"])

核心问题是先对齐df1和df2的特征名称（df1中的Education-Num对应df2的EducationNum），再逐行匹配df2中对应特征的取值。具体实现代码如下：

import pandas as pd

# 1. 初始化df_new，复制df1的基础列
df_new = df1.copy()

# 2. 对齐特征名称：将df1中的"Education-Num"替换为"EducationNum"，和df2列名匹配
df_new = df_new.replace("Education-Num", "EducationNum")

# 3. 生成每个特征对应的value列
for feat_col in ["feat0", "feat1", "feat2"]:
    # 遍历每行，根据当前feat列的特征名，从df2对应列取该行的值
    df_new[f"value_{feat_col}"] = [df2.loc[row_idx, feat_name] for row_idx, feat_name in enumerate(df_new[feat_col])]

# 4. 将数值列转换为整数类型（和预期输出格式一致）
value_cols = ["value_feat0", "value_feat1", "value_feat2"]
df_new[value_cols] = df_new[value_cols].astype(int)

# 5. 调整列顺序至预期格式
df_new = df_new[["feat0", "value_feat0", "feat1", "value_feat1", "feat2", "value_feat2"]]

运行上述代码后，df_new的结构和取值就会和预期输出完全一致。

补充说明 #

• 特征名称对齐是关键前提，否则无法从df2中正确匹配到对应列；
• 列表推导式逐行取值的方式简单直接，避免了melt+merge可能带来的行索引混乱问题；
• 数值类型转换是为了匹配预期输出的整数格式，若不需要可以省略该步骤。

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