如何转换Pandas DataFrame：拆分复合列头并重塑行结构

原始DataFrame #

目标结构 #

尝试的代码及问题 #

使用melt得到中间结构后，pivot_table未返回预期结果：

# Melt the DataFrame
df_melt = df.melt(id_vars=['A', 'B', 'C', 'D', 'E'], var_name='Type', value_name='Value')

# Split the 'Type' column into 'Delivery Year' and 'Type'
df_melt[['Delivery Year', 'Type']] = df_melt['Type'].str.rsplit('_', n=1, expand=True)

# Pivot the DataFrame
pivot_df = df_melt.pivot_table(index=['A', 'B', 'C', 'D', 'E', 'Delivery Year'], columns='Type', values='Value', fill_value=0).reset_index()

转换后的中间结构：

复现代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'A': 'abc',
    'B': 'abc',
    'C': np.NaN,
    'D': np.NaN,
    'E': 'abc',
    'Year 2020_AA': [0],
    'Year 2020_AB': [0],
    'Year 2021_AA': [0],
    'Year 2021_AB': [0]
})

解决方法 #

方法1：改进melt+拆分+pivot流程 #

优化拆分逻辑，直接提取纯年份数字，避免前缀干扰：

# 1. 熔宽表为长表
df_melt = df.melt(id_vars=['A', 'B', 'C', 'D', 'E'], var_name='Col', value_name='Value')

# 2. 从列名中提取年份和类型
df_melt['Year'] = df_melt['Col'].str.extract(r'Year (\d+)')  # 提取纯年份数字
df_melt['Type'] = df_melt['Col'].str.split('_', n=1).str[1]  # 提取AA/AB

# 3. 透视得到目标结构
result = df_melt.pivot(
    index=['A', 'B', 'C', 'D', 'E', 'Year'],
    columns='Type',
    values='Value'
).fillna(0).reset_index()

# 调整列名匹配目标结构
result.columns = ['A', 'B', 'C', 'D', 'E', 'Year', 'AA', 'AB']

方法2：使用pd.wide_to_long（简洁高效） #

wide_to_long专为带前缀-后缀的宽表转换设计，先调整列名适配函数要求：

# 重命名列，将"Year 2020_AA"改为"AA_2020"格式
df_renamed = df.rename(columns={
    'Year 2020_AA': 'AA_2020',
    'Year 2020_AB': 'AB_2020',
    'Year 2021_AA': 'AA_2021',
    'Year 2021_AB': 'AB_2021'
})

# 使用wide_to_long转换
result = pd.wide_to_long(
    df_renamed,
    stubnames=['AA', 'AB'],
    i=['A', 'B', 'C', 'D', 'E'],
    j='Year',
    sep='_',
    suffix=r'\d+'
).reset_index()

方法3：利用MultiIndex列+stack/unstack #

通过多级列索引直接堆叠转换：

# 分离标识列和数值列
id_cols = ['A', 'B', 'C', 'D', 'E']
value_cols = [col for col in df.columns if col not in id_cols]

# 拆分数值列名为(Year, Type)元组
new_cols = [col.replace('Year ', '').split('_') for col in value_cols]

# 设置多级列索引
df.columns = id_cols + pd.MultiIndex.from_tuples(new_cols, names=['Year', 'Type'])

# 堆叠年份、展开类型列
result = df.set_index(id_cols).stack(level='Year').unstack(level='Type').reset_index()
result.columns = id_cols + ['Year', 'AA', 'AB']

以上三种方法均可得到目标DataFrame结构。

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