Merging and Joining DataFrames

Analogy categoryAnalogies

Combining data spread across multiple tables — customers and orders, transactions and product catalogs — is one of the most common real-world data tasks, and pandas mirrors SQL join semantics through pd.merge (and the closely related DataFrame.merge and DataFrame.join methods). Understanding how join type affects row counts, and how key columns are matched, is essential: an incorrect join type or an unexpected duplicate key can silently multiply or drop rows in ways that corrupt downstream analysis.

🏏

Cricket analogy: Combining a table of players with a separate table of match innings mirrors an SQL join, and picking the wrong join type or hitting a duplicated player-ID key can silently double-count a batter's runs or drop an entire innings from the report.

The how parameter: inner, left, right, outer

pd.merge(left, right, how=..., on=...) accepts four core join types. 'inner' (the default) keeps only rows whose key exists in both DataFrames. 'left' keeps every row from the left DataFrame, filling unmatched right-side columns with NaN. 'right' does the mirror image, keeping every row from the right DataFrame. 'outer' keeps every row from both sides, filling NaN wherever a match is missing on either side. Choosing 'inner' when you actually need 'left' is a classic bug that silently drops legitimate rows that simply had no match — always sanity-check row counts before and after a merge.

🏏

Cricket analogy: An 'inner' merge of batters and bowling figures keeps only players who both batted and bowled, 'left' keeps every batter even without bowling stats (filled NaN), 'right' does the mirror for bowlers, and 'outer' keeps everyone, so choosing 'inner' when you meant 'left' silently drops specialist batters who never bowled.

python

import pandas as pd

customers = pd.DataFrame({
    'customer_id': [1, 2, 3],
    'name': ['Asha', 'Ben', 'Chen']
})

orders = pd.DataFrame({
    'order_id': [901, 902, 903],
    'customer_id': [1, 1, 4],
    'amount': [50.0, 20.0, 75.0]
})

inner = pd.merge(customers, orders, on='customer_id', how='inner')
print(inner.shape)   # (2, 4) - only customer_id 1 matches in both

left = pd.merge(customers, orders, on='customer_id', how='left')
print(left.shape)    # (3, 4) - Ben and Chen kept with NaN order fields

outer = pd.merge(customers, orders, on='customer_id', how='outer')
print(outer.shape)   # (4, 4) - includes customer_id 4's order with NaN name

Merge keys: on, left_on/right_on, and index-based joins

When the join key has the same column name in both DataFrames, on='key' is sufficient. When key names differ, left_on and right_on specify them separately. If you want to join on the index of one or both DataFrames instead of a column, left_index=True and/or right_index=True are used — this is exactly what DataFrame.join() does by default, making join a convenience wrapper around merge for index-based combination. The suffixes parameter ('_x', '_y' by default) disambiguates overlapping non-key column names that appear in both DataFrames.

🏏

Cricket analogy: on='player_id' works when both the roster and stats tables share that column name, but if one calls it 'batter_id' you'd use left_on/right_on, and joining on the index directly with left_index=True is what DataFrame.join() does by default when merging by match date.

Validating merges and detecting key duplication

The indicator=True parameter adds a _merge column labeling each row as 'left_only', 'right_only', or 'both', which is invaluable for auditing which rows failed to match. The validate parameter (e.g. validate='one_to_many') makes pandas raise a MergeError if the actual key cardinality doesn't match your assumption, catching cases where an unexpectedly duplicated key would silently fan out rows (a one-to-many merge where you expected one-to-one).

🏏

Cricket analogy: indicator=True adds a _merge column showing whether a player's row came from 'left_only' (batting data with no bowling match), 'right_only', or 'both', and validate='one_to_many' raises an error if a supposedly unique player key turns out duplicated, catching a data-entry mistake before it fans out rows.

If the join key appears multiple times in either DataFrame, merge performs a full Cartesian product of the matching groups — a key appearing 3 times on the left and 2 times on the right produces 6 rows in the output for that key, not 2 or 3. This is expected relational-join behavior but frequently surprises people the first time they see row counts balloon.

Always verify the row count after a merge against your expectations. A silently duplicated key on either side can multiply rows far beyond what an inner or left join 'should' produce, inflating downstream sums and averages without any error being raised.

pd.merge combines two DataFrames on shared key(s) using SQL-style join semantics: inner, left, right, or outer.
'inner' keeps only matching keys; 'left'/'right' preserve one side fully; 'outer' preserves both sides fully, filling NaN for non-matches.
on specifies a shared key column name; left_on/right_on handle differently named keys; left_index/right_index join on the index.
DataFrame.join() is a convenience method built on merge, defaulting to an index-based left join.
indicator=True adds a _merge column showing whether each row came from 'left_only', 'right_only', or 'both'.
Duplicate keys on either side cause a Cartesian expansion of matching rows — always sanity-check row counts post-merge.

Practice what you learned

1. In pd.merge(left, right, how='left', on='id'), what happens to rows in `right` whose id has no match in `left`?

2. Which how value keeps every row from both DataFrames, filling NaN wherever a match is missing on either side?

3. What does passing indicator=True to pd.merge add to the result?

4. If a join key value appears 3 times in the left DataFrame and 2 times in the right DataFrame, how many rows does that key contribute to an inner merge result?

5. What is the default join type used by DataFrame.join() when combining on indices?

Was this page helpful?

Concatenating DataFrames

Topics covered

#Python#PandasNumPyStudyNotes#DataScience#MergingAndJoiningDataFrames#Merging#Joining#DataFrames#Parameter#Git#StudyNotes#SkillVeris

Merging and Joining DataFrames