使用Pandas读取Excel时内存溢出问题及优化咨询

我正在将Excel文件加载到Pandas DataFrame中做数据处理，脚本在16GB内存机器上运行正常，但在8GB内存的目标机器上出现内存溢出。用df.info(memory_usage='deep')检查后发现，最终生成的DataFrame最大才4MB，所以推断内存溢出是在read_excel执行过程中发生的。

查资料得知，Pandas会读取Excel认定的已用范围，这个范围可能比实际存储数据的范围大很多。但这个文件是用户活跃使用的，手动删空白单元格不是长久办法，用户随时可能再添加；限制read_excel的最大行列数也不合理，未来数据量可能超过这个限制。

另外我的代码还存在nrows参数的异常：不设置时，原本88KB的DataFrame会变成136MB，但Excel实际数据仅略超100万行，为什么解析全部行和限制100万行的差异这么大？

我的代码如下：

df = pd.read_excel(
    file_location
    , sheet_name = sheet_name
    , header = None
    , skiprows = number_of_rows_to_skip
    , usecols = used_cols
    , names = col_names
    # 若不设置此参数，DataFrame会异常增大
    , nrows = 1000000
    )

• used_cols是包含列字符的字符串，例如"A, C, F"
• col_names是给各列指定名称的列表

解决方案 #

1. 精准定位实际有效数据范围后读取 #

用openpyxl引擎先加载工作簿，手动定位到真正有数据的最后一行，再用这个范围去读取，避免加载Excel标记的超大空白区域：

from openpyxl import load_workbook

# 只读模式加载工作簿，仅读取单元格实际值
wb = load_workbook(file_location, read_only=True, data_only=True)
ws = wb[sheet_name]

# 从Excel标记的最大行往上遍历，找到最后一行有数据的行
actual_max_row = ws.max_row
for row in range(actual_max_row, 0, -1):
    if any(cell.value is not None for cell in ws[row]):
        actual_max_row = row
        break

# 用实际有效行数读取数据，跳过指定行数后，有效行数为实际最大行减去跳过行数
df = pd.read_excel(
    file_location,
    sheet_name=sheet_name,
    header=None,
    skiprows=number_of_rows_to_skip,
    usecols=used_cols,
    names=col_names,
    nrows=actual_max_row - number_of_rows_to_skip
)

2. 分块读取并过滤空白行 #

如果不确定有效范围，用chunksize分批次读取，每次读取一小段数据后过滤掉全空行，再合并成最终DataFrame，降低单批次内存占用：

chunk_list = []
# 每次读取10000行，可根据内存情况调整
for chunk in pd.read_excel(
    file_location,
    sheet_name=sheet_name,
    header=None,
    skiprows=number_of_rows_to_skip,
    usecols=used_cols,
    names=col_names,
    chunksize=10000
):
    # 过滤当前块中的全空行
    chunk = chunk.dropna(how='all')
    chunk_list.append(chunk)

df = pd.concat(chunk_list, ignore_index=True)

3. nrows参数异常的原因 #

Excel的"已用范围"标记不会自动收缩：如果用户曾经在远超出实际数据的行（比如200万行）输入过内容又删除，Excel仍会把已用范围标记到200万行。不设置nrows时，Pandas会读取整个标记范围的所有行，包括大量全空行，读取过程中需要为这些行分配内存，导致内存飙升；而设置nrows=1000000时，刚好覆盖了实际数据的范围，不会加载后续的空白行，所以内存占用正常。

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