如何使用 Python 读取数据量庞大的 excel 文件

使用 pandas.read_excel 读取大文件时，的确会遇到性能瓶颈，特别是对于10万行20列这种规模的 .xlsx 文件，常规的 pandas 方法可能会比较慢。

要提高读取速度，关键是找到更高效的方式处理 Excel 文件，特别是在 Python 的生态圈中，已经有多个技术可以帮助解决这个问题。

一种办法是使用 openpyxl 直接处理 Excel 文件，结合 pandas 来读取数据。这可以让我们在处理数据时获得更大的灵活性，并通过分块读取文件来提高效率。

官网地址：
https://openpyxl.readthedocs.io/en/stable/

另外，还可以选择 pyxlsb 这个库，它可以更快速地处理 .xlsb 格式的文件，比传统的 .xlsx 格式快很多。

官网地址：https://pypi.org/project/pyxlsb/
如果可能的话，将文件转为 .csv 格式读取也会显著提高性能，因为 CSV 文件是纯文本格式，相较于 .xlsx 的结构化存储，读取会更加高效。

分析 pandas.read_excel 的性能问题

在实际中，pandas.read_excel 本身的性能瓶颈主要来自于两个方面：数据的解析与文件的格式。.xlsx 是一种基于 XML 的文件格式，因此在读取时需要解析 XML，这本身就是一个比较慢的过程。尤其当文件较大时，解析 XML 的时间会大幅增加。

为了解决这个问题，可以考虑以下几种优化策略：

1. 使用不同的引擎：pandas 支持多种 Excel 解析引擎，比如 openpyxl 和 xlrd。根据情况选择合适的引擎，可能会改善读取性能。

2. 分块读取：可以通过逐步读取文件的方式，避免一次性将整个文件加载到内存中。这可以显著减少内存占用，并提高读取的稳定性。

3. 选择合适的文件格式：如果文件格式不是必须的，可以将 .xlsx 文件转为 .csv 文件，这样可以使用更高效的读取方法。

优化方案 1：使用 openpyxl 和 pandas

openpyxl 是 pandas 内置支持的引擎之一，但它的读取速度较慢。在这种情况下，可以手动使用 openpyxl 读取数据，然后将其转换为 pandas 的 DataFrame。

代码示例

import pandas as pd
from openpyxl import load_workbook

# 读取xlsx文件的路径
file_path = "your_large_file.xlsx"

# 使用 openpyxl 直接加载工作簿
wb = load_workbook(filename=file_path, read_only=True)
sheet = wb.active

# 使用生成器按行读取数据，避免一次性加载所有数据
data = []
for row in sheet.iter_rows(values_only=True):
    data.append(row)

# 转换为 pandas DataFrame
df = pd.DataFrame(data[1:], columns=data[0])

# 打印读取的数据
print(df.head())

通过这种方法，我们避免了一次性将整个文件加载到内存中，而是使用了 openpyxl 的 iter_rows 方法逐行读取文件内容。这样，即使文件非常大，也能有效减轻内存负担。

优化方案 2：使用 pyxlsb 读取 .xlsb 文件

.xlsb 是二进制的 Excel 文件格式，它比 .xlsx 文件格式更为高效，尤其是在处理大文件时，可以显著减少读取时间。pyxlsb 库是一个专门用于读取 .xlsb 文件的高效库，配合 pandas 可以更快地读取数据。

代码示例

import pandas as pd
from pyxlsb import open_workbook

# 将 .xlsx 文件转换为 .xlsb 格式后使用此方法读取
file_path = "your_large_file.xlsb"

with open_workbook(file_path) as wb:
    with wb.get_sheet(1) as sheet:
        data = []
        for row in sheet.rows():
            data.append([item.v for item in row])

df = pd.DataFrame(data[1:], columns=data[0])
print(df.head())

使用 pyxlsb 可以有效加快 Excel 文件的读取速度，特别是在处理非常大的文件时，这个方法比 pandas.read_excel 提供的默认引擎快很多。不过需要注意的是，这种方法仅适用于 .xlsb 格式文件。

优化方案 3：使用 dask 分块处理大数据

dask 是一个支持并行计算的 Python 库，它可以用来处理大型数据集。如果我们遇到的数据文件过大，dask 提供了类似 pandas 的 API，但它会将大文件分块处理，避免一次性占用大量内存。

代码示例

import dask.dataframe as dd

# 使用 dask 读取大文件
file_path = "your_large_file.xlsx"
df = dd.read_excel(file_path)

# 使用 dask 处理数据
print(df.head())

dask 是一个非常强大的工具，它不仅支持分布式计算，还可以在多核环境下加快处理速度。通过将文件拆分成小块并行处理，dask 能够高效地应对大规模数据集的读取和计算。

优化方案 4：将文件转换为 CSV 格式

如果文件的格式不是必须的，那么将 .xlsx 文件转换为 .csv 格式是一种直接且有效的方式。.csv 格式相较于 .xlsx 没有复杂的 XML 结构，因此读取速度会快得多。转换后可以直接使用 pandas.read_csv 来读取数据，速度会比 read_excel 快很多。

代码示例

import pandas as pd

# 假设已经将文件转换为 CSV 格式
file_path = "your_large_file.csv"

# 使用 pandas 读取 CSV 文件
df = pd.read_csv(file_path)

# 打印前几行数据
print(df.head())

通过这种方式，能够显著提高数据读取速度，因为 .csv 格式的文件是纯文本，不需要复杂的解析过程。

其他可能的优化策略

除了前面提到的几种方法，还有一些其他技术可以用来进一步优化 Excel 文件的读取速度：

1. 并行读取：如果系统支持，可以将 Excel 文件按工作表或其他分块标准进行拆分，使用并行处理技术（如 multiprocessing）同时读取多个小文件。

2. 数据格式优化：如果文件的数据结构允许，转换为 Parquet 或 HDF5 格式，这些格式在大数据处理方面的性能往往优于 Excel 和 CSV。

3. 增加内存或硬件支持：在某些极端情况下，硬件资源不足也可能是瓶颈。增加内存或使用更快的硬盘（如 SSD）可以提高整体数据读取的性能。

总结

通过上述几种方法，可以大幅优化使用 Python 读取大型 Excel 文件的性能。openpyxl 适用于灵活处理 .xlsx 文件，pyxlsb 则是处理 .xlsb 文件的利器，而使用 dask 可以分块读取并行处理大数据集。此外，如果可以转换文件格式，使用 .csv 是提升读取速度的有效途径。

不同的方案适用于不同的场景，开发者可以根据具体需求选择最合适的解决方案。例如，当文件格式无法改变时，openpyxl 结合 pandas 是一个相对平衡的选择，而在文件格式灵活的情况下，将 .xlsx 转为 .csv 并使用 pandas.read_csv 则能最大化提高读取性能。