Pandas处理交错表头xls订单数据 构建多级索引或转换为目标表结构

整体逻辑等价于你熟悉的SSIS中头表与明细表关联操作，将交错存储的客户头行、商品明细行拆分关联后清洗为入库格式，步骤如下：

注意：所有示例中的列名占位符需要替换为你实际DataFrame对应的列名

1. 关联头行与明细行（核心步骤） #

首先对每个子DataFrame做处理，将头行的客户维度信息填充到对应下属的所有明细行：

• 假设第三列列名为flag_col，头行flag_col='P'，明细行flag_col为NaaN/Naan
• 处理函数示例代码：

import pandas as pd

def process_single_sub_df(sub_df):
    # 给每个头行及下属的所有明细分配唯一组ID
    sub_df['header_grp_id'] = sub_df['flag_col'].eq('P').cumsum()
    
    # 提取头行数据生成临时头表
    header_df = sub_df[sub_df['flag_col'].eq('P')].rename(columns={
        'col1': 'customer_name', # 替换为头行客户名实际所在列名
        'col2': 'customer_id', # 替换为头行客户ID实际所在列名
        'col4': 'bill_date' # 替换为头行账单日期实际所在列名
    })[['header_grp_id', 'customer_name', 'customer_id', 'bill_date', 'Group']] # 保留需要关联的字段，Group为你已新增的分组标识
    
    # 筛选明细行并关联头表信息
    detail_df = sub_df[sub_df['flag_col'].str.lower().eq('naan')].rename(columns={
        'col1': 'order_date', # 替换为明细行下单日期实际所在列名
        'col2': 'item_name', # 替换为明细行商品名实际所在列名
        'col4': 'item_price', # 替换为明细行商品单价实际所在列名
        'col5': 'seller', # 替换为明细行卖家实际所在列名
        'col6': 'department' # 替换为明细行部门实际所在列名
    })
    merged_df = pd.merge(detail_df, header_df, on=['header_grp_id', 'Group'], how='left')
    return merged_df

2. 字段清洗与多卖家拆分 #

• 生成第一级索引所需的姓名:ID组合字段：
  merged_df['name_id'] = merged_df['customer_name'].str.strip() + ':' + merged_df['customer_id'].astype(str).str.strip()
• 处理多卖家场景，拆分生成独立行（按实际分隔符替换split内参数）：

merged_df['seller'] = merged_df['seller'].str.split(',')
merged_df = merged_df.explode('seller', ignore_index=True)

• 筛选入库所需字段，自动对齐Billing_Import表结构即可，示例：
  final_df = merged_df[['Group', 'name_id', 'customer_name', 'customer_id', 'bill_date', 'order_date', 'item_name', 'item_price', 'seller', 'department']]

3. 构建多级索引 #

按照需求设置两级索引：
final_df = final_df.set_index(['name_id', 'order_date'])

若后续直接导入数据仓库，可根据入库要求选择保留索引，或转为普通列后导出

批量处理所有子DataFrame #

如果拆分后的子df存储在列表中，批量处理代码如下：

processed_list = [process_single_sub_df(df) for df in sub_df_list]
# 合并所有子df的处理结果为总表
total_final_df = pd.concat(processed_list, ignore_index=True)

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