如何将含三级表头的Excel按Country Name重新拆分工作表？

现有一个带三级表头的Excel文件，当前数据按Indicator Name拆分成多个工作表（如PPP_GDP、CPI、PPI），数据示例如下：

Country Name             Mexico            Moldova
0   Indicator Name            PPP_GDP            PPP_GDP
1   Indicator Code  NY.GDP.PCAP.PP.CD  NY.GDP.PCAP.PP.CD
2             1988                NaN                NaN
3             1989               8.9                 NaN
4             1990               9.9              102.5 
5             1991               9.9              103.4 
6             1992               9.8              105.4 
7             1993               9.7              101.6 
8             1994               9.7              101.2 
9             1995               9.6              100.2 
10            1996               9.5               99.8 
11            1997               9.4               99.2 
12            1998               9.2               99.3 
13            1999               9.0               99.4 
14            2000                NaN                NaN

需求是重新按Country Name拆分，生成以Mexico、Moldova、Nepal、Israel为表名的新Excel文件。用户尝试了以下Pandas代码，但未得到预期结果：

sheets = [PPP_GDP, CPI, PPI]
final = []
for sheet in sheets:
    df = pd.read_excel('./test_data_2022-10-25.xlsx', sheet_name=sheet, header=[0, 1, 2])
    final.append(df)
    
excel_merged = pd.concat(final, ignore_index=True)
excel_merged.to_excel('./output.xlsx')

用户代码的核心问题是直接堆叠不同指标的工作表，未处理三级表头的结构化信息，导致数据维度混乱，无法按国家拆分。需要先规整每个工作表的结构，统一数据格式后再分组处理。

完整代码实现 #

import pandas as pd

# 读取Excel文件并获取所有工作表名
excel_file = pd.ExcelFile('./test_data_2022-10-25.xlsx')
sheet_names = excel_file.sheet_names

all_country_data = []

for sheet in sheet_names:
    # 读取带三级表头的工作表
    df = excel_file.parse(sheet, header=[0, 1, 2])
    # 将第一列（年份）重命名为Year
    df = df.rename(columns={df.columns[0]: 'Year'})
    # 提取当前工作表的指标名称和代码
    indicator_name = df.columns[1][1]
    indicator_code = df.columns[1][2]
    
    # 遍历每个国家列，拆分数据
    for country_col in df.columns[1:]:
        country_name = country_col[0]
        # 提取当前国家的年份和对应指标数据
        country_df = df[['Year', country_col]].copy()
        # 重命名数据列为指标名称
        country_df = country_df.rename(columns={country_col: indicator_name})
        # 添加指标代码和国家名称列
        country_df['Indicator Code'] = indicator_code
        country_df['Country Name'] = country_name
        # 调整列顺序，保证数据结构统一
        country_df = country_df[['Country Name', 'Year', indicator_name, 'Indicator Code']]
        all_country_data.append(country_df)

# 合并所有国家的指标数据
merged_df = pd.concat(all_country_data, ignore_index=True)

# 按国家分组，写入新Excel文件
with pd.ExcelWriter('./country_split_output.xlsx') as writer:
    for country in merged_df['Country Name'].unique():
        # 筛选当前国家的所有数据
        country_data = merged_df[merged_df['Country Name'] == country]
        # 透视表格式：年份为索引，指标为列（可选，根据需求调整）
        pivot_df = country_data.pivot(
            index='Year',
            columns=['Indicator Name', 'Indicator Code'],
            values=merged_df.columns[2]
        )
        pivot_df.to_excel(writer, sheet_name=country)

代码说明 #

1. 自动读取工作表：通过excel_file.sheet_names获取所有工作表，避免硬编码指定表名。
2. 规整单表结构：提取每个工作表的指标名称、代码，拆分单个国家的数据并补充上下文字段（国家名、指标信息），确保每条数据结构统一。
3. 合并与分组写入：合并所有数据后，按国家筛选，用透视表将同一国家的不同指标整理成规范格式，写入对应工作表。

简化格式可选 #

如果不需要透视表格式，直接保留原始行式数据，可修改写入部分代码：

with pd.ExcelWriter('./country_split_output.xlsx') as writer:
    for country in merged_df['Country Name'].unique():
        country_data = merged_df[merged_df['Country Name'] == country]
        country_data.to_excel(writer, sheet_name=country, index=False)

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