如何用Pandas批量读取多Excel文件的同工作表并合并处理？

核心思路 #

直接批量读取所有目标Excel文件的KPI工作表，合并为单个DataFrame后统一完成清洗、格式转换，最后导出，避免逐个文件处理的重复耗时。

实现步骤 #

1. 导入所需库

import pandas as pd
import glob

注意：读取.xlsx文件需确保已安装openpyxl；读取.xls文件则安装xlrd（仅支持旧版xls格式）。

2. 获取所有Excel文件路径

指定目标文件夹路径，用glob匹配所有Excel文件：

# 替换为你的文件所在文件夹路径，支持通配符匹配
excel_files = glob.glob("/path/to/your/excel/files/*.xlsx")

3. 批量读取并合并KPI工作表

用列表推导式一次性读取所有KPI工作表，再通过pd.concat合并：

# 读取每个文件的KPI工作表，存储为DataFrame列表
kpi_dfs = [pd.read_excel(file, sheet_name="KPI") for file in excel_files]

# 合并所有DataFrame，重置索引避免重复
merged_df = pd.concat(kpi_dfs, ignore_index=True)

若需保留原文件来源信息（方便后续排查问题），可添加额外列标记：

kpi_dfs = []
for file in excel_files:
    df = pd.read_excel(file, sheet_name="KPI")
    df["source_file"] = file  # 添加列记录原文件名
    kpi_dfs.append(df)
merged_df = pd.concat(kpi_dfs, ignore_index=True)

4. 数据清洗与格式转换

以逆透视为例，假设KPI表有固定维度列和多个指标列，用pd.melt完成格式转换：

# 假设维度列为"日期"、"部门"，其余为指标列
merged_df = pd.melt(
    merged_df,
    id_vars=["日期", "部门"],  # 保留的维度列
    var_name="KPI指标",       # 转换后的指标名称列
    value_name="指标值"       # 转换后的指标值列
)

# 其他清洗操作示例：处理空值、转换数据类型
merged_df = merged_df.dropna(subset=["指标值"])  # 删除指标值为空的行
merged_df["指标值"] = merged_df["指标值"].astype(float)  # 转换为数值类型

5. 导出为单个Excel文件

merged_df.to_excel("合并后的KPI数据.xlsx", index=False, engine="openpyxl")

优化与异常处理 #

• 若文件体积过大，可分批次读取合并，避免内存溢出：

  batch_size = 20
  merged_df = pd.DataFrame()
  for i in range(0, len(excel_files), batch_size):
      batch_files = excel_files[i:i+batch_size]
      batch_dfs = [pd.read_excel(f, sheet_name="KPI") for f in batch_files]
      batch_merged = pd.concat(batch_dfs, ignore_index=True)
      merged_df = pd.concat([merged_df, batch_merged], ignore_index=True)
  

• 增加异常捕获，跳过损坏或无KPI工作表的文件：

  kpi_dfs = []
  for file in excel_files:
      try:
          df = pd.read_excel(file, sheet_name="KPI")
          df["source_file"] = file
          kpi_dfs.append(df)
      except Exception as e:
          print(f"处理文件{file}失败: {str(e)}")
  merged_df = pd.concat(kpi_dfs, ignore_index=True)
  

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