Оптимизация логистических маршрутов и анализ ключевых метрик

Описание

Этот проект — аналитическая система для логистической компании, цель которой — оптимизация маршрутов доставки и анализ ключевых метрик.
Основная идея — построить ETL-процесс, загрузить очищенные данные в базу данных и провести комплексный анализ с визуализацией.

Этапы

• Очистка данных, фильтрация аномалий (Python, Pandas)
• Загрузка данных в PostgreSQL (через psycopg2)
• SQL-аналитика
• Статистический анализ (корреляции, тесты гипотез)
• Визуализация данных (Power BI / Tableau)
• Оптимизация ETL-пайплайна (автоматизация)
• Подготовка дашбордов и презентации результатов

Используемые технологии

• Python: pandas, psycopg2
• База данных: PostgreSQL
• Jupyter Notebook — исследовательский анализ
• BI: Power BI (планируется)

Структура проекта на данный момент

├── data/  
│   ├── amazon_delivery.csv          # Исходные данные  
│   └── cleaned_delivery_data.csv    # Очищенные данные  
├── notebooks/  
│   └── notebook.ipynb               # Анализ исходных данных  
├── scripts/  
│   ├── data_cleaning.py             # Очистка данных  
│   └── load_db.py                   # Загрузка в PostgreSQL  
├── sql_analysis/                    # SQL-аналитика  
│   ├── route_ranking.sql            # Популярные маршруты  
│   ├── problem_orders.sql           # Заказы с долгой доставкой  
│   ├── area_ranking.sql             # Ранжирование районов  
│   ├── seasonality_analysis.sql     # Сезонность заказов  
│   ├── vehicle_ranking.sql          # Эффективность транспорта  
│   └── weather_ranking.sql          # Влияние погоды  
├── .gitignore  
├── LICENSE  
└── README.md  

Описание каждого этапа

Очистка данных и фильтрация аномалий

Этап предобработки данных для обеспечения качества и согласованности информации.

• Order_ID - Проверка уникальности идентификаторов заказов
• Agent_Age - Фильтрация аномальных значений возраста курьеров: 14-80 лет
• Agent_Rating - Фильтрация рейтинга курьеров: ≤5.0 (пропуски заполнены медианой)
• Delivery_Time - Фильтрация времени доставки: 5-1440 минут
• Store_Latitude/Store_Longitude/Drop_Latitude/Drop_Longitude - Проверка валидных диапазонов координат. (P.S. Координаты в датасете изменены его авторами для сохранения конфиденциальности, поэтому много координат из атлантического океана...)
• Преобразование типов данных для оптимизации памяти всех числовых столбцов
• Order_Date/Order_Time - Объединение даты и времени заказа в Order_DateTime
• Pickup_Time - Преобразование времени забора
• Weather/Traffic/Vehicle/Area/Category - Категоризация текстовых данных
• Удаление некорректных и пропущенных значений во всех столбцах

Результат:

• Уменьшение объема данных с 5.3 МБ до 2.5 МБ
• Сохранение 43,594 записей из исходных 43,739
• Готовый набор данных для анализа

Файл с очищенными данными: cleaned_delivery_data.csv

Загрузка данных в PostgreSQL

• Подключение к БД PostgreSQL с параметрами подключения
• Создание таблицы delivery_dataset с соответствующими типами данных:
  • Order_ID (VARCHAR) - первичный ключ
  • Agent_Age (SMALLINT), Agent_Rating (REAL)
  • Координаты: Store_Latitude, Store_Longitude, Drop_Latitude, Drop_Longitude (REAL)
  • Pickup_Time (INTERVAL), Order_DateTime (TIMESTAMP)
  • Категориальные поля: Weather, Traffic, Vehicle, Area, Category (VARCHAR)
  • Delivery_Time (SMALLINT)
• Прямая загрузка данных из CSV-файла в PostgreSQL для дальнейшего анализа

SQL-аналитика данных доставки

Выполненные запросы:

• Ранжирование районов (area_ranking.sql)

  • Area - ранжирование по количеству заказов
  • Расчет доли заказов для каждого района

• Проблемные заказы (problem_orders.sql)

  • Выявление заказов с временем доставки выше среднего
  • Анализ по районам, трафику, погоде и транспорту

• Популярные маршруты (route_ranking.sql)

  • Анализ 10 самых популярных маршрутов
  • Группировка по округленным координатам (для анонимизации)
  • Ранжирование по количеству заказов

• Сезонность заказов (seasonality_analysis.sql)

  • Анализ заказов по месяцам (Order_DateTime)
  • Среднее время доставки в каждом месяце
  • Сравнение с общим средним временем

• Эффективность транспорта (vehicle_ranking.sql)

  • Ранжирование типов транспорта (Vehicle) по скорости доставки
  • Расчет доли использования каждого транспорта

• Влияние погоды (weather_ranking.sql)

  • Анализ времени доставки для комбинаций погода-транспорт
  • Ранжирование от лучших к худшим условиям

Использовала: оконные функции, CTE, агрегатные функции, группировка и сортировка данных, подзапросы.