Как получить статистику по данным и графики из PostgreSQL: пример создания своего PDF-отчета средствами Python-библиотеки matplotlib. Пишем простой скрипт в Google Colab для анализа операционной деятельности интернет-магазина.
Постановка задачи
Сегодня рассмотрим не слишком типичный для работы системного и бизнес-аналитика случай, когда необходимо вручную получить данных из БД, чтобы сделать предложить варианты улучшения бизнеса. Чтобы выполнить такой анализ и сформировать рекомендации, необходимо иметь аналитику, т.е. фактические данные. Причем их получить сразу в агрегированном и презентативном виде, например, PDF-отчет. Как обычно, в качестве примера я возьму базу данных своего ранее спроектированного и реализованного интернет-магазина, которая имеет следующую схему для PostgreSQL:
Предположим, необходимо сделать выводы об операционной деятельности магазина по следующим данным за определенный период:
- количество заказов;
- максимальная, минимальная и средняя сумма чека;
- общая сумма дохода, т.е. выручки от продажи товаров;
- помесячная выручка и количество заказов;
- самые часто покупаемые товары.
Итоговый отчет в виде неизменяемого PDF-файла должен содержать все вышеперечисленные сведения в текстовой и графической формах. Например, для отображения помесячной выручки отлично подходят гистограммы, а для оценки популярности конкретного товара относительно общего числа продаж – круговая диаграмма. Все эти и другие виды графиков поддерживает Python-библиотека matplotlib. Кроме функций для работы с двумерной и трехмерной графикой, она также позволяет формировать PDF-документ с текстовыми, табличными, численными и графическими данными. Поэтому для решаемой задачи будем использовать именно ее, что и рассмотрим далее, сделав небольшой переход из роли аналитика в роль разработчика.
Разработка ТЗ на информационную систему по ГОСТ и SRS
Код курса
TTIS
Ближайшая дата курса
3 июня, 2024
Продолжительность
12 ак.часов
Стоимость обучения
27 000 руб.
Реализация PDF-отчета с matplotlib
Как обычно, напишу и запущу Python-скрипт в Google Colab. Скрипт будет состоять из 3-х ячеек:
- ячейка, выполняемая один раз для импорта пакетов, создания директории для сохранения отчетов и установки значений глобальных переменных;
- ячейка с объявлением функций обращения к БД для выполнения SQL-запросов – разумеется, при отладке эта ячейка выполняется несколько раз, но после того, как достигнута корректная работа всех функций, ее можно запускать однократно;
- ячейка с формированием PDF-отчета по заданному периоду. Поскольку именно эта ячейка генерирует PDF-отчет, она запускается многократно, каждый раз, когда необходимо получить этот документ. Имя файла будет содержать текущую дату.
Python-код выглядит следующим образом:
###############################################ячейка №1 в Google Colab#############################
#импорт пакетов
import matplotlib.pyplot as plt
from matplotlib.backends.backend_pdf import PdfPages
import datetime
import pandas as pd
import psycopg2
import traceback
import threading
from google.colab import files
#############################################
global connection_string #глобальная переменная строка подключения
connection_string = 'postgres://username:password@v-host.neon.tech/neondb' #ваша строка подключения к БД
#создание директории для сохранения отчетов
!mkdir -p /content/reports
###################################################ячейка №2 в Google Colab########################################################################
# Функция получения помесячной выручки из БД (сумма всех заказов, сгруппированных по месяцам)
def db_read_monthly_income_count(start, finish):
conn = psycopg2.connect(connection_string)
cursor = conn.cursor()
try:
cursor.execute("""
SELECT to_char(orders.date, 'Month') AS mes, SUM(orders.sum) AS income, COUNT(orders.sum) AS quantity
FROM orders
WHERE EXTRACT(MONTH FROM orders.date) BETWEEN %s::integer AND %s::integer
GROUP BY mes
ORDER BY (MIN(orders.date))
""", (start, finish))
results = cursor.fetchall()
return results
except Exception as e: # ловить конкретные исключения и сохранять информацию об исключении в переменную 'e'
traceback.print_exc()
return 'Ошибка при получении данных из базы данных'
finally:
cursor.close() #закрыть курсор, который выполняет запросы
conn.close() #закрыть соединение с базой данных
# Функция получения больше всего востребованных товаров из БД (тех товаров во всех заказах, которых купили больше 500 единиц)
def db_read_products_statiscics_count(start, finish):
conn = psycopg2.connect(connection_string)
cursor = conn.cursor()
try:
cursor.execute("""
SELECT product.name AS tovar, SUM(order_product.quantity) AS kolichestvo
FROM order_product
JOIN product on product.id=order_product.product
JOIN orders on orders.id=order_product.order
WHERE (EXTRACT(MONTH FROM orders.date) BETWEEN %s::integer AND %s::integer)
GROUP BY tovar
HAVING SUM(order_product.quantity)>500
ORDER BY kolichestvo
""", (start, finish))
results = cursor.fetchall()
return results
except Exception as e: # ловить конкретные исключения и сохранять информацию об исключении в переменную 'e'
traceback.print_exc()
return 'Ошибка при получении данных из базы данных'
finally:
cursor.close() #закрыть курсор, который выполняет запросы
conn.close() #закрыть соединение с базой данных
# Функция получения распределения данных по сумме заказа
def db_read_orders_stat(start, finish):
conn = psycopg2.connect(connection_string)
cursor = conn.cursor()
try:
cursor.execute("""
SELECT orders.sum AS summa
FROM orders
WHERE EXTRACT(MONTH FROM orders.date) BETWEEN %s::integer AND %s::integer
""", (start, finish))
results = cursor.fetchall()
return results
except Exception as e: # ловить конкретные исключения и сохранять информацию об исключении в переменную 'e'
traceback.print_exc()
return 'Ошибка при получении данных из базы данных'
finally:
cursor.close() #закрыть курсор, который выполняет запросы
conn.close() #закрыть соединение с базой данных
###############################################ячейка №3 в Google Colab############################################################################
start = 1 #месяц начала сбора статистики
finish = 12 #месяц окончания сбора статистики
# Создание DataFrame помесячной выручки и вывод его на экран
df = pd.DataFrame(db_read_monthly_income_count(start, finish), columns=['МЕСЯЦ', 'ДОХОД', 'КОЛИЧЕСТВО ЗАКАЗОВ'])
# Создание DataFrame самых востребованных товаров из БД и вывод его на экран
df2 = pd.DataFrame(db_read_products_statiscics_count(start, finish), columns=['ТОВАР', 'КОЛИЧЕСТВО'])
# Создание DataFrame суммы заказа и вывод его на экран
df3 = pd.DataFrame(db_read_orders_stat(start, finish), columns=['summa'])
# Получаем текущую дату
current_date = datetime.datetime.now()
# Форматируем дату в строку (например, '2023-03-15')
date_str = current_date.strftime('%Y-%m-%d')
# Создание PDF-файла для сохранения графиков и таблиц. В название файла включаем текущую дату
filename = '/content/reports/report_' + date_str + '.pdf'
# Запись данных в PDF-файл
with PdfPages(filename) as pdf:
# Рассчитаем статистику по суммам заказа
kolvo=len(df3['summa']) #количество наблюдений
df_max = df3['summa'].max() #максимум
df_min=df3['summa'].min() #минимум
df_mean = df3['summa'].mean() #среднее значение
df_sum=df3['summa'].sum() #сумма
# упаковываем статистику в датафрейм для отображения в таблице
df_stats = pd.DataFrame({
'Показатель': ['ВСЕГО заказов', 'Максимальный чек', 'Минимальный чек', 'Среднее значение', 'ИТОГО общая сумма дохода'],
'Значение': [kolvo, df_max, df_min, df_mean, df_sum]
})
# Создание фигуры и осей для таблицы
fig_table, ax_table = plt.subplots(figsize=(8, 3)) # Выберите подходящий размер фигуры
# Задание заголовка
ax_table.set_title(f"Статистика по сумме заказов, с {start}-го месяца по {finish}-й месяц")
# Задание содержимого таблицы со статистикой
ax_table.axis('tight') #задаем границы области для таблицы так, чтобы они плотно обрамляли содержимое
ax_table.axis('off') #выключаем отображение осей для таблицы (нет границ и делений)
table = ax_table.table(cellText=df_stats.values, colLabels=df_stats.columns, loc='center') #задаем содержимое ячеек таблицы, заголовки столбцов и расположение таблицы
table.set_fontsize(10) #устанавливаем размер шрифта для текста в таблице вручную
table.scale(1.2, 1.2) # Можно изменить масштаб таблицы для лучшего отображения
# Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
pdf.savefig(fig_table)
plt.close(fig_table)
# подробности по месячной выручке
# Создание фигуры и осей для таблицы
fig_table, ax_table = plt.subplots(figsize=(8, 6)) # Выберите подходящий размер фигуры
# Задание заголовка
ax_table.set_title(f"Доходы по месяцам, с {start}-го месяца по {finish}-й месяц")
# Задание содержимого таблицы со статистикой
ax_table.axis('tight') # задаем границы области для таблицы так, чтобы они плотно обрамляли содержимое
ax_table.axis('off') # выключаем отображение осей для таблицы (нет границ и делений)
table = ax_table.table(cellText=df.values, colLabels=df.columns, loc='center') #задаем содержимое ячеек таблицы, заголовки столбцов и расположение таблицы
table.set_fontsize(10) # устанавливаем размер шрифта для текста в таблице вручную
table.scale(1.2, 1.2) # Можно изменить масштаб таблицы для лучшего отображения
# Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
pdf.savefig(fig_table)
plt.close(fig_table)
# Создание фигуры и осей для гистограммы
fig_hist, ax_hist = plt.subplots(figsize=(14, 6)) # Выберите подходящий размер фигуры
# Установим метки на оси X с названиями месяцев
ax_hist.set_xticks(range(1+len(df['МЕСЯЦ'])))
# Строим столбики гистограммы
ax_hist.bar(df['МЕСЯЦ'], df['ДОХОД'], width=0.4, edgecolor="white", label='доход за месяц', linewidth=0.7)
# Рисуем число заказов
ax_hist.plot(df['МЕСЯЦ'], 5000*df['КОЛИЧЕСТВО ЗАКАЗОВ'], 'r', label='количество заказов (в масштабе x5000)', linewidth=2.0)
# Задание содержимого таблицы со статистикой
ax_hist.set_title(f"Доход от заказов и их количество по месяцам, с {start}-го месяца по {finish}-й месяц")
#Задание осей гистограммы
ax_hist.set_xlabel('Месяц')
ax_hist.set_ylabel('Доход')
# Добавляем легенду на график
ax_hist.legend()
#Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
pdf.savefig(fig_hist)
plt.close(fig_hist)
# Создание фигуры и осей для таблицы
fig_table, ax_table = plt.subplots(figsize=(8, 6)) # Выберите подходящий размер фигуры
# для самых часто покупаемых товаров
#Задание заголовка
ax_table.set_title(f"Самые часто покупаемые товары, с {start}-го месяца по {finish}-й месяц")
# Задание содержимого таблицы со статистикой
ax_table.axis('tight') #задаем границы области для таблицы так, чтобы они плотно обрамляли содержимое
ax_table.axis('off') #выключаем отображение осей для таблицы (нет границ и делений)
table = ax_table.table(cellText=df2.values, colLabels=df2.columns, loc='center') #задаем содержимое ячеек таблицы, заголовки столбцов и расположение таблицы
#table.auto_set_font_size(True)
table.set_fontsize(10) #устанавливаем размер шрифта для текста в таблице вручную
table.scale(1.2, 1.2) # Можно изменить масштаб таблицы для лучшего отображения
#Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
pdf.savefig(fig_table)
plt.close(fig_table)
# Создание фигуры и осей для круговой диаграммы
fig_circle, ax_circle = plt.subplots(figsize=(10, 8)) # 10 - ширина, 8 - высота фигуры в дюймах
#Задание заголовка
ax_circle.set_title(f"Самые часто покупаемые товары по месяцам, с {start}-го месяца по {finish}-й месяц")
# Строим круговую диаграмму с отображением данных
ax_circle.pie(df2['КОЛИЧЕСТВО'], labels=df2['ТОВАР'], autopct='%1.1f%%', startangle=90)
# Устанавливаем соотношение сторон диаграммы как 1, чтобы круг выглядел как круг, а не эллипс
plt.axis('equal')
#Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
pdf.savefig(fig_circle)
plt.close(fig_circle)
# Скачиваем файл на локальную машину
files.download(filename) Результат выглядит довольно симпатично.
Пример итогового PDF-отчета можно посмотреть здесь, а представленный код также доступен в моем Github-репозитории.
Основы архитектуры и интеграции информационных систем
Код курса
OAIS
Ближайшая дата курса
20 мая, 2024
Продолжительность
12 ак.часов
Стоимость обучения
27 000 руб.
Больше примеров, когда аналитику пригодится умение программировать и проектировать ИС я показываю на моих курсах по архитектуре и интеграции информационных систем в Школе прикладного бизнес-анализа на базе нашего лицензированного учебного центра обучения и повышения квалификации системных и бизнес-аналитиков в Москве:
