Python/Pandas三级分层DataFrame添加年度合计列及相关问题

1. 为三级分层列添加年度合计列 #

你已能通过groupby(level=[0,2])计算合计值，只需调整列索引结构后与原DataFrame合并，再整理列顺序即可：

import pandas as pd

# 示例DataFrame
col = pd.MultiIndex.from_product([['A', 'B'], ['F', 'M', 'DNC'],[2016,2017]])
df = pd.DataFrame([[1,2,6,16,20,19,17,9,4,2,8,19]], columns=col)

# 计算（业务指标，年份）维度的合计值
total_df = df.groupby(level=[0,2], axis=1).sum()
# 将合计列的索引调整为（业务指标，'Total'，年份），匹配原DataFrame的三级结构
total_df.columns = pd.MultiIndex.from_tuples(
    [(metric, 'Total', year) for metric, year in total_df.columns]
)

# 合并原数据与合计数据
combined_df = pd.concat([df, total_df], axis=1)

# 重新排序列，确保每个指标下先显示性别列，再显示年度合计列
combined_df = combined_df.reindex(columns=[
    (m, g, y) for m in df.columns.get_level_values(0).unique()
    for g in ['F', 'M', 'DNC', 'Total']
    for y in df.columns.get_level_values(2).unique()
])

print(combined_df)

2. 按指标导出至单指标单工作表的Excel #

无需扁平化列，直接遍历MultiIndex的第一层（业务指标），提取对应列写入Excel即可：

# 使用ExcelWriter批量导出
with pd.ExcelWriter('业务指标数据.xlsx') as writer:
    # 遍历所有唯一的业务指标
    for metric in combined_df.columns.get_level_values(0).unique():
        # 提取当前指标的所有列
        metric_data = combined_df.loc[:, metric]
        # 写入对应工作表，表名设为指标名称
        metric_data.to_excel(writer, sheet_name=metric)

3. 按部门和年份归一化数据 #

先将数据转换为长格式，合并客户数数据后计算归一化值，长格式数据可直接用于seaborn绘图：

# 假设客户数数据结构：索引为部门，列为年份，值为对应客户数（替换为你的实际数据）
customer_count = pd.DataFrame(
    [[100, 120], [150, 180]],
    index=['部门1', '部门2'],
    columns=[2016, 2017]
)

# 将合并后的指标数据转为长格式
long_data = combined_df.stack([1,2]).reset_index()
long_data.columns = ['部门', '性别', '年份', '指标值']

# 将客户数数据转为长格式，方便合并
customer_long = customer_count.stack().reset_index()
customer_long.columns = ['部门', '年份', '客户数']

# 合并指标数据与客户数数据
merged_data = pd.merge(long_data, customer_long, on=['部门', '年份'])

# 计算归一化值：指标值 / 对应部门当年的客户数
merged_data['归一化值'] = merged_data['指标值'] / merged_data['客户数']

# 使用seaborn绘制折线图示例
import seaborn as sns
import matplotlib.pyplot as plt

sns.lineplot(
    data=merged_data,
    x='年份',
    y='归一化值',
    hue='部门',
    style='性别',
    marker='o'
)
plt.title('各部门归一化业务指标趋势')
plt.show()

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