基于列名转置含_1/_2/_3/_4后缀的Pandas DataFrame

问题场景 #

现有一个Pandas DataFrame，需要批量转置所有带_1、_2、_3、_4后缀的列（实际数据包含上百个此类列）。示例数据如下：

import pandas as pd

data = {
        # ID列
        'id':[1],
        
        # 其他固定列
        'c1':['1c'],
        'c2':['2c'],
        'c3':['3c'],
        
        # OC指标
        'oc_1':[1],
        'oc_2':[0],
        'oc_3':[1],
        'oc_4':[1],
        
        # GC指标
        'gc_1':['T1'],
        'gc_2':['T2'],
        'gc_3':['T3'],
        'gc_4':['T4'],
        
        # PF指标
        'pf_1':['PF1'],
        'pf_2':['PF2'],
        'pf_3':['PF3'],
        'pf_4':['PF4'],
        
        # 数值列
        'V1_1':[11],
        'V1_2':[12],
        'V1_3':[13],
        'V1_4':[14],
        
        'S1_1':[21],
        'S1_2':[22],
        'S1_3':[23],
        'S1_4':[24]
}
    
df = pd.DataFrame(data)

用户尝试的代码（未达预期） #

用户使用pd.melt尝试转置，但结果不符合需求：

standard_cols = ['id','c1','c2','c3']
value_cols = ['V1_1','V1_2','V1_3','V1_4','S1_1','S1_2','S1_3','S1_4']
result_cols = standard_cols+['OC','GC','PF','Var','value']

melted_df = pd.melt(df, id_vars=standard_cols + ['oc_1','oc_2','oc_3','oc_4','gc_1','gc_2','gc_3','gc_4','pf_1','pf_2','pf_3','pf_4'],
                    value_vars=value_cols,var_name='Var',value_name='value')

print(melted_df)

正确实现方案 #

核心思路是先拆分指标列和数值列，分别按后缀序号转置，最后合并结果：

import pandas as pd

# 定义固定保留的列
standard_cols = ['id', 'c1', 'c2', 'c3']

# 1. 处理OC/GC/PF指标列，转成按序号分组的结构
indicator_cols = [col for col in df.columns if col.startswith(('oc_', 'gc_', 'pf_'))]
df_indicators = df.melt(id_vars=standard_cols, value_vars=indicator_cols, var_name='indicator_num', value_name='value')
# 拆分列名为指标名和序号
df_indicators[['indicator', 'num']] = df_indicators['indicator_num'].str.split('_', expand=True)
# 透视得到每个序号对应的OC/GC/PF值
df_indicators = df_indicators.pivot(index=standard_cols + ['num'], columns='indicator', values='value').reset_index()
df_indicators = df_indicators.rename(columns={'oc': 'OC', 'gc': 'GC', 'pf': 'PF'})

# 2. 处理数值列（V1/S1等），转成Var和value的结构
value_cols = [col for col in df.columns if col.startswith(('V1_', 'S1_'))]
df_values = df.melt(id_vars=standard_cols, value_vars=value_cols, var_name='var_num', value_name='value')
# 拆分列名为变量名和序号
df_values[['Var', 'num']] = df_values['var_num'].str.split('_', expand=True)

# 3. 合并两个结果集，得到最终格式
final_df = pd.merge(df_indicators, df_values, on=standard_cols + ['num']).drop(columns='num')

# 调整列顺序为预期的顺序
final_df = final_df[standard_cols + ['OC', 'GC', 'PF', 'Var', 'value']]
print(final_df)

最终输出效果 #

id  c1  c2  c3  OC  GC   PF Var  value
0   1  1c  2c  3c   1  T1  PF1  V1     11
1   1  1c  2c  3c   1  T1  PF1  S1     21
2   1  1c  2c  3c   0  T2  PF2  V1     12
3   1  1c  2c  3c   0  T2  PF2  S1     22
4   1  1c  2c  3c   1  T3  PF3  V1     13
5   1  1c  2c  3c   1  T3  PF3  S1     23
6   1  1c  2c  3c   1  T4  PF4  V1     14
7   1  1c  2c  3c   1  T4  PF4  S1     24

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