Объединение электронных таблиц, очистка экспортированных данных и разделение отчетов — необходимые, но скучные задачи. Эти скрипты на Python берут на себя рутинные части работы, позволяя вам сосредоточиться на главном.
Изображение предоставлено автором.
# Введение
Excel по-прежнему актуален для работы с данными, но значительная часть времени, затрачиваемого на его использование, тратится исключительно на механические задачи. Такие задачи, как объединение файлов из нескольких источников, поиск дубликатов записей, переформатирование несогласованных экспортированных данных и разделение сводной таблицы на отдельные файлы, не являются сложными, но они отнимают много времени и подвержены человеческим ошибкам.
Эти пять скриптов на Python помогут автоматизировать эти задачи. Каждый из них является самодостаточным, настраиваемым и предназначен для работы с неструктурированными данными из реального мира.
Все скрипты можно найти на GitHub .
# Объединение нескольких файлов Excel
// Болевая точка
При объединении данных из нескольких файлов Excel или файлов с разделителями-запятыми (CSV) ручной процесс — открытие каждого файла, копирование данных и вставка их в сводный лист — является медленным и подвержен ошибкам выравнивания, особенно если порядок столбцов различается в разных файлах.
// Что делает скрипт
Этот скрипт сканирует папку на наличие файлов .xlsx и .csv, объединяет все их данные в один унифицированный лист и записывает чистый объединенный выходной файл. При желании он может добавить столбец «Источник», чтобы вы всегда знали, какая строка из какого файла, и автоматически обрабатывает несоответствие порядка столбцов.
// Как это работает
Скрипт использует pandas для чтения всех файлов в целевом каталоге, выравнивает столбцы по имени, а не по положению, и объединяет все в один DataFrame. Настраиваемый флаг add_source_column добавляет исходное имя файла к каждой строке. Несоответствия столбцов регистрируются в журнале, чтобы вы знали, были ли в некоторых файлах лишние или отсутствующие поля. Вывод записывается с помощью openpyxl и включает вкладку сводки, показывающую количество строк по каждому файлу.
⏩ Получите скрипт для объединения файлов Excel
# Поиск и пометка повторяющихся строк
// Болевая точка
Дубликаты записей часто встречаются в наборах данных, экспортированных и повторно импортированных из разных систем. Точные совпадения найти легко, но почти идентичные записи — одинаковые, но с немного отличающимся форматированием или интервалами — сложнее обнаружить вручную в больших масштабах.
// Что делает скрипт
Этот скрипт сканирует файл Excel на наличие повторяющихся строк на основе определенных вами столбцов, отмечает точные дубликаты и близкие к ним значения с помощью нечеткого сопоставления строковых полей и записывает аннотированный выходной файл, в котором каждая группа предполагаемых дубликатов выделена цветовой кодировкой и оценкой достоверности.
// Как это работает
Скрипт использует pandas для точного обнаружения дубликатов и RapidFuzz для нечеткого сопоставления строк по настраиваемым ключевым столбцам. Каждой строке присваивается идентификатор группы дубликатов и процент достоверности совпадения. Выходной файл Excel использует форматирование openpyxl для выделения кластеров дубликатов. На отдельном сводном листе отображается общее количество найденных дубликатов с разбивкой по типу совпадения.
⏩ Получите скрипт для поиска дубликатов
# Очистка и стандартизация неструктурированных экспортированных данных
// Болевая точка
Данные, экспортируемые из внешних систем, часто имеют непоследовательный формат: смешанные форматы дат, непоследовательное написание регистра букв, номера телефонов с различными разделителями и пробелы в конце. Ручная очистка перед анализом быстро приводит к значительным затратам.
// Что делает скрипт
Этот скрипт применяет настраиваемый набор правил очистки к файлу Excel или CSV. К ним относятся стандартизация дат, удаление пробелов, исправление регистра букв, нормализация номеров телефонов и почтовых индексов, удаление пустых строк и пометка ячеек, которые кажутся некорректными. В результате скрипт выводит очищенный файл и журнал изменений, в котором точно указано, что было изменено.
// Как это работает
Скрипт считывает конфигурационный файл, в котором имена столбцов сопоставляются с операциями очистки: date_format, title_case, strip_whitespace, phone_normalize, remove_blank_rows и другие. Каждая операция применяется последовательно. На второй лист выходного файла записывается сравнительный журнал изменений, показывающий исходные и очищенные значения для каждой измененной ячейки. Ничего не удаляется автоматически. Если значение не может быть обработано, оно помечается в столбце _clean_errors.
⏩ Получите скрипт для очистки данных
# Разделение одного листа на отдельные файлы по значению столбца
// Болевая точка
Основной набор данных часто необходимо распространять в виде отдельных файлов — например, по одному файлу на каждый регион, отдел или категорию. Выполнение этой задачи вручную предполагает многократную фильтрацию, копирование и сохранение, что сопряжено с высоким риском смешения данных между файлами.
// Что делает скрипт
Этот скрипт считывает один лист Excel и разбивает его на отдельные выходные файлы — по одному для каждого уникального значения в указанном столбце. Каждый выходной файл содержит только строки для этого значения, с сохранением исходного форматирования. Имена файлов генерируются автоматически на основе значений столбцов. При желании, он может отправлять каждый файл в качестве вложения в электронное письмо, используя предоставленное вами сопоставление имени и адреса электронной почты.
// Как это работает
Скрипт группирует DataFrame по целевому столбцу с помощью pandas, а затем записывает каждую группу в отдельный файл .xlsx с помощью openpyxl. Шаблон именования, например Sales_Report_{value}_{date}.xlsx, позволяет управлять форматом имени выходного файла. Заголовки столбцов, типы данных и базовое форматирование сохраняются в каждом выходном файле. Дополнительный режим отправки по электронной почте считывает CSV-сопоставление {value} → {email address} и отправляет каждый файл по протоколу Simple Mail Transfer Protocol (SMTP) .
⏩ Получите скрипт для разделения листов
# Создание сводного отчета на основе исходных данных
// Болевая точка
Создание сводного отчета на основе исходных данных — итогов по категориям, ежемесячных тенденций или показателей эффективности — включает в себя построение сводных таблиц, их форматирование и копирование результатов в удобочитаемый формат. При регулярном обновлении исходных данных этот процесс каждый раз повторяется с нуля.
// Что делает скрипт
Этот скрипт считывает исходный файл Excel с необработанными данными, создает настраиваемые сводные таблицы и записывает отформатированный многовкладочный сводный отчет. Диаграммы генерируются и встраиваются в выходной файл. Вы можете запустить его повторно при каждом изменении исходных данных.
// Как это работает
В конфигурационном файле определяются поле даты, поле значения, столбцы группировки и конкретные агрегации для выполнения. Скрипт использует pandas для всей логики агрегации и openpyxl с Matplotlib для построения диаграмм. Каждому типу сводки отведена отдельная вкладка. Условное форматирование выделяет самые высокие и самые низкие значения. Отчет предназначен для перегенерации по запросу, и повторный запуск скрипта корректно перезапишет предыдущий результат.
⏩ Получите скрипт генератора сводных отчетов
# Завершение
Эти пять скриптов охватывают распространенные задачи в Excel, которые легко автоматизировать, но утомительно выполнять вручную. Выберите тот, который отвечает за наиболее часто выполняемую задачу в вашем рабочем процессе, и начните с него. Вот краткий обзор:
| Название скрипта | Цель | Основные характеристики | Наилучший вариант использования |
|---|---|---|---|
| Объединение файлов Excel | Объединение нескольких файлов Excel/CSV | Выравнивание столбцов, отслеживание источников, сводная таблица | Объединение данных из нескольких источников |
| Поиск дубликатов | Выявление точных и неточных дубликатов | Нечеткое сопоставление, показатели достоверности, цветовая подсветка | Очистка наборов данных с повторяющимися записями |
| Очиститель данных | Стандартизация неструктурированных экспортированных данных | Правила форматирования, нормализация, журнал изменений | Предварительная обработка исходных внешних данных |
| Разделитель листового металла | Разделите один лист на несколько файлов. | Автоматическое именование файлов, группировка, дополнительная отправка электронных писем. | Распределение отчетов по категориям/регионам |
| Генератор сводных отчетов | Создание сводных отчетов на основе исходных данных | Автоматизированное создание сводных таблиц, диаграмм, вывод данных в несколько вкладок. | Регулярная отчетность и информационные панели |
Успешной автоматизации!
Бала Прия С. — разработчик и технический писатель из Индии. Ей нравится работать на стыке математики, программирования, анализа данных и создания контента. В сферу её интересов и компетенции входят DevOps, анализ данных и обработка естественного языка. Она любит читать, писать, программировать и пить кофе! В настоящее время она работает над изучением и распространением своих знаний среди сообщества разработчиков, создавая учебные пособия, руководства, аналитические статьи и многое другое. Бала также создает увлекательные обзоры ресурсов и обучающие материалы по программированию.
Источник: www.kdnuggets.com
