如何在Python中处理双层表头DataFrame并进行透视转换？

原始双层表头DataFrame：

Country Year    Stunting prevalence in children aged < 5 years (%)  Stunting prevalence in children aged < 5 years (%)  Stunting prevalence in children aged < 5 years (%)  Stunting prevalence in children aged < 5 years (%)  Stunting prevalence in children aged < 5 years (%)  Underweight prevalence in children aged < 5 years (%)   Underweight prevalence in children aged < 5 years (%)   Underweight prevalence in children aged < 5 years (%)   Underweight prevalence in children aged < 5 years (%)   Underweight prevalence in children aged < 5 years (%)
        Q1 (Poorest)    Q2  Q3  Q4  Q5 (Richest)    Q1 (Poorest)    Q2  Q3  Q4  Q5 (Richest)
Albania 2017    17.1 [14.3-20.2]    10.5 [7.6-14.3] 7.3 [4.8-10.8]  11.3 [7.6-16.6] 9.2 [5.1-15.8]  2.4 [1.5-3.9]   1.2 [0.6-2.3]   0.9 [0.3-2.7]   2.0 [0.6-6.3]   0.7 [0.2-2.1]
Albania 2008    31.5 [24.9-39.1]    24.9 [18.3-32.9]    20.0 [15.3-25.7]    21.9 [17.6-27.0]    16.2 [12.0-21.4]    9.2 [5.8-14.1]  5.6 [3.4-9.1]   6.6 [3.8-11.0]  5.4 [3.2-9.0]   4.0 [2.2-7.3]
Albania 2005    35.2 [29.2-41.6]    28.0 [21.4-35.7]    28.3 [21.8-35.9]    22.0 [16.5-28.6]    18.1 [13.5-23.8]    11.8 [7.8-17.6] 7.5 [4.6-12.1]  7.6 [4.6-12.2]  2.5 [1.2-5.2]   2.8 [1.3-5.8]

期望转换后的格式：

Country Year    Quarter Stunting prevalence in children aged < 5 years (%)  Underweight prevalence in children aged < 5 years (%)
Albania 2017    Q1  17.1 [14.3-20.2]    2.4 [1.5-3.9]
Albania 2017    Q2  10.5 [7.6-14.3] 1.2 [0.6-2.3]
Albania 2017    Q3  7.3 [4.8-10.8]  0.9 [0.3-2.7]
Albania 2017    Q4  11.3 [7.6-16.6] 2.0 [0.6-6.3]
Albania 2017    Q5  9.2 [5.1-15.8]  0.7 [0.2-2.1]
Albania 2008    Q1  31.5 [24.9-39.1]    9.2 [5.8-14.1]
Albania 2008    Q2  24.9 [18.3-32.9]    5.6 [3.4-9.1]
Albania 2008    Q3  20.0 [15.3-25.7]    6.6 [3.8-11.0]
Albania 2008    Q4  21.9 [17.6-27.0]    5.4 [3.2-9.0]
Albania 2008    Q5  16.2 [12.0-21.4]    4.0 [2.2-7.3]
Albania 2005    Q1  35.2 [29.2-41.6]    11.8 [7.8-17.6]
Albania 2005    Q2  28.0 [21.4-35.7]    7.5 [4.6-12.1]
Albania 2005    Q3  28.3 [21.8-35.9]    7.6 [4.6-12.2]
Albania 2005    Q4  22.0 [16.5-28.6]    2.5 [1.2-5.2]
Albania 2005    Q5  18.1 [13.5-23.8]    2.8 [1.3-5.8]

尝试代码：

df2 = pd.read_csv('wealthQuintileData.csv', header=[0, 1])
df3 = (df2.set_index(df.columns[0]).stack([0, 1]).rename_axis(['Country', 'Measure', 'Quarter']).reset_index(name='Value'))

遇到的问题：转换结果缺失Year列，且格式不符合预期。

核心问题是未将Year纳入索引，且后续数据重塑逻辑不匹配目标格式。以下是修正后的完整代码：

import pandas as pd

# 读取双层表头数据
df = pd.read_csv('wealthQuintileData.csv', header=[0, 1])

# 将Country和Year设为多层索引，避免后续操作丢失
df = df.set_index([('Country', ''), ('Year', '')])

# 堆叠双层列索引，转换为长格式数据
df_long = df.stack([0, 1]).reset_index()
df_long.columns = ['Country', 'Year', 'Measure', 'Quarter', 'Value']

# 提取Quarter的简称（如Q1 (Poorest) → Q1）
df_long['Quarter'] = df_long['Quarter'].str.extract(r'(Q\d+)')

# 透视回宽格式，将Measure转换为列
df_final = df_long.pivot_table(
    index=['Country', 'Year', 'Quarter'],
    columns='Measure',
    values='Value',
    aggfunc='first'
).reset_index()

# 调整列顺序，匹配目标格式
target_cols = [
    'Country', 'Year', 'Quarter',
    'Stunting prevalence in children aged < 5 years (%)',
    'Underweight prevalence in children aged < 5 years (%)'
]
df_final = df_final[target_cols]

print(df_final)

关键步骤说明： #

1. 设置多层索引：同时将Country和Year设为索引，确保这两列在堆叠操作中保留。
2. 堆叠列索引：通过stack([0,1])把双层表头的指标和季度转换为行维度，得到包含所有数据的长格式表。
3. 清洗Quarter列：用正则提取季度简称，去掉括号内的附加描述。
4. 透视回宽格式：将长格式数据重新转换为目标的宽格式，让两个指标作为独立列。
5. 调整列顺序：确保最终列的排列和预期格式一致。

内容的提问来源于stack exchange，提问作者Vishal A.