如何将多行分组错位表头的Excel读取为pandas DataFrame？

针对你提到的多行分组表头、空白行分隔且用户信息统一的特殊Excel文件，不需要用open直接读取（Excel是二进制格式，直接读会乱码），可以用openpyxl结合pandas处理，以下是具体实现方案：

核心思路 #

1. 逐行读取Excel内容，识别表头行（含Header标识）和对应的数据行
2. 建立列位置→完整表头的映射（比如把同一列的Header3/Header31拼接成完整列名）
3. 收集每个列位置对应的用户数据值
4. 按列位置排序后生成标准结构的DataFrame

代码实现 #

首先安装依赖：

pip install openpyxl pandas

处理函数：

import pandas as pd
from openpyxl import load_workbook

def parse_special_excel(file_path):
    # 只读模式加载工作簿，节省内存
    wb = load_workbook(file_path, read_only=True)
    ws = wb.active
    
    col_header_map = {}  # 列索引 → 完整表头名
    col_data_map = {}    # 列索引 → 用户数据值
    current_target_col = None  # 当前表头对应的列位置

    for row in ws.iter_rows(values_only=True):
        # 跳过全空白行
        if all(cell is None for cell in row):
            continue
        
        # 识别表头行：包含以Header开头的字符串
        has_header = any(isinstance(cell, str) and cell.startswith('Header') for cell in row)
        if has_header:
            for idx, cell in enumerate(row):
                if cell and cell.startswith('Header'):
                    # 拼接同一列的多级表头
                    if idx in col_header_map:
                        col_header_map[idx] = f"{col_header_map[idx]}/{cell}"
                    else:
                        col_header_map[idx] = cell
                    current_target_col = idx
        else:
            # 数据行：将值对应到最近记录的表头列
            if current_target_col is not None:
                col_data_map[current_target_col] = row[current_target_col]

    # 按列索引排序，生成最终的表头和数据
    sorted_cols = sorted(col_header_map.keys())
    final_columns = [col_header_map[col] for col in sorted_cols]
    final_data = [[col_data_map[col] for col in sorted_cols]]

    # 生成DataFrame
    return pd.DataFrame(final_data, columns=final_columns)

# 调用示例
df = parse_special_excel("your_special_file.xlsx")
print(df)

适配调整 #

如果你的文件有以下情况，可修改对应逻辑：

• 多用户数据：如果文件包含多个用户的分组数据，只需在循环中添加用户数据的收集逻辑，每次遇到新的用户分组（比如通过特定标识或空白行间隔）就新建一条数据记录
• 表头与数据行的对应规则不同：如果表头块是多行后才跟数据行，可增加变量记录表头块的列范围，后续数据行直接提取对应列的值
• .xls格式文件：替换openpyxl为xlrd（注意xlrd 2.0+不支持.xlsx，需安装1.2.0版本），读取逻辑基本一致

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