如何将给定的Pandas DataFrame重排为目标表格结构？

你提供的DataFrame属于复合表头+行索引混乱的结构，直接用pd.melt会因为表头和数据未拆分导致结果不符合预期。下面是分步解决的方法：

步骤1：整理表头与数据拆分 #

原始DataFrame的前两行是列的分组信息（第一行是Entity，第二行是X/Y/Z子类别），第三行及以后是变量（Var1-Var4）的数值。先拆分这两部分：

import pandas as pd

df = pd.DataFrame({
"Unnamed:0": ["Entity","","Var1","Var2","Var3","Var4"],
"Unnamed:1": ["A","X","0.45","0.14","0.16","0.28"],
"Unnamed:2": ["A","Y","0.66","0.55","0.39","0.49"],
"Unnamed:3": ["A","Z","0.3","0.24","0.31","0.13"],
"Unnamed:4": ["B","X","0.22","0.08","0.74","0.41"],
"Unnamed:5": ["B","Y","0.94","0.47","0.17","0.16"],
"Unnamed:6": ["B","Z","0.76","0.4","0.93","0.15"],
"Unnamed:7": ["C","X","0.4","0.76","0.71","0.01"],
"Unnamed:8": ["C","Y","0.86","1","0.26","0.32"],
"Unnamed:9": ["C","Z","0.35","0.1","0.36","0.4"],
})

# 提取前两行作为列的多级标签（Entity + 子类别）
col_tuples = list(zip(df.iloc[0, 1:], df.iloc[1, 1:]))
# 提取变量名作为行索引
var_names = df.iloc[2:, 0].tolist()
# 提取数值数据
data = df.iloc[2:, 1:].values

# 重新构建规整的DataFrame
clean_df = pd.DataFrame(data, index=var_names, columns=pd.MultiIndex.from_tuples(col_tuples, names=['Entity', 'SubCategory']))

步骤2：转换为目标长格式 #

如果目标是长格式表格（包含Entity、SubCategory、Variable、Value四列），可以用stack()和reset_index()完成，比pd.melt更直接：

result = clean_df.stack(level=['Entity', 'SubCategory']).reset_index()
result.columns = ['Variable', 'Entity', 'SubCategory', 'Value']
# 转换数值类型
result['Value'] = result['Value'].astype(float)

最终输出的result核心结构示例：

为什么直接用pd.melt不行？ #

原始DataFrame的列名无意义（Unnamed），且表头信息分散在第一、二行，pd.melt无法自动识别这些复合分组信息，必须先整理出清晰的索引和列标签后再进行转换。

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