Pandas groupby и merge: как собрать аналитический отчёт из таблиц

Коротко

groupby отвечает на вопрос «как посчитать показатель по группам». merge соединяет таблицы по ключу. pivot_table превращает длинный результат в компактную матрицу. Все три операции простые по синтаксису, но ошибки в зерне легко превращают 10 заказов в 100 или незаметно теряют пользователей.

• Перед операцией запишите, что означает одна строка в каждой таблице.
• Сначала агрегируйте факты до нужного уровня, потом присоединяйте справочники.
• Для контроля соединения проверяйте количество строк и долю пропусков после merge.
• Не используйте sum для показателя, который уже был агрегирован выше.

Начните с зерна таблиц

Представим учебный набор: в orders одна строка — один заказ, в order_items одна строка — одна товарная позиция, в users одна строка — один пользователь. Если присоединить users к order_items, строки не должны умножиться: для каждого user_id в справочнике должна быть максимум одна запись.

groupby: агрегируйте факты по смысловой группе

В groupby сначала укажите измерение, затем выберите поля и агрегаты. Для продуктового отчёта часто нужны не только сумма выручки, но и количество заказов, уникальные покупатели и средний чек. Явные имена агрегатов читаются лучше, чем цепочка из нескольких промежуточных таблиц.

channel_report = (
    orders.groupby('channel', as_index=False)
    .agg(
        orders=('order_id', 'nunique'),
        buyers=('user_id', 'nunique'),
        revenue=('revenue', 'sum'),
        average_order=('revenue', 'mean'),
    )
    .sort_values('revenue', ascending=False)
)

channel_report['revenue_share'] = (
    channel_report['revenue'] / channel_report['revenue'].sum()
)

merge: соединяйте таблицы после агрегации

Если вам нужен канал в отчёте по заказам, сначала соберите одну строку на заказ, а затем добавьте атрибуты пользователя. how="left" сохраняет все строки левой таблицы и позволяет заметить пользователей, для которых справочник не нашёлся. validate="many_to_one" полезен как предохранитель: он упадёт, если справа ключ оказался неуникальным.

order_report = (
    orders[['order_id', 'user_id', 'channel', 'revenue']]
    .merge(
        users[['user_id', 'plan', 'country']],
        on='user_id',
        how='left',
        validate='many_to_one',
        indicator=True,
    )
)

missing_users = (
    order_report['_merge'].eq('left_only').mean()
)

pivot_table: сделать отчёт читаемым

После группировки данные часто удобны для кода, но неудобны для просмотра. pivot_table раскладывает измерение по строкам и колонкам: например, выручку по каналам и планам. Это хороший последний шаг перед графиком или выгрузкой, но не замена проверке исходного зерна.

revenue_matrix = pd.pivot_table(
    order_report,
    index='channel',
    columns='plan',
    values='revenue',
    aggfunc='sum',
    fill_value=0,
)

revenue_matrix = revenue_matrix.sort_values('pro', ascending=False)

pivot_table по умолчанию считает среднее, и дубль в long-таблице исчезает без ошибки

Продолжим отчёт по каналам. Пусть в monthly одна строка — канал за месяц: четыре месяца, четыре канала, шестнадцать строк. Суммы по каналам за весь период те же, что на графике выше: Organic 920, Paid 760, Referral 430, Email 280 тысяч ₽, всего 2390. Это контрольное число — единственная защита при смене формы таблицы. Длинный вид превращается в широкий и обратно без единой ошибки в консоли, поэтому проверять приходится суммой, а не взглядом.

Теперь представьте, что июльская строка Paid пришла дважды: 120 и 85 тысяч, две выгрузки одного месяца, которые должны были сложиться в 205. pivot на таком зерне падает — ValueError: Index contains duplicate entries, cannot reshape. А pivot_table без aggfunc берёт среднее и ставит в ячейку 102,5. Итог по каналам станет 2287,5 вместо 2390, разница — 102,5 тысячи, ровно половина спорной ячейки. Ни предупреждения, ни NaN: отчёт выглядит нормально, просто один месяц одного канала стал вдвое меньше.

Отсюда рабочее правило: пока вы не уверен в зерне, pivot полезнее pivot_table. Его падение — проверка, которую не надо писать. pivot_table с aggfunc="sum" вернёт верную сумму, но дубль останется невидимым и всплывёт в следующем отчёте, где зерно другое. Дешевле проверить зерно отдельной строкой — long.duplicated(["month", "channel"]).any() — и сделать это до изменения формы, а не после.

fill_value=0 — вторая тихая правка. Если Email запустили в мае, пары «апрель × Email» в длинной таблице просто нет, а в широкой на её месте появится ноль. Средняя месячная выручка канала посчитается по четырём месяцам вместо трёх и окажется на четверть ниже. Ноль означает «активности не было», отсутствие строки — «канала ещё не было». Подменять второе первым иногда правильно, но это решение, а не настройка по умолчанию.

melt разворачивает широкую таблицу обратно: id_vars="month", остальные колонки становятся парами variable и value. Круговая проверка — самый дешёвый тест формы: свернул, развернул, сравнил сумму с исходной. Если применялся fill_value, обратно приедет больше строк, чем уехало, и это нормально. Важно, чтобы расхождение объяснялось именно им, а не усреднением дубля: первое даёт лишние строки при той же сумме, второе — ту же сетку при меньшей сумме.

Где держать расчёт, решает не вкус, а то, кто его читает дальше. Длинный формат выдерживает новый канал без правки схемы, фильтруется одним условием и прямо ложится в hue у seaborn. Широкий нужен там, где у таблицы есть живой потребитель: четыре колонки на экране читаются глазами, шестнадцать строк — нет. Если в values передать список из двух метрик, колонки станут двухуровневыми, и следующий шаг пайплайна начнётся с распутывания MultiIndex. Ещё один довод держать широкий вид последним слоем, а не промежуточным.

long = monthly[['month', 'channel', 'revenue']]
assert not long.duplicated(['month', 'channel']).any(), 'зерно не month x channel'

wide = long.pivot(index='month', columns='channel', values='revenue')

long_again = (
    wide.reset_index()
    .melt(id_vars='month', var_name='channel', value_name='revenue')
    .dropna(subset=['revenue'])
)
assert long_again['revenue'].sum() == long['revenue'].sum()

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

Самая дорогая ошибка возникает, когда заказ присоединяют к таблице позиций, а затем суммируют сумму заказа. Один заказ с четырьмя позициями появится четыре раза, и revenue вырастет в четыре раза. Если нужна аналитика товаров, агрегируйте order_items по order_id или используйте сумму позиций, но не смешивайте её с готовой суммой заказа.

• Сохраните контрольные числа: заказы, покупатели, выручка до соединения.
• После merge сравните nunique(order_id) с исходным значением.
• Для one-to-many заранее решите, нужен ли разворот на уровень позиции.
• Не заполняйте пропуски нулём, пока не понял, означает ли пропуск отсутствие факта.

Проверяйте cardinality до merge

У соединения есть кардинальность: one-to-one, many-to-one, one-to-many или many-to-many. В аналитическом отчёте чаще всего ожидается many-to-one: много заказов могут ссылаться на одного пользователя. Если справочник пользователей содержит две строки на user_id, merge тихо удвоит часть заказов. validate превращает такое предположение в проверку.

После соединения сравните не только количество строк, но и уникальные ключи, сумму денег и долю строк без совпадения. indicator=True показывает, какие записи пришли только слева или только справа. Это особенно важно для справочника каналов: неизвестный канал не должен исчезать из отчёта только потому, что в mapping нет ключа.

Если связь many-to-many действительно нужна, сначала объясните, почему. Часто это означает, что выбран неправильный уровень анализа. Для отчёта на уровне заказа сначала агрегируйте позиции, а для отчёта на уровне товара не используйте готовую сумму заказа как будто она относится к каждой позиции.

assert users['user_id'].notna().all()
assert users['user_id'].is_unique

order_report = orders.merge(
    users[['user_id', 'plan']],
    on='user_id',
    how='left',
    validate='many_to_one',
    indicator=True,
)
print(order_report['_merge'].value_counts())

Агрегируйте на том уровне, на котором задаётся вопрос

Одна и та же таблица может дать разные ответы в зависимости от зерна. Средний чек считается на уровне заказа, средняя сумма позиции — на уровне строки товара, а ARPU — на уровне пользователя за период. Если сначала посчитать среднее по строкам, а потом усреднить группы, крупные и маленькие группы получат одинаковый вес. Для корректного общего среднего храните сумму и количество.

Перед groupby запишите строку результата словами: «одна строка — канал за месяц» или «одна строка — пользователь за день». После операции проверьте уникальность этого ключа. Такая привычка делает код ближе к SQL и помогает заметить, что в отчёт случайно попал ещё один dimension.

Сортировка и pivot_table должны быть последним слоем. Не используйте красивую широкую матрицу как источник следующего расчёта, если можно работать с длинной таблицей. Длинный формат проще проверять, соединять и строить из него несколько визуализаций.

Как ловить размножение строк на маленьком примере

Если merge выглядит подозрительно, не отлаживайте сразу на миллионах строк. Возьмите один проблемный order_id, выгрузите все строки слева и справа и покажите, сколько комбинаций образовалось. В маленьком примере сразу видно, что справочник содержит повторный user_id или таблица позиций была присоединена к фактам заказа без агрегации.

Полезно сохранить контрольные числа до соединения: число заказов, уникальных покупателей и revenue. После each merge проверяйте те же показатели. Если цель — добавить атрибут, сумма заказа обычно не должна измениться. Если она изменилась, это не обязательно ошибка, но причина должна быть ясна и записана.

Для сложной цепочки делайте промежуточные имена вроде orders_with_users, order_items_by_order и report_by_channel. Один длинный вызов с четырьмя merge экономит строки, но усложняет ревью и скрывает место, где поменялось зерно.

1000 заказов превращаются в 1280 строк: validate ловит дубль справочника, indicator — потерянные ключи

Разберём то же соединение в числах. До merge в orders 1000 строк, 1000 уникальных order_id и 3 200 000 ₽ выручки при среднем чеке 3 200 ₽. После merge со справочником пользователей — 1280 строк и 4 096 000 ₽. Разложение сходится: 960 заказов нашли пользователя, из них 280 попали на user_id, у которого в справочнике две строки, и удвоились, а 40 не нашли никого. 960 + 280 + 40 = 1280, и выручка выросла ровно на долю удвоенных строк — 28%.

В одном соединении два разных дефекта, и ловят их разные инструменты. Дубль справочника — дело validate="many_to_one": merge упадёт с MergeError: Merge keys are not unique in right dataset и не отдаст ни одной строки. Потерянные сорок — дело indicator=True: в колонке _merge появятся 40 значений left_only. Третий дефект не ловит ни один из них: изменившуюся сумму денег придётся сравнить самому, потому что для pandas 4 096 000 — такой же законный результат, как 3 200 000.

validate стоит читать как запись ожидания о кардинальности, а не как проверку соединения целиком. Он смотрит только на уникальность ключа — слева, справа или с двух сторон — и падает, если реальность не совпала с объявлением. Значение many_to_many не проверяет ничего: это способ сказать в коде «я знаю, что строк станет больше», и он полезнее молчания, потому что следующий читатель не будет гадать, забыли тут проверку или сняли нарочно.

Чего validate не видит вовсе — состояния самого ключа. user_id как int64 слева и как строка справа даёт ValueError ещё до проверки кардинальности, и это удача. Хуже, когда типы совпали, а значения разошлись: "42" и " 42 " — разные ключи, и сорок потерянных заказов могут быть артефактом пробелов в выгрузке, а не отсутствием пользователя. Отдельный сюрприз для пришедших из SQL: pandas соединяет NaN с NaN. Если слева двенадцать заказов без user_id, а справа одна строка с пропущенным ключом, все двенадцать найдут себе «пользователя» и окажутся в both. Выше справочник проверяется на notna — левую таблицу проверьте тем же способом.

Сорок строк — это 4% заказов, но их доля в выручке почти всегда другая. Потерянные ключи редко распределены случайно: обычно это один сломанный источник, один период загрузки или один канал, и средний чек у них свой. Поэтому считайте долю и в строках, и в деньгах, а решение принимай после: how="left" с явным unknown оставляет эти заказы в отчёте, inner убирает их вместе с выручкой, и в итоговой цифре след от них не остаётся.

Повторять четыре проверки после каждого соединения руками никто не будет, поэтому их проще один раз завернуть в функцию. Она печатает строки, уникальные ключи и деньги до и после, раскладывает _merge по значениям и падает, если число строк или сумма изменились без причины. Последняя деталь: перед возвратом удали _merge, иначе второй merge с indicator=True упадёт на конфликте имён колонки — ошибка не опасная, но сбивающая с толку в середине пайплайна.

def merge_checked(left, right, on, money, how='left', validate='many_to_one'):
    before = (len(left), left[on].nunique(), left[money].sum())

    out = left.merge(right, on=on, how=how, validate=validate, indicator=True)
    after = (len(out), out[on].nunique(), out[money].sum())
    print('before:', before, 'after:', after)
    print(out['_merge'].value_counts().to_dict())

    lost = out.loc[out['_merge'].eq('left_only'), money].sum()
    print('left_only:', round(lost / before[2] * 100, 1), '% выручки')

    assert after[0] == before[0], f'строк было {before[0]}, стало {after[0]}'
    assert abs(after[2] - before[2]) < 0.01, 'сумма изменилась после соединения'
    return out.drop(columns='_merge')

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

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

Отдельно посмотрите на пользователей без канала. Не выбрасывай их до того, как оцените долю. Если unknown занимает 12% выручки, это не косметическая проблема группировки, а вопрос качества источника и полноты атрибуции.

В конце сравните отчёт с независимой суммой по исходным заказам. Если общий revenue не совпал, разберите разницу по шагам. Пока контрольное число не объяснено, не переходите к красивой диаграмме и бизнес-выводу.

• Проверить уникальность ключей справочников.
• Определить зерно результата до groupby.
• Агрегировать one-to-many до нужного уровня.
• Сравнить строки, ключи и суммы после каждого merge.
• Показать unknown отдельно от нулевых значений.