如何用Pandas动态识别Excel表头行并合并多个xlsx文件

我有10个Excel文件，所有文件的表头/列名预期一致，文件名如ABC.xlsx、DEF_123.xlsx，没有统一命名规则。
但这些文件的表头位置不固定：部分文件表头在第1行，部分在第2行（第1行为空），还有部分在第5行（前4行全空）。
我的目标是将所有Excel文件的数据垂直合并为一个Pandas DataFrame。

我之前写的代码只能处理表头在第1行（索引0）或第2行（索引1）的情况，代码如下：

record_counts = []
dfs=[]
folder_path = Path.cwd()
filenames = sorted(glob.glob(str(folder_path)+"\*.xlsx"))

for f in filenames:
    filename = os.path.basename(f)
    df = pd.read_excel(filename, header=None)
    header_row = 0
    if df.iloc[0].isna().all():
        header_row = 1
    df = pd.read_excel(filename, header=header_row)
    count = len(df.index)
    record_counts.append(count)
    dfs.append(df)
combined_df = pd.concat(dfs, ignore_index=True)

现在需要修改代码，实现动态识别表头所在行并正确提取数据。

核心思路是：先读取整个文件（不指定表头），然后找到第一个非全空的行作为表头行——因为空行的所有单元格都是NaN，而表头行必然包含有效列名。

修改后的代码如下：

import os
import glob
from pathlib import Path
import pandas as pd

record_counts = []
dfs = []
folder_path = Path.cwd()
filenames = sorted(glob.glob(str(folder_path) + "\*.xlsx"))

for f in filenames:
    filename = os.path.basename(f)
    # 先读取整个文件，不指定表头
    temp_df = pd.read_excel(filename, header=None)
    
    # 找到第一个非全空的行（表头行）
    # 生成布尔序列：True表示该行全空，False表示非全空
    all_null_rows = temp_df.isna().all(axis=1)
    # 找到第一个非全空行的索引
    header_row = all_null_rows.idxmin()  # idxmin返回第一个False的位置
    
    # 重新读取文件，指定表头行
    df = pd.read_excel(filename, header=header_row)
    
    # 记录行数并添加到列表
    count = len(df.index)
    record_counts.append(count)
    dfs.append(df)

# 合并所有DataFrame
combined_df = pd.concat(dfs, ignore_index=True)

代码说明 #

• temp_df.isna().all(axis=1)：逐行检查是否所有单元格都是NaN，返回一个布尔Series，全空行标记为True，非全空行标记为False。
• all_null_rows.idxmin()：因为False的数值小于True，idxmin会返回第一个False的索引，也就是表头所在的行（第一个非全空行）。
• 重新读取文件时指定header=header_row，Pandas会自动将该行作为表头，并跳过之前的空行。

可选边界处理 #

如果担心存在全空文件，可以在找到表头行后添加判断：

# 如果所有行都是空的，跳过该文件
if all_null_rows.all():
    print(f"文件 {filename} 全为空，已跳过")
    continue

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