宽表转透视表并编写循环填充NaN为指定分类字符串的实现方法

核心思路 #

你的原始表是典型的合并单元格表头结构，核心是先补全层级表头的空值，再做宽表转长表的操作，最后处理值列的空值即可。

代码实现（基于Python pandas） #

首先安装依赖（如果未安装）：pip install pandas openpyxl（适配Excel源文件，csv格式可忽略openpyxl）

完整可运行代码：

import pandas as pd
import numpy as np

# 1. 读取原始表，不自动识别表头，先把前3行都读为普通数据
# 读取csv文件替换为pd.read_csv("你的原始文件路径.csv", header=None)
df_raw = pd.read_excel("你的原始文件路径.xlsx", header=None)

# 2. 补全第一行（一级分类行）的所有空值，统一为Submittal Information
df_raw.iloc[0] = df_raw.iloc[0].ffill().bfill()

# 3. 补全第二行（二级分类行）的空值，向前填充适配合并单元格规则
df_raw.iloc[1] = df_raw.iloc[1].ffill()

# 4. 绑定多级列名：第一行=一级分类、第二行=二级分类、第三行=字段名
df_raw.columns = pd.MultiIndex.from_arrays([df_raw.iloc[0], df_raw.iloc[1], df_raw.iloc[2]])

# 5. 剔除已经用作表头的前3行，保留实际业务数据
df = df_raw[3:].reset_index(drop=True)

# 6. 宽表转长表，生成目标三列结构
df_melted = df.melt(
    var_name=["Category", "Sub-category", "field_name"],
    value_name="value"
)
# 不需要字段名列可直接删除
df_melted = df_melted.drop(columns=["field_name"])

# 7. 遍历替换value列的空白/NaN值，替换为关联的分类+子分类值，可自行调整拼接规则
df_melted["value"] = df_melted.apply(
    lambda row: f"{row['Category']}-{row['Sub-category']}" if pd.isna(row["value"]) else row["value"],
    axis=1
)

# 输出结果
print(df_melted)
# 保存为本地文件
df_melted.to_excel("转换后的透视表.xlsx", index=False)

关键逻辑说明 #

• 表头补全用ffill()（向前填充）是通用适配逻辑，不需要硬编码二级分类对应的列数，后续分类数量变动也无需改核心逻辑
• 空值替换规则可根据实际需求调整，比如只保留子分类值或者自定义拼接格式，直接修改apply内的判断逻辑即可

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