Joining Without Losing Rows — Python Primer

Two tables walk into a join. What comes out the other side should be the sum of its parts, and every so often it is quietly rather more than that.

A join that fails loudly is easy to fix. The problem with joins is that they fail quietly: you get a DataFrame, it looks right, the columns are all there, and your revenue total is eleven per cent too high.

This chapter is short and it is the one I would most want a beginner to read twice.

The Four Joins

flowchart TB
    subgraph H["how='...'"]
        direction LR
        I["<b>inner</b><br/>keys in BOTH<br/><i>the default</i>"]
        L["<b>left</b><br/>all of left<br/>+ matches"]
        R["<b>right</b><br/>all of right<br/>+ matches"]
        O["<b>outer</b><br/>everything,<br/>gaps as NaN"]
    end
    style I fill:#f0efe9,stroke:#5c6670
    style L fill:#eef1fc,stroke:#2141c8

orders.merge(customers, on='customer_id', how='left')

Use how='left' as your default, not inner. A left join keeps every row of your main table, so if something fails to match you still see the row and can count the failures. An inner join silently deletes unmatched rows, and a table that quietly shrank is much harder to notice than a column full of NaN.

The Disaster

Here is the whole problem in nine lines.

orders = pd.DataFrame({
    'order_id': [1, 2, 3],
    'customer_id': ['A', 'B', 'C'],
    'revenue': [100, 200, 300],
})
customers = pd.DataFrame({
    'customer_id': ['A', 'B', 'C', 'B'],    # ← B appears twice
    'segment': ['smb', 'ent', 'smb', 'ent'],
})

merged = orders.merge(customers, on='customer_id', how='left')

len(orders)          # 3
len(merged)          # 4   ← a row appeared
orders.revenue.sum() # 600
merged.revenue.sum() # 800 ← revenue inflated by 33%

No error. No warning. Order 2 now appears twice, and every downstream sum, average and model is wrong.

One accidental duplicate in a reference table did that. Real reference tables have thousands of rows and are maintained by someone else.

The Three Lines That Prevent It

before = len(orders)

merged = orders.merge(
    customers,
    on='customer_id',
    how='left',
    validate='many_to_one',      # ← state what you believe
    indicator=True,              # ← adds a _merge column
)

assert len(merged) == before, f"row count changed: {before} → {len(merged)}"

validate is the most valuable argument in pandas and almost nobody uses it. It declares the relationship you expect and raises immediately if the data disagrees:

Value	Means
`'one_to_one'`	Keys unique on both sides
`'many_to_one'`	Keys unique on the right. The common case
`'one_to_many'`	Keys unique on the left
`'many_to_many'`	No guarantee. If you need this, be suspicious

many_to_one is what you want almost every time: many orders, one customer record each.

indicator=True adds a _merge column telling you where each row came from:

merged['_merge'].value_counts()
# both          2847
# left_only      153     ← orders with no matching customer. Why?
# right_only       0

Those 153 rows are information. Investigate them rather than filtering them away.

Why Keys Fail to Match

When rows do not join and you expected them to, it is nearly always one of four things.

Type mismatch. An int64 key and a string key describe the same customer and will never match. Modern pandas refuses outright with a ValueError, which is a genuine improvement over older versions that silently returned nothing.

orders.dtypes
customers.dtypes          # check BOTH before merging
customers['customer_id'] = customers.customer_id.astype('string')

Whitespace and case. 'Delhi ' and 'delhi' are different keys.

for d in (orders, customers):
    d['customer_id'] = d.customer_id.str.strip().str.lower()

Do this to both sides before every text join. It costs nothing and it fixes a surprising proportion of “the join produced nothing”.

Leading zeros lost at load time. '0012345' read as an integer becomes 12345. This is the identifier disaster from the files chapter, arriving three steps later.

Missing values. NaN never equals NaN, so rows with a missing key never match. Check before you merge:

orders.customer_id.isna().sum()

A quick diagnostic when a join disappoints:

left_keys = set(orders.customer_id.dropna())
right_keys = set(customers.customer_id.dropna())

print(f"left only:  {len(left_keys - right_keys)}")
print(f"right only: {len(right_keys - left_keys)}")
print(f"both:       {len(left_keys & right_keys)}")
print("examples:", list(left_keys - right_keys)[:5])

Those five examples usually tell you the answer instantly.

Joining on Several Columns

sales.merge(targets, on=['region', 'month'], how='left', validate='many_to_one')

When the columns are named differently on each side:

orders.merge(customers, left_on='cust_id', right_on='id', how='left')

That leaves you with both cust_id and id, which are identical. Drop one.

Overlapping Column Names

If both frames have a name column, pandas appends suffixes:

a.merge(b, on='id')                          # name_x and name_y — unhelpful
a.merge(b, on='id', suffixes=('_order', '_customer'))   # readable

Always set suffixes. Three months later, name_x means nothing to anybody.

Joining on the Index

left.join(right, how='left')              # joins on the index by default
pd.concat([a, b], axis=1)                 # aligns on the index

concat with axis=1 is a join on the index that gives you no validation and no indicator. It is convenient and it hides exactly the problems this chapter is about. Prefer merge when correctness matters.

Stacking frames vertically is the other use, and there the risk is different:

combined = pd.concat([jan, feb, mar], ignore_index=True)

Check that the columns actually align:

print(set(jan.columns) ^ set(feb.columns))    # symmetric difference: any mismatch

A column present in one frame and absent in another becomes a column half full of NaN, and concat will not mention it.

As-Of Joins

For time series, you often want “the most recent record at or before this moment” rather than an exact match. That is merge_asof.

trades = trades.sort_values('timestamp')
quotes = quotes.sort_values('timestamp')

merged = pd.merge_asof(
    trades, quotes,
    on='timestamp',
    by='symbol',                        # match within symbol
    direction='backward',               # the most recent quote at or before
    tolerance=pd.Timedelta('1min'),     # but not older than a minute
)

Both frames must be sorted on the join key or the result is silently wrong.

This matters far beyond finance. Any time you attach a customer’s attributes as they were at the time of the event, rather than as they are now, you want an as-of join. Using current attributes to predict a past event is a leakage bug, and it is one of the most common ones there is.

A Merge Helper Worth Keeping

Wrap the discipline so you cannot forget it:

def safe_merge(left, right, on, how='left', validate='many_to_one', **kwargs):
    """Merge, then verify the row count did not change."""
    before = len(left)
    out = left.merge(right, on=on, how=how, validate=validate,
                     indicator=True, **kwargs)

    if how == 'left' and len(out) != before:
        raise ValueError(f"row count changed: {before} → {len(out)}")

    counts = out['_merge'].value_counts()
    if counts.get('left_only', 0):
        print(f"warning: {counts['left_only']} rows found no match")

    return out.drop(columns='_merge')

Ten lines that turn the most dangerous operation in pandas into one that reports on itself.

What Carries Forward

Default to how='left'. Unmatched rows become visible NaN rather than silently vanishing.

Always pass validate. It states your assumption and fails immediately when it is wrong.

Always assert the row count. Two lines, and it catches the disaster that opens this chapter.

Normalise text keys on both sides before joining. Strip and lowercase, every time.

Use merge_asof for point-in-time joins. Attaching today’s attributes to a past event is leakage.

Next: working with text, where most of that normalisation actually happens.