← Все курсы · Курс Python · Все руководства
pandas.merge: левое соединение, внутреннее соединение и то, которое удвоило доход
К концу этой страницы вы сможете прикрепить столбцы из одного DataFrame к другому по общему ключу, выберите правильный how для вопроса сразу посмотрите, какие строки не совпадают, и уловите ошибку, которая незаметно увеличивает каждую сумму в кадре. Это около двадцати пяти минут, и все приведенные ниже выходные данные были получены путем запуска кода.
Вот что делать сегодня при каждом написанном вами слиянии. Выведите количество строк непосредственно перед и сразу после него. Левое слияние не должно изменять количество строк, а если это произошло, то правая таблица имеет ключ более одного раза, и ваши итоговые значения только что увеличились.
Краткая версия: merge объединяет строки из двух кадров, где их ключи совпадают, и количество получаемых строк зависит от того, сколько раз каждый ключ появляется на каждой стороне.
Одна клавиша дважды справа — это идея, поэтому она получает картину.
- объединить в одну строку
- Четыре «как» и строки, которые они дают
- показатель: с какой стороны пришел этот ряд
- Умножение
- проверить: запретить слияние
- Имена столбцов, которые конфликтуют
- Ключи с разными именами и разными типами
- объединить, объединить и объединить
- Полное до и после
- Пограничные случаи, нарушающие слияние
- Почему это работает
- Использование этого в вашем собственном проекте
- Все на одном экране
1. объединить в одну строку
Два кадра, один общий столбец, один вызов.
orders.merge(products, on="product", how="left")
order_id product category cost
0 1001 Desk Furniture 140
1 1002 Chair Furniture 52
2 1003 Desk Furniture 140
3 1004 Lamp Lighting 22
shape (16, 11) revenue 9890Шестнадцать строк на входе, шестнадцать строк на выходе, а доход не изменился и составил 9890. Две колонны прибыли, category и cost, прикрепляется к каждому заказу путем сопоставления названия продукта.
Прочтите аргументы как предложение: принимайте заказы, привозите продукцию, сопоставляйте. productи сохраняйте каждый порядок, независимо от того, совпадает он или нет. Эта последняя часть how, и именно аргумент решает всё.
Эти два числа — количество строк и сумма — составляют всю проверку. Произнесите их вслух, прежде чем запускать слияние, а затем сравните. Все на этой странице — это способ измениться, когда вы этого не ожидали.
2. Четыре «как» и ряды, которые они дают
Перед пояснением: в таблице товаров отсутствуют лампы, а четыре из шестнадцати заказов — лампы. Предскажите количество строк для каждого из четырех способов.
how=left rows= 16 revenue=9890 missing cost=4
how=inner rows= 12 revenue=8290 missing cost=0
how=right rows= 12 revenue=8290 missing cost=0
how=outer rows= 16 revenue=9890 missing cost=4Четыре разных ответа на один и тот же вопрос, и разница между первыми двумя составляет 1600 доходов, то есть от лампового бизнеса.
| Как | держит | Используйте его, когда |
|---|---|---|
left | Каждая строка левого кадра, совпадающая или нет | Почти всегда. Левый кадр — это ваши данные, и вы их украшаете. |
inner | Только строки, совпадающие с обеих сторон | Вы искренне хотите перекрёсток и проверили, что он выпадет. |
right | Каждая строка правого кадра | Редко. Поменяйте местами рамки и вместо этого используйте левую; оно читается лучше. |
outer | Всё с обеих сторон | Согласование двух списков и необходимость посмотреть, что находится только на каждой стороне. |
left это выбор по умолчанию по определенной причине: он не может потерять ни одной строки ваших данных. inner это то, что цепляет людей, потому что это самое естественное желание, и оно бесшумно удаляется. Двенадцать рядов вернулись туда, где было шестнадцать, никакого сообщения не появилось, и четыре заказа ламп просто невозможно пропустить.
Правило: если количество строк уменьшилось, внутреннее соединение что-то выбросило, и вы должны знать, что именно.
3. показатель: с какой стороны пришел этот ряд
Один аргумент превращает «некоторые строки не совпали» в число, которое вы можете прочитать.
orders.merge(products_missing_lamp, on="product", how="outer", indicator=True)
_merge
both 12
left_only 4
right_only 0Двенадцать совпали, в четырех заказах не было обнаружено ни одного товара, и ни один товар не остался неиспользованным. Эти три числа представляют собой форму соединения, и для их получения требуется одно ключевое слово.
Используйте его при разработке каждого слияния, а затем удалите его или сохраните столбец и отфильтруйте его. Таким образом вы получите сами несовпадающие строки, а не только их количество:
m = left.merge(right, on=key, how="outer", indicator=True)
m[m["_merge"] == "left_only"] # the rows that found no partnerright_only на ненулевое значение тоже стоит обратить внимание. Это означает, что в таблице поиска есть записи, которые никто не использует, что обычно безвредно и иногда сообщает вам, что ключ неправильный: поиск, в котором вообще ничего не совпадает, возвращается в виде каждой строки с одной стороны и каждой строки с другой.
4. Умножение
Перед пояснением: в таблице продуктов появится вторая строка для «Стол», идентичная первой. Теперь у него четыре ряда вместо трех. Предскажите, что вернет слияние.
rows 16 -> 23
revenue 9890 -> 14950
desk rows 7 -> 14Семь дополнительных строк и 5060 дополнительных доходов от добавления одной строки в таблицу поиска. К заказам ничего не добавлялось и ни один заказ не менялся.
Механизм именно такой, как на картинке вверху. Семь орденов — столы; каждый из них теперь соответствует двум строкам в таблице продуктов; поэтому каждый выходит дважды. Семь заказов превратились в четырнадцать рядов, и доход от каждого заказа на столе учитывается дважды, то есть 5060 учитываются во второй раз.
Самое опасное на странице то, что не происходит. Никаких ошибок и предупреждений нет. Фрейм выглядит совершенно нормально, каждое отдельное значение в нем правильное, а любое общее значение, вычисленное впоследствии, неверно, и это незаметно, если вы не знали заранее количество строк.
Это также совершенно обычно в реальных данных. Таблица продуктов с одной строкой для каждого продукта и одной строкой для каждого изменения цены. Таблица клиентов со строкой на адрес. Поиск, который был добавлен дважды. Каждая из них представляет собой разумную таблицу, в которой нет одной строки на ключ, и слияние с ней умножается.
Произнесите вслух, что бы вы увидели, если бы это произошло при слиянии внутри конвейера, который вы не писали. Цифра дохода слишком завышена, нигде нет ошибок, и каждая строка, которую вы проверяете, верна.
5. Подтвердите: откажитесь от слияния
pandas проверит мощность за вас и увеличит, а не умножит.
orders.merge(products, on="product", how="left", validate="m:1")
# clean table: accepted
orders.merge(products_with_duplicate_desk, on="product", how="left", validate="m:1")
# MergeError: Merge keys are not unique in right dataset; not a many-to-one mergeЭто единственный наиболее ценный аргумент во всей этой функции, и его почти никто не использует. validate="m:1" говорит: многие строки слева могут совпадать, но каждый ключ должен появляться не более одного раза справа. Это предположение, которое вы уже делаете каждый раз, когда прикрепляете поиск, записанное, чтобы компьютер мог его реализовать.
| подтвердить | Означает |
|---|---|
"m:1" | От многих левых рядов к одному правому ряду. Прикрепляю поиск. Обычный случай. |
"1:1" | Обе стороны уникальны. Объединение двух версий одной и той же записи. |
"1:m" | От одного левого ряда до множества правых рядов. Расширение родителя на его дочерние элементы. |
"m:m" | Никаких ограничений. Это поведение по умолчанию, и оно увеличивается. |
Добавляйте его к каждому написанному вами слиянию. Оно стоит девять символов, превращает невидимую инфляцию в исключение в тот момент, когда оно происходит, и исключение называет сторону, которая неправа.
6. Конфликтующие имена столбцов
Если в обоих фреймах есть столбец с одинаковым именем, и он не является ключом, pandas сохраняет оба и переименовывает их.
['units_x', 'units_y']_x это левый кадр и _y это правильно. Это разумное значение по умолчанию, и ужасно обнаружить в сценарии шесть месяцев спустя, потому что ничего о units_x говорит, из какого стола оно взято.
Два лучших варианта. Установите свои собственные суффиксы при слиянии, которое занимает две секунды и впоследствии читается правильно:
orders.merge(products, on="product", suffixes=("_order", "_catalogue"))Или, что еще лучше, удалите или переименуйте ненужные столбцы перед объединением, чтобы никогда не возникало коллизий. Слияние, которое добавляет три нужных вам столбца, гораздо легче читать, чем слияние, которое включает пятнадцать и переименовывает четыре из них.
7. Ключи с разными именами и разными типами.
Если ключевой столбец с каждой стороны называется по-разному, назовите оба.
orders.merge(catalogue, left_on="product", right_on="item", how="left")
rows 16, and the result keeps both key columns: ['product', 'item']Обратите внимание на вторую половину. Оба ключевых столбца сохраняются в результате, сохраняя одинаковые значения, поэтому в кадре имеется избыточный столбец. Немедленно отбросьте это, иначе следующий человек будет задаваться вопросом, кто из них авторитетнее.
Типы более строгие, и pandas здесь ведет себя лучше, чем большинство инструментов. Слияние текстового ключа с целочисленным ключом ничего не означает молча; это повышает:
ValueError: You are trying to merge on string and int64 columns for key 'order_id'.Это действительно хорошая новость, потому что та же ошибка при поиске в электронной таблице приводит к #N/A в некоторых строках и в SQL выдает нулевые совпадения, и оба эти явления выглядят скорее как проблема с данными, чем как проблема с типом. Вот это тебя останавливает. Исправьте типы, обычно делая обе стороны текстовыми, если ключ является идентификатором, который является аргументом в как загружается CSV.
Что такое нет поймана разница в самих значениях: конечный пробел, другой регистр, код с ведущим нулем только с одной стороны. Они сливаются без ошибок и ничему не соответствуют, а признаком этого является подозрительно круглое количество несовпадающих строк в таблице. indicator столбец.
8. объединить, объединить и объединить
Три функции, которые люди путают на разных работах.
merge совпадения по столбцам. Это то, что хочется почти всегда, и все выше об этом.
join это удобный метод, который соответствует индекс по умолчанию. Он короче, когда ваши кадры действительно проиндексированы по ключу, и является источником путаницы, когда это не так, потому что он молча использует индекс, о котором вы забыли. Если есть сомнения, используйте merge и назовите столбцы.
concat ничему не соответствует. Он укладывает кадры либо друг на друга, что представляет собой объединение строк, либо рядом, что выравнивает их по позиции индекса, что почти никогда не является тем, что вам нужно. Проверка на вертикаль concat является арифметическим: количество строк результата должно равняться сумме входных данных.
pd.concat([jan, feb, mar], ignore_index=True) # rows must add up
orders.merge(products, on="product", how="left") # rows must not changeПредставьте себе конвейер, который вы поддерживаете. Сколько в нем слияний и для скольких из них вы знаете ожидаемое количество строк?
Полное до и после
Та же работа: привязать стоимость продукции к шестнадцати заказам.
До
df = orders.merge(products, on="product")
# no how, so it defaults to inner
# no validate, so a duplicate key would multiply
# no row count check, so neither would be noticedТри варианта по умолчанию приняты без принятия решения. Если при поиске когда-либо теряется продукт, строки исчезают. Если когда-либо появится дубликат, строки умножатся. Оба меняют итоги и ни один ничего не говорит.
после
before = len(orders) # 16
df = orders.merge(
products[["product", "category", "cost"]], # only what is needed
on="product",
how="left", # never lose a row of my data
validate="m:1", # and refuse if the lookup repeats
indicator=True,
)
assert len(df) == before, f"merge changed the row count: {before} -> {len(df)}"
print(df["_merge"].value_counts()) # both 16, left_only 0
print(df["cost"].isna().sum()) # 0 unmatchedПодсчет строк подтвержден, кардинальность обеспечивается pandas, а не надеждой, несовпадающие строки подсчитываются, а не предполагаются, и передаются только три нужных столбца.
Претензия и вот почему validate принадлежит при каждом слиянии: добавление одной повторяющейся строки в справочную таблицу из трех строк привело к увеличению числа строк с 16 строк и 9890 до 23 строк и 14 950, и при этом не возникло ни ошибок, ни предупреждений, ни неправильных отдельных значений где-либо в кадре.
Пограничные случаи, нарушающие слияние
Шесть, которые стоит знать.
Нули в ключе. По умолчанию pandas сопоставляет нуль с нулем, поэтому каждая строка с отсутствующим ключом слева соединяется с каждой строкой с отсутствующим ключом справа, что представляет собой умножение, ожидающее своего выполнения. Удалите или заполните их перед объединением.
Пробелы и регистр в ключе. Сливается без ошибок, ничего не соответствует и отображается только в indicator имеет значение. Сначала очистите ключевые столбцы, как и при любом другом объединении.
Индекс после слияния. merge отбрасывает индекс левого кадра и дает вам новый, поэтому все, что зависит от старого индекса, спокойно ломается. Сбросьте или повторно подключите специально.
Слияние в цикле. Многократное объединение одного кадра в растущий результат происходит медленно и увеличивает риск того, что умножение произойдет один раз и останется незамеченным. Создайте список и объедините его один раз или сократите список, каждый раз проверяя счетчик.
Память. При слиянии создается новый фрейм со столбцами обоих, поэтому объединение двух широких таблиц может в несколько раз превышать размер каждой из них. Выберите нужные вам столбцы справа до объединения, а не после.
Многие ко многим намеренно. Иногда это действительно правильно, например, разложить заказ на строки. Тогда скажи это с validate="1:m" и сверить количество строк с ожидаемым расширением, чтобы запланированное умножение было задокументировано, а непреднамеренное все равно не удалось.
Почему это работает
Умножение — это не причуда pandas, это то, что означает сопоставление. Соединение соединяет каждую строку на одной стороне с каждой строкой на другой, ключ которой согласуется, поэтому размер вывода определяется тем, сколько раз каждый ключ встречается на каждой стороне, а не количеством строк в каждой таблице. В реляционной модели ключ — это именно столбец или набор столбцов, значение которых однозначно идентифицирует строку, и соединение с чем-то, что не является ключом, лишает этой гарантии (Кодд, 1970, Коммуникации ACM, 13(6), 377–387). «Один дубликат в таблице поиска» — это еще один способ сказать, что объединяемый столбец не является ключевым на этой стороне, и как только вы скажете это таким образом, семь дополнительных строк перестанут удивлять.
pandas намеренно отражает этот словарь, а не изобретает свой собственный, поэтому how берет те же четыре слова, которые использует база данных, и объясняет, почему поведение совпадает: библиотека была создана для выполнения операций реляционного стиля с табличными данными в памяти в Python, чтобы люди могли выполнять соединения и изменения, которые они уже делали в SQL, без перемещения данных (МакКинни, 2010, Материалы 9-й конференции «Питон в науке», 51–56). Это полезная часть для аналитика: то, что вы узнаете здесь, напрямую переносится на объединяется в SQL, включая два способа, которыми они идут не так.
Одно примечание к вопросам, заданным перед каждым ответом на этой странице. Побуждение себя сделать прогноз до того, как прочитать результат, — это один из немногих шагов обучения, который надежно улучшает то, что вы извлекаете, и это работает, даже если прогноз неверен (Фиорелла & Майер, 2016, Обзор педагогической психологии, 28(4), 717–741). 23 палочки, потому что вас попросили угадать 16.
Использование этого в вашем собственном проекте
Аудит каждого слияния в базе кода — это проект. Вместо этого сделайте это по порядку.
- Найдите слияния без
how. По умолчанию это внутренние соединения, и их авторы обычно имели в виду левые. - добавлять
validate="m:1"для каждого слияния поиска. Девять символов, и умножение становится исключением. - Утвердить количество строк вокруг любого левого слияния. Одна строчка, и она громко проваливается в момент ошибки.
- Использовать
indicator=Trueразвивая и прочтите три пункта, прежде чем удалять его. - Перед объединением выберите правые столбцы, поэтому коллизии и память перестают быть проблемами.
- Сначала очистите ключевые столбцы, например, пробелы и тип, поскольку ни один из них не приводит к ошибке.
Если у вас под рукой есть бумага, один дополнительный эскиз стоит пяти минут. Запишите две таблицы в виде двух полей и рядом с каждой напишите, сколько раз в ней может появляться одно и то же значение ключа: один раз или несколько раз. Эти два ответа и есть validate строка, и большинство людей обнаруживают, что их запись приводит к появлению таблицы, которая, как они предполагали, содержала одну строку на ключ, но это не так.
Все на одном экране
Это лист поиска. Закройте правую колонку, проработайте левую и произнесите каждый ответ вслух, прежде чем проверять его.
| вещь | Что он делает |
|---|---|
merge(on=, how=) | Пары строк, где совпадают ключи. |
| Чек | Количество строк и одна сумма до и после. |
how="left" | Сохраняет каждую левую строку. Выбор по умолчанию. |
how="inner" | Только совпадающие строки. Удаляет молча. |
how="outer" | Всё с обеих сторон. Для примирения. |
| Количество строк уменьшилось | Внутреннее соединение удаляет строки. Узнайте какой. |
| Количество строк увеличилось | Правый ключ не уникален. Итоговые суммы теперь завышены. |
indicator=True | Добавляет _merge: оба, только слева, только справа. |
validate="m:1" | Повышается, если правая клавиша повторяется. Используйте его каждый раз. |
| Другие значения проверки | 1:1, 1:m, и m:m что является незащищенным значением по умолчанию. |
| Столкновение имен столбцов | Становиться _x и _y. Набор suffixes или сначала бросьте их. |
| Различные названия клавиш | left_on и right_on. Обе колонны сохранились; бросьте один. |
| Различные типы ключей | Вызывает Ошибка значения. Это лучше, чем ничего не соответствовать. |
| Различные ключевые значения | Регистр, пробелы, ведущие нули. Ни ошибок, ни совпадений. |
| Нули в ключе | Соответствуйте друг другу, что умножается. Сначала удалите их. |
join | По умолчанию соответствует индексу. Предпочитайте объединить и назвать столбцы. |
concat | Стеки, а не совпадения. Количество строк должно суммироваться. |
validate= при каждом слиянии. Это единственное, что на этой странице превращает молчаливый провал в громкий в тот момент, когда он происходит, и его написание заставляет вас заявить, что вы думаете о двух таблицах, и именно в этом обычно и заключается ошибка. Если слияние ведет себя не так, как описано на этой странице, существует общая ошибка. шлейф диагностики на предмет застревания.Еще одна мысль, и мне бы искренне хотелось услышать ответы других людей. В моей таблице цен во время исправления данных появилась одна повторяющаяся строка, поэтому месячный доход за два месяца оказался выше восьми процентов, и каждая проверяемая строка была верной. Что для вас незаметно умножило объединение?
Ссылки
- Кодд, EF (1970). Реляционная модель данных для крупных общих банков данных. Коммуникации ACM, 13(6), 377–387.
- МакКинни, В. (2010). Структуры данных для статистических вычислений в Питон. Материалы 9-й конференции «Питон в науке», 51–56.
- Фиорелла Л. и Майер Р.Э. (2016). Восемь способов продвижения генеративного обучения. Обзор педагогической психологии, 28(4), 717–741.
Python для аналитиков составляет 203 страницы, каждый тип соединения читается построчно, с указанием количества строк до и после.
Python для аналитиков, 19 $ →тот Курс Python дает вам повторы: поиск повторяющихся ключей, отсутствующих ключей и несовпадающих типов, а также флеш-карточки, на которых how сохраняет список строк и проводит пробный экзамен, который запрашивает количество строк перед запуском слияния. Теперь вы владеете методом; комплект - это практика. Ничего устанавливать, учетная запись не требуется.