多索引DataFrame多列转行：拆分月份与指标实现结构化输出

1. 合并所有Sheet的DataFrame #

先把读取到的字典格式DataFrame合并为单个DataFrame：

import pandas as pd

# 读取Excel文件
mx_dict = pd.read_excel('ABC Bookings.xlsx', header=[1,2], sheet_name=None)
# 合并所有sheet数据，忽略原索引
df = pd.concat(mx_dict.values(), ignore_index=True)

2. 清理多级列名 #

前两列的Level0为空白值，先给它们统一命名方便后续处理：

# 提取当前多级列名
cols = df.columns.to_list()
# 给前两列的Level0设置名称"Client Info"
cols[0] = ("Client Info", cols[0][1])
cols[1] = ("Client Info", cols[1][1])
# 重新设置DataFrame的列名
df.columns = pd.MultiIndex.from_tuples(cols)

3. 重塑为目标结构化格式 #

通过索引设置、堆叠、重置索引完成格式转换：

# 将客户信息列设为索引
df = df.set_index([("Client Info", "Client"), ("Client Info", "Client Full Name")])
# 将Level0的月份列堆叠成行，此时月份会成为索引的一部分
df_stacked = df.stack(level=0).reset_index()
# 重命名列名匹配需求
df_stacked.columns = ["Client", "Client Full Name", "Month", "Gross", "Net", "Billable"]

可选优化 #

• 若合并后存在重复客户数据，可执行df_stacked = df_stacked.drop_duplicates()去重
• 若月份为字符串格式，可转换为日期类型：

  df_stacked["Month"] = pd.to_datetime(df_stacked["Month"], format="%Y-%m")
  

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