使用pandas.read_excel读取相似Excel文件时列映射异常问题

背景 #

我尝试把第二个文件的DOB列数据复制到第一个文件，让两者外观一致，但在Excel里用Ctrl+F搜索空内容时，第一个文件找不到匹配结果，第二个文件在E1至G7单元格区域搜到21个匹配项，推测第二个文件存在空白/隐形单元格，这可能导致read_excel行为异常。

我用pandas.read_excel读取两个高度相似的Excel文件，返回结果差异极大。

测试代码 #

import pandas

data1 = pandas.read_excel(r'D:\User Files\Downloads\Programming\stackoverflow\test_1.xlsx')
print(data1)
data2 = pandas.read_excel(r'D:\User Files\Downloads\Programming\stackoverflow\test_2.xlsx')
print(data2)

读取结果 #

test_1.xlsx输出 #

Name        DOB Class Year  GPA
0  Redeye438 2008-09-22      Fresh    1
1  Redeye439 2009-09-20       Soph    2
2  Redeye440 2010-09-22     Junior    3
3  Redeye441 2011-09-20     Senior    4
4  Redeye442 2008-09-20      Fresh    4
5  Redeye443 2009-09-22       Soph    3

test_2.xlsx输出 #

Name  DOB  Class Year  GPA
Redeye438 2011-09-20 Fresh      1  NaN         NaN  NaN
Redeye439 2010-09-22 Soph       2  NaN         NaN  NaN
Redeye440 2009-09-20 Junior     3  NaN         NaN  NaN
Redeye441 2008-09-22 Senior     4  NaN         NaN  NaN
Redeye442 2011-09-22 Fresh      4  NaN         NaN  NaN
Redeye443 2010-09-20 Soph       3  NaN         NaN  NaN

问题原因 #

第二个文件的列映射完全错误，核心问题出在隐藏的空白单元格：

• 你在Excel中搜到的E1-G7区域的空匹配项，说明这些单元格虽然看起来是空的，但实际被Excel标记为“已占用”（可能是曾输入内容后删除，或设置过单元格格式）。
• pandas的read_excel默认会扫描Excel中所有被标记为“存在”的单元格来确定数据范围和表头。当第二份文件的表头行（第一行）在E、F、G列存在这些空白单元格时，pandas会误将它们当作表头的一部分，导致列索引识别混乱——原本的多列数据被合并到第一列，后续列因为没有对应数据全部显示为NaN。

解决方法 #

可以通过以下两种方式修复：

• 指定读取列范围，直接跳过空列区域：

data2 = pandas.read_excel(r'D:\User Files\Downloads\Programming\stackoverflow\test_2.xlsx', usecols="A:D")

• 让pandas自动过滤空表头列：

data2 = pandas.read_excel(r'D:\User Files\Downloads\Programming\stackoverflow\test_2.xlsx', header=0, usecols=lambda col: pandas.notna(col))

内容的提问来源于stack exchange，提问作者holts-shoe