如何使用Pandas将特定格式Excel表格的列转换为行？

处理思路 #

原表格是多层表头的宽表结构，需要先解析多层列索引，再将宽表转成包含时间维度的长表，最后重新整理为目标的宽表格式。核心步骤包括：读取带多层表头的Excel、提取固定标识列、重塑数据结构、合并并调整字段与列名。

代码实现 #

import pandas as pd

# 1. 读取Excel文件，指定前4行作为多层列索引
df = pd.read_excel('your_file.xlsx', header=[0, 1, 2, 3])

# 2. 提取固定标识列（姓名、是否商务？、是否专属？）
id_cols = df.loc[:, [('', '', '', '姓名'), ('', '', '', '是否商务？'), ('', '', '', '是否专属？')]]
id_cols.columns = ['姓名', '是否商务？', '是否专属？']

# 3. 提取数值列并设置索引名称，方便后续处理
value_cols = df.drop(id_cols.columns, axis=1, level=3)
value_cols.columns = value_cols.columns.set_names(['类别', '年份', '月份', '月份缩写'])

# 4. 将宽表转成长表，展开多层列索引
melted = value_cols.stack(['类别', '年份', '月份', '月份缩写']).reset_index()
melted.columns = ['行索引', '类别', '年份', '月份', '月份缩写', '数值']

# 5. 合并标识列与长表数据
merged = pd.concat([id_cols.loc[melted['行索引']], melted.drop('行索引', axis=1)], axis=1)

# 6. 将类别转为单独列，得到目标宽表格式
final_df = merged.pivot_table(
    index=['姓名', '是否商务？', '是否专属？', '年份', '月份缩写'],
    columns='类别',
    values='数值',
    aggfunc='first'
).reset_index()

# 7. 修正列名拼写与字段名称
final_df = final_df.rename(columns={'Vaction': 'Vacation', '月份缩写': '月份'})

# 8. 保存转换后的文件
final_df.to_excel('converted_file.xlsx', index=False)

关键说明 #

• 读取Excel时通过header=[0,1,2,3]指定前4行作为多层列索引，确保完整解析表头的层级信息；
• 使用stack将多层列索引展开为行维度，实现宽表到长表的转换；
• 通过pivot_table将长表重新转换为目标宽表，把Prod/Work/Vacation转为独立列；
• 修正原数据中Vaction的拼写错误，统一为目标格式的Vacation，同时替换月份字段为缩写形式。

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