pandas.merge: keys, join types and checking the result

Before joining tables, I ask what one output row should represent. When adding a customer segment to an order, I want to preserve the order and find the appropriate customer. An operation completing without an error does not establish that this happened. An unsuitable key can remove observations or multiply rows that I later sum in a report.

pandas.merge combines objects using columns or indexes. Here I focus on exact column matching in pandas 3.x. The examples are synthetic and their results follow from the documented rules, without claiming execution. The merge reference contains the complete signature. The sections below reuse the base tables unless a separate dataset is explicitly defined.

Define the data and choose the key explicitly

There are three orders and a customer-segment lookup:

import pandas as pd

orders = pd.DataFrame({
    'order_id': [101, 102, 103],
    'customer_id': [1, 2, 3],
    'units': [10, 20, 30],
})
customers = pd.DataFrame({
    'customer_id': [1, 2, 4],
    'segment': ['A', 'B', 'C'],
})

Both tables contain customer_id. Customer three has no segment in the lookup; customer four has no order. I deliberately include both cases so the effect of the join type is visible. Perfectly matching inputs make it easy to overlook how left differs from inner when a real dataset contains unmatched records.

I specify on explicitly. Without it, when not joining indexes, pandas can use the shared column names. Adding another equally named column could then change the condition. Continuing to execute successfully is not enough to preserve a report’s meaning. I prefer to record which fields actually form the key.

A left merge with row provenance

result = pd.merge(
    orders,
    customers,
    on='customer_id',
    how='left',
    validate='many_to_one',
    indicator=True,
)

The first row matches customer one with A and the second matches customer two with B. The third remains because this is a left join, but it has no segment. “Missing” describes the absent value without depending on its printed representation. Customer four does not appear because orders form the base. This output is not a list of all customers.

The _merge column distinguishes matched rows from left-only rows. An outer join can also contain right_only. I examine these groups before deciding whether missing matches are expected. Only then do I choose how to show an absent segment. Filling it with A automatically would conceal the underlying data problem.

What inner, right and outer change

With these inputs, how='inner' retains orders 101 and 102. A right join retains customer keys one, two and four, leaving order information missing for four. An outer join also includes the order for customer three. This describes matching sets rather than promising the same displayed ordering for all join types.

I avoid choosing inner merely because it produces a table without missing values. Here, the missing segment disappears together with order 103. A subsequent units total would concern a different population. I establish the report’s intended population before selecting the join type. If the task is to identify missing matches, removing them before inspection makes that question harder to answer.

Duplicates and validate

The pandas merging guide explains multiplication caused by repeated keys. Two left rows and three right rows for the same key form six pairs. That can be a valid many-to-many relationship. When adding one segment per customer, I generally expect a different rule.

customers_bad = pd.DataFrame({
    'customer_id': [1, 1],
    'segment': ['A', 'B'],
})

pd.merge(
    orders,
    customers_bad,
    on='customer_id',
    how='left',
    validate='many_to_one',
)

This example is intended to raise MergeError: the right key is repeated. many_to_one allows repeated left keys while requiring unique right keys. one_to_one requires uniqueness on both sides, and one_to_many on the left. Choosing many_to_many allows repeats and does not perform a uniqueness check.

Relationship validation does not establish that the business key is correct or that every row matched. A lookup can have unique identifiers and incorrect segments. I therefore combine the relationship check with _merge and inspection of meaning. When validation fails, I investigate before deleting records. Two segments might represent different periods that require an additional matching condition.

A composite key using columns that exist

The old English example selected ['ID', 'Name'], although the second table had no Name. This separate example defines both key fields on both sides:

stock = pd.DataFrame({
    'shop': ['north', 'south'],
    'product': ['pen', 'pen'],
    'units': [2, 3],
})
prices = pd.DataFrame({
    'shop': ['north', 'south'],
    'product': ['pen', 'pen'],
    'price': [4, 5],
})
matched = pd.merge(
    stock, prices,
    on=['shop', 'product'],
    how='left',
    validate='one_to_one',
)

The north shop’s pen receives price four and the south shop’s pen receives five. Product alone cannot identify the relevant shop’s price. Both columns form the condition and therefore need to exist in both tables. When key names differ, use left_on and right_on rather than adding an absent column name to on.

Suffixes for genuinely overlapping names

The base tables have no overlapping non-key columns, so specifying suffixes alone would demonstrate nothing. I add a status column with a different meaning on each side:

orders_status = orders.assign(status='new')
customers_status = customers.assign(status='active')
with_status = pd.merge(
    orders_status,
    customers_status,
    on='customer_id',
    how='left',
    suffixes=('_order', '_customer'),
    validate='many_to_one',
)

The output contains status_order and status_customer. For order 101, their values are new and active. For order 103, customer status remains missing: adding a status to the lookup does not create a matching customer. Suffixes distinguish names without deciding which information should take precedence.

Missing keys differ from ordinary SQL equality

Pandas matches missing key values on both sides. The merge reference explicitly warns about this difference from usual SQL join behaviour. Two unknown identifiers in a report do not necessarily describe the same customer, even if the library can match them.

Before joining, I decide whether to exclude missing keys from matching, separate them for inspection or apply another rule. Replacing them with a shared fictional identifier also needs a decision about meaning. I check key types and representation as well. A number and a string representing it require deliberate preparation; a join does not establish their identity for me.

Exercise before the next report

Add another order for customer one to the base example and predict the row count with the valid lookup. Then consider adding a second segment for that customer. Which validation would stop the work, and why? The answer follows from the number of matching pairs without needing large data. Then describe how an order without a segment should appear to the report’s reader.

When the key is already the right table’s index, continue with DataFrame.join. When appending data fragments rather than finding key matches, see pandas.concat. Decide what the operation should mean before choosing its expression in the library.