使用Pandas解析Excel时如何处理合并列单元格及Unnamed列？

问题场景 #

原Excel表格存在合并列头xyz_123，对应下方多列数据：

|Student ID|x_1|x_2|xyz_123|
|6677889955|3  |4  |A|B| |D|
|0987654321|1  |4  |A| |C|3|
|1324689764|2  |2  |A|5|C| |

用Pandas解析后，合并列对应的后续列被命名为Unnamed:4、Unnamed:5、Unnamed:6，结构如下：

|Student ID|x_1|x_2|xyz_123|Unnamed:4|Unnamed:5|Unnamed:6|
|6677889955|3  |4  |A      |B        |         |D        |
|0987654321|1  |4  |A      |         |C        |3        |
|1324689764|2  |2  |A      |5        |C        |         |

尝试用fillna()填充列名未达预期，需将这些Unnamed列关联到xyz_123列头并整理数据。

解决方案 #

方法1：读取时识别合并单元格（推荐） #

借助openpyxl引擎读取Excel，先解析表头的合并单元格信息，再重命名列：

import pandas as pd
from openpyxl import load_workbook

# 加载目标Excel文件
wb = load_workbook('your_file.xlsx')
ws = wb.active
header_row_num = 1  # Excel中表头所在行号（openpyxl行号从1开始）

# 收集合并单元格的列名映射关系
col_name_map = {}
for merged_range in ws.merged_cells.ranges:
    # 获取合并单元格的主列名
    main_col_name = ws.cell(row=merged_range.min_row, column=merged_range.min_col).value
    # 给合并范围内的所有列绑定主列名
    for col_idx in range(merged_range.min_col, merged_range.max_col + 1):
        col_name_map[col_idx] = main_col_name

# 读取Excel时跳过表头行，后续手动设置表头
df_raw = pd.read_excel('your_file.xlsx', header=None, skiprows=1)

# 生成新表头列表
new_headers = []
for col_idx in range(1, df_raw.shape[1] + 1):
    if col_idx in col_name_map:
        new_headers.append(col_name_map[col_idx])
    else:
        # 非合并列直接取原表头值
        new_headers.append(ws.cell(row=header_row_num, column=col_idx).value)

# 设置新表头
df_raw.columns = new_headers

# 可选：将同一列名下的多列转为多行，过滤空值
df_clean = df_raw.melt(id_vars=['Student ID', 'x_1', 'x_2'], 
                       var_name='category', value_name='value').dropna(subset=['value'])

方法2：读取后重命名列并整理 #

如果已读取带Unnamed列的DataFrame，直接处理列名和数据：

import pandas as pd

# 假设df是已读取的目标DataFrame
target_col = 'xyz_123'
# 获取合并列的起始索引
start_col_idx = df.columns.get_loc(target_col)

# 重命名后续的Unnamed列
new_columns = df.columns.tolist()
for idx in range(start_col_idx + 1, len(new_columns)):
    if 'Unnamed' in new_columns[idx]:
        new_columns[idx] = target_col

df.columns = new_columns

# 整理数据：转多行并过滤空值
df_clean = df.melt(id_vars=['Student ID', 'x_1', 'x_2'], 
                   var_name='category', value_name='value').dropna(subset=['value'])

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