如何用Pandas处理多索引表头并转换为长格式？

现有包含信用利差的Excel文件，表头为合并单元格形成的多级结构，示例结构如下：

USD All Sectors A  USD All Sectors A  USD All Sectors A
Level  Date        1D                7D                14D
0      05/30/2023  66.42             66.42             66.42
0      05/31/2023  65.95             65.95             65.95

需要将其转换为如下长格式：

DATO        INDEKS            TENOR  NIVEAU
05/30/2023  USD All Sectors A  1D     66.42
05/31/2023  USD All Sectors A  1D     65.95
05/30/2023  USD All Sectors A  7D     66.42
05/31/2023  USD All Sectors A  7D     65.95
05/30/2023  USD All Sectors A  14D    66.42
05/31/2023  USD All Sectors A  14D    65.95

尝试了以下代码，输出不符合预期，问题出在多级表头处理不当：

import pandas as pd

# Load the data from the Excel file
data = pd.read_excel('credit_spreads.xlsx', header=[0, 1], skiprows=12)

df_stacked = df.stack(level=1).reset_index()
print(df_stacked.head(10))

疑问：stack是否是正确的函数？还是应该用melt或pivot？

核心问题分析 #

你的Excel表头中，前两列（Level、Date）的第一级表头为空（合并单元格导致），后续列的第一级是指数名称（INDEKS）、第二级是期限（TENOR）。直接用stack会因为前两列的多级索引结构混乱导致结果错误，需要先分离元数据列和数据列，再进行格式转换。

方法一：使用stack（贴合多级表头场景） #

import pandas as pd

# 读取Excel，指定两行作为多级表头
df = pd.read_excel('credit_spreads.xlsx', header=[0, 1], skiprows=12)

# 给前两列的第一级表头统一命名，方便分离
df.columns = df.columns.set_levels(['Meta', 'Meta'] + list(df.columns.levels[0][2:]), level=0)

# 提取元数据列（Level、Date）和数据列
meta_df = df['Meta'].reset_index(drop=True)
data_df = df.drop('Meta', axis=1)

# 将数据列的第二级表头（TENOR）转为行，得到长格式
stacked_data = data_df.stack(level=1).reset_index()

# 重命名列并合并元数据
stacked_data.columns = ['行索引', 'INDEKS', 'TENOR', 'NIVEAU']
final_df = pd.concat([meta_df.loc[stacked_data['行索引']], stacked_data.drop('行索引', axis=1)], axis=1)

# 调整列顺序、重命名并删除冗余列
final_df = final_df[['Date', 'INDEKS', 'TENOR', 'NIVEAU']].rename(columns={'Date': 'DATO'})
final_df = final_df.drop('Level', axis=1)

print(final_df)

方法二：使用melt（直观的列转行操作） #

import pandas as pd

# 读取Excel，指定两行作为多级表头
df = pd.read_excel('credit_spreads.xlsx', header=[0, 1], skiprows=12)

# 将多级列名合并为单级，方便后续拆分
df.columns = [f"{col[0]}_{col[1]}" if col[0] is not None else col[1] for col in df.columns]

# 将数据列转为行，保留Level和Date作为标识列
melted_df = pd.melt(df, id_vars=['Level', 'Date'], var_name='INDEKS_TENOR', value_name='NIVEAU')

# 拆分合并的列名为INDEKS和TENOR
melted_df[['INDEKS', 'TENOR']] = melted_df['INDEKS_TENOR'].str.split('_', expand=True)

# 调整列顺序、重命名并删除冗余列
final_df = melted_df[['Date', 'INDEKS', 'TENOR', 'NIVEAU']].rename(columns={'Date': 'DATO'})
final_df = final_df.drop('Level', axis=1)

print(final_df)

总结 #

• stack更适合处理原生多级表头的场景，只要先分离非数据类的表头列即可；
• melt操作更直观，适合将普通列转为行，需要先把多级列名合并再拆分；
• 两种方法都能实现目标格式，可根据个人习惯选择。

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