使用Pandas读取含合并行单元格的Excel并规整数据

问题场景 #

用Pandas清理Excel数据时，遇到合并单元格形式的分组类数据，需要将分组信息转换为独立列，实现数据规整分类。

当前数据格式 #

Sample   CD4     CD8
---------Group1-------------
Day 1   8311    17.3    6.44
Day 2   8312    13.6    3.50
Day 3   8321    19.8    5.88
---------Group2-------------
Day 1   8322    13.5    4.09
Day 2   8311    16.0    4.92
Day 3   8312    5.67    2.28
---------Group3-------------
Day 1   8321    13.0    4.34
Day 2   8322    10.6    1.95
Day 3   8312    5.67    2.28

期望输出格式 #

Sample   CD4     CD8    Group
Day 1   8311    17.3    6.44    Group1
Day 2   8312    13.6    3.50    Group1
Day 3   8321    19.8    5.88    Group1
Day 1   8322    13.5    4.09    Group2
Day 2   8311    16.0    4.92    Group2
Day 3   8312    5.67    2.28    Group2
Day 1   8321    13.0    4.34    Group3
Day 2   8322    10.6    1.95    Group3
Day 3   8312    5.67    2.28    Group3

解决代码与步骤 #

import pandas as pd

# 读取Excel文件，不自动设置表头，保留所有原始行
df = pd.read_excel("data.xlsx", header=None)

# 提取分组名称：从包含"Group"的行中匹配分组标识，生成Group列
df["Group"] = df[0].str.extract(r'(Group\d+)')[0]

# 向前填充分组值，让每个数据行继承上方最近的分组信息
df["Group"] = df["Group"].ffill()

# 过滤掉分组分隔行（含"---------"的行）和空行
df = df[~df[0].str.contains("---------", na=False)]

# 设置正确的表头：原表头在第一行，替换后跳过表头行
new_header = df.iloc[0]
df = df[1:]
df.columns = ["Day", "Sample", "CD4", "CD8", "Group"]

# 重置索引，整理成规整表格
df = df.reset_index(drop=True)

# 查看结果
print(df)

关键步骤说明 #

• 读取数据：用header=None避免Pandas误判表头，保留所有行的原始信息。
• 提取分组：通过正则匹配Group\d+提取分组名称，生成新列。
• 填充分组：ffill()方法将上方的分组值向下填充，确保每个数据行都有对应的分组。
• 过滤无效行：移除包含分隔线的分组标题行，只保留有效数据行。
• 设置表头：将原始表头行设为DataFrame的列名，完成格式规整。

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