Условие

Дана плоская таблица рейсов (booking_id, booking_month, itinerary, flights_count, origin, destination, airline, baggage, passengers, price, passenger_price) — каждая строка соответствует одному рейсу в составе бронирования. Нужно собрать исследование «Московский узел» в виде ноутбука или дашборда (Tableau Public, Metabase, etc.).

Из Москвы в Петербург (LED) можно улететь из трёх аэропортов:

Шереметьево SVO,
Внуково VKO,
Домодедово DME.

Вопросы:

Как пассажиропоток распределён между этими тремя аэропортами?
Какая динамика по месяцам? Если есть изменения — чем их можно объяснить?
Конкуренция есть и у авиакомпаний — как распределён трафик между ними внутри каждого аэропорта?
Цены какой авиакомпании наиболее выгодны? Самый дешёвый способ улететь на самолёте из Москвы в Питер?
Кто зарабатывает больше всех (в сумме и в среднем на одном пассажире)? Выручка авиакомпании ≈ сумма цен билетов.

Структура данных

Развёрнутая таблица рейсов: booking_id, booking_month, itinerary, flights_count, origin, destination, airline, baggage, passengers, price, passenger_price.

Решение

Подход

Это исследовательская задача. Нужны: 4–5 фокусных визуализаций, выводы по каждой, sanity-check данных.

Шаг 1. Подготовка датасета

Фильтруем только Москва — Питер прямым рейсом:

import pandas as pd
flights = pd.read_parquet("flights.parquet")  # из ETL
 
mow_led = flights[
    (flights["origin"].isin(["SVO", "VKO", "DME"]))
    & (flights["destination"] == "LED")
    & (flights["flights_count"] == 1)  # прямые рейсы
].copy()

Важно: passenger_price = price / passengers. Полную выручку считаем через price * passengers? Нет! price — это цена бронирования (на всех пассажиров). Поэтому:

Число пассажиров = passengers.
Выручка авиакомпании = сумма price по бронированиям.

# Чтобы избежать дублирования при explode (но у нас прямой рейс — 1 строка = 1 бронирование)
mow_led["pax"] = mow_led["passengers"]
mow_led["revenue"] = mow_led["price"]

Шаг 2. Распределение по аэропортам

by_airport = mow_led.groupby("origin").agg(
    bookings=("booking_id", "nunique"),
    pax=("pax", "sum"),
    revenue=("revenue", "sum"),
).sort_values("pax", ascending=False)
 
by_airport["pax_share"] = by_airport["pax"] / by_airport["pax"].sum()
print(by_airport)

Визуализация: bar chart share % по pax. Обычно SVO ~ 45–50%, DME ~ 30–35%, VKO ~ 15–25% (зависит от сезона).

Шаг 3. Динамика по месяцам

monthly = mow_led.groupby(["booking_month", "origin"])["pax"].sum().unstack()
monthly.plot(kind="line", marker="o", figsize=(10, 5))

Что искать:

Сезонность — пик летом и в конце декабря.
Структурные сдвиги — закрытие/открытие аэропорта (например, VKO исторически плотно работает с лоукостерами; их уход меняет долю).
COVID-провалы — весна 2020.
Гео-события — закрытие воздушного пространства.

В выводе: «доля VKO упала с X% до Y% после месяца Z, потому что лоукостер Pobeda (DP) перешёл в SVO».

Шаг 4. Авиакомпании внутри аэропорта

by_airport_airline = mow_led.groupby(["origin", "airline"])["pax"].sum().unstack(fill_value=0)
by_airport_airline_pct = by_airport_airline.div(by_airport_airline.sum(axis=1), axis=0) * 100

Визуализация: stacked bar по аэропортам, где сегменты — авиакомпании. Подсветить ТОП-3 в каждом аэропорту.

Типично:

SVO — SU (Аэрофлот) доминирует.
DME — S7 плюс несколько небольших.
VKO — лоукостеры (DP, FV).

Шаг 5. Самый выгодный билет

«Выгодный» можно определять по-разному. Минимум:

Средняя цена за пассажира по авиакомпании:

mow_led["price_per_pax"] = mow_led["price"] / mow_led["passengers"]
cheap = mow_led.groupby("airline").agg(
    avg_price=("price_per_pax", "mean"),
    median_price=("price_per_pax", "median"),
    n=("booking_id", "nunique"),
).sort_values("median_price")

Лучше использовать медиану — средняя сильно искажается выбросами (бизнес-классом). Также добавить «без багажа / с багажом» как сегмент:

cheap_seg = mow_led.groupby(["airline", "baggage"])["price_per_pax"].median().unstack()

«Самый дешёвый способ» — авиакомпания с минимальной медианой No baggage. Часто это DP (Pobeda) или FV (Россия).

Шаг 6. Кто зарабатывает больше всех

revenue_by_airline = mow_led.groupby("airline").agg(
    total_revenue=("price", "sum"),  # сумма выручки
    pax=("pax", "sum"),
    bookings=("booking_id", "nunique"),
)
revenue_by_airline["revenue_per_pax"] = (
    revenue_by_airline["total_revenue"] / revenue_by_airline["pax"]
)

Два разных ответа:

В сумме — обычно лидер SU (большой объём по умеренной цене).
Per pax — премиальные перевозчики или авиакомпании с большим багажным апсейлом.

Слайды/дашборд

Минимально 5 чартов:

Pie / bar — доли аэропортов.
Линия — динамика pax по месяцам, разбивка по аэропортам.
Stacked bar — авиакомпании внутри каждого аэропорта.
Box / violin — распределение price_per_pax по авиакомпаниям.
Scatter — total_revenue vs revenue_per_pax (точки — авиакомпании; размер — pax).

Подводные камни

flights_count > 1 — это пересадки, и Москва-Питер не является «прямым». Фильтровать.
origin == LED, destination ∈ Москва — обратный поток. По условию мы смотрим только из Москвы; обратный — отдельная история. Уточнить при выполнении.
price — это сумма на всё бронирование, не на пассажира. Не складывайте price для расчёта «средней цены за билет».
Двойной счёт при groupby("airline") если бронирование уже было explode-нуто. У нас прямой рейс — 1 строка на бронирование, проблем нет. Для flights_count > 1 — revenue нужно делить или приписывать одной авиакомпании (например, той, что выполняет первый рейс).
Сезонность важна. Один срез по году скрывает падение VKO весной 2022, рост DME в июне.
Бизнес-класс искажает среднюю цену. Используйте медиану и/или фильтруйте по passenger_price < 100k₽.
Малые авиакомпании с одним-двумя рейсами в датасете — выкидывайте из per-airline сравнения (bookings >= 30), иначе шум.

Эталонный ответ

5 ключевых визуализаций + аннотированные выводы:

Аэропорты: SVO ~45–50% pax, DME ~30%, VKO ~20%; в высокий сезон растёт SVO.
Динамика: сезонные пики лето/декабрь; структурные сдвиги — упомянуть, какие.
Конкуренция: концентрация в SVO (SU), фрагментация в DME, лоукостеры в VKO.
Цена: самый дешёвый — DP (No baggage), самая дорогая — SU (с багажом).
Выручка: SU лидирует в абсолюте, но per pax — премиум-сегмент.

Финальный артефакт — ноутбук или 1 дашборд с этими графиками + 5–7 строк нарратива.

Aviasales Aviastats — BI: пассажиропоток Москва — Питер