如何将多工作表Excel合并为单CSV并解决DataFrame异常问题

问题根源 #

你的代码只读取了每个Excel文件的第一个工作表，但每个文件有30多个工作表，这直接导致大部分数据没被加载，进而出现列缺失、错位和NaN值。另外Eurostat的Excel数据常带有复杂表头（比如合并单元格、多行表头），也会干扰数据读取后的结构。

修正代码 #

import os
import pandas as pd

folder_path = 'file'
dfs = []

for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'): 
        file_path = os.path.join(folder_path, filename)
        
        try:
            # 获取当前Excel里的所有工作表名称
            sheet_names = pd.ExcelFile(file_path).sheet_names
            # 遍历每个工作表
            for sheet_name in sheet_names:
                # 读取工作表，header参数根据你的实际表头位置调整（比如表头在第1行就写header=0）
                df = pd.read_excel(file_path, sheet_name=sheet_name, header=0)
                # 可选：添加来源标记，方便后续排查数据问题
                df['来源文件'] = filename
                df['来源工作表'] = sheet_name
                # 删掉全是空值的行
                df = df.dropna(how='all')
                dfs.append(df)
        except Exception as e:
            print(f"读取文件 '{filename}' 出错: {str(e)}")

# 合并所有数据，ignore_index=True重置行索引
combined_df = pd.concat(dfs, ignore_index=True)

# 导出为CSV
combined_df.to_csv('combined_data.csv', index=False)

额外优化技巧 #

• 处理复杂表头：如果原Excel是多行表头，可合并表头后再读取：

  # 示例：把前两行合并成一个表头
  df = pd.read_excel(file_path, sheet_name=sheet_name, header=[0,1])
  df.columns = ['_'.join(col).strip() for col in df.columns.values]
  

• 检查列一致性：合并前可以先核对每个工作表的列名，避免合并后出现大量空值：

  # 打印每个工作表的列名，确认是否统一
  for sheet_name in sheet_names:
      temp_df = pd.read_excel(file_path, sheet_name=sheet_name, header=0)
      print(f"工作表 {sheet_name} 的列: {temp_df.columns.tolist()}")
  

• 关键列清洗：合并后针对国家、时长、产品名称这些核心列做清洗，过滤空值：

  combined_df = combined_df.dropna(subset=['国家', '时长', '产品名称'])
  

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