pandas против SQL: groupby, merge и pivot_table

Бран · продюсер «Хроник Пограничья»

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

В прошлом уроке мы загрузили выгрузку покупок в pandas, отфильтровали и отсортировали её. Сегодня переводим на pandas три главные вещи из SQL: группировку, соединение и сводную таблицу. Числа будем сверять с теми, что уже считали запросами: если pandas и SQL сходятся, мы поняли оба.

groupby это GROUP BY

В уроке про группировку выручка по платформам считалась так:

SELECT platform, count(*) AS purchases, sum(amount_rub) AS revenue
FROM purchases
GROUP BY platform;

В pandas то же самое пишется в два шага: разложить строки по стопкам и посчитать в каждой.

df.groupby("platform", as_index=False).agg(
    purchases=("id", "count"),
    revenue=("amount_rub", "sum"),
)

  platform  purchases   revenue
0  android        586  317275.0
1      ios        388  227221.0
2       pc        276  181937.0

Цифры те же, что в SQL-уроке: 317 275 + 227 221 + 181 937 = 726 433.

Разберём, что тут откуда. groupby("platform") это GROUP BY platform. В agg каждая пара имя=("столбец", "функция") работает как функция(столбец) AS имя. as_index=False оставляет платформу обычным столбцом, иначе pandas увёл бы её в индекс, подпись строк слева. Функции называются почти как в SQL: count, sum, mean вместо avg, min, max и nunique вместо count(DISTINCT …):

df.groupby("platform", as_index=False).agg(payers=("player_id", "nunique"))

Платящих 232 на Android, 183 на iOS и 99 на PC.

merge это JOIN

У покупки есть только offer_id, а тип оффера лежит в файле маркетинга store_offers.csv. В SQL мы бы написали JOIN store_offers o ON o.id = p.offer_id. В pandas соединение называется merge:

offers = pd.read_csv("store_offers.csv")
paid = df[df["status"] == "paid"]
merged = paid.merge(offers, left_on="offer_id", right_on="id", how="left")

how задаёт тип соединения: "inner" (по умолчанию), "left", "right" и "outer", как INNER, LEFT, RIGHT и FULL JOIN из урока про LEFT JOIN. Столбец id есть в обеих таблицах, поэтому pandas переименует их в id_x и id_y. Чтобы не гадать, где чей, ключ лучше переименовать заранее и соединять по одному имени:

offers = offers.rename(columns={"id": "offer_id"})
merged = paid.merge(offers[["offer_id", "kind"]], on="offer_id", how="left")
merged.groupby("kind", as_index=False).agg(revenue=("amount_rub", "sum"))

           kind   revenue
0       booster   11542.0
1        bundle   77047.0
2      cosmetic   65400.0
3          gems  483126.0
4          pass   45327.0
5       service    4480.0
6  subscription   18148.0

groupby раскладывает группы по алфавиту, а sort_values("revenue", ascending=False) поставит сверху самые денежные. Гемы дают 483 126 ₽ из 705 070 ₽ оплаченной выручки, почти 69%. Облики покупают редко, 50 раз, но они дорогие и по выручке стоят третьими.