Анализ данных из Excel с помощью Python

Подготовка среды: Python, Jupyter, библиотеки для Excel

Зачем вообще «готовить среду»

Чтобы анализировать данные из Excel на Python, вам нужна воспроизводимая рабочая среда:

установленный Python

изолированное окружение под проект

Jupyter (чтобы удобно экспериментировать)

библиотеки для чтения и записи Excel

Идея простая: один проект — одно окружение — один набор версий библиотек. Тогда код будет одинаково запускаться у вас, у коллег и на сервере.

!Схема, показывающая как Excel читается в pandas и как результаты возвращаются обратно

Варианты установки Python

Есть два популярных пути.

Вариант A: Anaconda (проще для старта)

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

Плюсы:

- всё «в комплекте»: Python, Jupyter, менеджер окружений - удобно новичкам

Минусы:

- занимает много места - иногда сложнее строго контролировать версии

Официальный сайт:

Anaconda

Вариант B: Обычный Python + venv + pip (универсально и «как в индустрии»)

Подходит, если вы хотите легковесный и стандартный подход.

Плюсы:

- стандартный инструмент venv есть в Python - проще переносить проекты

Минусы:

- нужно один раз разобраться с окружением и pip

Официальный сайт:

Python

В рамках курса можно использовать любой вариант. Ниже будет инструкция для варианта B (Python + venv), потому что он наиболее универсален.

Установка Python

Проверка, установлен ли Python

Откройте терминал (или PowerShell на Windows) и выполните:

Если команда не находится, попробуйте:

Установка

Скачайте установщик с сайта:

- Python Downloads

На Windows во время установки включите опцию добавления Python в PATH (часто называется Add Python to PATH).

После установки перезапустите терминал и снова проверьте python --version.

Создание папки проекта

Рекомендуемая структура на старте:

папка проекта

подпапка data для Excel-файлов

подпапка notebooks для Jupyter-ноутбуков

Пример:

Виртуальное окружение (venv)

Виртуальное окружение — это отдельная «копия» набора библиотек для конкретного проекта.

Создание окружения

В папке проекта:

Активация окружения

Windows (PowerShell):

Windows (cmd):

macOS / Linux:

После активации обычно появляется префикс (.venv) в терминале.

Обновление pip

Установка Jupyter

Jupyter позволяет запускать код по ячейкам и сразу видеть результат — это очень удобно для анализа данных.

Установка:

Запуск (из папки проекта):

Если интерфейс Lab не нужен, можно использовать классический Notebook:

Официальный сайт:

Project Jupyter

Библиотеки для работы с Excel

Минимальный набор для курса

Установим основной набор:

Что это такое:

| Библиотека | Для чего нужна | Важные заметки | |---|---|---| | pandas | основная библиотека анализа данных (таблицы, фильтры, группировки) | Excel обычно читают в DataFrame | | openpyxl | чтение/запись файлов .xlsx | часто используется как движок для pandas.read_excel | | XlsxWriter | удобная запись .xlsx с форматированием | часто используется через pandas.ExcelWriter |

Документация:

pandas documentation

openpyxl documentation

XlsxWriter documentation

Дополнительно (по ситуации)

Иногда встречаются другие форматы:

старые файлы .xls

бинарные .xlsb

Часто полезные пакеты:

xlrd — исторически использовался для Excel, но современные версии не читают .xlsx (это частая ловушка)

pyxlsb — для чтения .xlsb

Если вам реально нужно это в работе, можно поставить позже. Сейчас важнее уверенно освоить .xlsx.

Страница пакета xlrd:

xlrd на PyPI

Фиксация зависимостей: requirements.txt

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

Сохранить текущие версии:

Восстановить на другом компьютере (после создания и активации venv):

Быстрая проверка: читаем и записываем Excel

Подготовьте тестовый файл

Создайте файл data/sales.xlsx с листом Sheet1 и колонками:

date

product

qty

price

Можно сделать 5–10 строк вручную.

Код для проверки

Создайте ноутбук notebooks/01_check_environment.ipynb и выполните:

Если файл сохранился и открывается в Excel — окружение настроено.

Типичные проблемы и как их быстро диагностировать

Jupyter не запускается или команда не найдена

Проверьте, что:

окружение активировано

jupyter установлен именно в это окружение

Команды диагностики:

На macOS/Linux вместо where обычно используют:

Ошибка про движок Excel

Если при чтении .xlsx возникают ошибки, обычно причина в том, что не установлен openpyxl.

Проверьте:

Если не найдено — установите:

Важно про безопасность

Не запускайте макросы из неизвестных файлов.

Если в компании используются защищённые/парольные Excel-файлы, доступ к ним может требовать отдельного подхода (мы разберём типовые сценарии позже).

Что дальше по курсу

Дальше мы будем учиться:

уверенно читать Excel (листы, диапазоны, заголовки, типы данных)

очищать данные (пропуски, даты, числа, дубликаты)

делать сводные расчёты и готовить отчёты

записывать результаты обратно в Excel (несколько листов, форматирование)

Чтение и запись Excel: листы, диапазоны, типы данных

Как эта тема связана с предыдущей статьёй

В предыдущей статье вы настроили окружение: Python, Jupyter и библиотеки pandas, openpyxl, xlsxwriter. Теперь мы переходим к практике работы с Excel-файлами: как правильно читать данные из разных листов и диапазонов, как контролировать типы данных и как записывать результаты обратно в Excel.

!Общая схема: Excel → pandas → обработка → Excel-отчёт

Базовое чтение Excel в pandas

Самый частый сценарий: прочитать таблицу в DataFrame.

Что важно понимать:

read_excel читает данные в табличный объект DataFrame.

По умолчанию читается первый лист (если не указать иначе).

Типы данных (df.dtypes) определяются автоматически, но это часто нужно контролировать вручную.

Полезная документация:

pandas.read_excel

Выбор листа: по имени, по индексу, сразу несколько

Прочитать конкретный лист

По имени:

По индексу (нумерация с нуля):

Прочитать несколько листов

Если передать список, вы получите словарь: имя листа → DataFrame.

Если указать sheet_name=None, прочитаются все листы:

Узнать список листов без чтения всех данных

Когда файл большой, полезно сначала посмотреть структуру.

Что в Excel считается «диапазоном» и как его читать

В Excel диапазон часто задают как колонки (например, A:D) и строки (например, 1:100). В pandas.read_excel нет прямого параметра вида "A1:D10", но почти всегда задачу можно решить комбинацией настроек.

Ограничить чтение по колонкам: usecols

Excel-стилем:

По именам колонок (если заголовки уже есть в файле):

Ограничить чтение по строкам: skiprows и nrows

Прочитать, например, 100 строк после первых 2 строк (часто первые строки — это шапки/комментарии):

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

Заголовки: header и переименование

Если заголовок находится не в первой строке, можно указать номер строки заголовка:

Если в файле вообще нет заголовков, можно задать свои:

Точное чтение диапазона ячеек (когда без этого нельзя)

Иногда данные лежат в фиксированном месте, например B2:F20, и выше/левее есть служебные элементы. pandas удобнее для таблиц, а точечные диапазоны проще брать через openpyxl, а затем превращать в DataFrame.

Важно:

data_only=True говорит openpyxl возвращать значения, а не формулы (но значение формулы будет доступно только если файл был сохранён Excel с пересчётом).

Этот подход полезен для «нестандартных» Excel, но для чистых таблиц чаще выбирают pandas.read_excel.

Документация:

openpyxl

Типы данных: что может пойти не так и как контролировать

Проверка типов после чтения

Сразу после чтения почти всегда делайте:

Числа, которые нельзя превращать в числа

Частая проблема: идентификаторы (номера договоров, артикулы, ИНН, телефоны) выглядят как числа, но их нельзя хранить как числа, потому что:

могут быть ведущие нули (например, 001234)

могут быть очень длинные значения (Excel и Python могут отображать их в научной нотации)

по ним не делают арифметику

Решение: читать/хранить как текст.

Даты: parse_dates и явное преобразование

Excel может хранить даты как даты, как текст или как «числа-даты». Надёжный подход — явно преобразовать.

Вариант 1: попросить pandas сразу парсить колонки как даты:

Вариант 2: преобразовать после чтения (удобно, если в колонке мусор/пустые значения):

Параметр errors="coerce" превращает проблемные значения в пропуски, чтобы код не падал.

Числовые колонки, где попался текст

В Excel часто встречается: в колонке количества одна строка содержит "—" или комментарий. Тогда вся колонка может стать текстовой.

Надёжный способ:

Пропуски: что считать пустым

pandas распознаёт пустые ячейки, но в Excel часто используют специальные маркеры: "-", "нет", "N/A".

Точное управление преобразованием: converters

Если вам нужно «читать и сразу чистить» колонку, используйте converters.

Пример: колонка qty может быть строкой, но вы хотите целые числа, а пустоты — в пропуск.

Запись в Excel: один лист

Самый простой вариант:

Почему часто ставят index=False:

индекс DataFrame — это не всегда «настоящая колонка»

Excel-пользователям обычно не нужен служебный столбец индекса

Документация:

pandas.DataFrame.to_excel

Запись в Excel: несколько листов и один файл отчёта

Для отчётов обычно нужен файл с несколькими листами: исходные данные, агрегаты, своды.

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

ExcelWriter держит один файл открытым для записи

каждый to_excel добавляет новый лист

engine="xlsxwriter" удобен для отчётов (особенно если позже понадобится форматирование)

Документация:

pandas.ExcelWriter

XlsxWriter

Запись «поверх» существующего файла и добавление листа

Если нужно добавить лист в уже существующий файл, чаще используют openpyxl как движок.

Замечания:

mode="a" означает append (добавление)

if_sheet_exists управляет поведением, если лист уже существует (варианты зависят от версии pandas; распространённые — "error", "new", "replace", "overlay")

Практические советы для «живых» Excel-файлов

Если Excel-файл выглядит как «отчёт» с шапками, пустыми строками, итогами, объединёнными ячейками, то:

- сначала попробуйте skiprows, usecols, nrows - если не получается стабильно — считайте нужный прямоугольник через openpyxl

Всегда проверяйте типы после чтения: df.dtypes.

Для дат используйте pd.to_datetime(..., errors="coerce").

Для чисел используйте pd.to_numeric(..., errors="coerce").

Идентификаторы и коды чаще храните как текст (string), даже если они «похожи на числа».

Что дальше

После того как вы научились уверенно читать листы и диапазоны и контролировать типы, следующий логичный шаг — очистка данных: пропуски, дубликаты, приведение форматов и подготовка таблиц к анализу (группировки, сводные расчёты, метрики).

Очистка данных: пропуски, дубликаты, форматы и ошибки

Связь с предыдущими темами курса

В прошлых статьях вы:

настроили окружение для работы с Excel (pandas, openpyxl, xlsxwriter)

научились читать Excel по листам и диапазонам и контролировать типы данных (dtype, parse_dates, pd.to_numeric, pd.to_datetime)

Теперь следующий обязательный шаг перед анализом и построением отчётов — очистка данных. Даже если таблица выглядит аккуратно в Excel, в ней часто есть пропуски, дубликаты, «числа как текст», лишние пробелы, нераспознанные даты и строки-итоги.

!Пайплайн: от сырого Excel к чистым данным для анализа

Что значит “чистые данные” в контексте Excel

Чистые данные — это таблица, где:

каждая колонка имеет понятный смысл и корректный тип данных

пропуски либо корректно помечены, либо осмысленно обработаны

дубликаты либо удалены, либо объяснены

значения приведены к единому формату (даты, числа, текст)

ошибки и “мусорные” строки (итоги, комментарии, пустые строки) удалены или отделены

После очистки вы можете уверенно делать группировки, сводные расчёты и формировать отчёты.

Минимальный чек-лист после чтения Excel

Сразу после read_excel полезно выполнить несколько проверок.

Если вы видите неожиданные типы (например, object вместо числа или даты), это сигнал: анализировать рано — сначала чистим.

Документация:

pandas.DataFrame.isna

pandas.DataFrame.dtypes

Пропуски: как находить и как обрабатывать

Как понять, что считается пропуском

В pandas пропуск обычно представлен как NaN (для чисел) или NaT (для дат). Но в Excel пропуски часто маскируются значениями вроде "-", "нет", "N/A".

Если вы не задали na_values при чтении, можно заменить такие маркеры уже после:

Удаление строк с пропусками: dropna

Подходит, если строка без критически важного поля бесполезна.

Важно:

subset задаёт, по каким колонкам пропуск критичен

не удаляйте строки “на всякий случай”: можно потерять много данных

Документация:

pandas.DataFrame.dropna

Заполнение пропусков: fillna

Подходит, если пропуск можно осмысленно заменить.

Типовые варианты:

Осторожно:

“заполнить средним” можно, но только если это не искажает смысл показателя

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

Документация:

pandas.DataFrame.fillna

Дубликаты: как находить и удалять правильно

Почему дубликаты появляются

В Excel дубликаты часто возникают из-за:

повторных выгрузок и склеивания таблиц

ошибок при копировании строк

наличия “логических ключей”, которые должны быть уникальны (например, order_id), но не контролируются

Поиск дубликатов: duplicated

Документация:

pandas.DataFrame.duplicated

Удаление дубликатов: drop_duplicates

Параметр keep:

"first" — оставить первую

"last" — оставить последнюю

False — удалить все строки, которые входят в дубликаты

Документация:

pandas.DataFrame.drop_duplicates

Форматы и типы данных: приводим к единому виду

Обрезка пробелов и приведение регистра в тексте

В Excel часто встречаются значения вида " Москва", "москва", "МОСКВА". В анализе это превращается в разные категории.

Полезные методы:

str.strip() убирает пробелы по краям

str.lower() приводит к нижнему регистру

Документация:

pandas.Series.str

Даты: единый формат и обработка ошибок

Даже если в Excel колонка выглядит как дата, внутри могут быть:

текстовые даты ("2025-01-31")

даты в другом формате ("31.01.2025")

пустые и ошибочные значения

Надёжный паттерн:

Что делает errors="coerce":

если значение не удаётся распознать как дату, оно становится пропуском (NaT)

код не падает, а вы можете дальше осмысленно обработать пропуск

Документация:

pandas.to_datetime

Числа: превращаем “числа как текст” в числа

Сценарий из Excel: в колонке количества встречается "—" или "10 шт", и вся колонка становится текстовой.

Если вам нужны целые числа, но с поддержкой пропусков, используйте “nullable integer”:

Документация:

pandas.to_numeric

pandas nullable integer data type

Идентификаторы: не превращайте их в числа

Поля вроде order_id, inn, phone почти всегда нужно хранить как текст:

возможны ведущие нули

по ним не выполняют математику

важно точное совпадение символов

Ошибки и “мусорные” строки: как находить и фильтровать

Отрицательные значения, нули, невозможные суммы

Перед расчётами задайте простые правила валидности и выделите нарушения.

Итого, комментарии, пустые строки

Иногда внизу листа есть строки вроде "Итого", которые ломают анализ.

Если у вас заранее известно, что в файле всегда есть “подвал” из N строк, иногда проще обрезать чтение параметром nrows ещё на этапе read_excel (это было в прошлой статье).

Практический шаблон: функция очистки

Чтобы очистка была воспроизводимой, удобно собрать шаги в одну функцию.

Идея: очистка — это не “магия”, а набор явных правил. Чем яснее правила, тем меньше сюрпризов в итоговых отчётах.

Что дальше

После очистки данных обычно переходят к анализу:

расчёт метрик (выручка, средний чек, маржинальность)

группировки и агрегаты (groupby)

подготовка отчётов на несколько листов в Excel

Очистка — это фундамент: если он сделан хорошо, все следующие шаги становятся проще и надёжнее.

Преобразования и агрегации: группировки, сводные таблицы, объединения

Связь с предыдущими темами курса

Ранее вы научились:

настраивать среду для работы с Excel и Python

читать Excel-листы и диапазоны в pandas

приводить типы, устранять пропуски и дубликаты, фильтровать “мусорные” строки

Теперь у нас есть чистые таблицы в DataFrame, и можно делать то, ради чего обычно и используют Python вместо ручных операций в Excel:

считать метрики и агрегаты

строить аналитику по группам

собирать сводные таблицы

объединять данные из нескольких листов и файлов

!Общее представление процесса от исходных Excel-данных до готового отчёта

Базовые преобразования перед агрегациями

Вычисляемые колонки

Чаще всего перед группировками вы добавляете производные показатели.

Пример: выручка как количество умножить на цену.

Полезно сразу проверить типы, иначе умножение может “склеивать строки”, если числа прочитались как текст.

Фильтрация и сортировка

Фильтрация нужна, чтобы исключить нерелевантные строки до расчётов.

Группировки: groupby и агрегации

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

Типовые примеры:

выручка по товарам

количество заказов по менеджерам

средний чек по месяцам

Документация:

pandas.DataFrame.groupby

Простая агрегация

Посчитать суммарную выручку по товару.

Важные моменты:

groupby("product") разбивает строки на группы по значениям product

sum() суммирует revenue внутри каждой группы

as_index=False делает результат обычной таблицей, где product остаётся колонкой (удобно для Excel)

Несколько показателей за один проход: agg

Часто нужно сразу несколько метрик.

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

orders считает число уникальных заказов (чтобы одинаковый order_id не раздул статистику)

qty_sum и revenue_sum дают суммарные значения

revenue_avg даёт среднюю выручку на строку (иногда это полезно, но не всегда совпадает со “средним чеком”)

Группировка по нескольким колонкам

Например, выручка по менеджерам и товарам.

agg против transform

agg возвращает итоговую таблицу, где строка соответствует группе

transform возвращает колонку той же длины, что и исходные данные, то есть “подкладывает” групповой показатель обратно к каждой строке

Пример: доля строки в выручке своего товара.

Такой подход удобен для ABC-анализа и для поиска вкладов внутри категорий.

Сводные таблицы: pivot_table

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

Документация:

pandas.pivot_table

Пример: выручка по менеджерам (строки) и месяцам (колонки)

Параметры, которые чаще всего нужны:

index определяет строки сводной

columns определяет колонки сводной

values определяет показатель

aggfunc определяет способ агрегирования, например sum, mean, count

fill_value=0 заменяет пропуски (например, когда у менеджера нет продаж в месяце)

Чем pivot_table отличается от pivot

pivot_table умеет агрегировать (то есть допускает, что для одного пересечения категорий есть много строк)

pivot требует, чтобы для каждого пересечения была ровно одна строка, иначе будет ошибка

На практике для данных из Excel чаще применяют именно pivot_table.

Объединения таблиц: merge, concat, join

Большая часть “реальных” Excel-данных лежит не в одной таблице, а на нескольких листах:

продажи содержат product_id, но не содержат название категории

справочник товаров содержит product_id и атрибуты товара

справочник менеджеров содержит manager_id и отдел

merge: объединение по ключам

merge — аналог VLOOKUP/XLOOKUP и объединений в SQL.

Документация:

pandas.DataFrame.merge

Пример: соединяем продажи со справочником товаров.

Как читать этот код:

on="product_id" означает ключ объединения

how="left" означает: оставить все строки из sales и подтянуть данные из products, где ключ нашёлся

Основные типы объединения:

how="left" сохраняет все строки левой таблицы

how="inner" оставляет только совпавшие ключи

how="outer" оставляет все ключи из обеих таблиц

Контроль качества объединений: validate и indicator

Частая ошибка в Excel-логике: вы думали “ключ уникален”, а он повторяется, и объединение размножает строки.

Что означает validate="many_to_one":

слева может быть много строк с одним product_id (это нормально для продаж)

справа ожидается максимум одна строка на product_id (то есть справочник должен быть уникальным по ключу)

Если условие нарушено, pandas выдаст ошибку, и вы поймёте, что справочник нужно чистить.

concat: склеивание таблиц “друг под другом”

concat используют, когда у вас одинаковая структура, но данные разбиты на файлы или листы.

Документация:

pandas.concat

Пример: объединить продажи за два месяца.

ignore_index=True пересобирает индекс с нуля, что почти всегда удобно после склейки.

join: объединение по индексу

join удобен, когда ключ уже находится в индексе таблицы. Для большинства Excel-сценариев проще и привычнее merge, но полезно знать, что join существует.

Документация:

pandas.DataFrame.join

Практический пример: собрать Excel-отчёт с итогами и сводной

Сценарий:

лист sales содержит продажи с order_id, date, product_id, manager_id, qty, price

лист products содержит product_id, product, category

лист managers содержит manager_id, manager, team

Результат:

лист data с обогащёнными продажами

лист summary_by_category с агрегатами по категориям

лист pivot_manager_month со сводной по менеджерам и месяцам

Типичные ошибки и как их избегать

Если после merge строк стало больше, проверьте уникальность ключа в справочнике и используйте validate.

Если groupby “теряет” колонку ключа, используйте as_index=False или делайте .reset_index().

Если pivot_table создаёт много пропусков, используйте fill_value или заполняйте пропуски после.

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

Что дальше

Теперь вы умеете превращать очищенные Excel-данные в аналитические таблицы и отчёты: объединять справочники, считать метрики по группам и строить сводные. Следующий логичный шаг курса — автоматизация отчётности и более прикладные сценарии: форматирование Excel-отчётов, проверка качества данных, повторяемые пайплайны и регулярные выгрузки.

Анализ и метрики: описательная статистика и проверки качества

Зачем нужны метрики и проверки качества

На прошлых шагах курса вы научились:

читать Excel в pandas и управлять типами данных

очищать таблицы (пропуски, дубликаты, форматы)

делать преобразования, группировки, сводные и объединения (groupby, pivot_table, merge)

Теперь логичный следующий шаг: прежде чем строить отчёт и отправлять цифры бизнесу, нужно:

быстро получить описательную статистику (что вообще происходит с данными)

выполнить проверки качества (насколько данным можно доверять)

сформировать список проблемных строк и правил, по которым данные считаются корректными

!Общая схема: от Excel к метрикам и контролю качества

Описательная статистика: быстрый “профиль” таблицы

Описательная статистика отвечает на вопросы:

сколько строк и колонок

какие типы данных

есть ли пропуски

какие значения встречаются чаще всего

есть ли подозрительные значения (нулевые цены, отрицательные количества)

Минимальный чек-лист “после загрузки и очистки”

Полезные ссылки:

pandas.DataFrame.dtypes

pandas.DataFrame.isna

Числовые колонки: describe, квантили, проверка распределения

Самый быстрый способ получить базовые метрики по числам:

Что обычно смотрят в describe():

count — сколько непустых значений

mean — среднее

std — разброс (стандартное отклонение)

min, max — экстремумы

25%, 50%, 75% — квантили (включая медиану как 50%)

Ссылка:

pandas.DataFrame.describe

Если вы уже посчитали выручку, полезно проверить её отдельной колонкой:

Категориальные колонки: уникальные значения и частоты

Для текста и категорий (товары, менеджеры, города) важно понимать:

сколько уникальных значений

какие значения самые частые

нет ли “мусора” (разный регистр, пробелы, случайные категории)

Ссылки:

pandas.Series.nunique

pandas.Series.value_counts

Даты: период покрытия и “провалы”

Для колонок дат обычно проверяют диапазон и “пустые дни”.

Метрики: что считать, чтобы таблица стала отчётом

Метрики зависят от предметной области, но для “продаж из Excel” часто нужны:

| Метрика | Как посчитать в pandas | Для чего полезна | |---|---|---| | Выручка | qty * price | базовый показатель объёма | | Число заказов | nunique(order_id) | контроль повторов и масштаба | | Средняя цена | price.mean() | диагностика уровня цен | | Средняя выручка на заказ | сначала по заказу, потом mean() | ближе к “среднему чеку” | | Топ товаров | groupby(product).revenue.sum() | приоритизация |

Пример агрегатов по товарам:

Важно: если order_id отсутствует, временно можно считать “число строк”, но это хуже. Строки и заказы — разные сущности.

Проверки качества данных: что контролировать до публикации цифр

Проверки качества — это набор правил. Хорошая проверка:

формулируется явно

даёт список нарушений (какие строки плохие)

не “лечит” данные молча (исправления должны быть осознанными)

Ниже — практичный набор проверок для Excel-таблиц.

Проверка схемы: колонки и типы

Сначала убедитесь, что структура ожидаемая: нужные колонки на месте.

Типы лучше проверять “по смыслу”, потому что точные dtype могут отличаться. Например, дата должна быть датой:

Ссылка:

pandas.api.types

Проверка пропусков в критичных полях

Критичные поля — те, без которых строка не участвует в расчётах.

Стратегия обычно такая:

сохранить нарушения в отдельный файл

вернуть владельцу данных или исправить по согласованным правилам

Проверка уникальности ключей (если ключ должен быть уникальным)

Если есть поле, которое должно быть уникальным (например, order_id в таблице заказов), проверяйте дубликаты.

Важно: в таблице позиций заказа order_id может повторяться (несколько товаров в заказе). Тогда уникальным ключом может быть пара order_id + product_id или отдельный line_id.

Проверка диапазонов и “невозможных” значений

Самые частые правила для продаж:

qty > 0

price > 0

revenue не должна быть отрицательной

Такие проверки лучше делать до агрегаций, чтобы ошибочные строки не искажали итог.

Проверка выбросов (подозрительно большие/маленькие значения)

Выброс — это значение, которое сильно отличается от большинства. Частый практичный подход — сравнить с квартилями.

Это не “истина”, а фильтр внимания: выброс может быть реальной дорогой позицией, а может быть ошибкой ввода.

Ссылка:

pandas.Series.quantile

Проверки после объединений: не размножились ли строки, все ли ключи нашли справочник

После merge важно контролировать два риска:

справочник оказался не уникальным и “размножил” строки

часть ключей не нашла соответствий (появились пропуски в справочных полях)

Надёжный паттерн: validate и indicator.

Если validate падает с ошибкой, это хороший сигнал: справочник нужно чистить (например, убрать дубликаты product_id).

Ссылка:

pandas.DataFrame.merge

Сверка итогов: контроль, что итоговые суммы совпадают с ожиданиями

Сверка итогов полезна, когда:

вы сравниваете расчёты Python с “контрольной цифрой” из Excel/1С/CRM

вы склеили данные из нескольких файлов и боитесь потери строк

Пример: сравнить сумму выручки “до” и “после” фильтра по валидности.

Практический шаблон: отчёт по качеству в Excel

Идея: вместе с листами summary и pivot записывать лист quality_issues, чтобы нарушения были видны сразу.

Практический смысл такого файла:

бизнес смотрит summary_by_product

аналитик и владелец данных смотрят quality_issues и решают, что исправлять в источнике

Частые ошибки при анализе метрик

Если средние значения выглядят странно, проверьте типы (object вместо чисел) и наличие текстовых “маркеров” типа "-".

Если после merge строк стало больше, почти всегда это дубликаты ключа в справочнике; используйте validate.

Если “пропала выручка”, проверьте, что qty и price не стали NaN после to_numeric(errors="coerce").

Если “всё ноль”, проверьте, что вы агрегируете правильную колонку и не отфильтровали данные слишком жёстко.

Что дальше

После того как вы научились:

быстро строить описательную статистику

считать метрики

оформлять правила качества и выгружать нарушения

следующий шаг курса обычно связан с автоматизацией: регулярные обновления отчётов, оформление Excel (форматирование, условные выделения), упаковка кода в воспроизводимый пайплайн.

Визуализация: графики и дашборды по данным из Excel

Связь с предыдущими темами курса

К этому моменту вы уже умеете:

читать Excel в pandas и контролировать типы данных

очищать данные (пропуски, дубликаты, форматы)

делать преобразования, агрегации, сводные и объединения

считать метрики и выполнять проверки качества

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

!Схема, показывающая как данные проходят путь от Excel к графикам и дашбордам

Какие библиотеки используют для графиков и дашбордов

Базовый набор

matplotlib — фундаментальная библиотека для статичных графиков

seaborn — более “красивые” статистические графики поверх matplotlib

plotly — интерактивные графики (подсказки, зум, фильтрация)

Установка (если ещё не ставили):

Документация:

Matplotlib

Seaborn

Plotly Python

Дашборды

Для быстрого дашборда из Python-кода без сложного фронтенда чаще всего выбирают streamlit.

Документация:

Streamlit

Подготовка данных к визуализации

Графики “ломаются” не из-за библиотек, а из-за данных: неверные типы, пропуски, смешанные единицы измерения, даты как текст.

Минимальный шаблон перед визуализацией:

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

Статичные графики: pandas + matplotlib

pandas умеет строить базовые графики через matplotlib. Это удобно для быстрых проверок.

Линейный график: динамика выручки по дням

Соберём выручку по дням и построим тренд.

Что важно:

figsize помогает сделать график читабельным

tight_layout() уменьшает риск “обрезанных” подписей

rotation=45 почти всегда нужен для дат

Столбчатая диаграмма: топ товаров по выручке

Практический совет: на категориальных графиках почти всегда нужен поворот подписей и ограничение “топ-N”, иначе график становится нечитаемым.

Более “аналитичные” графики: seaborn

seaborn удобен для задач анализа качества и понимания распределений.

Гистограмма: распределение цен

Зачем это нужно:

быстро увидеть “хвост” и подозрительно большие значения

понять типичный диапазон цен

Boxplot: сравнение распределений по категориям

Например, сравним цены по менеджерам (или по категориям товаров, если есть колонка category).

Как читать boxplot на практике:

“коробка” показывает типичный диапазон (середину распределения)

точки далеко сверху/снизу часто сигнализируют о выбросах или ошибках

Heatmap: “матрица” менеджер × месяц

Это визуальный аналог сводной таблицы.

Сохранение графиков в файлы

Это полезно, если вы:

собираете презентацию

прикладываете графики к письму

складываете результаты в папку отчёта

Ключевые моменты:

dpi влияет на качество картинки

plt.close() полезен, если вы генерируете много графиков в цикле

Интерактивные графики: plotly

Интерактивность особенно полезна для “дашбордного” сценария: пользователь может навести курсор и увидеть точные значения, приблизить участок, выключить серии.

Пример: интерактивная динамика выручки

Пример: топ товаров (горизонтальные столбцы)

Горизонтальный bar часто читается лучше, когда длинные названия.

Экспорт интерактивного графика в HTML

Это удобно: файл открывается в браузере и не требует Python.

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

Дашборд — это приложение, где пользователь:

выбирает фильтры (период, менеджер, категория)

видит обновляющиеся графики и таблицы

получает итоговые метрики

Для данных из Excel типовой подход:

Excel хранится в data/ или на сетевом диске

Python читает Excel, чистит, считает метрики

дашборд показывает агрегаты и графики

!Пример компоновки дашборда для продаж из Excel

Практика: простой дашборд на Streamlit, который читает Excel

Структура проекта

data/ — Excel-файлы

app.py — файл приложения Streamlit

Код app.py

Как запустить

В терминале из папки проекта:

После запуска откроется браузер. Вы сможете:

указать путь к Excel

выбрать период

отфильтровать по менеджеру (если колонка существует)

смотреть KPI, графики и таблицу

Практические принципы хорошей визуализации для Excel-данных

Делайте подписи осей и заголовки: график без подписей — это “картинка”, а не аналитика.

Сначала агрегируйте, потом рисуйте: миллионы строк лучше превратить в “по дням/по товарам”, иначе будет медленно и нечитаемо.

Ограничивайте категории: “топ-10/15” обычно полезнее, чем 200 столбиков.

Разделяйте “данные” и “проблемы”: метрики и графики делайте по очищенным данным, а нарушения качества — отдельным листом/таблицей/виджетом.

Проверяйте типы перед графиками: если price или date не того типа, визуализация будет неверной или не построится.

Что дальше

После того как вы научились строить графики и простой дашборд, следующий практический шаг — собрать всё в воспроизводимый мини-пайплайн:

чтение Excel

очистка и проверки качества

метрики и визуализации

выгрузка Excel-отчёта и публикация дашборда

Это превращает разовые действия в регулярную автоматизированную отчётность.

Автоматизация: шаблоны скриптов, отчёты и экспорт результатов в Excel

Связь с предыдущими темами курса

Ранее вы научились:

читать Excel в pandas, выбирать листы и диапазоны, контролировать типы

очищать данные (пропуски, дубликаты, форматы, ошибки)

делать преобразования, агрегации, сводные и объединения

считать метрики и выполнять проверки качества

визуализировать результаты и даже собирать простой дашборд

Автоматизация превращает эти разрозненные действия в повторяемый процесс: вы запускаете один скрипт и получаете готовый Excel-отчёт с нужными листами, метриками и списком проблемных строк.

!Диаграмма показывает, как данные проходят путь от исходных Excel-файлов до итогового отчёта

Что такое автоматизация в контексте Excel-аналитики

Автоматизация — это когда вы:

описываете правила обработки в коде (а не руками в Excel)

запускаете обработку одинаково сегодня, завтра и через месяц

получаете отчёт в одном и том же формате

Ключевые принципы хорошей автоматизации:

воспроизводимость: одинаковые входные данные дают одинаковый результат

прозрачность: правила очистки и проверок видны в коде

контроль качества: проблемы не прячутся, а выгружаются отдельным листом

минимум ручных шагов: один запуск — один отчёт

Минимальная структура проекта

Практичная структура, с которой удобно жить в реальном проекте:

data/ — входные Excel-файлы

reports/ — готовые отчёты

src/ — код (модули)

run_report.py — точка входа (скрипт запуска)

Пример:

Зачем это нужно:

код отделён от данных и результатов

легко перенести проект на другой компьютер

проще тестировать и переиспользовать функции

Шаблон пайплайна: чтение → очистка → метрики → качество → экспорт

Ниже — рабочий шаблон, который объединяет темы курса в один процесс.

Шаг чтения данных из нескольких файлов

Частый сценарий: продажи лежат в нескольких файлах с одинаковой структурой. Мы склеиваем их через pd.concat.

src/io_excel.py:

Что здесь важно:

pathlib.Path удобен для путей и кроссплатформенности

source_file помогает расследовать проблемы (из какого файла пришла строка)

Документация:

pandas.concat

pathlib — работа с путями

Шаг очистки как отдельная функция

Автоматизация начинается с того, что очистка перестаёт быть набором действий в ноутбуке и превращается в функцию.

src/cleaning.py:

Почему это лучше, чем “почистить пару раз руками”:

одинаковые правила применяются каждый запуск

меньше риска забыть шаг и получить другие цифры

Метрики и сводные как отдельный модуль

src/metrics.py:

Проверки качества: не “исправляем молча”, а выгружаем нарушения

Идея: отчёт должен содержать не только красивые итоги, но и лист с тем, что нужно поправить в источнике.

src/metrics.py можно дополнить таким блоком:

Экспорт отчёта в Excel: несколько листов и базовое форматирование

Экспорт — это место, где автоматизация становится “удобной для бизнеса”: итоговый файл открывают в Excel, и он уже пригоден для чтения.

Почему часто выбирают xlsxwriter

xlsxwriter удобен для отчётов, потому что поддерживает:

оформление шапки

заморозку строк и фильтры

условное форматирование

(при необходимости) диаграммы

Документация:

pandas.ExcelWriter

XlsxWriter

Функция экспорта

src/export_excel.py:

Что делает этот код:

пишет несколько DataFrame в один Excel-файл разными листами

замораживает шапку и включает фильтры

оформляет заголовки

применяет числовой формат для денежных колонок

Логирование: чтобы понимать, что произошло при запуске

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

Для этого используют стандартный модуль logging.

Документация:

logging — журналирование в Python

Пример настройки логирования в run_report.py:

И далее по коду:

Параметры запуска: argparse вместо правок кода

Если вы каждый раз меняете пути внутри кода, автоматизация ломается. Правильнее — передавать параметры запуска.

Для этого подходит стандартный модуль argparse.

Документация:

argparse — разбор аргументов командной строки

Итоговый скрипт запуска: run_report.py

Этот файл связывает всё вместе.

Как запускать:

Практические правила, чтобы автоматизация не ломалась

Делайте обработку идемпотентной: повторный запуск должен перезаписывать отчёт и давать тот же результат на тех же данных.

Никогда не “чините данные молча”: если строка подозрительна, лучше вынести её в quality_issues.

После merge используйте validate, чтобы справочники не размножали строки.

Храните зависимости в requirements.txt, чтобы окружение воспроизводилось.

Держите “точку входа” одну: run_report.py, а всю логику — в src/.

Что дальше

После автоматизации отчёта следующий практический шаг обычно один из двух:

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

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