Задача SQL + Python

Анализ данных — это не только финансы, маркетинг и продажи. SQL и Python позволяют исследовать самые неожиданные темы, например, атаки акул. Эти инструменты помогают находить закономерности, выявлять тренды и строить прогнозы, даже если данные на первый взгляд кажутся хаотичными.

Сегодня мы попробуем себя в роли аналитиков-исследователей:

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

Хотите уметь работать со связкой инструментов SQL + Python так же хорошо, как с инструментами Microsoft Office? Приглашаем вас на курс Аналитик PRO, где мы учим как базовому, так и продвинутому Python, а еще SQL, Excel, Tableau и другим полезным навыкам и инструментам. 

Готовы проверить, какие выводы можно сделать на основе данных? Тогда начнем!
Для анализа у нас есть реальная база данных об атаках акул по всему миру. Она содержит информацию о месте, времени, обстоятельствах и последствиях каждого инцидента.

Основные столбцы в таблице global shark attacks:

• date – дата инцидента (например, 2023-05-13)
• year – год атаки (например, 2023)
• type – тип атаки (например, Unprovoked – неспровоцированная)
• country – страна, где произошла атака
• area – регион внутри страны
• location – конкретное место инцидента
• activity – чем занимался человек в момент атаки (плавание, серфинг и тд)
• name – имя пострадавшего (если известно)
• sex – пол пострадавшего (M – мужчина, F – женщина)
• age – возраст пострадавшего
• fatal_y_n – была ли атака смертельной (Y – да, N – нет)
• species – вид акулы (если установлен)

Что мы будем исследовать?

1. В каких странах чаще всего происходят атаки?
2. Какие виды акул наиболее опасны?
3. Какие активности связаны с наибольшим числом нападений?
4. В какие годы было больше всего атак?

Загрузите файл с таблицей себе на компьютер по ссылке Важно сохранить название таблицы global_shark_attacks, так как с ним связаны все запросы в задаче.
Для тренировки работы с SQL мы будем использовать бесплатный сервис SQLite Online. Работа с ним не требует регистрации или других дополнительных действий. Перейдите на сайт SQLite Online и загрузите файл данных, с которым будем работать.

Как загрузить файл для старта работы, описали в этой статье – https://changellenge-education.com/blog/sql-dlya-starta-raboty. Кстати, это еще одна задача по SQL, которую вы сможете решить по подробным инструкциям.
Перейдем к первому SQL-запросу?

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

Для этого используем SQL-запрос:

SELECT country, COUNT(*) AS attack_count

FROM global_shark_attacks

GROUP BY country

ORDER BY attack_count DESC;

Как работает этот запрос?

1. SELECT country, COUNT(*) AS attack_count

• country — выбираем столбец со странами.
• COUNT(*) — считаем количество строк в каждой группе (то есть количество атак в каждой стране).
• AS attack_count — даем столбцу с количеством атак более понятное имя.

2. FROM shark_attacks

• Указываем таблицу, в которой хранятся данные.

3. GROUP BY country

• Группируем все строки по значению в столбце country.

Почему мы используем GROUP BY? Без него SQL просто вернул бы длинный список всех стран (с повторами), а GROUP BY как бы "собирает" одинаковые страны в группы, чтобы можно было посчитать, сколько раз каждая встречается в данных.

4. ORDER BY attack_count DESC

• Сортируем страны по количеству атак (attack_count).
• DESC означает сортировку по убыванию (от большего к меньшему).

Что мы ожидаем увидеть?
На первых позициях, скорее всего, будут страны с развитой пляжной культурой и серфингом:

• США (особенно Флорида и Калифорния).
• Австралия (пляжи, серфинг).
• Южная Африка (высокая активность белых акул)

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

Используем следующий SQL-запрос:

SELECT species, COUNT(*) AS attack_count

FROM global_shark_attacks

WHERE species IS NOT NULL

GROUP BY species

ORDER BY attack_count DESC

LIMIT 10;

Как работает этот запрос?

1. SELECT species, COUNT(*) AS attack_count

• species — выбираем столбец с видами акул.
• COUNT(*) — считаем, сколько раз каждая акула фигурирует в базе.
• AS attack_count — называем новый столбец, чтобы результат был читаемым.

2. FROM global_shark_attacks

• Указываем таблицу с данными.

3. WHERE species IS NOT NULL

• Исключаем строки, где вид акулы не указан (NULL).

Это условие важно: если не исключить строки с пустыми значениями (NULL), в топ-10 могут попасть случаи, где вид акулы не указан вовсе. Это исказит результаты.

4. GROUP BY species

• Группируем данные по виду акулы, чтобы посчитать количество атак для каждого из них.

5. ORDER BY attack_count DESC

• Сортируем результат по количеству атак (по убыванию).

6. LIMIT 10

• Оставляем только 10 самых «агрессивных» видов акул.

Что мы ожидаем увидеть?
Вероятно, лидерами станут:

• Белая акула (White shark) 🦈 – главная героиня «Челюстей».
• Тигровая акула (Tiger shark) – известна своим всеядным аппетитом.
• Бычья акула (Bull shark) – может жить как в морской, так и в пресной воде.

Как мы видим, для большинства случаев нападений не был определен вид акулы, поэтому в таблице такие случаи занимают первую позицию. Где-то вместо вида акулы указан её размер.

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

Используем следующий SQL-запрос:

SELECT activity, COUNT(*) AS attack_count
FROM global_shark_attacks
WHERE activity IS NOT NULL
GROUP BY activity
ORDER BY attack_count DESC
LIMIT 10;

Как работает этот запрос?

1. SELECT activity, COUNT(*) AS attack_count

• activity — выбираем колонку с занятием человека в момент атаки.
• COUNT(*) — подсчитываем количество атак для каждого вида активности.
• AS attack_count — даем имя новому столбцу.

2. FROM global_shark_attacks

• Берем данные из таблицы global_shark_attacks.

3. WHERE activity IS NOT NULL

• Исключаем строки, где нет информации о виде активности.

4. GROUP BY activity

• Группируем данные по типу активности.

5. ORDER BY attack_count DESC

• Сортируем по количеству атак (по убыванию).

6. LIMIT 10

• Оставляем только топ-10 самых рискованных активностей.

Чего ожидаем?
Наиболее вероятные лидеры:

• Серфинг (Surfing) – акулы могут путать доску с добычей.
• Плавание (Swimming) – люди часто оказываются в воде без защиты.
• Подводная охота (Spearfishing) – кровь добычи привлекает акул.

Теперь посмотрим, как изменялось количество атак акул с течением времени. Это поможет выявить тренды: растет ли число нападений или остается стабильным?
Используем SQL-запрос:

SELECT year, COUNT(*) AS attack_count
FROM global_shark_attacks
WHERE year IS NOT NULL
GROUP BY year
ORDER BY attack_count DESC
LIMIT 1;

Как работает этот запрос?

1. SELECT year, COUNT(*) AS attack_count

• year — выбираем колонку с годом происшествия.
• COUNT(*) — считаем количество атак в каждом году.
• AS attack_count — даем имя новому столбцу.

2. FROM global_shark_attacks

• Берем данные из таблицы global_shark_attacks.

3. WHERE year IS NOT NULL

• Исключаем строки без указания года.

4. GROUP BY year

• Группируем атаки по годам.

5. ORDER BY attack_count DESC

• Сортируем по количеству атак (по убыванию).

6. LIMIT 1

• Выбираем только год с наибольшим числом атак.

Чего ожидаем?
Вероятно, больше всего атак зафиксировано в последние десятилетия. Возможные причины:

• Большее число туристов в океанах.
• Развитие серфинга и дайвинга.
• Улучшение сбора данных (раньше атаки могли просто не фиксироваться).

Теперь самое интересное: визуализируем тренд атак с помощью Python! Готовы перейти ко второй части?

SQL помог нам обработать данные, но намного легче увидеть тренды на графике. Давайте построим динамику атак акул по годам с помощью Python и библиотеки matplotlib.

Для работы с данными мы будем использовать Python и библиотеку Pandas. Вам понадобится среда разработки Google Colab. Google Colab (Google Colaboratory) — это бесплатный облачный сервис от Google, который предоставляет среду для выполнения Python-кода прямо в браузере.

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

Сначала импортируем нужные инструменты:

import pandas as pd

import matplotlib.pyplot as plt

Теперь загрузим данные:

# Загружаем CSV-файл

file_path = "global_shark_attacks.csv"

df = pd.read_csv(file_path)

# Выводим первые строки, чтобы проверить, как выглядят данные
df.head()

Что здесь происходит?

• pandas позволяет работать с таблицами как в SQL.
• matplotlib.pyplot используется для построения графиков.
• df.head() выводит первые 5 строк, чтобы мы убедились, что данные загружены правильно.

Также стоит привести названия столбцов к единому формату — например, сделать их все строчными:

df.columns = df.columns.str.lower()

Так вы избежите ошибок, связанных с тем, что в одном месте написано Country, а в другом country.

Чтобы построить график, нужно сгруппировать данные по годам:

# Преобразуем колонку 'year' в числовой формат

df['year'] = pd.to_numeric(df['year'], errors='coerce')

# Группируем атаки по годам

yearly_attacks = df.groupby('year').size()

# Убираем некорректные года (например, 0 или очень маленькие значения)

yearly_attacks = yearly_attacks[yearly_attacks.index > 1900]

Объяснение кода:

• pd.to_numeric(df['year'], errors='coerce') — преобразует колонку year в число, удаляя ошибки.

Этот приём помогает очистить колонку: если в ячейке вдруг окажется что-то, что не похоже на год (например, ошибка в записи), такое значение превратится в NaN и не повлияет на итоговый график.

• .groupby('year').size() — считает, сколько атак было в каждом году.

Метод .groupby() в pandas работает похожим образом на GROUP BY в SQL. А .size() считает, сколько строк попало в каждую группу — в нашем случае, сколько атак было в каждом году.

• yearly_attacks[yearly_attacks.index > 1900] — фильтруем странные значения (например, если в данных есть нулевые года).

Теперь визуализируем изменения числа атак по годам:

plt.figure(figsize=(10,5))  # Размер графика

plt.plot(yearly_attacks.index, yearly_attacks.values, marker='o', linestyle='-')

plt.xlabel("Год")  # Подпись оси X

plt.ylabel("Количество атак")  # Подпись оси Y

plt.title("Динамика атак акул по годам")  # Заголовок графика

plt.grid(True)  # Включаем сетку

plt.show()  # Показываем график

Что здесь происходит?

• plt.figure(figsize=(10,5)) задаёт размер графика — ширину 10 и высоту 5 условных единиц. Это помогает сделать изображение читаемым.

• plt.plot(...) рисует линию: по оси X идут года (yearly_attacks.index), по оси Y — количество атак (yearly_attacks.values).

• marker='o' добавляет кружочки в каждой точке, а linestyle='-' соединяет их линией.

• plt.xlabel(...) и plt.ylabel(...) добавляют подписи к осям, чтобы было понятно, что именно мы измеряем.

• plt.title(...) — заголовок графика.

• plt.grid(True) включает сетку, чтобы проще было ориентироваться по значениям.

• plt.show() выводит график на экран.

Что мы увидим?

• Скорее всего, рост числа атак в последние десятилетия.
• Возможные пики в 1990-х и 2000-х годах, когда серфинг и пляжный отдых стали популярнее.
• Иногда резкие скачки могут быть связаны с активностью СМИ (чем больше новостей об акулах, тем больше данных фиксируется).

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

Попробуйте изменить SQL-запросы и посмотреть другие закономерности. Например:

• Как часто происходят атаки ночью vs днем?
• Какие штаты в США наиболее опасны?
• Изменился ли средний возраст жертв с годами?

SQL и Python дают возможность исследовать любые данные — от бизнеса до экологии. Главное — задавать правильные вопросы!

Changellenge >> Education

Методист