如何基于多表头转置Pandas DataFrame并规整数据格式？

看起来你在处理供应商给的双表头数据时卡壳了——这种多层表头的宽表转长表确实容易踩坑。你之前直接删掉第一行表头的做法，会丢失日期和start/hours字段的关联关系，这就是结果不对的核心原因。下面我给你一步步拆解正确的处理流程：

步骤1：正确读取带双表头的数据 #

首先，读取文件时要告诉Pandas这两行都是表头，用header=[0,1]参数，这样会生成**多层索引（MultiIndex）**的列名，完整保留日期和字段的对应关系：

import pandas as pd

# 读取文件，指定两行作为表头
df = pd.read_csv('your_file.csv', header=[0, 1])

此时你的列名会是这样的多层结构，能清晰看到日期和start/hours的绑定：

MultiIndex([('Unnamed: 0',        'title'),
            ('Unnamed: 1', 'content title'),
            ('Unnamed: 2',       'season'),
            ('agg metrics',    'episode'),
            (    '10/20/22',      'start'),
            (    '10/20/22',       'hours'),
            (    '10/21/22',      'start'),
            (    '10/21/22',       'hours')],
           )

步骤2：清理列名，把多层索引转成可处理的格式 #

接下来把多层列名合并成日期_字段的格式，同时把前面的非日期列（比如Unnamed:0对应的title）整理成清晰的字段名：

# 遍历多层列名，合并成单层级的列名
df.columns = [
    f"{col[0]}_{col[1]}" 
    if col[0] not in ['Unnamed: 0', 'Unnamed: 1', 'Unnamed: 2', 'agg metrics'] 
    else col[1] 
    for col in df.columns
]

处理后列名就变成了：
['title', 'content title', 'season', 'episode', '10/20/22_start', '10/20/22_hours', '10/21/22_start', '10/21/22_hours']

步骤3：用wide_to_long快速重塑数据 #

Pandas的wide_to_long函数专门处理这种"前缀_后缀"格式的宽表，非常适合你的场景：

# 将宽表转长表，指定要提取的字段前缀（start、hours），以及作为索引的非日期列
df_long = pd.wide_to_long(
    df,
    stubnames=['start', 'hours'],
    i=['title', 'content title', 'season', 'episode'],
    j='date',
    sep='_',
    suffix='.+'
)

# 重置索引，把date从索引转成普通列
df_long = df_long.reset_index()

# 调整列的顺序，和你要的目标格式对齐
df_long = df_long[['title', 'content title', 'season', 'episode', 'date', 'start', 'hours']]

验证结果 #

现在打印df_long就能得到你想要的格式：

title content title season episode      date start hours
0   book          blue      1       3  10/20/22     2     2
1   book          blue      1       3  10/21/22     5     2
2  movie         orange      2       4  10/20/22    11     4
3  movie         orange      2       4  10/21/22     7     4

备选方案：用melt+pivot实现 #

如果你更习惯用melt，也可以这样做：

# 先把所有日期相关列转成键值对
df_melt = df.melt(
    id_vars=['title', 'content title', 'season', 'episode'],
    var_name='date_field',
    value_name='value'
)

# 拆分date_field为日期和字段
df_melt[['date', 'field']] = df_melt['date_field'].str.split('_', expand=True)

# 把字段转成列
df_pivot = df_melt.pivot(
    index=['title', 'content title', 'season', 'episode', 'date'],
    columns='field',
    values='value'
).reset_index()

# 去掉列名的层级
df_pivot.columns.name = None

# 调整列顺序
df_pivot = df_pivot[['title', 'content title', 'season', 'episode', 'date', 'start', 'hours']]

核心问题就是你一开始丢掉了第一行表头的信息，导致无法关联日期和对应的字段。保留多层表头后，再用合适的重塑函数就能轻松解决啦！

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