非标准格式Excel文件转换为csv或pandas DataFrame问题咨询

• 依赖安装：需要用到pandas和Excel解析库，执行以下命令安装
  pip install pandas openpyxl
• 第一步：先探测文件结构
  非标准Excel通常存在多层表头、前置无效行、合并单元格等问题，先读取前20行确认表头位置和数据起始位置

  import pandas as pd
  
  # header设为None不指定表头，读取前20行查看原始结构
  df_raw = pd.read_excel("你的本地Excel文件路径.xlsx", nrows=20, header=None)
  print(df_raw)
  

• 第二步：正式读取文件
  根据上一步确认的表头行号、数据起始位置调整参数，示例如下：

  df = pd.read_excel(
      "你的本地Excel文件路径.xlsx",
      header=[2,3], # 此处替换为实际表头行号，行号从0开始计数，多层表头传入列表即可
      skiprows=0, # 若文件开头有完全无效的说明行，可指定跳过的行数
      # usecols="A:F" 仅需要指定列时启用该参数，对应Excel列号范围
  )
  

• 第三步：处理特殊结构
  1. 多层表头合并：如果读取后是多层列索引，可以合并为单层列名方便后续操作

  df.columns = ['_'.join(col).strip() for col in df.columns.values]
  

  2. 合并单元格填充：Excel合并单元格仅首个位置存值，其余为空，可向前填充补全

  # 假设第一列为合并单元格的分类列，按实际情况调整列位置
  df.iloc[:,0] = df.iloc[:,0].ffill()
  

  3. 无效行过滤：如果存在汇总行、备注行等无效数据，可以按列值过滤

  # 示例：保留关键列不为空的行，按实际业务逻辑调整
  df = df[df["对应列名"].notna()]
  

• 第四步：导出为CSV

  df.to_csv("输出的CSV文件路径.csv", index=False, encoding="utf-8-sig")
  

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