基于行依赖列合并两个DataFrame数据的高效实现方案问询

问题背景 #

现有两个Pandas DataFrame：

• df1包含ID、dates、choices列，每行的choices指定需要从df2中提取的列名
• df2包含dates列和多个以choiceX命名的数值列，需根据df1的dates匹配行，再按choices提取对应列的值到df1的新列values中

当前采用逐个choice设置掩码的方法，因数据量大且choice数量超10个，需更简洁高效的实现方式。

原始数据 #

df1：

ID       dates  choices
0  1  2022-07-05  choice1
1  2  2022-07-05  choice2
2  3  2022-07-05  choice2
3  4  2022-01-01  choice2
4  5  2022-07-10  choice7

代码定义：

import pandas as pd

df1 = pd.DataFrame({'ID': ['1', '2', '3','4','5'],
                    'dates': ['2022-07-05', '2022-07-05', '2022-07-05','2022-01-01','2022-07-10'],
                    'choices': ['choice1', 'choice2', 'choice2','choice2','choice7']})

df2：

dates choice1 choice2 choice3 choice7
0  2022-01-01   122.4      34      20      33
1  2022-07-05   111.0   202.1    31.1    31.1
2  2022-07-10   115.6  1000.2    34.2    51.4

代码定义：

df2 = pd.DataFrame({'dates': ['2022-01-01', '2022-07-05', '2022-07-10'],
                    'choice1': ['122.4', '111.0', '115.6'],
                    'choice2': ['34', '202.1', '1000.2'],
                    'choice3': ['20', '31.1', '34.2'],
                    'choice7': ['33', '31.1', '51.4']})

预期结果 #

在df1中新增values列，最终输出如下：

ID       dates  choices values
0  1  2022-07-05  choice1  111.0
1  2  2022-07-05  choice2  202.1
2  3  2022-07-05  choice2  202.1
3  4  2022-01-01  choice2   34.0
4  5  2022-07-10  choice7   51.4

高效解决方案 #

使用pd.melt将df2转为长格式，再通过merge关联df1，无需逐个处理choice，适配任意数量的choice列，性能更优。

步骤1：转换df2为长格式 #

将df2中所有choiceX列转为行，保留dates作为关联键：

# 宽表转长表，指定列名映射
df2_melted = df2.melt(id_vars='dates', var_name='choices', value_name='values')
# 转换values为数值类型（原始数据为字符串，可选但推荐）
df2_melted['values'] = df2_melted['values'].astype(float)

转换后的df2_melted部分数据：

dates  choices  values
0  2022-01-01  choice1   122.4
1  2022-07-05  choice1   111.0
2  2022-07-10  choice1   115.6
3  2022-01-01  choice2    34.0
...

步骤2：合并df1与转换后的df2 #

通过dates和choices双键进行左连接，直接得到目标列：

df1_final = df1.merge(df2_melted, on=['dates', 'choices'], how='left')

验证结果 #

执行代码后，df1_final输出与预期完全一致：

ID       dates  choices  values
0  1  2022-07-05  choice1   111.0
1  2  2022-07-05  choice2   202.1
2  3  2022-07-05  choice2   202.1
3  4  2022-01-01  choice2    34.0
4  5  2022-07-10  choice7    51.4

方案优势 #

• 无需手动枚举所有choice列，适配任意数量的choice类型
• 利用Pandas内置向量化操作，比循环/掩码方法性能提升明显，适合大数据量场景
• 代码简洁易维护，逻辑清晰

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