复杂Excel转Pandas DataFrame遇阻，求数据分析适配方案

问题背景 #

• 生产一款产品需经过6次相同迭代流程：单次迭代称为batch，6次迭代的集合称为BIG BATCH，每次迭代会收集多个参数数据
• 完成6次迭代后合并产物得到最终产品，对产品进行测试，结果为0%-100%的百分比，合格标准为45%-65%
• 核心目标：分析各参数与最终测试结果的相关性，明确参数调整方向，让结果稳定落在合格区间

数据格式问题 #

原始Excel数据采用了跨行跨列的复合表头结构，每个BIG BATCH对应6个batch的参数列，同时关联最终测试结果列，这种非规整的格式无法直接用于Pandas的相关性分析。

已尝试的操作 #

尝试通过手动调整表头、插入辅助列、转置数据、构建多级索引的方式规整数据，核心代码如下：

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

url = "path\simplified fake data.xlsx"

df = pd.read_excel(url, sheet_name='Feuille2', header=None)
# 提取并转置前两行
first_two_rows = df.iloc[:1]
transposed_headers = first_two_rows.T
# 删除前两行
df = df.iloc[1:]

df.loc[2,0] = "General"
df.loc[1,0] = "test"
new_row = ["General", "Final test", "45% - 65%"] + [np.nan] * (df.shape[1] - 3)
df.loc[3] = new_row

def add_column_every_n(df, n, new_col_name, new_col_value):
    num_cols = df.shape[1]
    cpt = 0
    for i in range(n+3, num_cols  +int(num_cols/n), n + 1):
        df.insert(i, new_col_name + str(i), new_col_value)
        df.iloc[2,i] = first_two_row.iloc[0,i-n-cpt]
        df.iloc[1,i] = "Lot"
        cpt = cpt + 1
    return df

df = add_column_every_n(df, 6, 'NewCol', pd.NA)

# 向前填充空值
df[0] = df[0].ffill()
df.iloc[0,:] = df.iloc[0,:].ffill()

# 转置数据并构建多级索引
df = df.T
new_headers = df.iloc[:2].values.tolist()
df = df.iloc[2:]
df.columns = pd.MultiIndex.from_arrays(new_headers)

但最终构建的多级索引数据结构仍不符合分析需求，不确定后续如何有效关联参数与最终结果。

预期的数据格式 #

理想格式应为每一行对应一个BIG BATCH，包含：

• BIG BATCH的唯一标识
• 6个batch的所有参数（可命名为参数名_批次1、参数名_批次2……以此类推）
• 该BIG BATCH对应的最终测试结果
  这样的规整表格能直接用于相关性计算、可视化等后续分析操作。

求助内容 #

请问如何将这类复合结构的Excel数据转换为适合Pandas分析的规整格式？或者有没有更合理的数据结构建议，能支持后续的参数与结果相关性分析？常规教程未覆盖这类复杂格式处理，希望得到具体的处理思路或代码示例。

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