如何将长格式DataFrame转为带列MultiIndex的宽格式表格？

核心思路 #

1. 使用Pandas的pivot/pivot_table将长格式数据转换为带双层索引列的宽格式DataFrame
2. 借助openpyxl库手动处理Excel文件，合并服务名称对应的上层表头单元格

完整代码实现 #

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment

# 1. 替换为你的实际长格式数据
df_long = pd.DataFrame({
    'Location': ['NYC', 'NYC', 'NYC', 'LA', 'LA', 'Chicago'],
    'Service': ['IT Support', 'IT Support', 'Cleaning', 'IT Support', 'Cleaning', 'IT Support'],
    'Tier': [1, 2, 1, 1, 1, 1],
    'Supplier': ['Vendor A', 'Vendor B', 'Vendor C', 'Vendor D', 'Vendor E', 'Vendor F']
})

# 2. 转换为宽格式，生成双层表头
# 若存在重复的Location+Service+Tier组合，改用pivot_table并指定聚合函数
try:
    df_wide = df_long.pivot(index='Location', columns=['Service', 'Tier'], values='Supplier').reset_index()
except ValueError:
    df_wide = df_long.pivot_table(
        index='Location',
        columns=['Service', 'Tier'],
        values='Supplier',
        aggfunc='first'  # 可根据需求替换为'join'等
    ).reset_index()

# 3. 清理列名层级，让Excel表头显示更清晰
df_wide.columns = df_wide.columns.set_names(['', ''], level=[0, 1])

# 4. 导出初始宽格式数据到Excel
output_file = 'supplier_allocation.xlsx'
df_wide.to_excel(output_file, index=False, header=True)

# 5. 处理Excel表头合并：将服务名称跨对应层级列合并
wb = load_workbook(output_file)
ws = wb.active

# 统计每个服务对应的层级数量
service_tier_counts = df_long.groupby('Service')['Tier'].nunique()

# 遍历服务，合并对应表头单元格
current_col = 2  # 从第2列开始，第1列是Location
for service, tier_num in service_tier_counts.items():
    start_col = current_col
    end_col = current_col + tier_num - 1
    # 合并第一行的服务名称单元格
    ws.merge_cells(
        start_row=1,
        start_column=start_col,
        end_row=1,
        end_column=end_col
    )
    # 设置合并后单元格的内容和居中对齐
    merged_cell = ws.cell(row=1, column=start_col)
    merged_cell.value = service
    merged_cell.alignment = Alignment(horizontal='center', vertical='center')
    # 更新下一个服务的起始列
    current_col = end_col + 1

# 保存最终处理后的Excel文件
wb.save(output_file)

关键说明 #

• 双层表头生成：通过columns=['Service', 'Tier']指定列的双层索引，确保宽格式数据的层级结构正确
• 重复值处理：若原始数据存在同一地点-服务-层级的重复记录，用pivot_table替代pivot并设置聚合函数（如first取第一条记录）
• Excel合并逻辑：通过统计每个服务的层级数量，精准合并第一行中对应服务的所有层级列，保证导出后表头符合需求

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