Описание:

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

Решение:

```python

def filter_products_by_price(min_price):

query = f"SELECT * FROM sales WHERE price > {min_price}"

result_df = pd.read_sql(query, engine)

return result_df

# Фильтрация продуктов с ценой выше 400

filtered_products = filter_products_by_price(400)

print(filtered_products)

```

Результат:

```

id product price quantity

0 1 Laptop 1000 3

1 2 Phone 500 5

```

Задача 7: Определение наиболее активных пользователей

Описание:

В таблице `activity_log` содержатся данные о действиях пользователей:

– `id` – идентификатор записи.

– `user_id` – идентификатор пользователя.

– `action` – выполненное действие.

– `timestamp` – время выполнения действия.

Определите, кто из пользователей совершил наибольшее количество действий.

Решение:

```python

from sqlalchemy import Table, Column, Integer, String, DateTime

from datetime import datetime

# Определение таблицы activity_log

activity_log = Table(

'activity_log', metadata,

Column('id', Integer, primary_key=True),

Column('user_id', Integer),

Column('action', String),

Column('timestamp', DateTime)

)

metadata.create_all(engine)

# Добавление данных

with engine.connect() as conn:

conn.execute(activity_log.insert(), [

{'user_id': 1, 'action': 'login', 'timestamp': datetime(2025, 1, 1, 10, 0)},

{'user_id': 1, 'action': 'purchase', 'timestamp': datetime(2025, 1, 1, 10, 5)},

{'user_id': 2, 'action': 'login', 'timestamp': datetime(2025, 1, 1, 11, 0)},

{'user_id': 1, 'action': 'logout', 'timestamp': datetime(2025, 1, 1, 10, 10)},

{'user_id': 2, 'action': 'purchase', 'timestamp': datetime(2025, 1, 1, 11, 5)},

{'user_id': 2, 'action': 'logout', 'timestamp': datetime(2025, 1, 1, 11, 10)}

])

# Чтение данных

activity_df = pd.read_sql("SELECT * FROM activity_log", engine)

# Подсчет количества действий по пользователям

user_activity = activity_df.groupby('user_id')['id'].count().reset_index()

user_activity.columns = ['user_id', 'action_count']

# Поиск самого активного пользователя

most_active_user = user_activity.loc[user_activity['action_count'].idxmax()]

print(most_active_user)

```

Результат:

```

user_id 1

action_count 3

```

Задача 8: Подсчет действий по типу

Описание: Для каждого типа действия из таблицы `activity_log` подсчитайте, сколько раз оно выполнялось.

Решение:

```python

# Подсчет количества каждого типа действия

action_counts = activity_df['action'].value_counts().reset_index()

action_counts.columns = ['action', 'count']

print(action_counts)

```

Результат:

```

action count

0 login 2

1 purchase 2

2 logout 2

```

Задача 9: Анализ временных меток

Описание: Определите, в какие часы дня пользователи наиболее активны.

Решение:

```python

# Извлечение часа из временных меток

activity_df['hour'] = activity_df['timestamp'].dt.hour

# Подсчет действий по часам

hourly_activity = activity_df.groupby('hour')['id'].count().reset_index()

hourly_activity.columns = ['hour', 'action_count']

print(hourly_activity)

```

Результат:

```

hour action_count

0 10 3

1 11 3

```

Задача 10: Создание таблицы доходов от пользователей

Описание: Используя таблицу `sales`, определите, сколько дохода принёс каждый пользователь, и сохраните результаты в таблицу `user_revenues`.

Решение:

```python

# Добавление данных о продажах с указанием user_id

with engine.connect() as conn:

conn.execute(sales.insert(), [

{'product': 'Laptop', 'price': 1000, 'quantity': 1, 'user_id': 1},

{'product': 'Phone', 'price': 500, 'quantity': 2, 'user_id': 1},

{'product': 'Tablet', 'price': 300, 'quantity': 3, 'user_id': 2}

])

# Чтение данных из sales

sales_df = pd.read_sql("SELECT * FROM sales", engine)

# Расчёт дохода для каждого пользователя

sales_df['revenue'] = sales_df['price'] * sales_df['quantity']

user_revenues = sales_df.groupby('user_id')['revenue'].sum().reset_index()

# Сохранение в новую таблицу

user_revenues.to_sql('user_revenues', engine, if_exists='replace', index=False)

# Проверка результатов

saved_user_revenues = pd.read_sql("SELECT * FROM user_revenues", engine)

print(saved_user_revenues)

```

Результат:

```

user_id revenue

0 1 2000

1 2 900

```

Задача 11: Поиск последнего действия пользователей

Описание:Для каждого пользователя из таблицы `activity_log` найдите его последнее действие.

Решение:

```python

# Поиск последнего действия

last_actions = activity_df.sort_values('timestamp').groupby('user_id').last().reset_index()

last_actions = last_actions[['user_id', 'action', 'timestamp']]

print(last_actions)

```

Результат:

```

user_id action timestamp

0 1 logout 2025-01-01 10:10:00

1 2 logout 2025-01-01 11:10:00

```

Задача 12: Фильтрация пользователей с высоким доходом

Описание: Используя таблицу `user_revenues`, выберите всех пользователей, чей доход превышает 1500.

Решение:

```python

# Чтение данных из user_revenues

user_revenues = pd.read_sql("SELECT * FROM user_revenues", engine)

# Фильтрация пользователей с доходом > 1500

high_revenue_users = user_revenues[user_revenues['revenue'] > 1500]

print(high_revenue_users)

```

Результат:

```

user_id revenue

0 1 2000

```

Задача 13: Распределение доходов по продуктам

Описание: Определите, какой процент от общего дохода приносит каждый продукт.

Решение:

```python

# Подсчет общего дохода

total_revenue = sales_df['revenue'].sum()

# Расчет процента дохода по продуктам

sales_df['revenue_percent'] = (sales_df['revenue'] / total_revenue) * 100

product_revenue_percent = sales_df.groupby('product')['revenue_percent'].sum().reset_index()

print(product_revenue_percent)

```

Результат:

```

product revenue_percent

0 Laptop 50.793651

1 Phone 25.396825

2 Tablet 23.809524

```

Эти задачи демонстрируют, как SQLAlchemy и Pandas могут использоваться вместе для создания, управления и анализа данных в базах данных. Они покрывают такие аспекты, как фильтрация данных, выполнение группировок и агрегатов, интеграция данных и сохранение результатов. Эти примеры помогут вам освоить основные техники работы с базами данных в Python.

Глава 2. Интерактивная визуализация и аналитика

2.1 Использование Plotly для интерактивных графиков

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

Для работы с Plotly необходимо установить библиотеку:

```bash

pip install plotly

```

После установки можно использовать Plotly в сочетании с Pandas, что упрощает построение графиков на основе данных из DataFrame. Далее мы подробно рассмотрим примеры использования Plotly для создания различных типов графиков.

Построение простого линейного графика

Рассмотрим пример, где мы визуализируем изменение температуры в течение недели.

```python

import plotly.graph_objects as go

# Данные

days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

temperatures = [22, 24, 19, 23, 25, 28, 26]

# Создание графика

fig = go.Figure()

fig.add_trace(go.Scatter(

x=days,

y=temperatures,

mode='lines+markers', # Линии с точками

name='Temperature',

line=dict(color='blue', width=2),

marker=dict(size=8)

))

# Настройка заголовков

fig.update_layout(

title='Изменение температуры за неделю',

xaxis_title='День недели',

yaxis_title='Температура (°C)',

template='plotly_white'

)

fig.show()

```

Объяснение:

1. Мы создаём объект `Figure`, добавляя в него данные с помощью `add_trace`.

2. Используем `Scatter` для отображения данных в виде линии с точками.

3. С помощью `update_layout` задаём заголовок графика и подписываем оси.

4. Метод `fig.show()` открывает интерактивный график в браузере.

Построение столбчатого графика

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

```python

categories = ['Electronics', 'Clothing', 'Groceries', 'Books', 'Furniture']

sales = [1000, 1500, 700, 1200, 900]

fig = go.Figure()

fig.add_trace(go.Bar(

x=categories,

y=sales,

name='Sales',

marker=dict(color='orange')

))

fig.update_layout(

title='Продажи по категориям товаров',

xaxis_title='Категории',

yaxis_title='Сумма продаж ($)',

template='plotly_dark'

)

fig.show()

```

Особенности:

– Используем `go.Bar` для построения столбчатого графика.

– Цвет столбцов задаётся через параметр `marker`.

Построение комбинированного графика

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

```python

profit = [300, 500, 200, 400, 350]

fig = go.Figure()

fig.add_trace(go.Bar(

x=categories,

y=sales,

name='Sales',

marker=dict(color='blue')

))

fig.add_trace(go.Scatter(

x=categories,

y=profit,

mode='lines+markers',

name='Profit',

line=dict(color='green', width=2)

))

fig.update_layout(

title='Продажи и прибыль по категориям товаров',

xaxis_title='Категории',

yaxis_title='Сумма ($)',

barmode='group',

template='plotly_white'

)

fig.show()

```

Что добавлено:

– Комбинация `Bar` и `Scatter` позволяет визуализировать данные разных типов.

– Параметр `barmode='group'` размещает столбцы по группам, чтобы они не перекрывались.

Построение круговой диаграммы

Для отображения долей в процентах часто используется круговая диаграмма. Например, распределение продаж по категориям.

```python

fig = go.Figure()

fig.add_trace(go.Pie(

labels=categories,

values=sales,

hole=0.3 # Полудонат (дырка в центре)

))

fig.update_layout(

title='Распределение продаж по категориям',

template='plotly_white'

)

fig.show()

```

Особенности:

– Используем `go.Pie` для построения круговой диаграммы.

– Параметр `hole` задаёт размер центральной части, превращая график в "пончиковую" диаграмму.

Построение тепловой карты