如何将透视格式的Python DataFrame转为标准行列格式？pd.melt使用遇阻

你的问题核心是需要将带结构化前缀的列名（如a_1_1）拆分、筛选后重塑数据格式，单纯用pd.melt只能完成第一步列转行，还需要结合列名拆分和 pivot 操作来达到目标。

步骤1：构造原始DataFrame #

先把你给出的数据转换成可操作的 pandas DataFrame：

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'id': [1, 2],
    'a_1_1': [10, 33],
    'a_1_2': [20, 34],
    'a_1_3': [30, 35],
    'a_1_4': [40, 36],
    'b_1_1': [90, np.nan],
    'b_1_2': [80, np.nan],
    'b_1_3': [70, np.nan],
    'c_1_1': [np.nan, 11],
    'c_1_2': [np.nan, 12],
    'c_1_3': [np.nan, 13]
})

方法一：先Melt再Pivot（更直观） #

1. 用melt将所有指标列转为行，保留id作为标识：

melted = df.melt(id_vars='id', var_name='col_full', value_name='value')

2. 拆分完整列名，提取col_name（a/b/c）和目标列序号（1/2/3）：

melted[['col_name', '_', 'num']] = melted['col_full'].str.split('_', expand=True)

3. 过滤掉不需要的num=4的行，以及值为NaN的行：

melted = melted[(melted['num'] != '4') & (~melted['value'].isna())]

4. 用pivot将序号转为列，还原成目标宽格式：

result = melted.pivot(index=['id', 'col_name'], columns='num', values='value').reset_index()
# 调整列顺序与目标一致
result = result[['id', 'col_name', '1', '2', '3']]

方法二：用多层索引堆叠 #

1. 筛选掉num=4的列（如a_1_4）：

cols = df.columns.drop('id')
col_info = cols.str.split('_', expand=True)
filtered_cols = cols[col_info[2] != '4']
df_filtered = df[['id'] + filtered_cols.tolist()]

2. 将列名转为多层索引（col_name和num）：

df_filtered.columns = pd.MultiIndex.from_tuples(
    [('id', '')] + [(col_info.loc[c,0], col_info.loc[c,2]) for c in filtered_cols]
)

3. 堆叠col_name层索引，再过滤空行：

result = df_filtered.stack(level=0).reset_index()
result = result.rename(columns={'level_1': 'col_name'}).drop(columns=['1'])
result = result.dropna(subset=['1', '2', '3'])[['id', 'col_name', '1', '2', '3']]

最终输出 #

运行任意一种方法后，都会得到你要的目标格式：

id col_name   1   2   3
0   1        a  10  20  30
1   1        b  90  80  70
2   2        a  33  34  35
3   2        c  11  12  13

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