多Sheet Excel文件解析与分类数据处理问题（Pandas）

阿华AIGC实验室

2026-5-8

针对多格式Excel Sheet的分类数据解析与自动识别方案

看起来你正在处理一个混合格式的Excel文件，需要为不同类别做回归分析，我来帮你梳理下实用的解决方案：

问题1：拆分第二种格式Sheet中的多类别数据

首先假设你的第二种格式Sheet是同一Sheet内包含多个类别区块（比如每个类别以标题行开头，后面跟着对应Price和Quantity数据）——这是这类场景的常见情况。如果你的格式略有不同，可以根据实际情况调整下面的逻辑：

具体步骤（代码示例）：

先读取整个Sheet的原始数据（不指定表头）：

import pandas as pd

# 读取目标Sheet的原始数据，不设置表头
df_raw = pd.read_excel('./question.xlsx', sheet_name=j, header=None)

识别类别标记行：
假设你的类别标记是类似"类别A"、"A"这样的内容，且位于第一列（可根据实际调整列索引）：

# 找出所有类别标题行的索引（这里假设第一列包含类别标识，且不是空值）
category_rows = df_raw[df_raw[0].notna() & df_raw[0].str.contains('类别|A|B|C', regex=True)].index.tolist()
# 加上Sheet末尾索引，方便处理最后一个类别
category_rows.append(len(df_raw))

拆分每个类别的数据区块：

category_dfs = {}

for i in range(len(category_rows)-1):
    # 获取当前类别的起始和结束行索引
    start_row = category_rows[i]
    end_row = category_rows[i+1] - 1
    
    # 提取类别ID（比如从标题行中提取A/B/C）
    category_id = df_raw.iloc[start_row, 0].replace('类别', '').strip()
    
    # 提取该类别的数据（表头行是类别行的下一行）
    category_data = df_raw.iloc[start_row+1:end_row]
    # 设置表头
    category_data.columns = ['Price', 'Quantity']
    # 清理空行和无效数据
    category_data = category_data.dropna(subset=['Price', 'Quantity']).reset_index(drop=True)
    
    # 保存到字典，键为类别ID
    category_dfs[category_id] = category_data

这样你就得到了每个类别对应的DataFrame，可以直接用于后续的回归分析。

问题2：自动识别Sheet格式并兼容解析

要实现自动识别，核心是找到两种格式的特征差异。根据你的描述，第一种格式的Sheet名称就是类别ID，且数据是单一类别的；第二种格式的Sheet包含多个类别。我们可以基于这两个特征来做判断：

自动识别与解析流程：

def process_excel_sheet(sheet_name):
    # 先尝试按格式1读取
    try:
        df_format1 = pd.read_excel('./question.xlsx', sheet_name=sheet_name, header=1)
        # 验证是否符合格式1：存在Price和Quantity列，且Sheet名称是有效类别ID
        if 'Price' in df_format1.columns and 'Quantity' in df_format1.columns and sheet_name in ['A', 'B', 'C']:
            # 返回格式1的结果：字典，键为Sheet名称（类别ID），值为DataFrame
            return {sheet_name: df_format1.dropna(subset=['Price', 'Quantity']).reset_index(drop=True)}
    except Exception as e:
        # 格式1读取失败，进入格式2处理
        pass
    
    # 按格式2处理（复用问题1的逻辑）
    df_raw = pd.read_excel('./question.xlsx', sheet_name=sheet_name, header=None)
    category_rows = df_raw[df_raw[0].notna() & df_raw[0].str.contains('类别|A|B|C', regex=True)].index.tolist()
    category_rows.append(len(df_raw))
    
    category_dfs = {}
    for i in range(len(category_rows)-1):
        start_row = category_rows[i]
        end_row = category_rows[i+1] - 1
        category_id = df_raw.iloc[start_row, 0].replace('类别', '').strip()
        category_data = df_raw.iloc[start_row+1:end_row]
        category_data.columns = ['Price', 'Quantity']
        category_data = category_data.dropna(subset=['Price', 'Quantity']).reset_index(drop=True)
        category_dfs[category_id] = category_data
    
    return category_dfs

# 遍历所有Sheet处理
all_sheets = pd.ExcelFile('./question.xlsx').sheet_names
all_category_data = {}

for sheet in all_sheets:
    sheet_data = process_excel_sheet(sheet)
    all_category_data.update(sheet_data)

# 现在all_category_data里是所有类别对应的DataFrame，直接用于回归分析
for category, df in all_category_data.items():
    print(f"开始处理类别{category}的回归分析...")
    # 这里加入你的回归分析代码（比如statsmodels或sklearn的回归实现）