Используйте класс Python c_download_prep_excel для автоматической загрузки электронных таблиц Excel с веб-сайтов и подготовки их для использования в проектах анализа данных.
Введение
В недавней статье я поделился классом Python, который загружает отчеты с analytics.usa.gov. Файлы содержат данные о том, как общественность получает доступ к примерно 5700 правительственным веб-сайтам США. Они были чистыми, однородными по дизайну и готовы к визуализации в Tableau.
Напротив, некоторые файлы Excel, опубликованные Федеральным бюро расследований США (ФБР) на своем веб-сайте, создают проблемы. Мне нужно было преобразовать их данные, прежде чем я смогу использовать их в проекте анализа данных. В этой статье представлен многоразовый класс Python, называемый c_download_prep_excel, который загружает книги Excel, подобные тем, которые опубликованы ФБР, с веб-сайтов и реструктурирует их данные, чтобы подготовить их к проектам анализа данных.
Представленный здесь метод и класс c_download_prep_excel можно использовать для загрузки и обработки файлов Excel с других сайтов. Любой может также изменить класс для обработки файлов Excel из разных источников и для выполнения других задач.
Образец отчета ФБР
Я использовал Статистический отчет о насильственных преступлениях за 2019 год ФБР для разработки и тестирования класса c_download_prep_excel. Щелкните Загрузить Excel - Таблица 1, чтобы получить книгу Excel.
Сообщить о проблемах
Образец таблицы Excel, показанный ниже, не готов для анализа данных. Хотя его можно просматривать в Excel, его следует очистить и преобразовать, чтобы подготовить для аналитики. Вот некоторые из его проблем:
- Строки с 1 по 3 содержат информацию о заголовке, которая является метаданными.
- Строки с 25 по 31 содержат информацию о нижнем колонтитуле, которая также является метаданными.
- Некоторые годы в столбце 1 содержат надстрочные индексы, добавляющие цифру к значению года.
- Матричный формат данных не поддается загрузке в инструмент анализа данных.
Решение
Чтобы подготовить отчет для анализа данных, я написал класс и программу Python для преобразования данных из исходного листа в новый лист. Результирующий формат показан ниже с одной точкой данных (сочетание года, типа преступления и уровня преступности) в каждой строке.
Класс можно использовать и расширять по мере необходимости для загрузки и подготовки других файлов Excel с сайта ФБР или других сайтов. Пример программы, представленный далее в этой статье, выполняет следующие действия:
- Загрузите книгу Excel с веб-сайта ФБР в локальную папку.
- Если книга представляет собой файл xls, сохраните копию в формате xlsx.
- Оставьте исходный лист файла xlsx без изменений. Вместо этого создайте новый рабочий лист, который будет содержать данные в желаемом формате. Следующие шаги описывают преобразование данных из рабочего листа 19tbl01 в новый рабочий лист.
- Создайте три столбца с названием: Год, Тип преступления и Уровень преступности.
- Для типа преступления впишите по одной строке на каждый год в столбец 1 и тип преступления в столбец 2.
- Усеките значения года, содержащие верхний индекс, до четырех цифр.
- Запишите данные об уровне преступности для каждого типа преступления из исходной таблицы в новую таблицу.
- Выровняйте имена столбцов для столбцов 1 и 3 по правому краю.
- Установите ширину столбца.
Инструменты, используемые в этом проекте
Для разработки программы я использовал следующие инструменты.
- Python 3.9.2
- Microsoft Excel 365
- Сообщество Microsoft Visual Studio
Программа, класс c_download_prep_excel и модули Python
Программа
Программа для загрузки и подготовки книги Excel для анализа данных состоит из модуля контроллера в файле process_fbi_data.py и класса c_download_prep_excel в файле c_download_prep_excel.py. Код в process_fbi_data.py управляет программой. Контроллер создает экземпляр c_download_prep_excel и вызывает его функции для расшифровки и преобразования данных из исходного рабочего листа файла отчета ФБР в новый рабочий лист.
c_download_prep_excel Класс
Класс c_download_prep_excel выполняет задачи, запрошенные контроллером, как описано в разделе решения выше. Его можно модифицировать и расширять по мере необходимости для соответствия конкретным требованиям.
См. Приложение A для программного контроллера и кода класса c_download_prep_excel.
Модули Python
Программа вызывает функции в модуле openpyxl для создания нового рабочего листа и форматирования данных. Программа также вызывает функции pyexcel, которые используют pyexcel-xls и pyexcel-xlsx для копирования книги xls в книгу xlsx.
Выполните перечисленные ниже команды из командной строки или, если поддерживается, в вашей интегрированной среде разработки (IDE), чтобы установить модули:
- pip установить openpyxl
- pip install pyexcel
- pip install pyexcel-xls
- pip install pyexcel-xlsx
Python включает модули операционной системы и запросов, которые использует программа.
Публичная панель управления Tableau
Я загрузил готовый для аналитики лист Excel в Tableau Public и создал простую панель инструментов, чтобы показать тенденции во времени по различным типам преступлений.
Возможные улучшения
Программа хорошо выполняет свои основные задачи по подготовке данных о насильственных преступлениях из электронной таблицы отчетов ФБР для анализа данных. Следующие улучшения могут сделать программу готовой к работе и расширить ее возможности для работы с различными книгами Excel в разных форматах.
- Добавьте обработку ошибок, чтобы исправить серьезные ошибки или корректно завершить работу программы.
- Записывайте действия программы в файл журнала, чтобы отслеживать запуск, завершение, ошибки и действия программы.
- Добавьте в класс функции для выполнения дополнительного форматирования и решения проблем, связанных с новыми электронными таблицами.
- Найдите и замените данные из других файлов или базы данных.
- Выполните дополнительную очистку данных.
Вывод
Таблицы Excel можно вручную загрузить с веб-сайтов и вручную преобразовать в готовую форму для аналитики. Но программа может автоматизировать задачу. Это полезно, когда необходимо загрузить и подготовить несколько файлов для использования в проектах анализа данных. Кроме того, при правильной разработке вы можете повторно использовать класс и программу Python для аналогичных задач в будущем.
об авторе
Рэнди Рунч - аналитик данных, разработчик программного обеспечения, писатель, фотограф, велосипедист и искатель приключений. Он и его жена живут на юго-востоке Миннесоты, США.
Следите за предстоящими статьями Рэнди о общедоступных наборах данных, которые помогут анализировать данные и принимать решения, программировать, анализировать данные, фотографии, велосипедные туры и многое другое. Вы можете увидеть некоторые из его фотографий на сайтах shotproof.com и shutterstock.com.
Приложение A - Исходный код
Код контроллера Python и код класса c_download_prep_excel для программы показаны ниже. Не стесняйтесь копировать код и адаптировать его под свои нужды.
