将含多行列标签的Excel数据转换为数据库结构

需求说明 #

原始Excel为行列均带多层标签的宽表结构：

• 列方向：3层标签（列标签A/B/C系列）
• 行方向：3层行标签（行标签A/B/C系列），中间包含标签名称行和空行

目标转换为标准数据库长表结构：每行包含完整的行标签、列标签和对应数据值。

为什么选择Pandas？ #

Pandas是处理这类表格转换的最优方案之一：

• 原生支持多层索引（MultiIndex），完美匹配原始Excel的多层标签结构
• 内置melt函数，专门用于宽表转长表，代码简洁高效
• 支持批量处理大型文件，性能优于手动遍历或其他轻量工具

实现步骤与代码 #

1. 读取Excel文件 #

指定前3行作为列的多层索引，跳过中间的标签名称行和空行：

import pandas as pd

# 替换为你的文件路径
df = pd.read_excel('multi_label_data.xlsx', header=[0,1,2], skiprows=[3,4])

2. 重命名行标签列 #

原始前3列的默认列名为空元组，需重命名为目标结构的行标签名称：

# 按顺序重命名前3列
df.columns = ['行标签A', '行标签B', '行标签C'] + list(df.columns[3:])

3. 宽表转长表（核心转换） #

使用melt函数将列的多层标签转为独立列，数据值统一存入“数据”列：

melted_df = df.melt(
    id_vars=['行标签A', '行标签B', '行标签C'],  # 保留作为标识的行标签列
    var_name=['列标签A', '列标签B', '列标签C'],  # 列多层标签对应的新列名
    value_name='数据'  # 原始数据值对应的列名
)

4. 可选：清理与导出 #

如果存在空值可清理，最后导出为数据库友好的格式：

# 移除数据为空的行（可选）
melted_df = melted_df.dropna(subset=['数据'])

# 导出为CSV格式（可直接导入数据库）
melted_df.to_csv('structured_data.csv', index=False, encoding='utf-8-sig')

结果验证 #

转换后的表格将完全匹配目标数据库结构：每行包含3个行标签、3个列标签和对应的数据值，可直接用于数据库导入或后续分析。

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