Алексей Куличевский Блог · Телеграм · Курс

Когортный анализ в Pandas

Привет! Продолжем изучать Pandas. В прошлый раз я рассказал о возможностях библиотеки , а сегодня покажу, как с её помощью делать когортный анализ.

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

Когорта — это группа людей, которая совершила нужное действие в определенный промежуток времени.

Когортный анализ — это наблюдение за когортами. Выбираем одну или несколько метрик, измеряем их и делаем выводы.


Например, социологи могут отслеживать, сколько людей, родившихся в 1980 году, получили высшее образование. Когорта здесь — те, кто родились в 1980 году. Метрика — доля людей с высшим образованием.

Еще пример: маркетологи хотят узнать, сколько заказов и выручки принесли пользователи, совершившие свой первый заказ год назад. Теперь когорта — это прошлогодние покупатели, а метрики — количество заказов и выручка.

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

Действие Время Метрика
Родились В 1980 году % людей с высшим образованием
Впервые что-то купили Год назад Количество заказов и выручка
Установили приложение Неделю назад % пользователей, открывших приложение еще раз

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

Месяц Клиенты Покупок в 1й месяц Покупок на клиента
Январь 2018 134 161 1.20
Февраль 2018 164 194 1.18
Март 2018 193 200 1.03

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

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

Задача

Допустим, мы работаем в интернет-магазине и хотим понять, сколько заказов и денег клиенты приносят в течение года после первой покупки. Для этого у нас есть данные о заказах:

Каждая строка таблицы orders — это покупка. Мы знаем, когда она произошла, кто её сделал и сколько денег она принесла в магазин. Дата заказа лежит в поле order_date, номер покупателя — в customer_id, а выручка — в sales.

Часто бывает, что даты загружаются в виде текста. Преобразим колонку order_date из текста в дату:

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

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

Чтобы посчитать общую выручку, просуммируем колонку sales:

Количество заказов можно посчитать с помощью этой же колонки, но вместо суммы используем метод count():

Теперь посчитаем обе метрики для каждого пользователя. Сгруппируем датафрейм по полю customer_id:

Видим, например, что пользователь AA-10315 сделал 5 заказов и принес $5563 выручки.

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

Считаем дату первой покупки

Чтобы вычислить дату первой покупки каждого пользователя, сгруппируем данные по customer_id и найдем минимальное значение поля order_date. Результат сохраним в переменную first_orders:

Видим, что пользовать AA-10315 впервые что-то купил 31 марта 2014 года, а пользователь AA-10375 — 21 апреля того же года.

Зная даты первых покупок, можем строить когорты.

Строим когорты

Итак, когортами будем считать людей, сделавших первую покупку в тот или иной день. Метрики для отслеживания — количество заказов и выручка.

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

Приступим. Добавим дату первой покупки с помощью метода merge() и сохраним получившийся датафрейм в переменную orders_merged:

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

Агрегируем по дате первой покупки и посчитаем нужные показатели:

Видим, что клиенты от 3 января 2014 года, всего сделали 9 заказов на $1050.6. Посмотрим, когда были эти заказы. Для этого добавим к группировке колонку order_date:

Ага, первый заказ этой когорты был 3 января на $16. В следующий раз клиент вернулся почти год спустя и купил что-то ещё, в этот раз на $153. Следующая покупка была уже в апреле 2015 и так далее.

Когорты готовы, теперь решим задачу.

Решаем задачу

Напомню, что мы хотим посчитать, сколько в среднем заказов и выручки приносят клиенты в течение года, после первой покупки.

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

Сначала узнаем, сколько дней прошло между первой покупкой и последующим заказом, и удалим те, которые случились позже 365 дней. Чтобы посчитать количество дней между заказами, вычтем из колонки order_date столбец first_order:

Вуаля. Видим, что, например, заказ 131884 случился 455 дней спустя первой покупки. 455 days — это тип данных под названием «Timedelta», его специально придумали, чтобы показывать временные промежутки.

Чтобы удалить поздние заказы, добавим условие <= '365 days':

Сохраним результат в переменную year_1_filter, отфильтруем ненужные заказы из когортного отчета и сохраним результат в переменную year_1_orders:

В датафрейме остались только заказы, сделанные когортами в первый год после первой покупки. Теперь сгруппируем заказы по дате первой покупки и посчитаем нужные метрики. Результат сохраним в переменную cohorts:

Последний шаг: посчитаем, сколько в среднем заказов и приносят клиенты в течение первого года. Для этого сначала просуммируем показатели каждой когорты, а затем усредним значения методом mean():

Готово! В среднем за первый год когорты делают по 4 заказа и приносят по $1949 долларов.

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

Обо всем этом в следующих сериях. Подписывайтесь на канал, чтобы не пропустить. Ну и да, приходите учиться на курс!

Адиос!

Cпасибо Наташе Асаул за то, что помогла отредактировать статью.

Аналитикам: большая шпаргалка по Pandas

Привет. Я задумывал эту заметку для студентов курса Digital Rockstar, на котором мы учим маркетологов автоматизировать свою работу с помощью программирования, но решил поделиться шпаргалкой по Pandas со всеми. Я ожидаю, что читатель умеет писать код на Python хотя бы на минимальном уровне, знает, что такое списки, словари, циклы и функции.

  1. Что такое Pandas и зачем он нужен
  2. Структуры данных: серии и датафреймы
  3. Создаем датафреймы и загружаем в них данные
  4. Исследуем загруженные данные
  5. Получаем данные из датафреймов
  6. Считаем производные метрики
  7. Объединяем несколько датафреймов
  8. Решаем задачу

Что такое Pandas и зачем он нужен

Pandas — это библиотека для работы с данными на Python. Она упрощает жизнь аналитикам: где раньше использовалось 10 строк кода теперь хватит одной.

Например, чтобы прочитать данные из csv, в стандартном Python надо сначала решить, как хранить данные, затем открыть файл, прочитать его построчно, отделить значения друг от друга и очистить данные от специальных символов.

> with open('file.csv') as f:
...    content = f.readlines()
...    content = [x.split(',').replace('\n','') for x in content]

В Pandas всё проще. Во-первых, не нужно думать, как будут храниться данные — они лежат в датафрейме. Во-вторых, достаточно написать одну команду:

> data = pd.read_csv('file.csv')

Pandas добавляет в Python новые структуры данных — серии и датафреймы. Расскажу, что это такое.

Структуры данных: серии и датафреймы

Серии — одномерные массивы данных. Они очень похожи на списки, но отличаются по поведению — например, операции применяются к списку целиком, а в сериях — поэлементно.

То есть, если список умножить на 2, получите тот же список, повторенный 2 раза.

> vector = [1, 2, 3]
> vector * 2
[1, 2, 3, 1, 2, 3]

А если умножить серию, ее длина не изменится, а вот элементы удвоятся.

> import pandas as pd
> series = pd.Series([1, 2, 3])
> series * 2
0    2
1    4
2    6
dtype: int64

Обратите внимание на первый столбик вывода. Это индекс, в котором хранятся адреса каждого элемента серии. Каждый элемент потом можно получать, обратившись по нужному адресу.

> series = pd.Series(['foo', 'bar'])
> series[0]
'foo'

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

> months = ['jan', 'feb', 'mar', 'apr']
> sales = [100, 200, 300, 400]
> data = pd.Series(data=sales, index=months)
> data
jan    100
feb    200
mar    300
apr    400
dtype: int64

Теперь можем получать значения каждого месяца:

> data['feb']
200

Так как серии — одномерный массив данных, в них удобно хранить измерения по одному. На практике удобнее группировать данные вместе. Например, если мы анализируем помесячные продажи, полезно видеть не только выручку, но и количество проданных товаров, количество новых клиентов и средний чек. Для этого отлично подходят датафреймы.

Датафреймы — это таблицы. У их есть строки, колонки и ячейки.

Технически, колонки датафреймов — это серии. Поскольку в колонках обычно описывают одни и те же объекты, то все колонки делят один и тот же индекс:

> months = ['jan', 'feb', 'mar', 'apr']
> sales = {
...    'revenue':     [100, 200, 300, 400],
...    'items_sold':  [23, 43, 55, 65],
...    'new_clients': [10, 20, 30, 40]
...}
> sales_df = pd.DataFrame(data=sales, index=months)
> sales_df
     revenue  items_sold  new_clients
jan      100          23           10
feb      200          43           20
mar      300          55           30
apr      400          65           40

Объясню, как создавать датафреймы и загружать в них данные.

Создаем датафреймы и загружаем данные

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

> df = pd.DataFrame()

А иногда данные уже есть, но хранятся в переменной из стандартного Python, например, в словаре. Чтобы получить датафрейм, эту переменную передаем в ту же команду:

> df = pd.DataFrame(data=sales, index=months))

Случается, что в некоторых записях не хватает данных. Например, посмотрите на список goods_sold — в нём продажи, разбитые по товарным категориям. За первый месяц мы продали машины, компьютеры и программное обеспечение. Во втором машин нет, зато появились велосипеды, а в третьем снова появились машины, но велосипеды исчезли:

> goods_sold = [
...     {'computers': 10, 'cars': 1, 'soft': 3},
...     {'computers': 4, 'soft': 5, 'bicycles': 1},
...     {'computers': 6, 'cars': 2, 'soft': 3}
... ]

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

> pd.DataFrame(goods_sold)
   bicycles  cars  computers  soft
0       NaN   1.0         10     3
1       1.0   NaN          4     5
2       NaN   2.0          6     3

Обратите внимание, продажи велосипедов в первом и третьем месяце равны NaN — расшифровывается как Not a Number. Так Pandas помечает отсутствующие значения.

Теперь разберем, как загружать данные из файлов. Чаще всего данные хранятся в экселевских таблицах или csv-, tsv- файлах.

Экселевские таблицы читаются с помощью команды pd.read_excel(). Параметрами нужно передать адрес файла на компьютере и название листа, который нужно прочитать. Команда работает как с xls, так и с xlsx:

> pd.read_excel('file.xlsx', sheet_name='Sheet1')

Файлы формата csv и tsv — это текстовые файлы, в которых данные отделены друг от друга запятыми или табуляцией:

# CSV
month,customers,sales
feb,10,200

# TSV
month\tcustomers\tsales
feb\t10\t200

Оба читаются с помощью команды .read_csv(), символ табуляции передается параметром sep (от англ. separator — разделитель):

> pd.read_csv('file.csv')
> pd.read_csv('file.tsv', sep='\t')

При загрузке можно назначить столбец, который будет индексом. Представьте, что мы загружаем таблицу с заказами. У каждого заказа есть свой уникальный номер, Если назначим этот номер индексом, сможем выгружать данные командой df[order_id]. Иначе придется писать фильтр df[df[‘id’] == order_id ].

О том, как получать данные из датафреймов, я расскажу в одном из следующих разделов. Чтобы назначить колонку индексом, добавим в команду read_csv() параметр index_col, равный названию нужной колонки:

> pd.read_csv('file.csv', index_col='id')

После загрузки данных в датафрейм, хорошо бы их исследовать — особенно, если они вам незнакомы.

Исследуем загруженные данные

Представим, что мы анализируем продажи американского интернет-магазина. У нас есть данные о заказах и клиентах. Загрузим файл с продажами интернет-магазина в переменную orders. Раз загружаем заказы, укажем, что колонка id пойдет в индекс:

> orders = pd.read_csv('orders.csv', index_col='id')

Расскажу о четырех атрибутах, которые есть у любого датафрейма: .shape, .columns, .index и .dtypes.

.shape показывает, сколько в датафрейме строк и колонок. Он возвращает пару значений (n_rows, n_columns). Сначала идут строки, потом колонки.

> orders.shape
(5009, 5)

В датафрейме 5009 строк и 5 колонок.

Окей, масштаб оценили. Теперь посмотрим, какая информация содержится в каждой колонке. С помощью .columns узнаем названия колонок:

> orders.columns
Index(['order_date', 'ship_mode', 'customer_id', 'sales'], dtype='object')

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

С помощью .dtypes узнаем типы данных, находящихся в каждой колонке и поймем, надо ли их обрабатывать. Бывает, что числа загружаются в виде текста. Если мы попробуем сложить две текстовых значения '1' + '1', то получим не число 2, а строку '11':

> orders.dtypes
order_date      object
ship_mode       object
customer_id     object
sales          float64
dtype: object

Тип object — это текст, float64 — это дробное число типа 3,14.

C помощью атрибута .index посмотрим, как называются строки:

> orders.index
Int64Index([100006, 100090, 100293, 100328, 100363, 100391, 100678, 100706,
            100762, 100860,
            ...
            167570, 167920, 168116, 168613, 168690, 168802, 169320, 169488,
            169502, 169551],
           dtype='int64', name='id', length=5009)

Ожидаемо, в индексе датафрейма номера заказов: 100762, 100860 и так далее.

В колонке sales хранится стоимость каждого проданного товара. Чтобы узнать разброс значений, среднюю стоимость и медиану, используем метод .describe():

> orders.describe()
         sales
count   5009.0
mean     458.6
std      954.7
min        0.6
25%       37.6
50%      152.0
75%      512.1
max    23661.2

Наконец, чтобы посмотреть на несколько примеров записей датафрейма, используем команды .head() и .sample(). Первая возвращает 6 записей из начала датафрейма. Вторая — 6 случайных записей:

> orders.head()
        order_date ship_mode customer_id    sales
id                                                                         
100006  2014-09-07  Standard    DK-13375  377.970
100090  2014-07-08  Standard    EB-13705  699.192
100293  2014-03-14  Standard    NF-18475   91.056
100328  2014-01-28  Standard    JC-15340    3.928
100363  2014-04-08  Standard    JM-15655   21.376

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

Получаем данные из датафреймов

Данные из датафреймов можно получать по-разному: указав номера колонок и строк, использовав условные операторы или язык запросов. Расскажу подробнее о каждом способе.

Указываем нужные строки и колонки

Продолжаем анализировать продажи интернет-магазина, которые загрузили в предыдущем разделе. Допустим, я хочу вывести столбец sales. Для этого название столбца нужно заключить в квадратные скобки и поставить после них названия датафрейма: orders['sales']:

> orders['sales']
id
100006     377.970
100090     699.192
100293      91.056
100328       3.928
100363      21.376
100391      14.620
100678     697.074
100706     129.440
...

Обратите внимание, результат команды — новый датафрейм с таким же индексом.

Если нужно вывести несколько столбцов, в квадратные скобки нужно вставить список с их названиями: orders[['customer_id', 'sales']]. Будьте внимательны: квадратные скобки стали двойными. Первые — от датафрейма, вторые — от списка:

> orders[['customer_id', 'sales']]
       customer_id     sales
id                                  
100006    DK-13375   377.970
100090    EB-13705   699.192
100293    NF-18475    91.056
100328    JC-15340     3.928
100363    JM-15655    21.376
100391    BW-11065    14.620
100363    KM-16720   697.074
100706    LE-16810   129.440
...

Перейдем к строкам. Их можно фильтровать по индексу и по порядку. Например, мы хотим вывести только заказы 100363, 100391 и 100706, для этого есть команда .loc[]:

> show_these_orders = ['100363', '100363', '100706']
> orders.loc[show_these_orders]
        order_date ship_mode customer_id    sales
id                                                             
100363  2014-04-08  Standard    JM-15655   21.376
100363  2014-04-08  Standard    JM-15655   21.376
100706  2014-12-16    Second    LE-16810  129.440

А в другой раз бывает нужно достать просто заказы с 1 по 3 по порядку, вне зависимости от их номеров в таблицемы. Тогда используют команду .iloc[]:

> show_these_orders = [1, 2, 3]
> orders.iloc[show_these_orders]
        order_date ship_mode customer_id    sales
id                                                             
100090  2014-04-08  Standard    JM-15655   21.376
100293  2014-04-08  Standard    JM-15655   21.376
100328  2014-12-16    Second    LE-16810  129.440

Можно фильтровать датафреймы по колонкам и столбцам одновременно:

> columns = ['customer_id', 'sales']
> rows = ['100363', '100363', '100706']
> orders.loc[rows][columns]
       customer_id    sales
id                                 
100363    JM-15655   21.376
100363    JM-15655   21.376
100706    LE-16810  129.440
...

Часто вы не знаете заранее номеров заказов, которые вам нужны. Например, если задача — получить заказы, стоимостью более 1000 рублей. Эту задачу удобно решать с помощью условных операторов.

Если — то. Условные операторы

Задача: нужно узнать, откуда приходят самые большие заказы. Начнем с того, что достанем все покупки стоимостью более 1000 долларов:

> filter_large = orders['sales'] > 1000
> orders.loc[filter_slarge]
        order_date ship_mode customer_id     sales
id                                                             
101931  2014-10-28     First    TS-21370  1252.602
102673  2014-11-01  Standard    KH-16630  1044.440
102988  2014-04-05    Second    GM-14695  4251.920
103100  2014-12-20     First    AB-10105  1107.660
103310  2014-05-10  Standard    GM-14680  1769.784
...

Помните, в начале статьи я упоминал, что в сериях все операции применяются по-элементно? Так вот, операция orders['sales'] > 1000 идет по каждому элементу серии и, если условие выполняется, возвращает True. Если не выполняется — False. Получившуюся серию мы сохраняем в переменную filter_large.

Вторая команда фильтрует строки датафрейма с помощью серии. Если элемент filter_large равен True, заказ отобразится, если False — нет. Результат — датафрейм с заказами, стоимостью более 1000 долларов.

Интересно, сколько дорогих заказов было доставлено первым классом? Добавим в фильтр ещё одно условие:

> filter_large = df['sales'] > 1000
> filter_first_class = orders['ship_mode'] == 'First'
> orders.loc[filter_large & filter_first_class]
        order_date ship_mode customer_id     sales
id                                                           
101931  2014-10-28     First    TS-21370  1252.602
103100  2014-12-20     First    AB-10105  1107.660
106726  2014-12-06     First    RS-19765  1261.330
112158  2014-12-02     First    DP-13165  1050.600
116666  2014-05-08     First    KT-16480  1799.970
...

Логика не изменилась. В переменную filter_large сохранили серию, удовлетворяющую условию orders['sales'] > 1000. В filter_first_class — серию, удовлетворяющую orders['ship_mode'] == 'First'.

Затем объединили обе серии с помощью логического ‘И’: filter_first_class & filter_first_class. Получили новую серию той же длины, в элементах которой True только у заказов, стоимостью больше 1000, доставленных первым классом. Таких условий может быть сколько угодно.

Язык запросов

Еще один способ решить предыдущую задачу — использовать язык запросов. Все условия пишем одной строкой 'sales > 1000 & ship_mode == 'First' и передаем ее в метод .query(). Запрос получается компактнее.

> orders.query('sales > 1000 & ship_mode == First')
        order_date ship_mode customer_id     sales
id                                                           
101931  2014-10-28     First    TS-21370  1252.602
103100  2014-12-20     First    AB-10105  1107.660
106726  2014-12-06     First    RS-19765  1261.330
112158  2014-12-02     First    DP-13165  1050.600
116666  2014-05-08     First    KT-16480  1799.970
...

Отдельный кайф: значения для фильтров можно сохранить в переменной, а в запросе сослаться на нее с помощью символа @: sales > @sales_filter.

> sales_filter = 1000
> ship_mode_filter = 'First'
> orders.query('sales > @sales_filter & ship_mode > @ship_mode_filter')
         order_date ship_mode customer_id     sales
id                                                           
101931  2014-10-28     First    TS-21370  1252.602
103100  2014-12-20     First    AB-10105  1107.660
106726  2014-12-06     First    RS-19765  1261.330
112158  2014-12-02     First    DP-13165  1050.600
116666  2014-05-08     First    KT-16480  1799.970
...

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

Считаем производные метрики

Задача: посчитаем, сколько денег магазин заработал с помощью каждого класса доставки. Начнем с простого — просуммируем выручку со всех заказов. Для этого используем метод .sum():

> orders['sales'].sum()
2297200.8603000003

Добавим класс доставки. Перед суммированием сгруппируем данные с помощью метода .groupby():

> orders.groupby('ship_mode')['sales'].sum()
ship_mode              
First      3.514284e+05
Same Day   1.283631e+05
Second     4.591936e+05
Standard   1.358216e+06

3.514284e+05 — научный формат вывода чисел. Означает 3.51 * 105. Нам такая точность не нужна, поэтому можем сказать Pandas, чтобы округлял значения до сотых:

> pd.options.display.float_format = '{:,.1f}'.format
> orders.groupby('ship_mode')['sales'].sum()
ship_mode            
First       351,428.4
Same Day    128,363.1
Second      459,193.6
Standard  1,358,215.7

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

> orders.groupby(['ship_mode', 'order_date'])['sales'].sum()
ship_mode order_date        
First     2014-01-06    12.8
          2014-01-11     9.9
          2014-01-14    62.0
          2014-01-15   149.9
          2014-01-19   378.6
          2014-01-26   152.6
...

Видно, что выручка прыгает ото дня ко дню: иногда 10 долларов, а иногда 378. Интересно, это меняется количество заказов или средний чек? Добавим к выборке количество заказов. Для этого вместо .sum() используем метод .agg(), в который передадим список с названиями нужных функций.

> orders.groupby(['ship_mode', 'order_date'])['sales'].agg(['sum', 'count'])
                       sum  count
ship_mode order_date             
First     2014-01-06  12.8      1
          2014-01-11   9.9      1
          2014-01-14  62.0      1
          2014-01-15 149.9      1
          2014-01-19 378.6      1
          2014-01-26 152.6      1
...

Ого, получается, что это так прыгает средний чек. Интересно, а какой был самый удачный день? Чтобы узнать, отсортируем получившийся датафрейм: выведем 10 самых денежных дней по выручке:

> orders.groupby(['ship_mode', 'order_date'])['sales'].agg(['sum']).sort_values(by='sum', ascending=False).head(10)
                          sum
ship_mode order_date         
Standard  2014-03-18 26,908.4
          2016-10-02 18,398.2
First     2017-03-23 14,299.1
Standard  2014-09-08 14,060.4
First     2017-10-22 13,716.5
Standard  2016-12-17 12,185.1
          2017-11-17 12,112.5
          2015-09-17 11,467.6
          2016-05-23 10,561.0
          2014-09-23 10,478.6

Команда разрослась, и её теперь неудобно читать. Чтобы упростить, можно разбить её на несколько строк. В конце каждой строки ставим обратный слеш \:

> orders \
... .groupby(['ship_mode', 'order_date'])['sales'] \
... .agg(['sum']) \
... .sort_values(by='sum', ascending=False) \
... .head(10)
                          sum
ship_mode order_date         
Standard  2014-03-18 26,908.4
          2016-10-02 18,398.2
First     2017-03-23 14,299.1
Standard  2014-09-08 14,060.4
First     2017-10-22 13,716.5
Standard  2016-12-17 12,185.1
          2017-11-17 12,112.5
          2015-09-17 11,467.6
          2016-05-23 10,561.0
          2014-09-23 10,478.6

В самый удачный день — 18 марта 2014 года — магазин заработал 27 тысяч долларов с помощью стандартного класса доставки. Интересно, откуда были клиенты, сделавшие эти заказы? Чтобы узнать, надо объединить данные о заказах с данными о клиентах.

Объединяем несколько датафреймов

До сих пор мы смотрели только на таблицу с заказами. Но ведь у нас есть еще данные о клиентах интернет-магазина. Загрузим их в переменную customers и посмотрим, что они собой представляют:

> customers = pd.read_csv('customers.csv', index='id')
> customers.head()
                     name    segment           state             city
id                                                                   
CG-12520      Claire Gute   Consumer        Kentucky        Henderson
DV-13045  Darrin Van Huff  Corporate      California      Los Angeles
SO-20335   Sean O'Donnell   Consumer         Florida  Fort Lauderdale
BH-11710  Brosina Hoffman   Consumer      California      Los Angeles
AA-10480     Andrew Allen   Consumer  North Carolina          Concord

Мы знаем тип клиента, место его проживания, его имя и имя контактного лица. У каждого клиента есть уникальный номер id. Этот же номер лежит в колонке customer_id таблицы orders. Значит мы можем найти, какие заказы сделал каждый клиент. Например, посмотрим, заказы пользователя CG-12520:

> cust_filter = 'CG-12520'
> orders.query('customer_id == @cust_filter')
                order_date ship_mode customer_id   sales
id                                                          
CA-2016-152156  2016-11-08    Second    CG-12520  993.90
CA-2017-164098  2017-01-26     First    CG-12520   18.16
US-2015-123918  2015-10-15  Same Day    CG-12520  136.72

Вернемся к задаче из предыдущего раздела: узнать, что за клиенты, которые сделали 18 марта заказы со стандартной доставкой. Для этого объединим таблицы с клиентами и заказами. Датафреймы объединяют с помощью методов .concat(), .merge() и .join(). Все они делают одно и то же, но отличаются синтаксисом — на практике достаточно уметь пользоваться одним из них.

Покажу на примере .merge():

> new_df = pd.merge(orders, customers, how='inner', left_on='customer_id', right_index=True)
> new_df.columns
Index(['order_date', 'ship_mode', 'customer_id', 'sales', 'name', 'segment',
       'state', 'city'],
      dtype='object')

В .merge() я сначала указал названия датафреймов, которые хочу объединить. Затем уточнил, как именно их объединить и какие колонки использовать в качестве ключа.

Ключ — это колонка, связывающая оба датафрейма. В нашем случае — номер клиента. В таблице с заказами он в колонке customer_id, а таблице с клиентами — в индексе. Поэтому в команде мы пишем: left_on='customer_id', right_index=True.

Решаем задачу

Закрепим полученный материал, решив задачу. Найдем 5 городов, принесших самую большую выручку в 2016 году.

Для начала отфильтруем заказы из 2016 года:

> orders_2016 = orders.query("order_date >= '2016-01-01' & order_date <= '2016-12-31'")
> orders_2016.head()
       order_date ship_mode customer_id   sales
id                                             
100041 2016-11-20  Standard    BF-10975   328.5
100083 2016-11-24  Standard    CD-11980    24.8
100153 2016-12-13  Standard    KH-16630    63.9
100244 2016-09-20  Standard    GM-14695   475.7
100300 2016-06-24    Second    MJ-17740 4,823.1

Город — это атрибут пользователей, а не заказов. Добавим информацию о пользователях:

> with_customers_2016 = pd.merge(customers, orders_2016, how='inner', left_index=True, right_on='customer_id')

Cруппируем получившийся датафрейм по городам и посчитаем выручку:

> grouped_2016 = with_customers_2016.groupby('city')['sales'].sum()
> grouped_2016.head()
city
Akron               1,763.0
Albuquerque           692.9
Amarillo              197.2
Arlington           5,672.1
Arlington Heights      14.1
Name: sales, dtype: float64

Отсортируем по убыванию продаж и оставим топ-5:

> top5 = grouped_2016.sort_values(ascending=False).head(5)
> print(top5)
city
New York City   53,094.1
Philadelphia    39,895.5
Seattle         33,955.5
Los Angeles     33,611.1
San Francisco   27,990.0
Name: sales, dtype: float64

Готово!

Попробуйте сами:

Возьмите данные о заказах и покупателях и посчитайте:

  1. Сколько заказов, отправлено первым классом за последние 5 лет?
  2. Сколько в базе клиентов из Калифорнии?
  3. Сколько заказов они сделали?
  4. Постройте сводную таблицу средних чеков по всем штатам за каждый год.

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

До скорого!

Кстати, большое спасибо Александру Марфицину за то, что помог отредактировать статью.

Упрощаем вложенные SQL-запросы с помощью представлений

Заметка для читателей онлайн-курса по аналитике, которые прошли хотя бы 5 уроков, уже знают, что такое SQL и умеют писать запросы к базе. Если SQL для вас в новинку, почитайте вводный урок про базы данных.

Результат любого SQL-запроса — это таблица. Значит, к ней можно направлять новые запросы, её можно джоинить с другими таблицами и использовать в фильтрах. Это позволяет реализовывать сложную логику.

Например, в седьмом уроке я рассказываю, как с помощью SQL достать данные для когортного анализа.

Начинаем с безобидного «давайте подсчитаем, сколько людей в каждой когорте»:

SELECT
  reg_month AS cohort_id,
  count(*)  AS cohort_size
FROM
  users
GROUP BY 1

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

SELECT
  users.reg_month AS cohort_id,
  orders.month AS month,
  sum(orders.sum) AS cohort_revenue
FROM
  users INNER JOIN orders
  ON users.uid = orders.uid
GROUP BY 1, 2

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

SELECT
  rev.cohort_id AS cohort_id,
  rev.month AS month,
  rev.month - rev.cohort_id AS cohort_age,
  rev.cohort_revenue  / size.cohort_size AS LTV,
  rev.cohort_revenue / size.costs * 100 AS ROI
FROM
(
  SELECT
    reg_month AS cohort_id,
    count(*) AS cohort_size
  FROM
    users
  GROUP BY 1
  ) AS size
  INNER JOIN
(
  SELECT
    users.reg_month AS cohort_id,
    orders.month AS month,
    sum(orders.sum) AS cohort_revenue
  FROM
    users INNER JOIN orders
    ON users.uid = orders.uid
    GROUP BY 1, 2
) AS rev
ON size.cohort_id = rev.cohort_id

Запрос правильный, но слишком громоздкий. Исправлять ошибки и вносить в него изменения — особый тип пытки.

Код можно упростить, если сохранить промежуточные результаты в виде представлений.

Что такое представления и как они работают

Представления (ещё их называют «вью» от английского view) — это сохранённые запросы.

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

Например, мы сохранили запрос SELECT * FROM some_table в представление my_view. Теперь my_view можно использовать в запросах:

SELECT …
FROM my_view
WHERE …

Отправляем запрос — база находит и подставляет сохранённый код:

SELECT …
FROM (SELECT * FROM some_table)
WHERE …

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

Как создать представления

В Бигквери нужно сначала выполнить запрос, затем нажать на кнопку Save View и выбрать название.

В других системах кнопки нет, но зато есть специальный запрос CREATE VIEW.

CREATE VIEW my_view AS
  SELECT * FROM some_table

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

Сохраню первый запрос в представлении cohort_sizes:

Теперь могу просто запрашивать SELECT * FROM cohort_size, добавлять фильтры, группировки и сортировки.

Повторю процесс для второго запроса: отправлю, получу результат, сохраню представление cohorts.

Теперь заменю вложенные селекты в большом запросе на созданные представления. Смотрите, насколько аккуратней стал запрос:

Было:

SELECT
  rev.cohort_id AS cohort_id,
  rev.month AS month,
  rev.month - rev.cohort_id AS cohort_age,
  rev.cohort_revenue  / size.cohort_size AS LTV,
  rev.cohort_revenue / size.costs * 100 AS ROI
FROM
(
SELECT
  reg_month AS cohort_id,
  count(*) AS cohort_size
FROM
  users
GROUP BY 1
) AS size
INNER JOIN
(
SELECT
  users.reg_month AS cohort_id,
  orders.month AS month,
  sum(orders.sum) AS cohort_revenue
FROM
  users INNER JOIN orders
  ON users.uid = orders.uid
  GROUP BY 1, 2
) AS rev
ON size.cohort_id = rev.cohort_id

Стало:

SELECT
  rev.cohort_id AS cohort_id,
  rev.month AS month,
  rev.month - rev.cohort_id AS cohort_age,
  rev.cohort_revenue  / size.cohort_size AS LTV,
  rev.cohort_revenue / size.costs * 100 AS ROI
FROM
  cohort_sizes AS size
INNER JOIN
  cohorts AS rev
ON size.cohort_id = rev.cohort_id

На этом всё. Успехов.