Joining DataFrames with pandas merge: SQL Joins and VLOOKUP in Python

Introduction

You've got two spreadsheets sitting in front of you. One has your customer orders — order IDs, product codes, and amounts. The other has your product catalog — product codes, names, and categories. Neither spreadsheet is useful alone. To answer "Which product categories are generating the most revenue?" you need to combine them, matching rows from one table to the corresponding rows in the other.

If you've done this in Excel, you've probably used VLOOKUP. If you've worked in SQL, you know it as a JOIN. In pandas, the tool is called merge(), and it's more powerful than VLOOKUP and nearly as expressive as SQL — all within your Python workflow. Once you understand merge(), you'll stop thinking of data as isolated tables and start thinking relationally, which is one of the most valuable shifts you can make as a data analyst.

By the end of this lesson, you'll know exactly how to combine DataFrames using the most common join types, handle the messy situations that arise in real data, and debug the problems that trip up beginners. Here's what we'll cover:

What you'll learn:

• What a join is conceptually, and why it matters
• How to perform inner, left, right, and outer merges with pd.merge()
• How to merge on columns with different names (the pandas equivalent of VLOOKUP)
• How to detect and handle duplicate keys that cause row explosions
• How to merge on multiple columns simultaneously

---

Prerequisites

You'll get the most out of this lesson if you've already worked with DataFrames at a basic level. Specifically, you should be comfortable loading CSV and Excel files into pandas and exploring the resulting DataFrames, and it helps to have some familiarity with selecting and filtering data with loc, iloc, and boolean masks. If you're newer to Python in general, the Python basics article for Excel users is worth a read first.

---

The Conceptual Foundation: What Is a Join?

Before writing a single line of code, let's build the mental model correctly. A join is an operation that combines two tables by finding matching values in a shared column (called the key). Every row in the first table is checked against every row in the second table, and when the key values match, the columns from both rows are combined into a single output row.

Think of it like a company directory that has two binders. Binder A lists every employee with their employee ID and department. Binder B lists every department with its budget and location. If you want to know each employee's location, you need to look up their department ID in Binder B. That lookup — matching on department ID — is a join.

The reason different join types exist is to answer the question: what happens when a row in one table has no match in the other? Should it be dropped? Should it appear with empty values? That decision determines which join type you need.

---

Setting Up Our Working Example

Let's build two realistic DataFrames we'll use throughout this lesson. Instead of loading files, we'll create them directly so you can reproduce everything immediately.

import pandas as pd

# Orders placed by customers
orders = pd.DataFrame({
    'order_id': [1001, 1002, 1003, 1004, 1005],
    'customer_id': [201, 202, 201, 203, 204],
    'product_code': ['PRD-A', 'PRD-B', 'PRD-C', 'PRD-A', 'PRD-D'],
    'amount': [149.99, 89.50, 220.00, 149.99, 65.00]
})

# Product catalog
products = pd.DataFrame({
    'product_code': ['PRD-A', 'PRD-B', 'PRD-C', 'PRD-E'],
    'product_name': ['Wireless Headphones', 'USB-C Hub', 'Mechanical Keyboard', 'Webcam'],
    'category': ['Audio', 'Accessories', 'Peripherals', 'Accessories']
})

Print both DataFrames and study them. Notice something deliberate: the orders table has PRD-D (a product we apparently don't have details for), and the products table has PRD-E (a product that nobody has ordered). This mismatch is completely normal in real data, and the join type you choose determines how it's handled.

---

Inner Join: Keep Only the Matches

The inner join is the most common join type and the default behavior of pd.merge(). It keeps only the rows where the key column has a matching value in both tables. Rows that don't find a match on either side are dropped.

inner_result = pd.merge(orders, products, on='product_code')
print(inner_result)

Output:

   order_id  customer_id product_code  amount         product_name     category
0      1001          201        PRD-A  149.99  Wireless Headphones        Audio
1      1004          203        PRD-A  149.99  Wireless Headphones        Audio
2      1002          202        PRD-B   89.50            USB-C Hub  Accessories
3      1003          201        PRD-C  220.00  Mechanical Keyboard  Peripherals

Notice that order 1005 (with PRD-D) is gone — no matching product. And the Webcam (PRD-E) never appears because it had no orders. The inner join gave us only the 4 rows where a product code existed in both tables.

The on='product_code' argument tells pandas which column to use as the key. This works when the column has the same name in both DataFrames.

---

Left Join: Keep Everything from the Left Table

A left join keeps every row from the left (first) DataFrame, and fills in columns from the right (second) DataFrame wherever a match exists. Where there's no match, the right-side columns get filled with NaN (pandas' representation of a missing value).

This is the closest analog to VLOOKUP in Excel. You have a main table (your orders) and you're pulling in additional information from a lookup table (your products). You want to keep all your orders regardless of whether product details exist.

left_result = pd.merge(orders, products, on='product_code', how='left')
print(left_result)

Output:

   order_id  customer_id product_code  amount         product_name     category
0      1001          201        PRD-A  149.99  Wireless Headphones        Audio
1      1002          202        PRD-B   89.50            USB-C Hub  Accessories
2      1003          201        PRD-C  220.00  Mechanical Keyboard  Peripherals
3      1004          203        PRD-A  149.99  Wireless Headphones        Audio
4      1005          204        PRD-D   65.00                  NaN          NaN

Order 1005 is back! It appears with NaN in the product_name and category columns, because PRD-D has no corresponding row in the products table. This is extremely useful — you can see the missing data instead of silently losing the row.

The how='left' argument is what switches the join type. The syntax pattern is always the same: pd.merge(left_df, right_df, on='key_column', how='join_type').

---

Right Join: Keep Everything from the Right Table

A right join is the mirror image of a left join — it keeps every row from the right DataFrame and fills in NaN where the left side has no match.

right_result = pd.merge(orders, products, on='product_code', how='right')
print(right_result)

Output:

   order_id  customer_id product_code  amount         product_name     category
0    1001.0        201.0        PRD-A  149.99  Wireless Headphones        Audio
1    1004.0        203.0        PRD-A  149.99  Wireless Headphones        Audio
2    1002.0        202.0        PRD-B   89.50            USB-C Hub  Accessories
3    1003.0        201.0        PRD-C  220.00  Mechanical Keyboard  Peripherals
4       NaN          NaN        PRD-E     NaN               Webcam  Accessories

Now the Webcam (PRD-E) appears, even though it was never ordered, and the unmatched order (PRD-D) is dropped. In practice, right joins are less commonly used — most analysts prefer to rewrite them as left joins by swapping the order of the DataFrames, which feels more intuitive.

---

Outer Join: Keep Everything from Both Tables

The outer join (also called a full outer join) is the most inclusive — it keeps every row from both tables, filling in NaN wherever there's no match on either side.

outer_result = pd.merge(orders, products, on='product_code', how='outer')
print(outer_result)

Output:

   order_id  customer_id product_code  amount         product_name     category
0    1001.0        201.0        PRD-A  149.99  Wireless Headphones        Audio
1    1004.0        203.0        PRD-A  149.99  Wireless Headphones        Audio
2    1002.0        202.0        PRD-B   89.50            USB-C Hub  Accessories
3    1003.0        201.0        PRD-C  220.00  Mechanical Keyboard  Peripherals
4    1005.0        204.0        PRD-D   65.00                  NaN          NaN
5       NaN          NaN        PRD-E     NaN               Webcam  Accessories

Both unmatched rows from both sides appear. Outer joins are particularly useful for data auditing — you run an outer join between two versions of a dataset to find records that exist in one but not the other, or to find all discrepancies between two systems.

---

Merging on Columns with Different Names

In the real world, the column you're matching on won't always have the same name in both DataFrames. One table might call it product_code and the other might call it prod_id. This is exactly the problem VLOOKUP was designed to solve, and pd.merge() handles it elegantly.

Let's say our products table was exported from a different system and uses a different column name:

products_renamed = products.rename(columns={'product_code': 'prod_id'})

merged = pd.merge(
    orders,
    products_renamed,
    left_on='product_code',
    right_on='prod_id',
    how='left'
)
print(merged.columns.tolist())
# ['order_id', 'customer_id', 'product_code', 'amount', 'prod_id', 'product_name', 'category']

Notice that when you use left_on and right_on, pandas keeps both columns in the result — product_code and prod_id — even though they contain the same values. You'll typically want to drop the redundant one:

merged = merged.drop(columns=['prod_id'])

---

Merging on Multiple Columns

Sometimes a single column isn't enough to uniquely identify a match. Imagine you have monthly sales data where you need to match on both region and month together — neither column alone is unique. You can pass a list of column names to on, left_on, and right_on.

# Monthly targets by region
targets = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South'],
    'month': ['Jan', 'Feb', 'Jan', 'Feb'],
    'target': [50000, 55000, 45000, 48000]
})

# Actual sales by region and month
actuals = pd.DataFrame({
    'region': ['North', 'North', 'South', 'East'],
    'month': ['Jan', 'Feb', 'Jan', 'Jan'],
    'sales': [52000, 49000, 46500, 31000]
})

comparison = pd.merge(targets, actuals, on=['region', 'month'], how='outer')
print(comparison)

Output:

  region month   target    sales
0  North   Jan  50000.0  52000.0
1  North   Feb  55000.0  49000.0
2  South   Jan  45000.0  46500.0
3  South   Feb  48000.0      NaN
4   East   Jan      NaN  31000.0

Now you can instantly see that the South region had no February actual sales recorded, and the East region had sales but no target set. This is exactly the kind of combined analysis that would take several VLOOKUPs and manual comparisons in Excel.

---

The Row Explosion Problem: Duplicate Keys

This is the most dangerous pitfall in merging, and it doesn't always produce an error — it silently creates a result that looks plausible but is completely wrong.

When the key column has duplicate values in both DataFrames, pandas creates a row for every possible combination of matching rows. This is called a Cartesian product of the matched rows.

# What if a product appeared twice in our catalog (e.g., different suppliers)?
products_duped = pd.DataFrame({
    'product_code': ['PRD-A', 'PRD-A', 'PRD-B'],
    'product_name': ['Wireless Headphones v1', 'Wireless Headphones v2', 'USB-C Hub'],
    'category': ['Audio', 'Audio', 'Accessories']
})

exploded = pd.merge(orders, products_duped, on='product_code', how='inner')
print(exploded.shape)
# (5, 6) — we went from 4 matching orders to 5 rows!

PRD-A appears twice in orders (orders 1001 and 1004) and twice in products_duped. That's 2 × 2 = 4 rows just for PRD-A, when you'd expect 2. Your row count has inflated, and any aggregation you run on this result will be wrong.

To check for duplicates before merging:

# Safe habit: check for duplicate keys before merging
print(products['product_code'].duplicated().any())  # False = you're safe

---

Checking Your Merge Quality with indicator=True

One of the most useful but underused features of pd.merge() is the indicator parameter. Setting indicator=True adds a column called _merge that tells you, for each row, whether the match came from the left only, the right only, or both.

audit = pd.merge(orders, products, on='product_code', how='outer', indicator=True)
print(audit[['product_code', '_merge']])

Output:

  product_code      _merge
0        PRD-A        both
1        PRD-A        both
2        PRD-B        both
3        PRD-C        both
4        PRD-D   left_only
5        PRD-E  right_only

You can filter for just the unmatched rows from either side, which is a powerful data reconciliation technique:

# Orders with no product details
orphaned_orders = audit[audit['_merge'] == 'left_only']

# Products that have never been ordered
never_ordered = audit[audit['_merge'] == 'right_only']

This is especially valuable when you're reconciling data between two systems and need to report on gaps. After a merge like this, you might combine it with groupby aggregations to summarize the matched vs. unmatched records.

---

Hands-On Exercise

Let's put everything together with a realistic scenario. You work for a subscription SaaS company. Your task is to produce a report showing each customer's total spending, along with their account tier and whether they're still active.

Set up the data:

import pandas as pd

transactions = pd.DataFrame({
    'transaction_id': range(1, 9),
    'customer_id': [301, 302, 301, 303, 304, 302, 305, 301],
    'amount': [99.00, 299.00, 99.00, 149.00, 499.00, 299.00, 49.00, 99.00]
})

customers = pd.DataFrame({
    'customer_id': [301, 302, 303, 304, 306],
    'customer_name': ['Apex Corp', 'Blue Sky LLC', 'Cascade Inc', 'Delta Partners', 'Evergreen Co'],
    'tier': ['Starter', 'Professional', 'Starter', 'Enterprise', 'Professional'],
    'active': [True, True, False, True, True]
})

Your tasks:

1. Perform a left join to attach customer details to every transaction. How many rows does the result have? Are there any transactions from customers not in the customer table?

2. After joining, group by customer_name and tier to calculate total spending per customer. (Hint: use .groupby() with .agg().)

3. Run an outer join with indicator=True. Which customer IDs appear in transactions but not in the customer table? Which appear in the customer table but have no transactions?

4. Filter the joined result to show only active customers.

Expected findings: Customer 305 has a transaction but doesn't appear in the customers table (a data quality problem). Customer 306 (Evergreen Co) is in the customer table but has no transactions.

---

Common Mistakes & Troubleshooting

Mistake 1: Using on= when column names differ If you get a KeyError or a MergeError: key must be the same type in both left and right, check whether your key column has the same name in both DataFrames. Switch to left_on= and right_on= if they differ.

Mistake 2: Merging on columns with mismatched data types If one DataFrame stores IDs as integers and another stores them as strings, the merge will fail silently (no rows match) or raise an error. Always check data types with .dtypes before merging. Fix with .astype() — for example, df['customer_id'] = df['customer_id'].astype(str).

Mistake 3: Forgetting that merge produces a new DataFrame pd.merge() doesn't modify either input DataFrame. It returns a new one. Always assign the result: result = pd.merge(df1, df2, on='key').

Mistake 4: Assuming column name conflicts are handled automatically If both DataFrames have a column called date (other than the key column), pandas will keep both and rename them date_x and date_y. This is helpful but can be surprising. Use suffixes=('_orders', '_products') to give them meaningful names: pd.merge(orders, products, on='key', suffixes=('_orders', '_products')).

Mistake 5: Row explosion from duplicate keys Already covered above, but worth repeating: always validate your key column for uniqueness in the DataFrame you're merging onto. If the right-side key should be unique (like a product catalog), verify it with .duplicated().any() before merging.

---

Summary & Next Steps

You now have the full toolkit for combining DataFrames in pandas. Let's recap the core ideas:

• pd.merge() is the pandas equivalent of SQL JOINs and Excel VLOOKUP, and it's more flexible than both
• Inner join (how='inner'): keep only rows with matches in both tables — the default
• Left join (how='left'): keep all rows from the left table, fill NaN where no match exists — the closest analog to VLOOKUP
• Right join (how='right'): keep all rows from the right table
• Outer join (how='outer'): keep all rows from both tables
• Use left_on= and right_on= when key columns have different names
• Merge on multiple columns by passing a list to on=
• Always check for duplicate keys and validate row counts after merging
• Use indicator=True to audit your merge and find unmatched rows

The next natural step is to take your merged DataFrames and start aggregating them. Once you've joined your orders with product details, you'll want to answer questions like "What's the revenue by category?" — which is where groupby and aggregation comes in. You should also make sure your key columns are clean before merging — stray spaces, inconsistent capitalization, and mixed types are common problems that cleaning messy data with pandas addresses directly.

Merging is where your analysis starts to feel genuinely powerful. You stop being limited by what's in any single table and start building the composite views of your data that answer real business questions.