Условие

Сделать end-to-end проект: от ETL до выводов:

Продумать обработку, хранение, визуализацию данных и используемое ПО. Нарисовать диаграммы для ETL (Data Flow) и ER (DWH).
Построить отчёт(ы) на основе данных.
Сделать выводы об обеспеченности районов и округов инфраструктурой по трём датасетам.

Источники:

data.mos.ru — велопарковки (№916), спортивные залы (№60622), площадки для выгула собак (№2663).
Wikipedia — население районов.

Обязательно — отчёт по обеспеченности «ночной» инфраструктурой, при этом фильтры по объектам:

Велопарковки, работающие круглосуточно.
Спортзалы, в адресе которых есть «2».
Освещённые площадки для выгула собак.

Решение

Шаг 1. Архитектура

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

[data.mos.ru API / выгрузки CSV]   [Wikipedia таблицы]
              \                      /
               \                    /
              [Python ETL: pandas, requests]
                       |
                  [Staging tables]
                       |
                 [Cleaning, geocoding, joining]
                       |
               [DWH: PostgreSQL + PostGIS]
                       |
              [BI: Metabase / Superset / Tableau]

Зачем PostGIS: объекты имеют координаты, нужно по ним считать «попадание в район/округ» — это spatial join ST_Within.

Шаг 2. ER-схема DWH

dim_district(district_id, name, okrug, population, area_km2, geom POLYGON)
dim_object_type(type_id, name)        -- 'bike_parking', 'gym', 'dog_area'

fact_objects(
  object_id,
  type_id,
  district_id,    -- определяется spatial join
  geom POINT,
  address,
  is_24h,         -- для парковок
  has_lighting,   -- для собачьих площадок
  attrs JSONB     -- остальные атрибуты
)

Для отчёта рассчитываем витрину:

mart_provision(
  district_id, district_name, okrug,
  population,
  cnt_bikeparking_24h,
  cnt_gym_addr_with_2,
  cnt_dog_area_lighted,
  per_10k_bikeparking_24h,
  per_10k_gym,
  per_10k_dog_area
)

Шаг 3. ETL Data Flow

Extract: pandas-скриптом качаем CSV/JSON из data.mos.ru (или вручную через UI). Wikipedia парсим через pandas.read_html.
Transform:
- Чистим адреса, нормализуем районы (один и тот же район пишут «Хамовники» / «р-н Хамовники»).
- Геокодим точки (если в выгрузке нет координат — Yandex Geocoder API).
- Spatial join с границами районов → проставляем district_id.
- Применяем фильтры (работа круглосуточно, наличие «2» в адресе, освещение).
Load: вставляем в fact_objects, dim_district. Регенерируем mart_provision на основе фактов (idempotent).

Шаг 4. Расчёт «обеспеченности»

Простейший показатель — на 10 тыс. населения:

SELECT district_name,
       okrug,
       cnt_bikeparking_24h * 10000.0 / NULLIF(population, 0) AS bp_per_10k,
       cnt_gym_addr_with_2 * 10000.0 / NULLIF(population, 0) AS gym_per_10k,
       cnt_dog_area_lighted * 10000.0 / NULLIF(population, 0) AS dog_per_10k
FROM mart_provision
ORDER BY okrug, district_name;

Дополнительно — сводный индекс обеспеченности (например, средневзвешенный z-score по трём метрикам).

Можно перейти от «на 10к» к density на км² — для районов с разной плотностью застройки.

Шаг 5. Дашборд

Карта Москвы с заливкой районов по выбранному показателю (хороплет).
Топ-10 / антитоп-10 районов — bar chart.
Сравнение по округам — boxplot или average.
Таблица с возможностью drill-down: район → конкретные объекты на карте.

Фильтры: тип объекта (велопарковка / спортзал / выгул собак), показатель (на 10k / абсолют / на км²), округ.

Шаг 6. Выводы

Типовые вытекающие наблюдения (в реальной выгрузке могут отличаться):

ЦАО — больше всего объектов в абсолюте, но обеспеченность на 10к жителей часто ниже из-за высокой плотности.
Спальные округа (СВАО, ЮВАО) — низкая обеспеченность 24/7-парковками.
«Освещённые» собачьи площадки — редкость, преимущественно в новых ЖК.
Связь «спортзал в адресе с 2» — это формальный фильтр из задания, нужно подсветить, что это не семантика, а именно адрес содержит цифру 2.

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

Фильтры из задания — про адрес, не про звёздность. «Спортзалы с 2 в адресе» — это формальный текстовый фильтр. Не надо превращать его в «двухзвёздочные» или «дом 2» — может быть в любой части адреса.
«Освещённость» площадок в датасете — отдельный атрибут (булева/строковая категория). Нужно маппить значения вручную, в данных бывает «есть/нет/неизвестно».
Координаты vs адрес. В выгрузках data.mos.ru обычно есть и то, и другое, но не всегда консистентно. Геокодинг через адрес — резервный путь.
Население районов. На Wikipedia табличка может быть устаревшей или собранной из разных лет. Зафиксировать год.
Spatial join без индекса. На больших объёмах нужен CREATE INDEX ... USING GIST(geom).
Деление на 0. Если у района нет населения (пром-зона) — NULLIF.
Отчёт без цифр на 10к — обманчив. ЦАО «обеспечен» парковками в абсолюте, но плохо «на жителя».
«Ночная инфраструктура» — это интерпретация. В задании прописаны три конкретных фильтра — именно их и применяем.

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

ETL: Python (pandas, requests) → Postgres+PostGIS (DWH) → BI (Metabase/Superset/Tableau).
DWH: dim_district (с геометрией района и населением), fact_objects (точки с типом и атрибутами), витрина mart_provision с агрегатами.
Расчёт обеспеченности — количество объектов / 10k населения (главный показатель), дополнительно — на км² и сводный индекс.
Фильтры из задания применяем буквально: круглосуточные парковки, спортзалы с «2» в адресе, освещённые собачьи площадки.
Дашборд — карта-хороплет + топ/антитоп + drill-down.
Выводы — обеспеченность распределена неравномерно, абсолютные цифры в центре высокие, а на 10к жителей часто ниже, чем в спальных районах. Подчёркнуть, что результат — функция выбранных фильтров, а сами фильтры довольно искусственные.

Кейс — обеспеченность районов Москвы ночной инфраструктурой