Pandas: Merge and Join

Last updated: 2026-08-26

Real-world data never lives in a single table — user info in one table, order records in another, product details in yet another. Analysis requires linking them together, and that is exactly what merge does. Pandas merge implements every SQL JOIN type. In this section, we use Mermaid diagrams to illustrate the effect of all 4 join types so you can clearly understand "what gets kept from the left table and what gets kept from the right table."

⚠️ Note: The code below must be run in a local Python environment.

1. What You Will Learn

❶ The 4 merge join types
❷ on / left_on / right_on
❸ Multi-key merges
❹ suffixes and indicator
❺ validate and cross join

2. Bob's User-Order Association

(1) The Problem: Two Separate Tables, No Way to Analyze

Bob has a users table and an orders table. He wants to see "what each user bought":

PYTHON

import pandas as pd

users = pd.DataFrame({
    'user_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'Carol']
})

orders = pd.DataFrame({
    'order_id': ['O001', 'O002', 'O003'],
    'user_id': [1, 2, 2],
    'amount': [120, 85, 200]
})

# How to combine? Need to link by user_id

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) The Solution: One-Line merge

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Basic merge (Difficulty ⭐)

PYTHON

import pandas as pd

users = pd.DataFrame({
    'user_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'Carol']
})

orders = pd.DataFrame({
    'order_id': ['O001', 'O002', 'O003'],
    'user_id': [1, 2, 2],
    'amount': [120, 85, 200]
})

# Inner join (default) — only matching user_ids
result = pd.merge(users, orders, on='user_id')
print(result)
#    user_id     name order_id  amount
# 0        1    Alice     O001     120
# 1        2      Bob     O002      85
# 2        2      Bob     O003     200

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

3. The 4 Join Types

(1) Mermaid Diagram

graph TB
    subgraph Inner["inner — keep only matches"]
        I1["user_id 1: Alice+O001"]
        I2["user_id 2: Bob+O002"]
        I3["user_id 2: Bob+O003"]
    end
    subgraph Left["left — keep all left rows"]
        L1["user_id 1: Alice+O001"]
        L2["user_id 2: Bob+O002"]
        L3["user_id 2: Bob+O003"]
        L4["user_id 3: Charlie+NaN"]
        L5["user_id 4: Carol+NaN"]
    end
    subgraph Right["right — keep all right rows"]
        R1["user_id 1: Alice+O001"]
        R2["user_id 2: Bob+O002"]
        R3["user_id 2: Bob+O003"]
    end
    subgraph Outer["outer — keep all rows from both sides"]
        O1["user_id 1: Alice+O001"]
        O2["user_id 2: Bob+O002"]
        O3["user_id 2: Bob+O003"]
        O4["user_id 3: Charlie+NaN"]
        O5["user_id 4: Carol+NaN"]
    end

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Comparison of the 4 Join Types

Type	how	Left table no match	Right table no match	Row count
inner	'inner'	Dropped	Dropped	≤min(left, right)
left	'left'	Kept (filled with NaN)	Dropped	= left rows × matches
right	'right'	Dropped	Kept (filled with NaN)	= right rows × matches
outer	'outer'	Kept (filled with NaN)	Kept (filled with NaN)	≥max(left, right)

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Comparing the 4 join types (Difficulty ⭐⭐)

PYTHON

import pandas as pd

left = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

right = pd.DataFrame({
    'id': [2, 3, 4],
    'score': [85, 92, 78]
})

# Inner — only id 2, 3 (both tables have them)
print("INNER:")
print(pd.merge(left, right, on='id', how='inner'))
#    id    name  score
# 0   2     Bob     85
# 1   3 Charlie     92

# Left — all left rows, right fills NaN where no match
print("\nLEFT:")
print(pd.merge(left, right, on='id', how='left'))
#    id     name  score
# 0   1    Alice    NaN  ← no match in right
# 1   2      Bob   85.0
# 2   3  Charlie   92.0

# Right — all right rows
print("\nRIGHT:")
print(pd.merge(left, right, on='id', how='right'))
#    id    name  score
# 0   2     Bob     85
# 1   3 Charlie     92
# 2   4    NaN     78  ← no match in left

# Outer — all rows from both sides
print("\nOUTER:")
print(pd.merge(left, right, on='id', how='outer'))
#    id     name  score
# 0   1    Alice    NaN
# 1   2      Bob   85.0
# 2   3  Charlie   92.0
# 3   4     NaN    78.0

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

4. Multi-Key Merges and Different Column Names

(1) Multi-Key Merge

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Multi-key merge (Difficulty ⭐⭐)

PYTHON

import pandas as pd

sales = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South'],
    'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'revenue': [5000, 800, 2000, 600]
})

targets = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South'],
    'category': ['Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'target': [4500, 1000, 2500, 500]
})

# Merge on multiple keys
result = pd.merge(sales, targets, on=['region', 'category'])
print(result)
#   region    category  revenue  target
# 0  North  Electronics     5000    4500
# 1  North     Clothing      800    1000
# 2  South  Electronics     2000    2500
# 3  South     Clothing      600     500

# Calculate achievement rate
result['achievement'] = (result['revenue'] / result['target'] * 100).round(1)
print(result[['region', 'category', 'achievement']])

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Merging on Different Column Names

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: left_on/right_on (Difficulty ⭐⭐)

PYTHON

import pandas as pd

employees = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

salaries = pd.DataFrame({
    'employee_id': [1, 2, 3],
    'salary': [75000, 92000, 68000]
})

# Column names differ: emp_id vs employee_id
result = pd.merge(
    employees, salaries,
    left_on='emp_id', right_on='employee_id',
    how='inner'
)
print(result)
#    emp_id     name  employee_id  salary
# 0       1    Alice            1   75000
# 1       2      Bob            2   92000
# 2       3  Charlie            3   68000

# Drop redundant column
result = result.drop(columns='employee_id')

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

5. suffixes and indicator

(1) Handling Column Name Conflicts

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: suffixes for duplicate column names (Difficulty ⭐)

PYTHON

import pandas as pd

df1 = pd.DataFrame({
    'id': [1, 2],
    'value': [100, 200]
})

df2 = pd.DataFrame({
    'id': [1, 2],
    'value': [300, 400]
})

# Default suffixes: _x and _y
result = pd.merge(df1, df2, on='id')
print(result)
#    id  value_x  value_y
# 0   1      100      300
# 1   2      200      400

# Custom suffixes
result2 = pd.merge(df1, df2, on='id', suffixes=('_left', '_right'))
print(result2)
#    id  value_left  value_right

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) indicator for Diagnosing Merge Sources

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: indicator for tracking row origins (Difficulty ⭐⭐)

PYTHON

import pandas as pd

left = pd.DataFrame({'id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie']})
right = pd.DataFrame({'id': [2, 3, 4], 'score': [85, 92, 78]})

# Add _merge column to see where each row came from
result = pd.merge(left, right, on='id', how='outer', indicator=True)
print(result)
#    id     name  score      _merge
# 0   1    Alice    NaN   left_only
# 1   2      Bob   85.0        both
# 2   3  Charlie   92.0        both
# 3   4     NaN    78.0  right_only

# Filter by merge source
only_left = result[result['_merge'] == 'left_only']
print(f"Only in left table: {len(only_left)} rows")  # 1 (Alice)

# Validate data integrity — find unmatched records
unmatched = result[result['_merge'] != 'both']
print(f"Unmatched records: {len(unmatched)}")  # 2

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

6. validate and Cross Join

(1) validate for Verifying Merge Relationships

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: validate verification (Difficulty ⭐⭐)

PYTHON

import pandas as pd

users = pd.DataFrame({'user_id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie']})
orders = pd.DataFrame({'order_id': ['O1', 'O2', 'O3'], 'user_id': [1, 2, 2]})

# Validate: each user should have at most one order (1:1)
# This will FAIL because user 2 has 2 orders
try:
    result = pd.merge(users, orders, on='user_id', validate='1:1')
except Exception as e:
    print(f"Validation failed: {e}")

# Validate: each user can have many orders (1:m) — passes
result = pd.merge(users, orders, on='user_id', validate='1:m')
print(result)  # OK

# Validation types: '1:1', '1:m', 'm:1', 'm:m'

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

(2) Cross Join

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: cross join (Difficulty ⭐)

PYTHON

import pandas as pd

colors = pd.DataFrame({'color': ['Red', 'Blue']})
sizes = pd.DataFrame({'size': ['S', 'M', 'L']})

# Every combination (cartesian product)
result = pd.merge(colors, sizes, how='cross')
print(result)
#   color size
# 0   Red    S
# 1   Red    M
# 2   Red    L
# 3  Blue    S
# 4  Blue    M
# 5  Blue    L
# 6 rows = 2 colors × 3 sizes

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

7. merge vs join

Feature	merge	join
Call style	`pd.merge(df1, df2)`	`df1.join(df2)`
Default join key	Column specified by on	Index
Multi-column join	✅ on=['a','b']	❌ Index only
Join types	inner/left/right/outer	left (default)
Flexibility	High	Low
Best for	General-purpose joins	Index alignment

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: join shortcut (Difficulty ⭐)

PYTHON

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2, 3]}, index=['x', 'y', 'z'])
df2 = pd.DataFrame({'B': [4, 5]}, index=['x', 'y'])

# join uses Index by default
result = df1.join(df2)  # how='left' by default
print(result)
#    A    B
# x  1  4.0
# y  2  5.0
# z  3  NaN  ← left join keeps all left rows

# join with how parameter
result2 = df1.join(df2, how='inner')

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

8. Full Example: Three-Table Join Analysis

▶ Example

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

: Three-table merge workflow (Difficulty ⭐⭐⭐)

PYTHON

import pandas as pd

# ============================================
# Comprehensive example: 3-table merge
# users + orders + products → full analysis
# ============================================

# 1. Users table
users = pd.DataFrame({
    'user_id': [1, 2, 3, 4, 5],
    'name': ['Alice', 'Bob', 'Charlie', 'Carol', 'David'],
    'region': ['North', 'South', 'East', 'North', 'West']
})

# 2. Orders table
orders = pd.DataFrame({
    'order_id': ['O001', 'O002', 'O003', 'O004', 'O005', 'O006'],
    'user_id': [1, 2, 2, 3, 1, 5],
    'product_id': ['P01', 'P02', 'P03', 'P01', 'P04', 'P02'],
    'quantity': [2, 1, 3, 1, 5, 2]
})

# 3. Products table
products = pd.DataFrame({
    'product_id': ['P01', 'P02', 'P03', 'P04', 'P05'],
    'product_name': ['Laptop', 'Phone', 'Tablet', 'Mouse', 'Keyboard'],
    'price': [999, 699, 349, 29, 79]
})

# Step 1: orders + products → order details with price
order_details = pd.merge(orders, products, on='product_id', how='left')
order_details['total'] = order_details['quantity'] * order_details['price']

# Step 2: order_details + users → full picture
full = pd.merge(order_details, users, on='user_id', how='left')

# Step 3: Analyze by region
region_sales = full.groupby('region')['total'].sum().sort_values(ascending=False)
print("=== Sales by Region ===")
print(region_sales)

# Step 4: Top customers
top_customers = full.groupby('name')['total'].sum().sort_values(ascending=False)
print("\n=== Top Customers ===")
print(top_customers)

# Step 5: Unmatched products (in products but never ordered)
unmatched = pd.merge(
    products, orders[['product_id']].drop_duplicates(),
    on='product_id', how='left', indicator=True
)
never_ordered = unmatched[unmatched['_merge'] == 'left_only']
print(f"\n=== Never Ordered Products ===")
print(never_ordered[['product_id', 'product_name']])

TEXT 📖 Display only

> **Output:** Run in a local Python environment (pandas 2.x). The Piston server does not have pandas pre-installed. Please install it locally (`pip install pandas`) and follow along. Actual values may vary slightly depending on the pandas version.

❓ FAQ

Q Should I use inner or left?

A Default to inner (keeps only matching rows, resulting in clean data). Use left when you need all rows from the left table (e.g., "all users regardless of whether they have orders"). right is rarely used (just swap the tables and use left instead). Use outer to find differences ("which users have no orders + which orders have no user"). In 80% of everyday scenarios, inner is the right choice.

Q What if the key column names differ between tables?

A Use left_on and right_on to specify the key column in each table separately. For example, pd.merge(a, b, left_on='emp_id', right_on='employee_id'). After merging, both columns are retained — use drop to remove the redundant one. If one key is an Index, use left_index=True / right_index=True.

Q How do I handle suffixes conflicts?

A When both tables have a column with the same name (other than the key), merge automatically appends _x and _y suffixes. Use suffixes=('_left','_right') to customize them. A best practice is to rename columns before merging to avoid ambiguity: df.rename(columns={'value': 'value_a'}).

Q What is the difference between merge and join?

A merge is more versatile — it supports column-based joins, multi-key joins, and all 4 join types. join is more concise — it joins on the Index by default and defaults to a left join. Use merge when you need multi-column joins; use join for simple Index alignment. In general, prefer merge for its completeness and treat join as a shortcut.

Q How do I verify that the merge result is correct?

A A three-step check: ① Compare row counts before and after the merge (inner should be ≤ min, left should equal left rows × matches); ② Use indicator=True to inspect the origin of each row (both/left_only/right_only); ③ Use validate='1:1' or '1:m' to assert the expected relationship — it raises an error if violated.

Q What if merge causes a row count explosion?

A A row explosion indicates an m:m (many-to-many) merge — when each key in the right table matches N rows, the result equals left rows × N. Fix: deduplicate or aggregate (groupby + agg) the right table before merging to reduce matching rows. Use validate='m:1' to detect this early.

Q When should I use cross join?

A cross join produces a Cartesian product (every row × every row), with row count = left rows × right rows. Use it only for generating combinations (e.g., colors × sizes = all SKU variants). Never use cross join on large tables — 1000 × 1000 = 1 million rows!

📖 Summary

merge has 4 join types: inner (matches only) / left (keep all left rows) / right (keep all right rows) / outer (keep everything from both sides)
Multi-key merge uses on=['key1','key2']; different column names use left_on/right_on
suffixes handles duplicate column names; indicator tracks row origins
validate verifies merge relationships (1:1 / 1:m / m:1) to prevent surprises
cross join produces a Cartesian product — use only with small tables
join is a shortcut for merge (defaults to Index + left join)
Always check row counts and indicator after merging to confirm correctness

📝 Exercises

Basic (Difficulty ⭐): Create a students table and a grades table (sharing student_id). Perform inner/left/outer merges and observe how the row count changes.
Intermediate (Difficulty ⭐⭐): Create a departments table (dept_id/dept_name) and an employees table (emp_id/dept_id/salary). Merge them, compute the average salary per department, and use indicator to find departments with no employees.
Challenge (Difficulty ⭐⭐⭐): Simulate three tables (users/orders/products) and complete the following: inner merge to join them → calculate total spending per user → left merge to find users with zero spending → validate to verify relationships → indicator to diagnose origins.

← Previous: GroupBy and Aggregation · Next: Concat and Append →

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Pandas: Merge and Join

1. What You Will Learn

2. Bob's User-Order Association

(1) The Problem: Two Separate Tables, No Way to Analyze

(2) The Solution: One-Line merge

▶ Example

3. The 4 Join Types

(1) Mermaid Diagram

(2) Comparison of the 4 Join Types

▶ Example

4. Multi-Key Merges and Different Column Names

(1) Multi-Key Merge

▶ Example

(2) Merging on Different Column Names

▶ Example

5. suffixes and indicator

(1) Handling Column Name Conflicts

▶ Example

(2) indicator for Diagnosing Merge Sources

▶ Example

6. validate and Cross Join

(1) validate for Verifying Merge Relationships

▶ Example

(2) Cross Join

▶ Example

7. merge vs join

▶ Example

8. Full Example: Three-Table Join Analysis

▶ Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好