使用Pandas读取Excel多段类表数据并合并为单个DataFrame

我有一个Excel文件，里面有多组类表格格式的数据（未设置为Excel正式表格），这些表格上下堆叠，需要读取并合并成单个DataFrame。数据结构示例如下：

Commodities Cashflows

Heating Oil
Counterparty Ref#   TradeDate  Commodity    Price  MTM
------------ ----   ---------  ----------   -----  ----
xxx          DF9-1  23-Sep-19  Heating Oil  10.00  10,000
xxx          DF9-2  23-Sep-19  Heating Oil  10.00  10,000
xxx          DF9-3  23-Sep-19  Heating Oil  10.00  10,000
xxx          DF9-4  23-Sep-19  Heating Oil  10.00  10,000

WTI-IPE
Counterparty Ref#   TradeDate  Commodity    Price  MTM
------------ ----   ---------  ----------   -----  ----
xxx          DF9-1  23-Sep-19  WTI-IPE      10.00  10,000
xxx          DF9-1  23-Sep-19  WTI-IPE      10.00  10,000
xxx          DF9-1  23-Sep-19  WTI-IPE      10.00  10,000
xxx          DF9-1  23-Sep-19  WTI-IPE      10.00  10,000

每组表格的商品名称不固定，没法靠商品名称定位表格起始行。我原本想通过固定首列“Counterparty”查找表格起始行，写了如下函数：

def find_start_of_file(file):
    with open(file, 'r') as f:
        for line_num, line in enumerate(f):
            if line.startswith('Counterparty'):
                return line_num

但这个函数只能定位第一个表格的起始位置，没法读取后续所有表格并合并到同一个DataFrame里，该怎么解决？

思路说明 #

直接用文本读取Excel文件的方式不合适，应该用pandas读取整个工作表，通过识别所有以Counterparty开头的表头行来拆分多个表格，清理无效内容后合并成单个DataFrame。

具体实现代码 #

import pandas as pd

def merge_excel_tables(excel_path, sheet_name=0):
    # 读取整个工作表，不设置表头，保留所有原始行
    df_raw = pd.read_excel(excel_path, sheet_name=sheet_name, header=None)
    
    # 找到所有表头行的索引（第一列值为'Counterparty'的行）
    header_indices = df_raw[df_raw[0] == 'Counterparty'].index.tolist()
    
    all_valid_tables = []
    # 遍历每个表头行，处理对应表格
    for idx, header_row in enumerate(header_indices):
        # 确定当前表格的结束位置
        if idx < len(header_indices) - 1:
            # 下一个表头行的前两行：跳过空行和分隔线行
            end_row = header_indices[idx+1] - 2
        else:
            # 最后一个表格到工作表末尾
            end_row = df_raw.index[-1]
        
        # 提取当前表格数据
        table_data = df_raw.loc[header_row:end_row, :]
        # 设置表头为当前表头行的内容
        table_data.columns = table_data.iloc[0]
        # 移除表头行本身
        table_data = table_data[1:]
        
        # 清理无效行：去掉分隔线行和全空行
        table_data = table_data[table_data['Counterparty'] != '------------'].dropna(how='all')
        
        # 转换数据类型：Price转为数值，MTM去掉逗号后转为数值
        table_data['Price'] = pd.to_numeric(table_data['Price'], errors='coerce')
        table_data['MTM'] = pd.to_numeric(table_data['MTM'].str.replace(',', ''), errors='coerce')
        
        all_valid_tables.append(table_data)
    
    # 合并所有表格
    merged_df = pd.concat(all_valid_tables, ignore_index=True)
    return merged_df

# 使用示例
final_merged_data = merge_excel_tables('your_excel_file.xlsx')
print(final_merged_data)

代码解释 #

1. 读取原始数据：用pd.read_excel读取整个工作表，不指定表头，保留所有行，避免提前过滤掉有用信息。
2. 定位所有表头行：筛选第一列值为Counterparty的行，得到所有表格的起始索引。
3. 拆分处理单个表格：
   • 确定每个表格的结束位置：下一个表头行的前两行（跳过空行和分隔线），最后一个表格则到工作表末尾。
   • 重新设置表格表头，并移除表头行本身。
   • 清理无效行：去掉分隔线行和全空行，保证数据有效性。
   • 转换数据类型：将Price和MTM转为数值类型，方便后续统计计算。
4. 合并表格：用pd.concat将所有处理好的表格合并成一个完整的DataFrame。

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