Как объединить несколько Excel‑файлов с помощью Python и Pandas

TL;DR

Python и библиотека Pandas позволяют быстро консолидировать множество Excel‑файлов в один. Создайте папку с файлами, используйте pathlib/glob для перечисления, читайте файлы через pd.read_excel, объединяйте через pd.concat и сохраняйте результат в Excel или CSV. Ниже — практические примеры, улучшения производительности, проверка ошибок и чеклисты.

Python — универсальный инструмент для работы с табличными данными. Многие организации хранят данные в файлах Excel (.xlsx). Ручное объединение таких файлов отнимает время и может привести к ошибкам. Этот материал шаг за шагом показывает надёжные подходы к консолидации Excel‑рабочих книг с помощью Pandas, одновременно объясняя альтернативы и типичные ошибки.

Краткое определение: Pandas — библиотека Python для работы с табличными данными (DataFrame).

Что вы получите из этой инструкции

• Рабочий пример кода для простого объединения нескольких .xlsx в один файл.
• Улучшенные приёмы: отказ от устаревшего df.append, использование pd.concat, pathlib и glob.
• Обработка кейсов: разные заголовки, пропуски, большие файлы, несколько листов.
• Чек‑листы для разработчика, тесты и критерии приёмки.

Предварительные требования

• Python 3.7+ (рекомендуется 3.8/3.9/3.10)
• Установленный пакет pandas. Установка:

pip install pandas openpyxl

Примечание: для записи/чтения .xlsx Pandas использует движок openpyxl. Если вы работаете с большими файлами, рассмотрите также пакет xlrd для старых форматов (.xls) или fastparquet для других форматов.

Простая последовательность действий (интуитивная модель)

Ментальная модель: «Перечислить → Прочитать → Нормализовать → Объединить → Сохранить». Коротко:

1. Перечислите файлы в папке (glob / pathlib).
2. Читайте каждый файл в DataFrame.
3. Приведите столбцы к общей схеме (если нужно).
4. Объедините DataFrame-ы (pd.concat).
5. Сохраните итог.

Базовый рабочий пример (минимальный код)

Этот пример подходит, если все файлы имеют одинаковую структуру столбцов и небольшие по размеру.

# Простой скрипт для конвертации нескольких .xlsx в один
import pandas as pd
from pathlib import Path

input_dir = Path("C:/Users/gaurav/OneDrive/Desktop/Excel files/")
output_file = Path("C:/Users/gaurav/OneDrive/Desktop/Consolidated_file.xlsx")

# Получаем список всех файлов .xlsx в папке
excel_files = list(input_dir.glob("*.xlsx"))

# Считываем и собираем список DataFrame-ов
dfs = [pd.read_excel(f) for f in excel_files]

# Объединяем все DataFrame в один
result = pd.concat(dfs, ignore_index=True)

# Сохраняем итог
result.to_excel(output_file, index=False)

Важно: мы используем pd.concat вместо df.append. append() постепенно помечается как устаревший паттерн и медленнее при большом числе файлов.

Объяснение ключевых шагов

• pathlib.Path.glob — кроссплатформенный способ перечисления файлов.
• pd.read_excel(file) — читает лист по умолчанию; для конкретного листа используйте sheet_name=’Sheet1’ или sheet_name=None (вернёт dict).
• pd.concat(list_of_dfs, ignore_index=True) — соединяет по вертикали и сбрасывает индексы.

Улучшенный и устойчивый вариант с обработкой исключений и логированием

import pandas as pd
from pathlib import Path
from tqdm import tqdm

input_dir = Path("C:/Users/gaurav/OneDrive/Desktop/Excel files/")
output_file = Path("C:/Users/gaurav/OneDrive/Desktop/Consolidated_file.xlsx")

dfs = []
for file in tqdm(input_dir.glob("*.xlsx"), desc="Чтение файлов"):
    try:
        df = pd.read_excel(file)
    except Exception as e:
        print(f"Ошибка при чтении {file}: {e}")
        continue
    # Нормализация: например, приведение имён столбцов к нижнему регистру
    df.columns = [c.strip() for c in df.columns]
    dfs.append(df)

if dfs:
    result = pd.concat(dfs, ignore_index=True)
    result.to_excel(output_file, index=False)
else:
    print("Нет подходящих файлов для объединения.")

Заметки:

• tqdm добавляет прогресс‑бар (pip install tqdm).
• Обработка исключений пропускает битые файлы и логирует проблему.

Частые сценарии и как их решать

Разные наборы столбцов

Если файлы имеют разные столбцы, pd.concat автоматически подставит NaN для отсутствующих столбцов. Для контроля можно явно задать схему столбцов:

expected_cols = ["id", "date", "amount", "product"]
dfs = [df.reindex(columns=expected_cols) for df in dfs]

Несколько листов в каждой книге

Чтобы объединить определённый лист по имени:

pd.read_excel(file, sheet_name="Sheet1")

Чтобы объединить все листы одной книги в один DataFrame (с добавлением колонки source_sheet):

sheets = pd.read_excel(file, sheet_name=None)
for sheet_name, df in sheets.items():
    df["_sheet"] = sheet_name
    dfs.append(df)

Большие файлы и ограниченная память

• Читайте по частям: pd.read_excel не поддерживает chunking напрямую, но можно сохранять в CSV и читать через chunksize при конвертации.
• Используйте формат Parquet для промежуточного хранения (более компактно и быстро).
• Обрабатывайте и сливайте по батчам: читайте N файлов, объединяйте и записывайте временный файл, затем объединяйте временные файлы.

Обработка дат, локализация и чисел

• Явно парсите даты: pd.read_excel(…, parse_dates=[“date_column”]).
• Для локализации форматирования (разделитель тысяч, запятая/точка) приведите столбцы к числу через pd.to_numeric(…, errors=’coerce’).

Альтернативные варианты подхода

• Использовать модуль glob + os.path вместо pathlib (устарело для новых скриптов).
• openpyxl — читать/писать без Pandas, когда нужно менять форматирование, формулы, стили.
• xlrd / xlwt — только для очень старых форматов (.xls).
• Apache POI (через Java) — если проект основан на JVM.

Меры безопасности и приватность

• Не храните персональные данные в открытом виде при конкатенации.
• Перед объединением выполните проверку GDPR/политик безопасности данных: удалите идентификаторы, хэшируйте или агрегируйте при необходимости.

Важно: никогда не объединяйте чувствительные файлы без согласования и без шифрования конечного хранилища.

Чек‑лист разработчика (Role‑based)

Для инженера данных:

• Проверить структуру столбцов всех файлов.
• Убедиться, что все файлы читаются корректно.
• Прогнать скрипт в тестовом окружении на подвыборке.

Для аналитика:

• Сверить итоговую структуру с бизнес‑требованиями.
• Проверить агрегаты (суммы/количества) до и после объединения.

Для DevOps:

• Настроить расписание (cron/Windows Task) и права доступа на папки.
• Обеспечить ротацию и бэкап исходных файлов.

Критерии приёмки

• Скрипт собирает все .xlsx из указанной папки и создаёт Consolidated_file.xlsx в целевой папке.
• Количество строк в итоговом файле равно сумме строк исходных файлов (за вычетом заголовков).
• В случае битого файла — процесс не прерывается, ошибка логируется.
• Итоговый файл открывается в Excel без ошибок.

Тесты и приёмочные сценарии

• Положительный тест: 3 файла одинаковой схемы → итог содержит сумму строк.
• Отрицательный тест: один файл повреждён → ошибка логируется, остальные файлы объединены.
• Граничный тест: файлы с разными столбцами → итог содержит объединённую схему с NaN там, где данных нет.

Примеры ошибок и как их исправить

• “File is not a zip file” при чтении .xlsx — файл повреждён или имеет расширение .xlsx, но не соответствует формату. Проверьте исходный файл вручную.
• Проблемы с кодировкой при чтении CSV — используйте encoding=’utf-8’ или encoding=’cp1251’ в зависимости от источника.

Советы по производительности

• Используйте pd.concat над list comprehension (чтение в список DataFrame-ов, затем один concat).
• Сохраняйте в бинарных форматах (Parquet) для промежуточных шагов.
• Для очень большого числа файлов объединяйте пакетами и пишите промежуточные файлы.

Мини‑методология для команд

1. Создать тестовую папку с реальными файлами (анонимизировать данные).
2. Запустить скрипт в режиме dry run (только чтение + проверка схем).
3. Проверить основные статистики (кол‑во строк, суммы ключевых столбцов).
4. Запустить производство, проконтролировать логирование.

Шаблон команды для cron (пример)

0 2 * /usr/bin/python3 /opt/scripts/merge_excel.py >> /var/log/merge_excel.log 2>&1

Частые ошибки новичков (галерея кейсов)

• Использование df.append в цикле для большого числа файлов — медленно.
• Игнорирование различий в именах столбцов (лишние пробелы, регистр).
• Сохранение итогов с индексами по умолчанию (index=True) — приводит к лишнему столбцу при открытии в Excel.

Короткий глоссарий

• DataFrame — табличная структура данных в Pandas.
• pd.concat — функция для объединения DataFrame‑ов.
• pathlib — модуль для работы с путями, кроссплатформенный.

Решение компромиссов: когда этот подход не подходит

• Очень большие файлы (сотни ГБ): лучше использовать распределённые фреймворки (Dask, Spark).
• Нужна тонкая правка форматов/стилей Excel: используйте openpyxl или xlwings.

Быстрая шпаргалка (cheat sheet)

• Перечислить файлы: Path(“путь”).glob(“*.xlsx”)
• Считать: pd.read_excel(file)
• Объединить: pd.concat(dfs, ignore_index=True)
• Сохранить: df.to_excel(output_path, index=False)

FAQ

Как установить Pandas?

Установите через pip: pip install pandas openpyxl. Для Jupyter с Anaconda Pandas чаще всего уже установлен.

Как объединить файлы с разными столбцами?

Используйте pd.concat: отсутствующие столбцы будут заполнены NaN. Чтобы контролировать порядок и набор столбцов, примените df.reindex(columns=expected_cols).

Краткое резюме

Pandas даёт надёжные и гибкие способы объединения Excel‑файлов. Для простых задач достаточно pd.read_excel + pd.concat. Для промышленных сценариев добавьте обработку ошибок, логирование и пакетную запись. При работе с чувствительными данными прежде всего учитывайте требования безопасности и конфиденциальности.

Последние рекомендации

• Используйте версии Pandas, соответствующие вашему окружению.
• Тестируйте скрипт на подвыборке перед запуском в проде.
• Предпочитайте pd.concat вместо append при объединении большого количества файлов.