Ключи и ловушки

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

import pandas as pd

orders = pd.read_csv("data/orders.csv")
products = pd.read_csv("data/products.csv")
customers = pd.read_csv("data/customers.csv")
print(list(products.columns))
print(list(customers.columns))

['product_id', 'name', 'category', 'price', 'cost']
['customer_id', 'name', 'city', 'signup_date', 'segment']

Ключ называется по-разному

Если ключевой столбец в таблицах назван по-разному, вместо on указывают два параметра: left_on — название в левой таблице, right_on — в правой.

catalog = products.rename(columns={"product_id": "id"})
joined = orders.merge(catalog, left_on="product_id", right_on="id")
list(joined.columns)

['order_id', 'date', 'customer_id', 'product_id', 'quantity', 'channel', 'id', 'name', 'category', 'price', 'cost']

В результате остались оба ключевых столбца — product_id и id — с одинаковым содержимым. Лишний обычно удаляют: .drop(columns=["id"]).

Одноимённые столбцы

В справочниках товаров и клиентов есть столбец name: название товара и имя покупателя. Если соединить всё подряд, pandas различит их окончаниями _x и _y:

both = orders.merge(products, on="product_id").merge(customers, on="customer_id")
list(both.columns)

['order_id', 'date', 'customer_id', 'product_id', 'quantity', 'channel', 'name_x', 'category', 'price', 'cost', 'name_y', 'city', 'signup_date', 'segment']

name_x пришёл из левой таблицы, name_y — из правой. Понять это через месяц будет невозможно. Свои окончания задаёт параметр suffixes — пара строк для левой и правой таблицы:

both = orders.merge(products, on="product_id").merge(customers, on="customer_id", suffixes=("_product", "_customer"))
both[["order_id", "name_product", "name_customer", "city"]].head(3)

Окончания добавляются только к столбцам, названия которых совпали. Второй способ избежать путаницы — заранее взять из правой таблицы только нужные столбцы или переименовать их через rename.

supplier = pd.read_csv("data/supplier_prices.csv")
compare = ...
# переведите cost_new в числа и добавьте столбец cost_change

supplier = pd.read_csv("data/supplier_prices.csv")
compare = products.merge(supplier[["product_id", "cost", "stock"]], on="product_id", suffixes=("_now", "_new"))
compare["cost_new"] = pd.to_numeric(compare["cost_new"], errors="coerce")
compare["cost_change"] = compare["cost_new"] - compare["cost_now"]

Неверный ключ

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

returns = pd.read_csv("data/returns_raw.csv").drop_duplicates()
returns = returns.drop_duplicates(subset=["order_id", "product_id"])[["order_id", "product_id", "quantity"]]
print(len(returns))
returns.head(3)

150

Хотим добавить к строкам заказов число возвращённых штук. В обеих таблицах есть столбец quantity — зададим окончания так, чтобы левый остался как есть, а правый стал quantity_returned: пустая строка в suffixes значит «название не менять». Попробуем соединить по номеру заказа:

wrong = orders.merge(returns[["order_id", "quantity"]], on="order_id", how="left", suffixes=("", "_returned"))
print(len(orders), len(wrong))
print(wrong["quantity_returned"].sum(), returns["quantity"].sum())

2448 2458
402.0 214

Строк стало 2458 вместо 2448, а возвращённых штук — 402 вместо 214. Почти вдвое больше, чем на самом деле. Здесь сразу две ошибки, и у них одна причина: ключ order_id не определяет строку однозначно.

Первая: значение приклеилось не к той строке. Посмотрим на заказ 10001:

wrong.loc[wrong["order_id"] == 10001, ["order_id", "product_id", "quantity", "quantity_returned"]]

В заказе две позиции — кофемолка (P18) и травяной сбор (P09). Вернули только кофемолку, 2 штуки. Но соединение шло по номеру заказа, и возврат «приклеился» к обеим позициям: получилось, что вернули и сбор тоже. Число строк при этом не изменилось — такую ошибку по len не заметить. Её выдаёт только сверка сумм: 402 против 214.

Вторая: строки размножились. В заказе 10119 вернули оба товара — в журнале две записи с этим номером:

print(returns[returns["order_id"] == 10119])
wrong.loc[wrong["order_id"] == 10119, ["order_id", "product_id", "quantity", "quantity_returned"]]

    order_id product_id  quantity
12     10119        P07         1
15     10119        P05         1

merge соединяет каждую строку слева с каждой подходящей строкой справа. Две позиции заказа и две записи о возврате дали 2 × 2 = 4 строки. Если ключ повторяется с обеих сторон, строки перемножаются.

a = pd.DataFrame({"k": ["x", "x"], "a": [1, 2]})
b = pd.DataFrame({"k": ["x", "x"], "b": [3, 4]})
print(len(a.merge(b, on="k")))

Соединение по нескольким ключам

Возврат относится не к заказу, а к позиции — паре «заказ, товар». Значит, и ключ составной: в on передают список столбцов, и строки сопоставляются, только если совпали оба.

lines = ...
# замените пропуски в lines["quantity_returned"] нулями, тип — целый
return_rate = ...

lines = orders.merge(returns, on=["order_id", "product_id"], how="left", suffixes=("", "_returned"))
lines["quantity_returned"] = lines["quantity_returned"].fillna(0).astype("int64")
return_rate = lines["quantity_returned"].sum() / lines["quantity"].sum()

print(len(lines), lines["quantity_returned"].sum())
lines.loc[lines["order_id"].isin([10001, 10119]), ["order_id", "product_id", "quantity", "quantity_returned"]]

2448 214

validate: проверка при соединении

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

• "many_to_one" — ключ в правой таблице уникален (заказы → справочник);
• "one_to_one" — ключ уникален в обеих;
• "one_to_many" — уникален в левой.

checked = orders.merge(products, on="product_id", validate="many_to_one")
len(checked)

2448

А соединение по одному order_id проверку не проходит — в правой таблице номер заказа повторяется:

orders.merge(returns[["order_id", "quantity"]], on="order_id", how="left", validate="many_to_one")

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
  order_id
    10119
    10289
    10498
    10594 ...

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

indicator: откуда строка

Параметр indicator=True добавляет столбец _merge, в котором записано, нашлась ли пара: both — ключ есть в обеих таблицах, left_only — только в левой, right_only — только в правой.

buyers = orders[["customer_id"]].drop_duplicates()
marked = customers.merge(buyers, on="customer_id", how="left", indicator=True)
marked["_merge"].value_counts()

_merge
both          213
left_only      27
right_only      0
Name: count, dtype: int64

27 клиентов — left_only: в заказах их нет. Это те же «спящие» клиенты из прошлого урока, найденные без подсчёта заказов. indicator удобен для сверки двух списков: что есть в одном и отсутствует в другом.

sold = ...
# добавьте в sold столбец rate
rates = ...
worst_name = ...

sold = lines.groupby("product_id", as_index=False).agg(sold=("quantity", "sum"), returned=("quantity_returned", "sum"))
sold["rate"] = (sold["returned"] / sold["sold"]).round(3)
rates = sold.merge(products[["product_id", "name"]], on="product_id", validate="one_to_one").sort_values("rate", ascending=False)
worst_name = rates.iloc[0]["name"]

Итоги

• Ключи с разными названиями — left_on= и right_on=.
• Совпавшие названия столбцов получают окончания _x и _y; свои задаёт suffixes=("_a", "_b").
• Несколько ключей — on=["a", "b"].
• Неверный ключ размножает строки и значения: сверяйте len и суммы до и после, пишите validate="many_to_one".
• indicator=True показывает, у каких строк не нашлось пары.