LESSON 150 · PRACTICAL PYTHON30 min

Pandas Merge on Multiple Columns Without Duplicate Matches

Join orders with regional prices using both SKU and region, rejecting duplicate lookup keys and null join keys.

Before you start: Dictionaries · Read and Write JSON and CSV · Count and Group Data with Counter and defaultdict

Example check: Worked and guided code samples are checked against CPython 3.13. Practice uses Pyodide 0.29.5 (Python 3.13) in your browser. How we check lessons

WATCH

Video lesson: Pandas Merge on Multiple Columns Without Duplicate Matches

The preview is stored on this site. YouTube loads only when you press Play. Watch on YouTube

Jump to a chapter

Read the video transcript

Select a timestamp to watch that moment on YouTube.

00:00
A left merge connects orders to a pricebook. The pricebook has SKU A in East for ten and SKU A in West for twelve. An East order must receive ten, but a merge on SKU alone pairs it with both prices. The code looks legitimate and may not throw an error, yet the row count doubles. This is why a search for pandas merge on multiple columns deserves more than the syntax `on=['sku', 'region']`. We also need to state how many lookup rows may match, inspect orders that have no price, and decide what to do with missing key values. We will run the broken one-key join, fix it with the full composite key, then add
00:40
`validate` and `indicator` to make data mistakes visible. All fixtures are in memory. Local Python needs `python -m pip install pandas`; the browser lesson loads pandas in its supported console before running the examples. We had two orders and two price rows, but the merge produced four records. The suffixes region_x and region_y reveal the cause: region was not a join key, so pandas retained one region from each side. An East order is paired with a West price as well as its East price. If we multiply quantity by price now, the report is overstated. A left merge would not save us from this; join type chooses which unmatched rows
01:23
remain, not which key columns define a match. The complete business key here is the pair of SKU and region. Notice that the wrong output is not an exception. Both tables are valid pandas objects, and the merge is doing exactly what the requested single key means. This is why input and output row counts belong in tests for data joins. If you expect one output row per order, write that expectation and check it before calculating totals or publishing a report. Passing a list of two labels makes their combination the key. East A joins only to East A, and West A joins only to West A.
02:05
This fixes the sample, but it is not yet a complete production contract. Suppose the pricebook accidentally contains two East A rows. Even the composite join would multiply the East order. We know our relationship: many orders may share one regional price, while each SKU- region pair should appear at most once in the pricebook. Pandas can enforce that expectation with `validate='many_to_one'`. When the right-hand lookup key is duplicated, the merge raises a MergeError instead of silently creating repeated orders. If both inputs were supposed to have unique keys, we would choose one_to_one. Choosing validate requires thinking about the real relationship; copying the most
02:47
restrictive setting without understanding the data may reject legitimate repeated orders. We also need to decide whether an order with no price should disappear or remain visible. A left merge keeps both orders. The first has a matching price; the second has no matching price and receives a missing value. `indicator=True` adds a status column so we can distinguish those cases directly. This is more useful than assuming every returned row is fully priced. Before a revenue calculation, a program should count left_only records and choose whether to report, reject, or separately repair them.
03:28
The browser task returns the full joined DataFrame including this status. It also rejects null values in either input's SKU and region columns. Pandas can match null keys to null keys, which surprises developers accustomed to SQL behavior where null usually does not equal null in joins. There is no universal correct policy for null keys, so the lesson makes its own explicit rule. For real input, check that join columns have compatible dtypes as well. A numeric SKU in one table and a text SKU in another will not become the same key merely because they look similar when printed.
04:09
The duplicate right-hand key raises before a four-row or two-row multiplication can quietly enter a report. The null check returns true, giving our helper a reason to reject the lookup before merging. These checks are independent of the two- column syntax, but they turn a short merge expression into a reliable data contract. There are other edge cases: overlapping non-key columns will receive suffixes, and mismatched types can prevent matches. Name or select columns deliberately rather than relying on whichever suffixed name appears. Once the inputs pass checks, compare output rows to input orders and inspect the indicator distribution.
04:51
A merge can be syntactically correct yet semantically wrong if the chosen pair does not actually identify a price. On the lesson page, complete `join_orders`, deliberately add a second East price, and see the expected failure. Then add an unmatched order and inspect left_only. The goal is a join whose matches, misses, and cardinality are all visible.

THE IDEA

Understand the concept

To merge on multiple columns in pandas, pass a list to on, such as on=['sku', 'region']. The combination is the join key. A catalog may sell the same SKU in East and West with different prices; merging on SKU alone joins each order to both regional price rows, inflating the result and possibly duplicating revenue. The first example reproduces that surprising row multiplication.

A left merge keeps every order, even when there is no regional price. Set validate='many_to_one' when many orders may share one pricebook key but the pricebook must have at most one row per key pair. This raises MergeError for duplicate lookup keys before a many-to-many explosion can propagate. indicator=True adds a _merge column so left_only rows can be inspected instead of silently accepted as priced orders.

Pandas matches null join keys to other null join keys, unlike the usual SQL expectation. This tutorial rejects missing SKU or region in either input before merging; a real product might choose a different rule, but it should be explicit. Check key dtypes too: a numeric SKU in one table and a string SKU in the other do not represent the same key. Extra non-key columns with the same name receive suffixes unless you select or rename them deliberately.

Install pandas locally with `python -m pip install pandas`; the browser loads the supported package for its small in-memory fixtures. The task returns joined rows in order, with price and _merge status. Tests check regional matches, an unmatched order, duplicate pricebook rows, and null keys. A successful merge is not proof that every order found a price: review the indicator counts and decide whether left_only should be an error before calculating totals.

on=['sku', 'region'] defines a composite key
validate protects expected key cardinality
indicator reveals unmatched orders
pandas can match null keys to each other

WORKED EXAMPLES

See it step by step

Read the code, predict the output, then compare it with the result.

01. Reproduce a one-key row explosion

PYTHON

import pandas as pd
orders = pd.DataFrame({'sku':['A','A'], 'region':['East','West']})
prices = pd.DataFrame({'sku':['A','A'], 'region':['East','West'], 'price':[10,12]})
print(len(orders.merge(prices, on='sku')))
print(len(orders.merge(prices, on=['sku','region'])))

EXPECTED OUTPUT

4
2

SKU alone creates four pairings; both keys create the two intended matches.

02. Validate and label a composite left merge

PYTHON

import pandas as pd
orders = pd.DataFrame({'sku':['A','B'], 'region':['East','East']})
prices = pd.DataFrame({'sku':['A'], 'region':['East'], 'price':[10]})
joined = orders.merge(prices, on=['sku','region'], how='left', validate='many_to_one', indicator=True)
print(joined['_merge'].astype(str).tolist())
print(joined['price'].notna().tolist())

EXPECTED OUTPUT

['both', 'left_only']
[True, False]

The indicator distinguishes a matched order from one still missing a price.

03. Catch duplicate pricebook keys

PYTHON

import pandas as pd
from pandas.errors import MergeError
orders = pd.DataFrame({'sku':['A'], 'region':['East']})
prices = pd.DataFrame({'sku':['A','A'], 'region':['East','East'], 'price':[10,11]})
try:
    orders.merge(prices, on=['sku','region'], validate='many_to_one')
except MergeError:
    print('duplicate price key')

EXPECTED OUTPUT

duplicate price key

A duplicate right-side key violates the many-to-one contract.

GO DEEPER

A closer look

Follow the reasoning, inspect each result, then try the suggested changes in the console below.

01 / 03

Identify the complete key

An order's SKU is not enough to determine its regional price. The first test deliberately joins two orders against two price rows with on='sku'. Each order pairs with both prices, producing four output rows. The code runs without an exception, which makes this kind of mistake dangerous in reports.

Add region to the on list and compare counts again. The corrected join uses the pair of values as one composite key. Check columns and row count before using the resulting price in a calculation; a plausible-looking table can still contain multiplied records.

PYTHON

import pandas as pd
orders = pd.DataFrame({'sku':['A','A'], 'region':['East','West']})
prices = pd.DataFrame({'sku':['A','A'], 'region':['East','West'], 'price':[10,12]})
print(len(orders.merge(prices, on='sku')))
print(len(orders.merge(prices, on=['sku','region'])))

EXPECTED OUTPUT

4
2

Follow the reasoning

Run the one-key and two-key merges.
Explain which combinations are invalid in the four-row result.

02 / 03

Validate the lookup relationship

A composite key can still be duplicated in the pricebook. If there are two East prices for SKU A, a left merge duplicates the East order even with both columns in on. validate='many_to_one' declares that only the order table may repeat a key pair.

Pandas raises MergeError when that right-side uniqueness rule fails, stopping the report before totals are calculated. This is a data quality check, not a choice of which duplicate price to keep. If duplicates are legitimate, the relationship and intended aggregation need an explicit redesign.

PYTHON

import pandas as pd
from pandas.errors import MergeError
orders = pd.DataFrame({'sku':['A'], 'region':['East']})
prices = pd.DataFrame({'sku':['A','A'], 'region':['East','East'], 'price':[10,11]})
try:
    orders.merge(prices, on=['sku','region'], validate='many_to_one')
except MergeError:
    print('duplicate price key')

EXPECTED OUTPUT

duplicate price key

Follow the reasoning

Run the duplicate pricebook example.
State which input violated the cardinality rule.

03 / 03

Inspect unmatched records

A left merge keeps all orders, including an order with no corresponding price. indicator=True adds _merge values such as both and left_only. Count or inspect left_only rows before doing arithmetic; a missing price should not quietly turn into a misleading subtotal.

Pandas also matches null keys to null keys. This differs from typical SQL joins, so the task rejects missing SKU or region before merging. Review type consistency and overlapping non-key columns too. The example below keeps the unmatched order visible so the caller can decide how to handle it.

PYTHON

import pandas as pd
orders = pd.DataFrame({'sku':['A','B'], 'region':['East','East']})
prices = pd.DataFrame({'sku':['A'], 'region':['East'], 'price':[10]})
joined = orders.merge(prices, on=['sku','region'], how='left', validate='many_to_one', indicator=True)
print(joined['_merge'].astype(str).tolist())
print(joined['price'].notna().tolist())

EXPECTED OUTPUT

['both', 'left_only']
[True, False]

Follow the reasoning

Run the indicator example.
Find the order whose price is still absent.

HANDS-ON

Try it in Python

Edit the example and run it. Python starts in your browser the first time you click Run.

Python console

Ready to run

import pandas as pd

def join_orders(orders, pricebook):
    return orders.merge(pricebook, on='sku', how='left')

orders = pd.DataFrame({'sku':['A','A'], 'region':['East','West']})
prices = pd.DataFrame({'sku':['A','A'], 'region':['East','West'], 'price':[10,12]})
print(join_orders(orders, prices)[['sku','region_x','price']].to_dict('records'))

Need input()? Add one value per line

Input lines

Ctrl / ⌘ + Enter to run

OUTPUT

Your output appears here.

Your turnJoin orders to pricebook by both sku and region with a left merge. Require a unique pricebook row per key pair, retain _merge status, and reject null join keys. The sample must produce exactly two prices: East 10 and West 12.

Need a hint?

Reject missing values in both key columns, then use merge(..., on=['sku', 'region'], how='left', validate='many_to_one', indicator=True).

Complete the task and select Check task to verify your code.

DEBUG BETTER

Typical mistakes

Everyone meets these errors. See what causes them and how to fix them.

Joining on one of two keys

COMMON MISTAKE

orders.merge(pricebook, on='sku')

FIX

orders.merge(pricebook, on=['sku','region'])

What happens: An East order receives both East and West price rows.
Use the entire composite key.

Trusting a left join without checks

COMMON MISTAKE

orders.merge(pricebook, how='left', on=['sku','region'])

FIX

orders.merge(pricebook, how='left', on=['sku','region'], validate='many_to_one', indicator=True)

What happens: Duplicate lookup pairs can multiply orders and unmatched rows have NaN prices.
State cardinality and inspect left_only rows.

Browse this lesson’s problem fixes

VERIFY THE STORY

Sources and further reading