pd.merge: Inner and Outer Joins
Merge two DataFrames on a shared key column using inner and outer joins, and understand which rows are kept or dropped.
pd.merge: Inner and Outer Joins is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is a Join?
A join combines two DataFrames by matching rows based on a shared key column — the same concept as SQL's JOIN clause. Pandas implements joins through pd.merge(). Unlike pd.concat() which simply stacks data, pd.merge() intelligently aligns rows from two different tables based on matching values in one or more columns.
The Shared Key Column
For a merge to work, both DataFrames need at least one column with shared values — the key column. For example, an orders table has a customer_id column, and a customers table also has a customer_id column. Merging on customer_id attaches customer details to each order row. Specify the key with the on parameter when both DataFrames share the same column name.
import pandas as pd
orders = pd.DataFrame({
'order_id': [1, 2, 3, 4],
'customer_id': [101, 102, 101, 103],
'amount': [250, 80, 320, 150]
})
customers = pd.DataFrame({
'customer_id': [101, 102, 104],
'name': ['Alice', 'Bob', 'Diana']
})Inner Join: Intersection of Both Tables
An inner join (the default) keeps only rows where the key value exists in both DataFrames. Rows in either table with no matching key in the other table are silently dropped. In the orders example, customer 103 and 104 have no match in the other table, so their rows are excluded from the inner join result.
# Inner join (default): only matching rows from both
result = pd.merge(orders, customers, on='customer_id', how='inner')
print(result)
# order_id customer_id amount name
# 0 1 101 250 Alice
# 1 3 101 320 Alice
# 2 2 102 80 Bob
# Order 4 (customer 103) dropped - no match in customers
# Diana (customer 104) dropped - no match in ordersOuter Join: Union of Both Tables
An outer join (how='outer') keeps all rows from both DataFrames. Where a row has no matching key in the other table, the missing columns are filled with NaN. This is useful when you want a complete picture of both datasets, preserving records that did not find a match.
result = pd.merge(orders, customers, on='customer_id', how='outer')
print(result)
# order_id customer_id amount name
# 0 1.0 101 250.0 Alice
# 1 3.0 101 320.0 Alice
# 2 2.0 102 80.0 Bob
# 3 4.0 103 150.0 NaN <- order with unknown customer
# 4 NaN 104 NaN Diana <- customer with no ordersMerging on Columns with Different Names
When the key column has a different name in each DataFrame, use left_on and right_on instead of on. Pandas matches rows where left_on in the left DataFrame equals right_on in the right DataFrame. Both key columns appear in the result; you can drop the redundant one afterward.
products = pd.DataFrame({
'prod_id': [10, 20, 30],
'name': ['Widget', 'Gadget', 'Doohickey']
})
order_lines = pd.DataFrame({
'item_id': [10, 10, 20],
'qty': [3, 1, 5]
})
result = pd.merge(order_lines, products,
left_on='item_id', right_on='prod_id')
print(result.drop(columns='prod_id'))
# item_id qty name
# 0 10 3 Widget
# 1 10 1 Widget
# 2 20 5 GadgetMerging on Multiple Columns
To match rows on a combination of columns (a compound key), pass a list to on. This is common when a single column is not sufficient for a unique match, such as merging on both year and region. All listed columns must match simultaneously for a row to be included in the result.
targets = pd.DataFrame({
'year': [2023, 2023, 2024],
'region': ['East', 'West', 'East'],
'target': [100, 150, 120]
})
actuals = pd.DataFrame({
'year': [2023, 2024, 2024],
'region': ['East', 'East', 'West'],
'actual': [95, 115, 80]
})
result = pd.merge(targets, actuals, on=['year', 'region'], how='inner')
print(result)
# year region target actual
# 0 2023 East 100 95
# 1 2024 East 120 115Handling Overlapping Column Names
When both DataFrames have a column with the same name (other than the key), Pandas appends suffixes to distinguish them. The defaults are _x (left) and _y (right). You can customise these with the suffixes parameter to produce more meaningful names and avoid confusion in the result.
df_a = pd.DataFrame({'id': [1, 2], 'value': [10, 20]})
df_b = pd.DataFrame({'id': [1, 2], 'value': [100, 200]})
# Default suffixes
print(pd.merge(df_a, df_b, on='id'))
# id value_x value_y
# Custom suffixes
print(pd.merge(df_a, df_b, on='id', suffixes=('_budget', '_actual')))
# id value_budget value_actualChecking for Unexpected Duplicates
A common merge pitfall is an unexpected row multiplication. If the key column has duplicate values in both DataFrames, the merge produces a cartesian product of all matching rows. Always check the row count after a merge: if it's larger than you expected, investigate duplicates in the key column with df.duplicated(subset=['key']).sum().
# Both tables have duplicate keys -> cartesian product
left = pd.DataFrame({'id': [1, 1], 'val_l': ['a', 'b']})
right = pd.DataFrame({'id': [1, 1], 'val_r': ['x', 'y']})
result = pd.merge(left, right, on='id')
print(len(result)) # 4 rows! (2x2 cartesian product)
print(result)
# id val_l val_r
# 0 1 a x
# 1 1 a y
# 2 1 b x
# 3 1 b yvalidate Parameter for Safety
Pass the validate parameter to enforce relationship constraints on the merge and raise an error if they are violated. 'one_to_one' requires unique keys in both tables, 'one_to_many' requires unique keys only in the left table, and 'many_to_one' only in the right. This is an excellent defensive coding practice that catches data quality issues early.
# Safe merge: validate that customer_id is unique in customers
try:
result = pd.merge(orders, customers,
on='customer_id',
how='inner',
validate='many_to_one')
print('Merge OK, shape:', result.shape)
except Exception as e:
print('Merge error:', e)Checking Coverage with indicator
Pass indicator=True to add a _merge column to the result that shows where each row came from: 'left_only', 'right_only', or 'both'. This is useful after an outer join to identify which records found a match and which did not, helping you audit data quality before dropping the indicator column.
result = pd.merge(orders, customers, on='customer_id',
how='outer', indicator=True)
print(result['_merge'].value_counts())
# both 3
# left_only 1 <- orders with no customer record
# right_only 1 <- customers with no orders
# Find unmatched orders
print(result[result['_merge'] == 'left_only'])Inner vs Outer Summary
To summarise the two join types: inner join gives the intersection — only rows with matching keys in both DataFrames. Outer join gives the union — all rows from both DataFrames with NaN where there is no match. For completeness: left join preserves all rows from the left DataFrame, and right join preserves all rows from the right DataFrame. These are covered in the next lesson.
# Quick reference
# how='inner' -> intersection: only matched rows
# how='outer' -> union: all rows from both, NaN for no match
# how='left' -> all left rows + matched right rows
# how='right' -> all right rows + matched left rows
# Most common: inner (default) for enrichment, left for preserving recordsQuick Check
Test your understanding of pd.merge inner and outer joins from this lesson.
Lesson Recap
In this lesson you learned: pd.merge() with how='inner' keeps only matching rows from both DataFrames, while how='outer' keeps all rows with NaN for non-matches; on specifies the shared key, while left_on/right_on handle different column names; and the indicator parameter helps audit which rows matched. Next up we explore left and right joins for preserving all rows from one side.
Frequently asked questions
Is the “pd.merge: Inner and Outer Joins” lesson free?
Yes — the full text of “pd.merge: Inner and Outer Joins” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.
What will I learn in “pd.merge: Inner and Outer Joins”?
Merge two DataFrames on a shared key column using inner and outer joins, and understand which rows are kept or dropped. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Pandas & NumPy Academy?
No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “pd.merge: Inner and Outer Joins” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Pandas & NumPy Academy lesson?
Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- pd.concat for Stacking DataFrames
- pd.merge: Inner and Outer Joins
- Left and Right Joins
- Joining on the Index