如何优雅实现宽表转长表并合并分析师评分数据集？

需求说明 #

需要将存储分析师姓名的df1与对应评分的df2合并为df_desire格式：保留有分析师姓名的行，对应匹配同编号的评分（评分允许为NaN），最终按公司名称排序。

数据集定义 #

import pandas as pd
import numpy as np

df1 = pd.DataFrame({
    'company name': ['A','B','C'],
    'analyst 1 name': ['Tom','Mike',np.nan],
    'analyst 2 name': [np.nan,'Alice',np.nan],
    'analyst 3 name': ['Jane','Steve','Alex']
})

df2 = pd.DataFrame({
    'company name': ['A','B','C'],
    'score 1': [3,5,np.nan],
    'score 2': [np.nan,1,np.nan],
    'score 3': [6,np.nan,11]
})

# 目标结果
df_desire = pd.DataFrame({
    'company name': ['A','A','B','B','B','C'],
    'analyst': ['Tom','Jane','Mike','Alice','Steve','Alex'],
    'score': [3,6,5,1,np.nan,11]
})

现有实现 #

当前代码通过concat拼接两个melt后的结果，但会产生重复列，需要额外清理：

pd.concat([
    df1.melt(id_vars='company name', value_vars=['analyst 1 name','analyst 2 name','analyst 3 name']),
    df2.melt(id_vars='company name', value_vars=['score 1','score 2','score 3'])
], axis=1)

更优雅的实现方案 #

方案1：利用wide_to_long自动匹配编号 #

借助列名中的数字后缀（1/2/3），wide_to_long可以直接将宽表转为长表并对齐编号，代码简洁且扩展性强（新增分析师/评分列无需修改代码）：

# 将df1转为长表，提取分析师编号
df1_long = pd.wide_to_long(
    df1, 
    stubnames='analyst', 
    i='company name', 
    j='num', 
    sep=' ', 
    suffix='\\d+'
).reset_index()

# 将df2转为长表，提取评分编号
df2_long = pd.wide_to_long(
    df2, 
    stubnames='score', 
    i='company name', 
    j='num', 
    sep=' ', 
    suffix='\\d+'
).reset_index()

# 按公司和编号合并，清理无效行并整理格式
df_desire = pd.merge(df1_long, df2_long, on=['company name', 'num'])
df_desire = (df_desire
             .dropna(subset=['analyst'])  # 过滤无分析师姓名的行
             .drop('num', axis=1)
             .sort_values('company name')
             .reset_index(drop=True))
df_desire.columns = ['company name', 'analyst', 'score']

方案2：melt后提取编号合并 #

如果需要更灵活的列名处理，可先melt再提取编号进行合并：

# 处理df1：melt后提取数字编号
df1_melt = df1.melt(id_vars='company name', var_name='idx', value_name='analyst')
df1_melt['idx'] = df1_melt['idx'].str.extract('(\\d+)')

# 处理df2：同理提取编号
df2_melt = df2.melt(id_vars='company name', var_name='idx', value_name='score')
df2_melt['idx'] = df2_melt['idx'].str.extract('(\\d+)')

# 合并后清理格式
df_desire = pd.merge(df1_melt, df2_melt, on=['company name', 'idx'])
df_desire = (df_desire
             .dropna(subset=['analyst'])
             .drop('idx', axis=1)
             .sort_values('company name')
             .reset_index(drop=True))

两种方案均能直接生成目标格式，避免了重复列的问题，代码更简洁易维护。

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