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

Как инженер данных у меня было много возможностей повысить ценность, создав файл Excel в конце конвейера ETL.

Данные не всегда будут использоваться только аналитиками данных и учеными, часто другим областям нужен инструмент, который позволяет манипулировать и изменять данные, но они, вероятно, не будут знать SQL для запросов непосредственно из хранилищ данных, и именно здесь появляются отчеты Excel. .

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

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

  • Контроль версий важен — пользователю периодически требуется доступ к историческим данным.
  • Формулы важны.Пользователь должен активно изменять данные
  • У вас есть проблемы с безопасностью. Файлы необходимо отправлять внешним клиентам/пользователям без доступа к источникам данных.
  • Несколько запросов/источников данных.Обновления занимают много времени у пользователя, и трудно контролировать возможные изменения.

1 º Набор данных

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

Первый содержит данные о топовых играх на Twitch 2016–2021. Он находится в открытом доступе на kaggle

import pandas as pd

twitch_games_df = pd.read_csv('Twitch_game_data.csv')
twitch_games_df.head(5)

Второй содержит данные о топ-1000 стримеров прошлого года, также доступны на kaggle.

twitch_streamers_df = pd.read_csv('twitchdata-update.csv')
twitch_streamers_df.head(5)

2 º Генерация и форматирование

Как вы, наверное, уже знаете, создание файла Excel из DataFrame может быть таким же простым, как запуск DataFrame.to_excel(‘file_name.xlsx’). Однако есть лучший способ, обеспечивающий высокую степень индивидуальной настройки.

Обратите внимание, хотя вам не нужно импортировать дополнительные библиотеки, кроме pandas, вам нужно будет установить xlsxwriter с помощью «pip install xlsxwriter».

Давайте начнем создавать Excel с несколькими вкладками.

Мы создали «базовый» файл, теперь пришло время для форматирования! Обратите внимание, чтобы избежать повторений (мы просто хотим научиться!), давайте отформатируем только первую вкладку, «games_sheet».

Также обратите внимание, что следующий код является продолжением предыдущего.

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

давайте посмотрим на Excel, сгенерированный после запуска трех блоков кода выше:

Намного лучше, чем просто запустить df.to_excel(), верно?

Совет: вы можете еще больше улучшить форматирование, добавив специальное форматирование для текстовых столбцов с выравниванием по левому краю и большей шириной.

3 º Добавление изображений/диаграмм

Добавление изображения на рабочий лист — очень простой процесс:

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

Хитрость здесь в том, что вы можете создать любую визуализацию с помощью matplotlib, seaborn или предпочитаемой вами библиотеки, временно сохранить изображение, вставить его в Excel, а затем удалить.

Давайте начнем с создания функции, которая сохранит гистограмму о среднем времени просмотра 10 лучших стримеров Twitch.

Ps: цель здесь не в том, чтобы научить создавать хорошие визуализации, но если вам интересна эта тема, взгляните на одну из моих других статей: Поднимите гистограмму на новый уровень с помощью Python.

Эта функция сохранит график ниже как img.png в текущем каталоге.

Теперь мы можем добавить его в наш отчет Excel:

Конечный результат:

Круто, да? Я уверен, что с помощью этого трюка вы сможете создавать впечатляющие отчеты.

4 º Добавление формул

Есть два способа вставки формул в Excel. Первый и самый простой — это использование формул массива. Лично я не сторонник этого варианта, потому что он создает формулу для всего столбца, а пользователь не привык к такому формату.

Тем не менее, поскольку это проще, я считаю, что это стоит продемонстрировать. Для этого давайте создадим простую функцию в twitch_streamers_df:

  • watch_time_rate = время просмотра (минуты)/время трансляции (минуты)

Это будет работать, но учтите, что формула будет записана в виде массива и не может быть частично изменена:

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

Результат:

Именно так, как ожидает пользователь! Что, безусловно, облегчает использование вашего отчета, повышая ценность вашей работы.

Дополнительный! Назовите ваши файлы в соответствии с текущей датой

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

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

Последние мысли

Инженеры данных часто избегают работы с Excel по ряду очень веских причин. Однако не имеет значения, насколько хороши ваши конвейеры данных или ваша модель данных, если они не используются.

Создание качественных отчетов в Excel — отличный способ универсализировать данные, создать ценность и сделать вашу работу наглядной.

Я надеюсь, что это руководство поможет вам создавать профессиональные отчеты. Если у вас есть какие-либо вопросы или отзывы, пожалуйста, дайте мне знать в комментариях.

Спасибо за прочтение!!