Pandas透视表多索引列水平填充及格式调整问题

原始透视表代码 #

import pandas as pd

data = {
    'name': ['Comp1', 'Comp1', 'Comp2', 'Comp2', 'Comp3'],
    'entity_type': ['type1', 'type1', 'type2', 'type2', 'type3'],
    'code': ['code1', 'code2', 'code3', 'code1', 'code2'],
    'date': ['2024-01-31', '2024-01-31', '2024-01-29', '2024-01-31', '2024-01-29'],
    'value': [10, 10, 100, 10, 200],
    'source': [None, None, 'Estimated', None, 'Reported']
}
df = pd.DataFrame(data)

pivot_df = df.pivot(index='date', columns=['name', 'entity_type', 'source', 'code'], values='value').rename_axis([('name', 'entity_type', 'source', 'date')])
df = pivot_df.reset_index()
df

需要解决的问题 #

• 删除第一列
• 水平填充前3行的空白单元格（例如code2上方的空白需填充为Comp1、type1、NaN）
• 将列头中的NaN替换为空字符串

现有临时方案的不足 #

以下方案虽能将数据转为适合插入表格的数组，但会丢失多级列的结构化信息：

out = (df.pivot(index='date', columns=['name', 'entity_type', 'source', 'code'], values='value')
         .rename_axis([('name', 'entity_type', 'source', 'date')])
         .reset_index()
         .fillna('')
      )
out.columns.names = [None, None, None, None]
columns_df = pd.DataFrame(out.columns.tolist()).T
out = pd.concat([columns_df, pd.DataFrame(out.values)], ignore_index=True)
out

优化解决方案 #

以下方法既保留列的层级信息，又满足格式需求：

import pandas as pd

data = {
    'name': ['Comp1', 'Comp1', 'Comp2', 'Comp2', 'Comp3'],
    'entity_type': ['type1', 'type1', 'type2', 'type2', 'type3'],
    'code': ['code1', 'code2', 'code3', 'code1', 'code2'],
    'date': ['2024-01-31', '2024-01-31', '2024-01-29', '2024-01-31', '2024-01-29'],
    'value': [10, 10, 100, 10, 200],
    'source': [None, None, 'Estimated', None, 'Reported']
}
df = pd.DataFrame(data)

# 1. 生成透视表，保留原生多级列结构
pivot_df = df.pivot(index='date', columns=['name', 'entity_type', 'source', 'code'], values='value')

# 2. 将列层级中的NaN替换为空字符串
for level_idx in range(pivot_df.columns.nlevels):
    level_values = pivot_df.columns.levels[level_idx].astype(str).replace('nan', '')
    pivot_df.columns = pivot_df.columns.set_levels(level_values, level=level_idx)

# 3. 重置索引并删除第一列（date列）
df_formatted = pivot_df.reset_index().drop(columns=['date'])

# 4. 提取多级列作为表头，向前填充空白单元格
header_rows = pd.DataFrame(df_formatted.columns.tolist()).T
header_rows = header_rows.fillna(method='ffill', axis=1)

# 5. 合并表头与数据，得到最终结果
final_result = pd.concat([header_rows, df_formatted], ignore_index=True)
print(final_result)

步骤说明 #

• 保留原生多级列结构，避免丢失结构化信息
• 遍历列的每个层级，将NaN转为空白字符串
• 删除不需要的date列
• 将多级列转为表头行，用ffill横向填充空白，实现同一分组下的信息复用
• 合并表头与数据，输出符合要求的表格结构

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