Команда Python for Devs подготовила перевод статьи о том, как читать Excel в Python быстрее всех. В статье результаты тестирования pandas, openpyxl, Tablib, DuckDB, LibreOffice и даже связки с Rust. Кто справился лучше всех и как за 4 секунды Python «проглотил» полмиллиона строк — читайте в статье.

---

У меня нет статистики, чтобы подтвердить это утверждение, но я почти уверен: Excel — самый распространённый способ хранить, обрабатывать и даже (!) передавать данные. Поэтому нет ничего удивительного в том, что рано или поздно приходится читать Excel в Python. Недавно у меня возникла такая необходимость, и я решил протестировать и замерить несколько способов чтения файлов Excel в Python.

В этой статье я сравню разные методы чтения Excel из Python.

Что мы тестируем?

Чтобы сравнить способы чтения Excel в Python, сначала нужно определить, что именно измерять и как.

Для начала создадим Excel-файл размером 25 МБ, содержащий 500 тысяч строк с различными типами данных в колонках:

Excel поддерживает два формата — xls и xlsx. Мы будем использовать более современный формат xlsx.

Для бенчмарков реализуем функции, которые импортируют данные из Excel и возвращают итератор словарей:

def iter_excel(file: IO[bytes]) -> Iterator[dict[str, object]]:
    # TODO...

Мы возвращаем итератор, чтобы потребитель мог обрабатывать файл построчно. Это потенциально позволяет сократить использование памяти, так как весь файл не хранится в оперативке одновременно. Однако, как покажут замеры, это не всегда возможно.

Чтобы получить «чистое» время выполнения, мы перебираем генератор без какой-либо обработки:

for row in iter_excel(file):
    pass

Так генератор будет полностью вычислен с минимальными накладными расходами по памяти и производительности.

Скорость

Первое и самое очевидное, что нужно измерить, — это время. Самый точный способ замерить время в Python для задач производительности — использовать time.perf_counter:

import time

start = time.perf_counter()
for row in iter_excel(file): pass
elapsed = time.perf_counter() - start

Мы запускаем таймер, итерируем весь генератор и вычисляем прошедшее время.

Типы

Некоторые форматы, такие как Parquet и Avro, самодостаточны и хранят схему прямо в файле. Другие, например CSV, печально известны тем, что вообще не содержат никакой информации о типах данных.

Excel можно рассматривать как формат, который хранит сведения о типах содержимого: есть ячейки с датами, числами, десятичными значениями и другими типами. Поэтому при загрузке данных из Excel бывает полезно получать их в изначальном типе. Особенно это важно для дат, где формат может быть неочевиден или вовсе неизвестен, а также для строк, содержащих цифры, например телефонные номера или почтовые индексы. В таких случаях попытка «угадать» тип может привести к ошибкам — например, к удалению ведущих нулей или неверной интерпретации формата.

Конечно, можно возразить, что при загрузке данных в систему схема должна быть известна заранее, и сохранение типов — не всегда обязательное требование.

Корректность

Чтобы проверить корректность процесса импорта, мы добавляем контрольную строку в начало Excel-файла. Эта строка служит эталоном, с которым мы сверяем импортированные данные.

# Test correctness of imported data using a control row
for key, expected_value in (
    ('number', 1),
    ('decimal', 1.1),
    ('date', datetime.date(2000, 1, 1)),
    ('boolean', True),
    ('text', 'CONTROL ROW'),
):
    try:
        value = control_row[key]
    except KeyError:
        print(f'? "{key}" missing')
        continue
    if type(expected_value) != type(value):
        print(f'? "{key}" expected type "{type(expected_value)}" received type "{type(value)}"')
    elif expected_value != value:
        print(f'? "{key}" expected value "{expected_value}" received "{value}"')
    else:
        print(f'? "{key}"')

После каждого бенчмарка мы запускаем этот тест, чтобы убедиться: все ожидаемые ключи присутствуют в контрольной строке, а типы и значения совпадают с ожидаемыми.

Чтение Excel в Python

Теперь у нас есть тестовый файл, способ проверить содержимое и определённые критерии измерений — можно приступать к импорту данных!

Чтение Excel с помощью Pandas

Pandas — библиотека для анализа данных в Python, и её обычно используют для любых задач, связанных с данными. Отличная отправная точка.

Прочитать Excel с помощью pandas можно так:

import pandas

def iter_excel_pandas(file: IO[bytes]) -> Iterator[dict[str, object]]:
    yield from pandas.read_excel(file).to_dict('records')

Просто две команды, объединённые цепочкой, чтобы получить список словарей из Excel-файла. Вот как выглядит одна строка результата:

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_pandas(f)
...     row = next(rows)
...     print(row)
...
{'boolean': True,
 'date': Timestamp('2000-01-01 00:00:00'),
 'decimal': 1.1,
 'number': 1,
 'text': 'CONTROL ROW'}

Сразу бросается в глаза, что дата представлена не как datetime.date, а как pandas.Timestamp. Остальное выглядит нормально. Если Timestamp нежелателен и вам нужен именно datetime.date, можно передать функцию-конвертер в read_excel:

import pandas

def iter_excel_pandas(file: IO[bytes]) -> Iterator[dict[str, object]]:
    yield from pandas.read_excel(file, converters={
        'date': lambda ts: ts.date(),
    }).to_dict('records')

Конвертер принимает pandas.Timestamp и преобразует его в datetime.date. Вот так выглядит контрольная строка с пользовательским конвертером:

{
    'number': 1,
    'decimal': 1.1,
    'date': datetime.date(2000, 1, 1),
    'boolean': True,
    'text': 'CONTROL ROW',
}

Если вы используете pandas для чтения Excel, логично предположить, что дальше вы тоже будете работать с данными в pandas, поэтому мы принимаем Timestamp как допустимый тип для бенчмарка.

Теперь запускаем бенчмарк на большом Excel-файле:

iter_excel_pandas
elapsed 32.98058952600695
? "number"
? "decimal"
? "date" expected type "<class 'datetime.date'>" received type "<class 'pandas._libs.tslibs.timestamps.Timestamp'>"
? "boolean"
? "text"

Импорт занял около 32 секунд. Тип поля даты — pandas.Timestamp, а не datetime.date, но это вполне приемлемо.

Чтение Excel с помощью Tablib

Tablib — одна из самых популярных библиотек в Python для импорта и экспорта данных в разных форматах. Изначально её разработал автор знаменитой библиотеки requests, поэтому здесь такой же упор сделан на удобство для разработчика и простоту использования.

Установить Tablib можно командой:

$ pip install tablib

Прочитать Excel с помощью tablib:

import tablib

def iter_excel_tablib(file: IO[bytes]) -> Iterator[dict[str, object]]:
    yield from tablib.Dataset().load(file).dict

Одна строка кода — и библиотека берёт на себя всю тяжёлую работу.

Перед запуском бенчмарка посмотрим, как выглядит первая строка результата:

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_tablib(f)
...     row = next(rows)
...     print(row)
...
OrderedDict([('number', 1),
             ('decimal', 1.1),
             ('date', datetime.datetime(2000, 1, 1, 0, 0)),
             ('boolean', True),
             ('text', 'CONTROL ROW')])

OrderedDict — это подкласс обычного словаря Python с дополнительными методами для изменения порядка элементов. Он определён в стандартном модуле collections и именно его возвращает tablib, когда вы запрашиваете словарь. Так как OrderedDict — это наследник dict и часть стандартной библиотеки, для наших целей он нас вполне устраивает.

Теперь запустим бенчмарк на большом Excel-файле:

iter_excel_tablib
elapsed 28.526969947852194
? "number"
? "decimal"
? "date" expected type "<class 'datetime.date'>" received type "<class 'datetime.datetime'>"
? "boolean"
? "text"

Импорт через tablib занял 28 секунд — быстрее, чем у pandas (32 секунды). Ячейка с датой была возвращена как datetime.datetime, а не datetime.date, что вполне приемлемо.

Посмотрим, удастся ли ещё сократить время выполнения.

Чтение Excel с помощью Openpyxl

Openpyxl — библиотека для чтения и записи Excel-файлов в Python. В отличие от Tablib, она работает только с Excel и не поддерживает другие форматы. Более того, и tablib, и pandas под капотом используют Openpyxl при чтении файлов в формате xlsx. Возможно, такая специализация даст выигрыш в производительности.

Установить openpyxl можно командой:

$ pip install openpyxl

Чтение Excel с помощью openpyxl:

import openpyxl

def iter_excel_openpyxl(file: IO[bytes]) -> Iterator[dict[str, object]]:
    workbook = openpyxl.load_workbook(file)
    rows = workbook.active.rows
    headers = [str(cell.value) for cell in next(rows)]
    for row in rows:
        yield dict(zip(headers, (cell.value for cell in row)))

На этот раз кода придётся написать чуть больше, разберём его пошагово:

1. Загрузка workbook: функция load_workbook поддерживает как путь к файлу, так и поток. В нашем случае мы работаем с уже открытым файлом.

2. Получение активного листа: Excel-файл может содержать несколько листов, и мы можем выбрать любой. Здесь у нас всего один лист.

3. Формирование списка заголовков: первая строка в Excel содержит заголовки. Чтобы использовать их как ключи словаря, читаем первую строку и собираем список заголовков.

4. Возврат результата: итерируем строки и для каждой создаём словарь, сопоставляя заголовки и значения ячеек. Openpyxl использует тип Cell, который хранит не только значение, но и метаданные. Для наших целей важны только значения, поэтому мы обращаемся к ним через cell.value.

Вот так выглядит первая строка результата:

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_openpyxl(f)
...     row = next(rows)
...     print(row)
{'boolean': True,
 'date': datetime.datetime(2000, 1, 1, 0, 0),
 'decimal': 1.1,
 'number': 1,
 'text': 'CONTROL ROW'}

Выглядит многообещающе! Запустим бенчмарк на большом файле:

iter_excel_openpyxl
elapsed 35.62
? "number"
? "decimal"
? "date" expected type "<class 'datetime.date'>" received type "<class 'datetime.datetime'>"
? "boolean"
? "text"

Импорт большого Excel-файла с помощью openpyxl занял ~35 секунд — дольше, чем Tablib (28 с) и pandas (32 с).

Быстрый взгляд в документацию подсказал перспективный раздел под названием «performance». В нём openpyxl описывает «оптимизированные режимы», которые ускоряют работу, когда нужно только читать или только записывать файл:

import openpyxl

def iter_excel_openpyxl(file: IO[bytes]) -> Iterator[dict[str, object]]:
    workbook = openpyxl.load_workbook(file, read_only=True)
    rows = workbook.active.rows
    headers = [str(cell.value) for cell in next(rows)]
    for row in rows:
        yield dict(zip(headers, (cell.value for cell in row)))

Теперь лист загружается в режиме «только чтение». Поскольку нам нужно лишь читать содержимое, а не записывать, это подходит. Запустим бенчмарк ещё раз и посмотрим, как это повлияло на результат:

iter_excel_openpyxl
elapsed 24.79
? "number"
? "decimal"
? "date" expected type "<class 'datetime.date'>" received type "<class 'datetime.datetime'>"
? "boolean"
? "text"

Открытие файла в режиме «только чтение» снижает время с 35 до 24 секунд — быстрее, чем tablib (28 с) и pandas (32 с).

Чтение Excel с помощью LibreOffice

Мы исчерпали традиционные и очевидные способы импорта Excel в Python. Мы использовали профильные библиотеки и получили неплохие результаты. Пора выйти за рамки.

LibreOffice — свободная альтернатива другим офисным пакетам. Он умеет обрабатывать и xls, и xlsx, а ещё у него есть безголовый режим (headless) с полезными опциями командной строки:

$ libreoffice --help
LibreOffice 7.5.8.2 50(Build:2)

Usage: soffice [argument...]
       argument - switches, switch parameters and document URIs (filenames).
...

Одна из опций командной строки LibreOffice — конвертация файлов между форматами. Например, можно с помощью libreoffice преобразовать файл xlsx в csv:

$ libreoffice --headless --convert-to csv --outdir . file.xlsx
convert file.xlsx -> file.csv using filter: Text - txt - csv (StarCalc)

$ head file.csv
number,decimal,date,boolean,text
1,1.1,01/01/2000,TRUE,CONTROL ROW
2,1.2,01/02/2000,FALSE,RANDOM TEXT:0.716658989024692
3,1.3,01/03/2000,TRUE,RANDOM TEXT:0.966075283958641

Отлично! Соединим всё это в Python. Сначала сконвертируем xlsx в CSV, затем импортируем CSV в Python:

import subprocess, tempfile, csv

def iter_excel_libreoffice(file: IO[bytes]) -> Iterator[dict[str, object]]:
    with tempfile.TemporaryDirectory(prefix='excelbenchmark') as tempdir:
        subprocess.run([
            'libreoffice', '--headless', '--convert-to', 'csv',
            '--outdir', tempdir, file.name,
        ])
        with open(f'{tempdir}/{file.name.rsplit(".")[0]}.csv', 'r') as f:
            rows = csv.reader(f)
            headers = list(map(str, next(rows)))
            for row in rows:
                yield dict(zip(headers, row))

Разберём по шагам:

1. Создать временную директорию для хранения CSV-файла.
   Используем встроенный модуль tempfile, чтобы создать временную директорию, которая автоматически очистится по завершении работы. Идеально было бы конвертировать конкретный файл в файловый объект в памяти, но командная строка libreoffice не позволяет указывать конкретный файл назначения — только директорию.

2. Преобразовать в CSV через командную строку libreoffice.
   Запускаем системную команду с помощью встроенного модуля subprocess.

3. Прочитать сгенерированный CSV.
   Открываем только что созданный CSV-файл, парсим его встроенным модулем csv и получаем словари.

Вот как выглядит первая строка результата:

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_libreoffice(f)
...     row = next(rows)
...     print(row)
{'number': '1',
 'decimal': '1.1',
 'date': '01/01/2000',
 'boolean': 'TRUE',
 'text': 'CONTROL ROW'}

Мы сразу видим, что потеряли всю информацию о типах — все значения стали строками.

Запустим бенчмарк и посмотрим, стоит ли игра свеч:

iter_excel_libreoffice
convert file.xlsx -> file.csv using filter : Text - txt - csv (StarCalc)
elapsed 15.279242266900837
? "number" expected type "<class 'int'>" received type "<class 'str'>"
? "decimal" expected type "<class 'float'>" received type "<class 'str'>"
? "date" expected type "<class 'datetime.date'>" received type "<class 'str'>"
? "boolean" expected type "<class 'bool'>" received type "<class 'str'>"
? "text"

Честно говоря, получилось быстрее, чем я ожидал! Конвертация файла в CSV с помощью LibreOffice и последующая загрузка заняла всего 15 секунд — быстрее, чем pandas (35 с), tablib (28 с) и openpyxl (24 с).

Да, при конвертации в CSV мы потеряли информацию о типах, и если бы нам понадобилось восстанавливать типы, это, скорее всего, заняло бы больше времени (сериализация штука небыстрая). Но в целом — неплохой вариант!

Чтение Excel с помощью DuckDB

Раз уж мы пошли по пути внешних инструментов, почему бы не дать шанс и «новичку» побороться.

DuckDB — это «встраиваемая СУБД для OLAP-запросов». Из этого описания не сразу понятно, чем DuckDB может помочь в данном случае, но помочь она действительно может. DuckDB отлично умеет гонять данные туда-сюда и конвертировать форматы.

Чтобы установить Python-API для DuckDB, выполните команду:

$ pip install duckdb

Чтение Excel-файла с помощью duckdb в Python:

import duckdb

def iter_excel_duckdb(file: IO[bytes]) -> Iterator[dict[str, object]]:
    duckdb.install_extension('spatial')
    duckdb.load_extension('spatial')
    rows = duckdb.sql(f"""
        SELECT * FROM st_read(
            '{file.name}',
            open_options=['HEADERS=FORCE', 'FIELD_TYPES=AUTO'])
    """)
    while row := rows.fetchone():
        yield dict(zip(rows.columns, row))

Разберём по шагам:

1. Установка и загрузка расширения spatial.
   Чтобы импортировать данные из Excel через duckdb, нужно установить расширение spatial. Это немного странно, потому что spatial используется для геоданных, но именно оно здесь и требуется.

2. Запрос к файлу.
   При выполнении запросов напрямую через глобальную переменную duckdb по умолчанию используется база данных в памяти, примерно как в sqlite с опцией :memory:. Чтобы действительно импортировать Excel-файл, используем функцию st_read, передав путь к файлу первым аргументом. В параметрах функции указываем, что первая строка — это заголовки, и включаем автоопределение типов (это и так поведение по умолчанию).

3. Формирование результата.
   Итерируем строки и собираем словари, используя список заголовков и значения каждой строки.

Вот как выглядит первая строка при импорте Excel-файла через DuckDB:

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_duckdb(f)
...     row = next(rows)
...     print(row)
{'boolean': True,
 'date': datetime.date(2000, 1, 1),
 'decimal': 1.1,
 'number': 1,
 'text': 'CONTROL ROW'}

Теперь, когда у нас есть процесс чтения Excel-файла с помощью DuckDB в Python, посмотрим, как он показывает себя в бенчмарке.

iter_excel_duckdb
elapsed 11.36
? "number"
? "decimal"
? "date"
? "boolean"
? "text"

Прежде всего, у нас есть победитель по типам! DuckDB смог корректно определить все типы данных. Кроме того, время составило всего 11 секунд — мы вплотную приблизились к однозначным результатам!

Единственное, что меня смущало в этой реализации — несмотря на все усилия, мне не удалось передать имя файла параметром в функцию duckdb.sql. Использовать конкатенацию строк для генерации SQL опасно, уязвимо к инъекциям и по возможности этого следует избегать.

В одной из попыток решить проблему я попробовал использовать duckdb.execute вместо duckdb.sql. В этом случае параметры действительно принимаются:

import duckdb

def iter_excel_duckdb_execute(file: IO[bytes]) -> Iterator[dict[str, object]]:
    duckdb.install_extension('spatial')
    duckdb.load_extension('spatial')
    conn = duckdb.execute(
        "SELECT * FROM st_read(?, open_options=['HEADERS=FORCE', 'FIELD_TYPES=AUTO'])",
        [file.name],
    )
    headers = [header for header, *rest in conn.description]
    while row := conn.fetchone():
        yield dict(zip(headers, row))

Главных отличия здесь два:

1. Использование duckdb.execute вместо duckdb.sql. С execute я смог передавать имя файла как параметр, а не через конкатенацию строк. Это безопаснее.

2. Формирование списка заголовков. Согласно API, duckdb.sql возвращает DuckDBPyRelation, а duckdb.execute — DuckDBPyConnection. Чтобы получить список заголовков из объекта соединения, я не смог использовать .columns, как раньше, поэтому пришлось обратиться к свойству description, которое, судя по всему, описывает текущий набор результатов.

Запуск бенчмарка с новой функцией дал любопытные результаты:

iter_excel_duckdb_execute
elapsed 5.73
? "number" expected type "<class 'int'>" received type "<class 'str'>"
? "decimal" expected type "<class 'float'>" received type "<class 'str'>"
? "date" expected type "<class 'datetime.date'>" received type "<class 'str'>"
? "boolean" expected type "<class 'bool'>" received type "<class 'str'>"
? "text"

С execute мы «проглотили» файл всего за 5,7 секунды — вдвое быстрее предыдущей попытки, но при этом потеряли типы. Без глубокого знания и опыта работы с DuckDB я могу лишь предположить, что построение relation и приведение типов в предыдущем варианте добавляло накладные расходы.

Прежде чем переходить к другим вариантам, проверим, влияет ли предзагрузка и установка расширений на результат:

 import duckdb

+duckdb.install_extension('spatial')
+duckdb.load_extension('spatial')
+
 def iter_excel_duckdb_execute(file: IO[bytes]) -> Iterator[dict[str, object]]:
-    duckdb.install_extension('spatial')
-    duckdb.load_extension('spatial')
     rows = duckdb.execute(
        "SELECT * FROM st_read(?, open_options=['HEADERS=FORCE', 'FIELD_TYPES=AUTO'])",

Несколько запусков функции показали, что предзагрузка расширений не дала заметного эффекта.

iter_excel_duckdb_execute
elapsed 5.28
elapsed 5.69
elapsed 5.28

Теперь посмотрим, изменится ли что-то при отключении автоматического определения типов:

 duckdb.load_extension('spatial')
 def iter_excel_duckdb_execute(file: IO[bytes]) -> Iterator[dict[str, object]]:
     conn = duckdb.execute(
-        "SELECT * FROM st_read(?, open_options=['HEADERS=FORCE', 'FIELD_TYPES=AUTO'])",
+        "SELECT * FROM st_read(?, open_options=['HEADERS=FORCE', 'FIELD_TYPES=STRING'])",
         [file.name],
     )
     headers = [header for header, *rest in conn.description]

Несколько запусков функции показали, что удаление автоопределения типов также не оказало значимого влияния на время.

iter_excel_duckdb_execute
elapsed 5.80
elapsed 7.21
elapsed 6.45

Чтение Excel с помощью Calamine

В последние годы кажется, что любая проблема с производительностью в Python решается за счёт другого языка. Как Python-разработчик, я считаю это настоящим подарком: можно продолжать писать на привычном языке и при этом получать прирост скорости благодаря другим!

Calamine — это чисто Rust-библиотека для чтения файлов Excel и OpenDocument Spreadsheet. Чтобы установить python-calamine, привязку к Calamine для Python, выполните команду:

$ pip install python-calamine

Чтение Excel с помощью calamine в Python:

import python_calamine

def iter_excel_calamine(file: IO[bytes]) -> Iterator[dict[str, object]]:
    workbook = python_calamine.CalamineWorkbook.from_filelike(file)  # type: ignore[arg-type]
    rows = iter(workbook.get_sheet_by_index(0).to_python())
    headers = list(map(str, next(rows)))
    for row in rows:
        yield dict(zip(headers, row))

Дальше всё по привычному сценарию: загружаем workbook, выбираем лист, получаем заголовки из первой строки, итерируем результаты и формируем словарь для каждой строки.

Вот так выглядит первая строка результата:

>>> with open('file.xlsx', 'rb') as f:
...     rows = iter_excel_calamine(f)
...     row = next(rows)
...     print(row)
{'boolean': True,
 'date': datetime.date(2000, 1, 1),
 'decimal': 1.1,
 'number': 1.0,
 'text': 'CONTROL ROW'}

Запуск бенчмарка:

iter_excel_calamine
elapsed 3.58
? "number" expected type "<class 'int'>" received type "<class 'float'>"
? "decimal"
? "date"
? "boolean"
? "text"

Это серьёзный скачок! С помощью python-calamine удалось обработать весь файл всего за 3,5 секунды — самый быстрый результат на данный момент! Единственный «минус» в том, что целое число было интерпретировано как float — не критично.

После небольшого исследования выяснилось, что главный недостаток python-calamine в том, что он не может возвращать результат в виде итератора. Функция CalamineWorkbook.from_filelike загружает весь набор данных в память, что при большом размере файла может стать проблемой. Автор утилиты указал на ограничение в базовой библиотеке pyo3, из-за которого Python не может итерировать структуры, написанные на Rust.

Итоги

Вот сводка по методам чтения Excel-файлов в Python:

Так какой же вариант выбрать? Всё зависит от задачи. При выборе библиотеки для работы с Excel в Python важна не только скорость:

• Возможность записи. В бенчмарке мы сравнивали чтение, но нередко требуется и запись в Excel. Некоторые библиотеки не поддерживают запись. Например, Calamine может только читать файлы, но не создавать их.

• Поддержка других форматов. Иногда нужно работать не только с Excel. Некоторые библиотеки (например, pandas и Tablib) умеют обрабатывать множество форматов, тогда как calamine и openpyxl ограничиваются только Excel.

Исходный код для бенчмарков доступен в этом репозитории.

Русскоязычное сообщество про Python, Pycharm и DevTools

Друзья! Эту статью перевела команда Python for Devs — канала, где каждый день выходят самые свежие и полезные материалы о Python и его экосистеме. Подписывайтесь, чтобы ничего не пропустить!