Соединение таблиц: склеить два источника по ключу

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

Данные почти никогда не лежат в одной таблице. Банк отдаёт коды стран, названия живут в справочнике. Продажи в одном файле, цены — в другом. Заказы отдельно, клиенты отдельно.

Урок 28 закончился именно этим долгом: отчёт получился на кодах KAZ, UZB, RUS, потому что названий у нас не было. Сегодня мы их приставим — и заодно посмотрим, что соединение умеет ломать.

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

Сразу целиком

Файл sklejka.py. Слева инфляция по кодам, справа справочник — намеренно неполный и с лишним кодом.

"""Урок 29: соединить две таблицы по ключу.

Слева — инфляция по кодам стран, справа — справочник с названиями. Ключ один и
тот же, а вот что произойдёт со строками, которым пары не нашлось, зависит от
того, как соединять.
"""

import pandas as pd

# Инфляция, % за год. Данные Всемирного банка, округлены до десятых.
data = pd.DataFrame(
    [("KAZ", 2023, 14.5), ("KAZ", 2024, 8.7),
     ("UZB", 2023, 10.0), ("UZB", 2024, 9.6),
     ("RUS", 2023, 5.9), ("RUS", 2024, 8.4)],
    columns=["code", "year", "value"],
)
# Справочник: названия есть не для всех кодов, зато есть лишний код.
names = pd.DataFrame(
    [("KAZ", "Казахстан", "Центральная Азия"),
     ("UZB", "Узбекистан", "Центральная Азия"),
     ("KGZ", "Кыргызстан", "Центральная Азия")],
    columns=["code", "name", "region"],
)
print("слева строк:", len(data), "| справа строк:", len(names))

print()
print("== inner: остаётся только то, что нашлось с обеих сторон")
inner = data.merge(names, on="code")
print(inner)
print("строк стало:", len(inner), "— потеряли:", len(data) - len(inner))

print()
print("== left: слева сохраняется всё, справа появляется NaN")
left = data.merge(names, on="code", how="left")
print(left.tail(3))
print("без названия строк:", int(left["name"].isna().sum()))

print()
print("== indicator показывает, откуда каждая строка")
both = data.merge(names, on="code", how="outer", indicator=True)
print(both["_merge"].value_counts().to_dict())

print()
print("== ключи с разными именами")
other = names.rename(columns={"code": "iso"})
print(data.merge(other, left_on="code", right_on="iso").columns.tolist())

print()
print("== по подписям: join")
a = data.set_index("code")
b = names.set_index("code")
print(a.join(b, how="left").head(2))

print()
print("== дубликат в ключе размножает строки")
twice = pd.concat([names, names.iloc[[0]]], ignore_index=True)
print("справочник строк:", len(twice), "| после соединения:", len(data.merge(twice, on="code")))
try:
    data.merge(twice, on="code", validate="many_to_one")
except pd.errors.MergeError as err:
    print("validate поймал:", str(err).split("\n")[0])

Выводит:

слева строк: 6 | справа строк: 3

== inner: остаётся только то, что нашлось с обеих сторон
  code  year  value        name            region
0  KAZ  2023   14.5   Казахстан  Центральная Азия
1  KAZ  2024    8.7   Казахстан  Центральная Азия
2  UZB  2023   10.0  Узбекистан  Центральная Азия
3  UZB  2024    9.6  Узбекистан  Центральная Азия
строк стало: 4 — потеряли: 2

== left: слева сохраняется всё, справа появляется NaN
  code  year  value        name            region
3  UZB  2024    9.6  Узбекистан  Центральная Азия
4  RUS  2023    5.9         NaN               NaN
5  RUS  2024    8.4         NaN               NaN
без названия строк: 2

== indicator показывает, откуда каждая строка
{'both': 4, 'left_only': 2, 'right_only': 1}

== ключи с разными именами
['code', 'year', 'value', 'iso', 'name', 'region']

== по подписям: join
      year  value       name            region
code                                          
KAZ   2023   14.5  Казахстан  Центральная Азия
KAZ   2024    8.7  Казахстан  Центральная Азия

== дубликат в ключе размножает строки
справочник строк: 4 | после соединения: 6
validate поймал: Merge keys are not unique in right dataset; not a many-to-one merge

Разбор

inner по умолчанию, и это самая дорогая строчка урока

data.merge(names, on="code") оставляет только те строки, у которых ключ нашёлся с обеих сторон. В примере из шести строк осталось четыре: России в справочнике нет, и обе её строки исчезли. Ни ошибки, ни предупреждения.

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

Одной длины для этого мало. Потерять одни строки и размножить другие можно за одно соединение, и длина при этом не изменится. Ключи слева A и B, в справочнике A, A и C: inner выбросит B, удвоит A — и оставит те же две строки. Длина сошлась, а таблица уже не та.

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

• validate= — сказать, какой связи вы ждёте: "many_to_one" для «данные и справочник», "one_to_one", "one_to_many". Не совпало — MergeError там, где ошибка случилась;
• indicator=True с how="outer" — три числа до настоящего соединения: сколько нашлось с обеих сторон, сколько осталось без пары слева и справа;
• уникальность ключа — names["code"].duplicated().sum() для той стороны, которая должна быть справочником;
• длина и контрольная сумма — len до и после, а рядом сумма того столбца, который вы потом покажете в отчёте: если она выросла, строки размножились.

left: данные решают, справочник дополняет

how="left" сохраняет все строки левой таблицы; там, где пары не нашлось, в столбцах справа встаёт NaN. Это нормальный выбор, когда слева данные, а справа справочник: данные определяют, какие строки существуют, справочник только добавляет к ним слова.

Есть ещё how="right" (зеркально) и how="outer" — оставить всё с обеих сторон. outer полезен не столько для отчёта, сколько для проверки: вместе с indicator=True он показывает, сколько строк нашлось с обеих сторон, сколько осталось без пары слева и сколько справа.

{'both': 4, 'left_only': 2, 'right_only': 1}

Три числа, которые стоит посмотреть до того, как соединять по-настоящему. left_only — то, чего нет в справочнике; right_only — то, чего нет в данных.

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

left_on="code", right_on="iso" — соединяет по столбцам с разными именами. В ответе останутся оба столбца, дубликат придётся убрать самому.

Если ключ — это подписи строк, а не столбец, работает join: a.join(b) соединяет по индексу. Это тот же merge(left_index=True, right_index=True), только короче; в двадцать пятом уроке мы видели, что арифметика рядов тоже сопоставляется по подписям, — join про то же самое, но для таблиц.

Столбцы с одинаковыми именами в обеих таблицах не конфликтуют: они получают приписки _x и _y. Приписки лучше задать самому — suffixes=("_данные", "_справочник"), — иначе через месяц никто не вспомнит, что есть что.

Дубликат в ключе размножает строки

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

В разминке это видно на числах: сумма 300 превращается в 400 — не потому, что что-то добавили, а потому, что одну строку посчитали дважды.

Лекарство — validate:

• validate="one_to_one" — ключ уникален с обеих сторон;
• validate="many_to_one" — слева может повторяться, справа нет (самый частый случай: данные и справочник);
• validate="one_to_many" — наоборот.

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

merge и concat — разные вещи

merge соединяет по ключу, добавляя столбцы. concat складывает таблицы друг под друга (или рядом), не спрашивая ни о каком ключе: так собирают двенадцать месячных файлов в одну таблицу, ставя строки друг под друга. Если вы ищете «объединить таблицы», сначала решите, что именно вам нужно: больше столбцов или больше строк.

Карта урока

Скажите своими словами

Не подглядывая, ответьте вслух или на бумаге. Ответы — в конце урока.

1. Что происходит со строками, которым не нашлось пары, при inner и при left?
2. Как за одну строчку узнать, сколько строк не нашло пары с каждой стороны?
3. Почему после соединения строк может стать больше, чем было?

Разминка

Три коротких шага перед заданием: предсказать, дописать, починить. Ответы — в конце урока, но сначала ответьте сами.

1. Предскажите. Что напечатает эта программа?

import pandas as pd

left = pd.DataFrame({"code": ["KAZ", "UZB", "RUS"], "value": [8.7, 9.6, 8.4]})
right = pd.DataFrame({"code": ["KAZ", "UZB"], "name": ["Казахстан", "Узбекистан"]})
joined = left.merge(right, on="code")
print(len(left), "→", len(joined))
print(joined.to_string(index=False))

2. Заполните пропуск. Вместо ... сделайте так, чтобы строк осталось три, а у России вместо названия был пропуск.

# слева — данные, они пропасть не должны
import pandas as pd

left = pd.DataFrame({"code": ["KAZ", "UZB", "RUS"], "value": [8.7, 9.6, 8.4]})
right = pd.DataFrame({"code": ["KAZ", "UZB"], "name": ["Казахстан", "Узбекистан"]})
joined = left.merge(right, on="code", ...)
print(len(joined), "| без названия:", int(joined["name"].isna().sum()))

3. Почините. Строк было две, стало три, а сумма выросла со трёхсот до четырёхсот. Ничего не добавляли.

# в справочнике код встречается дважды
import pandas as pd

data = pd.DataFrame({"code": ["KAZ", "UZB"], "value": [100, 200]})
names = pd.DataFrame({"code": ["KAZ", "KAZ", "UZB"], "name": ["Казахстан", "Казахстан", "Узбекистан"]})
joined = data.merge(names, on="code")
print("строк:", len(joined), "| сумма:", int(joined["value"].sum()))

Задание

Обязательное. Даны восемь строк инфляции по четырём кодам и справочник на четыре страны — но не на те же самые. Соедините так, чтобы ни одна строка данных не пропала, и напечатайте:

1. сколько строк было и сколько стало, а также коды, которым не нашлось названия;
2. среднюю инфляцию по странам — с названием рядом с кодом;
3. среднюю по регионам, считая только те строки, у которых регион известен.

Ожидаемый вывод:

строк было: 8 | стало: 8
без названия: ['RUS']

средняя инфляция по странам:
code       name  value
 KAZ  Казахстан  11.60
 KGZ Кыргызстан   8.55
 RUS        NaN   7.15
 UZB Узбекистан   9.80

по регионам:
                  стран  средняя
region                          
Центральная Азия      3     9.98

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

На своих данных. Возьмите две свои таблицы с общим ключом — что угодно, где есть код или идентификатор. Сначала посмотрите merge(..., how="outer", indicator=True) и посчитайте три числа. Потом соедините так, как нужно вашей задаче, и сверьте длину до и после.

По желанию.

• Сделайте соединение с validate="one_to_one" на данных, где ключ повторяется, и прочитайте сообщение целиком.
• Соедините те же таблицы через join по индексу и сравните, что получилось.
• Сложите две таблицы через concat и объясните себе, чем это отличается от merge.

Куда это встанет в проекте

Десятый шаг: отчёт перестаёт говорить кодами. sholu/anyqtama.py — таблица из трёх столбцов (код, название, регион), и отчёт соединяется с ней по коду.

Два решения там стоит посмотреть в коде. Соединение — левое: какие строки существуют, решают данные, справочник только добавляет слова, и страна, которой в справочнике нет, остаётся в отчёте под своим кодом. И соединение проверено: validate="one_to_one", потому что повторённый код в справочнике размножил бы строки, а итоги выросли бы сами собой.

Долги. Справочник у нас неполный нарочно: России в нём нет, и в отчёте она осталась кодом с пустым регионом. Это правильное поведение, но пустая ячейка в отчёте — вопрос, на который кто-то должен ответить; чем заполнять такие пустоты, разберёт следующий урок про грязные данные.

Ответы

3 → 2
code  value       name
 KAZ    8.7  Казахстан
 UZB    9.6 Узбекистан

import pandas as pd

left = pd.DataFrame({"code": ["KAZ", "UZB", "RUS"], "value": [8.7, 9.6, 8.4]})
right = pd.DataFrame({"code": ["KAZ", "UZB"], "name": ["Казахстан", "Узбекистан"]})
joined = left.merge(right, on="code", how="left")
print(len(joined), "| без названия:", int(joined["name"].isna().sum()))

3 | без названия: 1

import pandas as pd

data = pd.DataFrame({"code": ["KAZ", "UZB"], "value": [100, 200]})
names = pd.DataFrame({"code": ["KAZ", "KAZ", "UZB"], "name": ["Казахстан", "Казахстан", "Узбекистан"]})
joined = data.merge(names.drop_duplicates("code"), on="code", validate="one_to_one")
print("строк:", len(joined), "| сумма:", int(joined["value"].sum()))

строк: 2 | сумма: 300

Источники

• Соединение и склейка таблиц — merge, join, concat и все их аргументы.
• Аргумент validate — проверка, которая ловит размножение строк.
• Инфляция потребительских цен, Всемирный банк — источник чисел этого урока.