如何让Python自动识别CSV文件中的多组测量类型表头

问题背景 #

我有一个包含多组测量数据的大型CSV文件，目前通过以下Pandas代码手动指定测量类型名称完成了多表头数据拆分：

import pandas as pd

df = pd.read_csv("three measurement.csv", header=None)

# find header rows
df_titles = ["Level and Distortion", "THD Ratio", "Reference Waveform"]
# create groups for each section
groupings = df.iloc[:, 0].str.contains("|".join(df_titles)).cumsum()

# split into new dataframes as dictionary
d = {}
for i, j in df.groupby(groupings):
    # define name of dictionary key as title, and set data of DF as values
    d[j.iloc[0, 0]] = pd.DataFrame(data=j.values[4:, :],
                                   # create MultiIndex from 3 header rows
                                   columns=pd.MultiIndex.from_arrays(
                                       j.iloc[0:4, :].ffill(axis=1).values))
    # suggested not to use, but you can set the variables directly (outside of the dictionary)
    globals()[j.iloc[0, 0]] = pd.DataFrame(data=j.values[4:, :],
                                           columns=pd.MultiIndex.from_arrays(
                                               j.iloc[0:4, :].ffill(axis=1).values))

但测量类型数量可能增减（最多11种），希望修改代码，无需手动定义df_titles变量，让Python自动识别CSV中的各类测量表头并完成数据拆分。

CSV示例内容：

"Level and Distortion",,,,,,,,,,,,,,,,
"Ch1 (F)",,"Ch1 (H2)",,"Ch1 (H3)",,"Ch1 (Total)",,"Ch2 (F)",,"Ch2 (H2)",,"Ch2 (H3)",,"Ch2 (Total)",
X,Y,X,Y,X,Y,X,Y,X,Y,X,Y,X,Y,X,Y
Hz,Vrms,Hz,Vrms,Hz,Vrms,Hz,Vrms,Hz,Vrms,Hz,Vrms,Hz,Vrms,Hz,Vrms
20,0.00772013164376534,20,5.60982648239952E-05,20,0.000389709733151927,20,0.011492581958802,20,0.00699792689186063,20,0.000151471712877565,20,0.000389940899485093,20,0.010080448380793
21.1179638886716,0.00747175133180212,21.1179638886716,8.83327496082501E-05,21.1179638886716,0.000426696028852445,21.1179638886716,0.0122462876404656,21.1179638886716,0.00756340531214287,21.1179638886716,0.000181697169530165,21.1179638886716,0.000443499862648762,21.1179638886716,0.0108494276048029
"THD Ratio",,,,,,,,,,,,,,,,
Ch1,,Ch2,,,,,,,,,,,,,
X,Y,X,Y,,,,,,,,,,,,
Hz,%,Hz,%,,,,,,,,,,,,
20,83.009797319554,20,82.1460991930652,,,,,,,,,,,,
21.1179638886716,85.3656629417084,21.1179638886716,82.0338466400102,,,,,,,,,,,,
22.2984199401618,90.6674826441566,22.2984199401618,85.7190774666039,,,,,,,,,,,,
"Reference Waveform",,,,,,,,,,,,,,,,
Ch1,,Ch2,,,,,,,,,,,,,
X,Y,X,Y,,,,,,,,,,,,
s,V,s,V,,,,,,,,,,,,
0,0,0,0,,,,,,,,,,,,
2.08333333333333E-05,6.47890208369956E-08,2.08333333333333E-05,6.47890208369956E-08,,,,,,,,,,,,
4.16666666666667E-05,5.18304721721536E-07,4.16666666666667E-05,5.18304721721536E-07,,,,,,,,,,,,
6.25E-05,1.74923655865586E-06,6.25E-05,1.74923655865586E-06,,,,,,,,,,,,

优化后的代码 #

import pandas as pd

df = pd.read_csv("three measurement.csv", header=None)

# 自动识别所有测量类型标题行：筛选第一列非空且非空白的行
title_rows = df[df.iloc[:, 0].notna() & df.iloc[:, 0].str.strip().ne("")]
df_titles = title_rows.iloc[:, 0].tolist()

# 按标题行分组，保持原分组逻辑
groupings = df.iloc[:, 0].str.contains("|".join(df_titles)).cumsum()

# 拆分数据到字典存储
d = {}
for i, j in df.groupby(groupings):
    title = j.iloc[0, 0]
    # 提取标题行后的第4行开始的内容作为数据
    data_rows = j.values[4:, :]
    # 用前4行构建多级表头，保留ffill补全逻辑
    multi_columns = pd.MultiIndex.from_arrays(j.iloc[0:4, :].ffill(axis=1).values)
    d[title] = pd.DataFrame(data=data_rows, columns=multi_columns)
    
    # 可选：创建全局变量（*不推荐使用，建议优先通过字典d访问数据*）
    globals()[title] = d[title]

关键改动说明 #

• 自动识别标题：通过筛选第一列非空且非空白的行，自动获取所有测量类型的标题，替代手动定义的df_titles，适配测量类型数量变化的场景
• 保留原有逻辑：分组、多级表头构建、数据提取的逻辑和原代码一致，确保拆分后的数据结构完全兼容
• 存储建议：优先使用字典d管理拆分后的DataFrame，避免globals()造成的全局变量污染，访问时直接用d["Level and Distortion"]即可

内容的提问来源于stack exchange，提问作者fauzan-mhd