Python 玩转 Excel 案例【第 27 期】：多层表头扁平化处理

📌 案例说明

原始数据表（测试数据.xlsx）：

        A           B          C          D          E    ┌─────────────────────────────────────────────────────┐  1 │           公司财务报表                              │    ├─────────────────────────────────────────────────────┤  2 │               2024年度                              │    ├─────────────────────────────────────────────────────┤  3 │                                                     │    ├──────────┬──────────┬──────────┬──────────┬────────┤  4 │   产品   │ 手机业务 │ 手机业务 │ 电脑业务 │ 电脑业务│    ├──────────┼──────────┼──────────┼──────────┼────────┤  5 │   型号   │   Q1     │   Q2     │   Q1     │   Q2    │    ├──────────┼──────────┼──────────┼──────────┼────────┤  6 │   名称   │   销量   │   销量   │   销量   │   销量  │    ├──────────┼──────────┼──────────┼──────────┼────────┤  7 │ iPhone15 │   100    │   120    │    80    │    90   │    ├──────────┼──────────┼──────────┼──────────┼────────┤  8 │ iPhone14 │    80    │    90    │    60    │    70   │    ├──────────┼──────────┼──────────┼──────────┼────────┤  9 │ MacBook  │    50    │    60    │    40    │    50   │    └──────────┴──────────┴──────────┴──────────┴────────┘

这是 Excel 数据处理中非常经典的场景：表格存在多层表头。

原始数据中：

• • 前3行是报表标题和空行（行1-3）
• • 第4-6行是三层表头（业务类型 → 季度 → 指标名称）
• • 第7行开始是具体数据

这种结构便于人工阅读，但对数据分析来说是“障碍”——pandas 默认读取会丢失层级关系，无法准确识别每一列的业务含义。

本期案例跟大家一起学习用 Python 自动处理多层表头，将三层表头扁平化为有意义的单层列名，为后续分析扫清障碍。

核心操作：提取多层表头 + 组合扁平化列名 + 数据区域对齐

• • 关键库：pandas
• • 核心逻辑：跳过无关行，提取三层表头区域，按列组合成扁平列名，再与数据区域拼接

📜 完整代码

"""多层表头扁平化处理 - 自动将三层表头组合成有意义的单层列名使用 pandas 实现“表头提取 + 列名组合 + 数据对齐”的自动化处理"""import pandas as pddef flatten_multiheader_excel(file_path, header_start_row=3, header_depth=3):    """    通用多层表头扁平化处理    参数:        file_path: Excel文件路径        header_start_row: 表头起始行（0-indexed）        header_depth: 表头层数    """    # 1. 读取原始数据（不设表头）    df_raw = pd.read_excel(file_path, header=None)    # 2. 提取表头区域    header_rows = df_raw.iloc[header_start_row:header_start_row + header_depth].values    # 3. 生成扁平化列名    columns = []    for col_idx in range(len(header_rows[0])):        if col_idx == 0:  # 第一列是产品名称列            columns.append('产品')        else:            # 组合多层表头            levels = [str(header_rows[r][col_idx]).strip() if pd.notna(header_rows[r][col_idx]) else ''                      for r in range(header_depth)]            col_name = '_'.join(filter(None, levels))            columns.append(col_name if col_name else f'col{col_idx}')    # 4. 提取数据区域    data = df_raw.iloc[header_start_row + header_depth:].values    df = pd.DataFrame(data, columns=columns)    # 5. 清理产品列（处理空值）    df['产品'] = df['产品'].fillna('').astype(str)    df = df[df['产品'].ne('')]  # 过滤掉空产品名的行    # 6. 设置产品为索引    df.set_index('产品', inplace=True)    # 7. 转换数值列    for col in df.columns:        df[col] = pd.to_numeric(df[col], errors='coerce')    return df# 使用if __name__ == "__main__":    df = flatten_multiheader_excel('测试数据.xlsx', header_start_row=3, header_depth=3)    print(df)    df.to_excel('测试数据_更健壮.xlsx')

运行结果（测试数据_更健壮.xlsx）：

💡 结果分析：三层杂乱的表头被组合成了清晰的单层列名。产品列变成了行索引，每一列的名称都清晰地表达了“业务_季度_指标”的含义，数据可以直接用于分组统计、透视分析等操作。

第一步：导入所需库

import pandas as pd

代码解释：

• • import pandas as pd导入 pandas 库并简写为 pd。pandas 是 Python 数据分析的核心库，提供了强大的数据读取、处理和转换功能。

第二步：读取原始数据

df_raw = pd.read_excel(file_path, header=None)

代码解释：

• • pd.read_excel(file_path)读取指定路径的 Excel 文件，返回 DataFrame 对象。
• • header=None这个参数非常关键！pandas 默认会将第一行作为列名。但我们这个 Excel 的前3行是标题和空行，第4行才开始是真正的表头。所以需要设置 header=None，让 pandas 将所有行都当作数据读取（包括表头行），行号会自动从0开始编号。

对比示例：

# 默认读取（header=0）df_default = pd.read_excel('测试数据.xlsx')print(df_default.columns)# 输出：Index(['公司财务报表', 'Unnamed: 1', 'Unnamed: 2', ...], dtype='object')# 第一行变成了列名，完全乱了！# 不设表头（header=None）df_raw = pd.read_excel('测试数据.xlsx', header=None)print(df_raw.iloc[0])  # 第1行# 输出：# 0    公司财务报表# 1      NaN# 2      NaN# ...# 所有行都被当作数据保留，原始结构完整

💡 小贴士：header=None 后，pandas 会自动给列编号为 0, 1, 2, 3... 方便我们按位置操作。

第三步：提取表头区域

header_rows = df_raw.iloc[header_start_row:header_start_row + header_depth].values

代码解释：

• • df_raw.iloc[行起点:行终点]用 iloc 按位置切片。pandas 的行索引从 0 开始计数。
• • header_start_row=3表示表头从 Excel 的第4行开始（因为 0→第1行，1→第2行，2→第3行，3→第4行）。
• • header_depth=3表示表头共有3行。
• • .values将切片结果从 DataFrame 转换为 NumPy 二维数组，去掉行列索引，方便后续按列操作。

执行过程可视化：

# header_start_row=3, header_depth=3# 切片范围：行3 到 行6（不包含行6）header_rows = df_raw.iloc[3:6].values# 得到的结果：# [#     ['产品', '手机业务', '手机业务', '电脑业务', '电脑业务'],  # 行3（原Excel第4行）#     ['型号', 'Q1', 'Q2', 'Q1', 'Q2'],                       # 行4（原Excel第5行）#     ['名称', '销量', '销量', '销量', '销量']                 # 行5（原Excel第6行）# ]

为什么用 .values？

在本例中，我们只需要遍历和修改表头数据，用数组更简洁。

第四步：生成扁平化列名

columns = []for col_idx in range(len(header_rows[0])):    if col_idx == 0:  # 第一列是产品名称列，保留原名称        columns.append('产品')    else:        # 组合多层表头        levels = [str(header_rows[r][col_idx]).strip() if pd.notna(header_rows[r][col_idx]) else '' for r in range(header_depth)]        col_name = '_'.join(filter(None, levels))        columns.append(col_name if col_name else f'col{col_idx}')

代码解释：

这是整个案例的核心——将纵向的三层表头按列组合成横向的单层列名。

步骤拆解：

① 确定总列数

len(header_rows[0])  # 表头第一行的长度 = 5列

② 遍历每一列

for col_idx in range(len(header_rows[0])):  # col_idx = 0,1,2,3,4

③ 第0列特殊处理（产品列）

if col_idx == 0:    columns.append('产品')  # 第一列保留为'产品'

④ 其他列：组合三层表头

# 以 col_idx=1 为例（第2列）levels = [    str(header_rows[0][1]),  # '手机业务'    str(header_rows[1][1]),  # 'Q1'    str(header_rows[2][1])   # '销量']# levels = ['手机业务', 'Q1', '销量']col_name = '_'.join(filter(None, levels))# filter(None, levels) 过滤掉空字符串# '_'.join(['手机业务', 'Q1', '销量']) → '手机业务_Q1_销量'

④-1 核心代码逐行拆解

levels = [str(header_rows[r][col_idx]).strip() if pd.notna(header_rows[r][col_idx]) else ''          for r in range(header_depth)]

这行代码是一个列表推导式，它的作用是将某一列的三层表头值提取出来，存入列表 levels。

拆解分析：

等价于传统循环写法：

levels = []for r in range(header_depth):    val = header_rows[r][col_idx]    if pd.notna(val):           # 如果不是空值        levels.append(str(val).strip())    else:                       # 如果是空值        levels.append('')

执行示例（col_idx=1）：

# r=0: val='手机业务' → 有值 → levels.append('手机业务')# r=1: val='Q1'       → 有值 → levels.append('Q1')# r=2: val='销量'     → 有值 → levels.append('销量')# 结果：levels = ['手机业务', 'Q1', '销量']

col_name = '_'.join(filter(None, levels))

这行代码将 levels 列表中的非空值用下划线 _ 连接成一个字符串。

filter(None, levels) 的作用：

过滤掉 levels 中的假值（空字符串 ''、None、False 等）。

# 示例：假设某一列有合并单元格导致第二层为空levels = ['手机业务', '', '销量']filter(None, levels)  # 过滤后 → ['手机业务', '销量']

'_'.join(...) 的作用：

将列表中的字符串用 _ 连接。

# 正常情况'_'.join(['手机业务', 'Q1', '销量'])  # → '手机业务_Q1_销量'# 有缺失值的情况（自动跳过空值）'_'.join(filter(None, ['手机业务', '', '销量']))  # → '手机业务_销量'

⑤ 防止空列名

columns.append(col_name if col_name else f'col{col_idx}')# 如果 col_name 是空字符串，用默认名称 'col2'、'col3' 等代替

完整执行过程表格：

最终生成的 columns 列表：

['产品', '手机业务_Q1_销量', '手机业务_Q2_销量', '电脑业务_Q1_销量', '电脑业务_Q2_销量']

💡 小贴士：如果你的表头层级不同（如2层或4层），只需修改 header_depth 参数即可，代码自动适配。

第五步：提取数据区域

data = df_raw.iloc[header_start_row + header_depth:].valuesdf = pd.DataFrame(data, columns=columns)

代码解释：

• • header_start_row + header_depth计算数据区域的起始行。表头占用了3行，数据从下一行开始。
• • 示例：3 + 3 = 6 → 从第7行开始（0-indexed，即原Excel的第7行）
• • df_raw.iloc[6:].values从第7行开始取到最后一行，转换为数组。
• • pd.DataFrame(data, columns=columns)用数据数组 + 刚生成的列名，创建新的 DataFrame。

执行过程：

# 原始数据区域（从行6开始）data = [    ['iPhone15', 100, 120, 80, 90],    ['iPhone14', 80, 90, 60, 70],    ['MacBook', 50, 60, 40, 50]]# 创建DataFramedf = pd.DataFrame(data, columns=['产品', '手机业务_Q1_销量', ...])

结果：

第六步：清理产品列

df['产品'] = df['产品'].fillna('').astype(str)df = df[df['产品'].ne('')]

代码解释：

• • .fillna('')将空值（NaN）替换为空字符串 ''。因为后续需要过滤空行。
• • .astype(str)将所有值转换为字符串类型，确保比较操作不出错。
• • df['产品'].ne('')ne 是 not equal 的缩写，筛选出产品列不等于空字符串的行。

为什么要清理？

原始数据中，产品列可能存在：

• • 空行（Excel中多余的空白行）
• • 合并单元格导致的部分空值

这些无效行需要被过滤掉，否则会影响后续分析。

过滤前后对比：

💡 小贴士：如果原始数据中产品列绝对没有空值，这一步可以省略。但保留它是个好习惯——防御性编程。

第七步：设置产品为索引

df.set_index('产品', inplace=True)

代码解释：

• • set_index('产品')将 产品 列设置为 DataFrame 的行索引（不再是普通列）。
• • inplace=True原地修改，不返回新对象。

为什么要设置索引？

效果对比：

# 设置索引前print(df)#      产品  手机业务_Q1_销量  ...# 0  iPhone15        100    ...# 1  iPhone14         80    ...# 设置索引后print(df)#          手机业务_Q1_销量  ...# 产品                      # iPhone15        100    ...# iPhone14         80    ...

第八步：转换数值列

for col in df.columns:    df[col] = pd.to_numeric(df[col], errors='coerce')

代码解释：

• • pd.to_numeric(df[col], errors='coerce')将列中的值转换为数字类型。无法转换的（如空字符串、文本）变成 NaN。
• • errors='coerce'遇错不报错，转为 NaN。

为什么要转换？

pandas 读取 Excel 时，所有数据默认都是 object 类型（相当于字符串）。如果不转换，无法进行数学运算：

# 转换前df['手机业务_Q1_销量'].sum()# 输出：'1008012050'  ← 字符串拼接，不是求和！# 转换后df['手机业务_Q1_销量'].sum()# 输出：230  ← 正确的求和结果

数据类型变化：

💡 小贴士：如果所有列都是数值，也可以用 df = df.apply(pd.to_numeric, errors='coerce') 一行搞定。

第九步：保存结果

df.to_excel('测试数据_更健壮.xlsx')

代码解释：

• • df.to_excel('文件名.xlsx')将 DataFrame 保存为 Excel 文件。
• • 默认行为：只保存数据，不保留原 Excel 格式（颜色、字体等）。

💡 小贴士：如果需要控制 Sheet 名称：df.to_excel('文件名.xlsx', sheet_name='Sheet1')。如果文件已存在，会被覆盖。

完整流程可视化

处理前（原始数据）：

处理过程：

步骤1: 读取（header=None）→ 保留所有行步骤2: 提取表头区域 → 行3-5，按列组合步骤3: 生成列名 → ['产品', '手机业务_Q1_销量', ...]步骤4: 提取数据区域 → 行6-8步骤5: 创建新DataFrame → 数据 + 新列名步骤6: 清理产品列 → 过滤空行步骤7: 设置产品为索引步骤8: 转换数值类型

处理后：

📚 本期核心知识点

📍 知识点 1：header=None 不设表头读取

df = pd.read_excel('文件.xlsx', header=None)

📍 知识点 2：iloc 按位置切片

df.iloc[行起点:行终点, 列起点:列终点]

• • 行号和列号都从 0 开始
• • 遵循 左闭右开 原则：包含起点，不包含终点

示例：

df.iloc[3:6]     # 取行3、行4、行5（不包含行6）df.iloc[3:6, 1]  # 取行3-5的第2列

📍 知识点 3：.values 转换为 NumPy 数组

header_rows = df_raw.iloc[3:6].values

📍 知识点 4：多层表头组合技巧

levels = [str(header_rows[r][col_idx]).strip() if pd.notna(header_rows[r][col_idx]) else ''          for r in range(header_depth)]col_name = '_'.join(filter(None, levels))

• • 列表推导式：一行代码提取多层表头
• • filter(None, levels)：过滤空字符串
• • '_'.join(...)：用下划线连接

📍 知识点 5：fillna() 和 astype() 处理空值

df['列名'] = df['列名'].fillna('').astype(str)

• • fillna('')：NaN → 空字符串
• • astype(str)：确保所有值都是字符串类型

📍 知识点 6：ne() 不等于筛选

df = df[df['产品'].ne('')]  # 相当于 df['产品'] != ''

ne = not equal，是 pandas 的比较方法。

📍 知识点 7：set_index() 设置行索引

df.set_index('产品', inplace=True)  # 原地修改df = df.set_index('产品')           # 返回新对象

📍 知识点 8：pd.to_numeric() 类型转换

df[col] = pd.to_numeric(df[col], errors='coerce')

🔄 本案例核心流程

① 读取（header=None） → ② 提取表头区域 → ③ 按列组合成扁平列名 → ④ 提取数据区域 → ⑤ 创建新DataFrame → ⑥ 清理产品列 → ⑦ 设置产品为索引 → ⑧ 转换数值类型 → ⑨ 保存文件

读取 Excel (header=None)        ↓提取表头区域 (iloc[3:6].values)        ↓遍历每一列，组合表头        ↓┌─────────────────────────────────────────────┐│ 对于第1列：columns.append('产品')            ││ 对于其他列：                                 ││   1. 提取三层值 → [手机业务, Q1, 销量]       ││   2. 连接成 '手机业务_Q1_销量'               ││   3. 添加到 columns 列表                     │└─────────────────────────────────────────────┘        ↓提取数据区域 (iloc[6:].values)        ↓创建新DataFrame (data, columns=columns)        ↓清理产品列 (fillna + astype + 过滤)        ↓设置产品为索引 (set_index)        ↓转换数值列 (to_numeric)        ↓保存到新文件 (to_excel)

🗳️ 点单时间到！ 🗳️

下期写什么？你来定，我来写。

评论区见！👇

📦 资源获取提示

关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！

❤️ 支持我们

如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～