You have customer data in one table and order data in another. To answer a real question, you need them side by side. This pandas merge guide shows you exactly which tool to reach for: merge for SQL (Structured Query Language) style joins (inner, left, right, outer), concat for stacking tables, and join for combining on the index.

“Pick the right join and the answer falls out in one line. Pick the wrong one and you spend the afternoon wondering where half your rows went.”

Every data analyst, eventually

Last Updated: July 2026 | Tested on: Python 3.14.6, Pandas 2.3.3 | Difficulty: Advanced | Reading Time: 22 minutes

Real data almost never lives in one tidy table. Customers sit in one table, their orders in another, the product catalog in a third. So the moment you want to answer something like “which customers from Mumbai spent over 5000 on electronics?” you have to stitch those tables back together. That stitching is what pd.merge, pd.concat, and df.join are for, and picking the wrong one is the number one reason a beginner’s row count suddenly looks weird.

Here is a quick way to feel the difference. Think of two spiral notebooks. A merge is like matching pages by a shared label: you flip to “page 10” in both notebooks and tape those two pages together. A concat is like binding the two notebooks into one fat notebook, page after page, no matching at all. A join is the same matching idea as merge, except the “page number” it matches on is the row index instead of a column. Get that picture straight and the rest of this post is just details.

A colleague named Pravin hit this last week. He had employee data in one CSV (Comma-Separated Values) file and department budgets in another, and he needed each person’s department budget next to their salary. One line of pd.merge(employees, budgets, on="dept_id") did it. Without merge he would have been writing nested loops and dictionary lookups by hand, which is slower to write and far easier to get wrong.

Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.

This diagram is the whole decision in one picture. It shows the four join types you can pass to pd.merge() through the how argument: inner (keep only keys present in both tables), left (keep every row from the left table), right (keep every row from the right table), and outer (keep everything from both, filling gaps with NaN). They map one to one onto SQL joins, so if you know INNER JOIN and LEFT JOIN from a database, you already know these. Glance at the colored boxes and you can predict, before running anything, which rows survive and where NaN will appear.

Prerequisites

You should be comfortable creating a DataFrame and selecting columns, which we covered in the Pandas introduction and the Pandas groupby tutorial. SQL knowledge helps because these joins are the same idea, but it is not required. All examples here run on Python 3.14.6 with Pandas 2.3.3 (current stable at the time of writing), where Copy-on-Write is the default, so none of these operations quietly mutate your original tables.

The 30-Second Decision Guide

Before any code, here is the fast answer. Most pandas merge decisions come down to one question: am I matching rows by a shared value, or am I stacking tables together? That single question sends you down one of three paths.

The rest of this post is just these three tools, one at a time, with the exact behavior you will see on screen. Screenshot the table above if you only have 30 seconds. If you have a few minutes, keep reading, because the difference between an inner join and a left join is where most surprises hide.

pd.merge: SQL-Style Joins

Start with pd.merge, the workhorse you will use most. Picture a school office matching two stacks of paper by roll number: the marks sheet in one hand, the fees register in the other, pairing each student up so their details sit on one line. That is exactly what merge does. You hand it two DataFrames and tell it which column they share with on=, and the how= argument decides which rows survive.

Below we run a pandas merge on an employees table and a departments table, joined on dept_id, with all four join types so you can compare them line by line. Notice that employee Pravin sits in department 30, and department 40 (Marketing) has no employees: those two mismatches are exactly what reveals the difference between the joins.

📄 merge_basics.py: the four join types, side by side

import pandas as pd

employees = pd.DataFrame({
    "emp_id": [1, 2, 3, 4],
    "name": ["Rahul", "Niranjan", "Viraj", "Pravin"],
    "dept_id": [10, 20, 10, 30]
})

departments = pd.DataFrame({
    "dept_id": [10, 20, 40],
    "dept_name": ["Engineering", "Data Science", "Marketing"]
})

print(f"Employees:\n{employees}\n")
print(f"Departments:\n{departments}\n")

# INNER JOIN: keep only dept_id values present in BOTH tables
inner = pd.merge(employees, departments, on="dept_id", how="inner")
print(f"INNER join (only matching):\n{inner}\n")

# LEFT JOIN: keep all employees, NaN where the dept has no match
left = pd.merge(employees, departments, on="dept_id", how="left")
print(f"LEFT join (all employees):\n{left}\n")

# RIGHT JOIN: keep all departments, NaN where no employee matches
right = pd.merge(employees, departments, on="dept_id", how="right")
print(f"RIGHT join (all departments):\n{right}\n")

# OUTER JOIN: keep everything from both, NaN where there is no match
outer = pd.merge(employees, departments, on="dept_id", how="outer")
print(f"OUTER join (everything):\n{outer}")

▶ Output

Employees:
   emp_id      name  dept_id
0       1     Rahul       10
1       2  Niranjan       20
2       3     Viraj       10
3       4    Pravin       30

Departments:
   dept_id     dept_name
0       10   Engineering
1       20  Data Science
2       40     Marketing

INNER join (only matching):
   emp_id      name  dept_id     dept_name
0       1     Rahul       10   Engineering
1       2  Niranjan       20  Data Science
2       3     Viraj       10   Engineering

LEFT join (all employees):
   emp_id      name  dept_id     dept_name
0       1     Rahul       10   Engineering
1       2  Niranjan       20  Data Science
2       3     Viraj       10   Engineering
3       4    Pravin       30           NaN

RIGHT join (all departments):
   emp_id      name  dept_id     dept_name
0     1.0     Rahul       10   Engineering
1     3.0     Viraj       10   Engineering
2     2.0  Niranjan       20  Data Science
3     NaN       NaN       40     Marketing

OUTER join (everything):
   emp_id      name  dept_id     dept_name
0     1.0     Rahul       10   Engineering
1     3.0     Viraj       10   Engineering
2     2.0  Niranjan       20  Data Science
3     4.0    Pravin       30           NaN
4     NaN       NaN       40     Marketing

What happened here: Same two tables, four different answers, all controlled by how. The inner join dropped Pravin (dept 30 has no matching department) and dropped Marketing (dept 40 has no employees), so only the fully matched rows remain. The left join kept all four employees and parked a NaN in dept_name for Pravin. The right join flipped that, keeping all three departments and showing NaN for Marketing’s missing employee.

The outer join is the union: everybody, with NaN wherever a side had nothing to offer. One thing that trips people up: in the right and outer results, emp_id shows up as 1.0 instead of 1. That is not a bug. The moment a column has to hold a NaN, pandas widens that integer column to float, because the classic integer type has no way to store “missing”.

Which one should you default to? In day-to-day analysis, the left join is the safe choice. You usually have a main table (your employees, your customers, your transactions) and you want to enrich it with lookup data without losing any of your original rows. A left join guarantees your row count stays the same. An inner join silently drops unmatched rows, which is occasionally what you want and frequently a nasty surprise. And once a left join leaves NaN gaps behind, filling or dropping them with fillna and dropna is exactly what the Pandas data cleaning tutorial covers.

When the keys have different names

Real tables rarely agree on column names. One CSV calls it dept_id, the other calls it department_id. You do not have to rename anything: tell merge which column to use on each side with left_on and right_on.

📄 left_on_right_on.py: matching columns that have different names

import pandas as pd

employees = pd.DataFrame({"name": ["Rahul", "Niranjan"], "dept_id": [10, 20]})
# Right table calls the key "department_id", not "dept_id"
departments = pd.DataFrame({
    "department_id": [10, 20],
    "dept_name": ["Engineering", "Data Science"],
})

# Tell merge which column on each side to line up
result = pd.merge(
    employees, departments,
    left_on="dept_id", right_on="department_id",
)
print(result)

▶ Output

       name  dept_id  department_id     dept_name
0     Rahul       10             10   Engineering
1  Niranjan       20             20  Data Science

What happened here: The rows matched correctly even though the key columns had different names. The only cost is that you now carry both dept_id and department_id in the result, since merge keeps both. A quick result.drop(columns="department_id") cleans that up.

Matching on more than one column

Sometimes a single column is not enough to identify a row. Sales by city and by month, for example, need both values to line up. Pass a list to on= and a row only joins when every column in that list matches, exactly like a SQL join with two conditions glued together by AND.

📄 multi_key_merge.py: a row joins only when city AND month match

import pandas as pd

# Sales recorded per city AND per month
sales = pd.DataFrame({
    "city": ["Pune", "Pune", "Mumbai"],
    "month": ["Jan", "Feb", "Jan"],
    "revenue": [40000, 52000, 61000],
})

# Targets are also set per city AND per month
targets = pd.DataFrame({
    "city": ["Pune", "Pune", "Mumbai"],
    "month": ["Jan", "Feb", "Jan"],
    "target": [38000, 50000, 65000],
})

# A row matches only when BOTH city and month line up
report = pd.merge(sales, targets, on=["city", "month"])
report["hit_target"] = report["revenue"] >= report["target"]
print(report)

▶ Output

     city month  revenue  target  hit_target
0    Pune   Jan    40000   38000        True
1    Pune   Feb    52000   50000        True
2  Mumbai   Jan    61000   65000       False

What happened here: Pune appears twice in both tables, but the join did not get confused, because city alone is not the key. Only when city and month both agree do the rows pair up. With one combined table in hand, the hit_target comparison is a one-liner. This multi-column pattern is everywhere in real analytics: order id plus line number, user id plus date, product plus region.

Handling overlapping column names

What if both tables have a column with the same name that is not the join key? Say a mid-term and a final-term table that both have a score column. Pandas refuses to silently overwrite one, so it tacks on suffixes. The default suffixes are _x and _y, which are useless six months later when you reread your code. Name them yourself.

📄 suffixes.py: rename clashing columns so they make sense

import pandas as pd

# Both tables have a "score" column that is NOT a join key
mid_term = pd.DataFrame({"name": ["Rahul", "Vinay"], "score": [72, 65]})
final_term = pd.DataFrame({"name": ["Rahul", "Vinay"], "score": [88, 91]})

# Default: pandas tacks on _x and _y, which tells you nothing
default = pd.merge(mid_term, final_term, on="name")
print(f"Default suffixes:\n{default}\n")

# Better: name the suffixes yourself so the columns make sense
clear = pd.merge(
    mid_term, final_term, on="name", suffixes=("_mid", "_final")
)
print(f"Custom suffixes:\n{clear}")

▶ Output

Default suffixes:
    name  score_x  score_y
0  Rahul       72       88
1  Vinay       65       91

Custom suffixes:
    name  score_mid  score_final
0  Rahul         72           88
1  Vinay         65           91

What happened here: Both runs joined the same rows. The only difference is the column labels. score_x and score_y are a guessing game, while score_mid and score_final read like English. The suffixes tuple is one of those tiny habits that makes your future self stop cursing your past self.

pd.concat: Stacking DataFrames

Merge matches rows. pd.concat does something simpler: it stacks tables together without trying to match anything. Think of pouring two buckets of rows into one bucket. The most common use is gluing together pieces that already share the same columns, like a January report and a February report.

📄 concat_examples.py: stack by rows (axis=0) or by columns (axis=1)

import pandas as pd

# Vertical stacking (axis=0): same columns, different rows
q1 = pd.DataFrame({"product": ["A", "B"], "sales": [100, 200]})
q2 = pd.DataFrame({"product": ["A", "B"], "sales": [150, 250]})

stacked = pd.concat([q1, q2], ignore_index=True)
print(f"Vertical stack:\n{stacked}\n")

# Use keys to remember which table each row came from
keyed = pd.concat([q1, q2], keys=["Q1", "Q2"])
print(f"With keys:\n{keyed}\n")

# Horizontal stacking (axis=1): same rows, different columns
names = pd.DataFrame({"name": ["Rahul", "Viraj"]})
scores = pd.DataFrame({"score": [95, 88]})
combined = pd.concat([names, scores], axis=1)
print(f"Horizontal stack:\n{combined}")

▶ Output

Vertical stack:
  product  sales
0       A    100
1       B    200
2       A    150
3       B    250

With keys:
     product  sales
Q1 0       A    100
   1       B    200
Q2 0       A    150
   1       B    250

Horizontal stack:
    name  score
0  Rahul     95
1  Viraj     88

What happened here: The first concat poured q2’s rows right under q1’s. The ignore_index=True renumbered the result 0 to 3, otherwise you would see the original 0, 1, 0, 1 indexes repeated, which is almost never what you want. The keys=["Q1", "Q2"] version is a neat trick: it adds an outer index label so you can still tell which quarter each row came from after stacking. The last example uses axis=1 to bolt columns on side by side instead of stacking rows.

A warning on that one: axis=1 lines rows up purely by position and index, not by any key, so if your two frames are not already aligned row for row, you will get mismatched data. When in doubt for column combining, prefer merge, which matches on a real key.

df.join: Combining on the Index

The third tool, df.join, is really just merge with a different default. Think of two attendance sheets that both list roll numbers down the left margin: line them up by roll number and every row snaps into place without you pointing at any column. Where merge matches on columns, join matches on the row index. That is its whole reason to exist: when two tables already use the same meaningful index (an employee id, a timestamp, a ticker symbol), join lets you combine them without naming a key at all.

📄 index_join.py: combine two frames that share a row index

import pandas as pd

# Both frames are indexed by the employee id
employees = pd.DataFrame(
    {"name": ["Rahul", "Niranjan", "Viraj"], "dept_id": [10, 20, 10]},
    index=[101, 102, 103],
)

salaries = pd.DataFrame(
    {"salary": [90000, 85000, 78000]},
    index=[101, 102, 103],
)

# join lines them up by index, no "on=" needed
joined = employees.join(salaries)
print(joined)

▶ Output

         name  dept_id  salary
101     Rahul       10   90000
102  Niranjan       20   85000
103     Viraj       10   78000

What happened here: No on= argument anywhere, yet the salary landed next to the right person. That is because both frames carry the same index (101, 102, 103), and join uses the index as the key by default. Under the hood, join simply calls merge for you with left_index=True, right_index=True, so it is not a separate engine, just a friendlier shortcut for the index case. If your data is keyed by a normal column rather than the index, skip join and use merge; you will fight the tool less.

Real-World Scenarios

Rules are easier to remember when they are attached to a real task. Here are three concrete situations and whether a pandas merge, a concat, or a join is the right call for each one.

• Enriching transactions with customer details (you have orders, you want each order’s city and name): pd.merge(orders, customers, on="cust_id", how="left"). Left join, because you must not lose a single order.
• Combining 12 monthly CSV files into one year (same columns, just more rows): pd.concat(monthly_frames, ignore_index=True). No matching needed, just stack.
• Attaching a precomputed feature indexed by user id (both tables already indexed by the same id): users.join(features). Index to index, so join is the cleanest.

Let us walk the first scenario all the way through, since it is the one you will write most often. We have an orders table and a customers table, and we want to answer a specific business question: which Mumbai customers spent more than 5000 on electronics? Merge first to get everything in one frame, then filter.

📄 customer_orders.py: merge two tables, then answer one question

import pandas as pd

customers = pd.DataFrame({
    "cust_id": [1, 2, 3],
    "name": ["Rahul", "Niranjan", "Vinay"],
    "city": ["Mumbai", "Pune", "Mumbai"],
})

orders = pd.DataFrame({
    "order_id": [501, 502, 503, 504],
    "cust_id": [1, 1, 3, 2],
    "category": ["electronics", "books", "electronics", "electronics"],
    "amount": [6200, 800, 7400, 3100],
})

# Attach each order's customer, then filter to the question we care about
full = pd.merge(orders, customers, on="cust_id", how="inner")
answer = full[
    (full["city"] == "Mumbai")
    & (full["category"] == "electronics")
    & (full["amount"] > 5000)
]
print(answer[["name", "city", "category", "amount"]])

▶ Output

    name    city     category  amount
0  Rahul  Mumbai  electronics    6200
2  Vinay  Mumbai  electronics    7400

What happened here: The merge turned two separate tables into one frame where every order already knows its customer’s name and city. After that, the question is a plain boolean filter. Niranjan’s electronics order was only 3100, so it dropped out, and Rahul’s books order failed the category test. The two surviving rows keep their original positions (0 and 2), which is why the index is not 0, 1. This merge-then-filter rhythm is the bread and butter of everyday pandas work.

A note on speed: when Pandas is not enough

For tables up to a few million rows, Pandas merges are fast enough that you will not think about it. Once you push into tens of millions of rows, or you are joining several large tables in a row, merges can get slow and memory-hungry. That is the point where people reach for Polars, a newer DataFrame library (version 1.41.2 at the time of writing) built in Rust with a query optimizer that often runs joins several times faster on large data.

The mental model is identical: Polars has join and concat too. We compare the two head to head later in this series, so for now just file away that Pandas is the default and Polars is the escape hatch for big data.

Common Mistakes

Mistake 1: Duplicate keys quietly multiply your rows

This is the most expensive pandas merge bug there is, because nothing crashes. If both tables have repeated keys, merge pairs every matching row on the left with every matching row on the right. Two duplicates on each side become four rows, not two. On real data that is how a 100,000-row table balloons into millions and your totals silently double.

📄 many_to_many.py: two times two becomes four

import pandas as pd

# Both tables have the key "A" twice
left = pd.DataFrame({"key": ["A", "A"], "val_l": [1, 2]})
right = pd.DataFrame({"key": ["A", "A"], "val_r": [3, 4]})

# This produces 4 rows, not 2: every A on the left pairs with every A on the right
result = pd.merge(left, right, on="key")
print(f"Many-to-many created {len(result)} rows:")
print(result)

▶ Output

Many-to-many created 4 rows:
  key  val_l  val_r
0   A      1      3
1   A      1      4
2   A      2      3
3   A      2      4

The fix is to declare the relationship you expect with validate. If reality disagrees, merge raises a clear error instead of handing you garbage. Use "one_to_one", "one_to_many", or "many_to_one" depending on your tables.

✅ Correct: state your assumption with validate

# You expect each left key to match exactly one right row.
# If the right side has duplicates, this raises instead of multiplying.
pd.merge(left, right, on="key", validate="many_to_one")

▶ Output

pandas.errors.MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Why: The error is the feature. It catches the duplication at the source, on a tiny example, instead of three steps later when your dashboard shows double revenue and you have no idea why. Add validate to any merge where the row count matters, which is most of them.

Mistake 2: Using an inner join when you meant a left join

The default how in pd.merge is "inner". That means any row without a match on the other side silently disappears. If your main table is the left one and you wanted to keep all of it, an inner join will quietly delete rows and your counts will be off.

🚫 Wrong: drops every employee whose department is missing

# Default how="inner": Pravin (dept 30, no match) vanishes from the result
report = pd.merge(employees, departments, on="dept_id")

✅ Correct: keep every employee, fill gaps with NaN

# how="left" keeps all of the left table; unmatched dept_name becomes NaN
report = pd.merge(employees, departments, on="dept_id", how="left")

Why: When you have a main table you must not shrink, always pass how="left" explicitly. A good sanity check after any merge: compare len(result) against len(your_main_table). If they differ and you did not expect that, you used the wrong join.

Mistake 3: Assuming both tables name the key column the same way

If you pass on="dept_id" but the right table calls its column department_id, merge does not guess. It raises a KeyError right away. This one at least fails loudly, but the message confuses people because the column clearly exists, just under a different name.

▶ Output (the error you will see)

KeyError: 'dept_id'

Why: on= requires the column to exist with that exact name in both frames. When the names differ, use left_on and right_on as shown earlier, or rename one column first so the names line up. Check your_df.columns whenever a merge throws a KeyError; nine times out of ten it is a spelling or naming mismatch.

Try It Yourself

1. Left versus inner: Build a students table (with a class_id column) and a classes table where one class has no students and one student has a class_id that is not in classes. Run an inner join and a left join, and confirm by eye which rows each one keeps.
2. Multi-key merge: Make a sales table and a targets table keyed by both region and quarter. Merge them on both columns and add a column that flags whether each region beat its quarterly target.
3. Catch a bad join: Create two tables with duplicate keys on purpose, then add validate="one_to_one" to the merge and watch it raise. Fix the data so the validation passes.

Conclusion

You now have all three tools straight. Use pd.merge to match rows on a shared column with an inner, left, right, or outer join, and default to a left join whenever you have a main table you must not shrink. Use pd.concat to stack tables that already share columns, and df.join to combine tables that already share a row index. Two habits will save you the most pain: pass how="left" on purpose instead of relying on the inner default, and add validate= whenever the row count matters so a bad key blows up early instead of silently doubling your data. With those two habits in place, a pandas merge becomes a one-line answer instead of an afternoon of debugging.

Next up we put all three tools to work on a messy real-world dataset and clean it into shape from start to finish. If you want the full path from first DataFrame to machine learning, the Python + AI/ML tutorial series home lays out every post in order.

Frequently Asked Questions

Interview Questions on Pandas Merge

Interviewers rarely ask for definitions. They ask what happens in situations like these.

Q: What is the difference between how=”inner” and how=”left” in pd.merge, and which is the safer default?

An inner join keeps only the keys present in both tables and silently drops any unmatched rows. A left join keeps every row from the left table and fills unmatched right-side columns with NaN. In day-to-day analysis the left join is safer, because you usually have a main table (customers, orders) that you must not shrink, and a left join guarantees the row count stays the same.

Q: Why might an integer column show up as float (like 1.0) after a right or outer join?

The moment a column has to hold a NaN for an unmatched row, pandas widens the classic integer column to float, because the plain integer type cannot represent “missing”. You can avoid this by using the nullable integer dtype (Int64), which keeps whole numbers while still allowing NaN.

Q: How do you merge two tables when the key column has a different name on each side?

Use left_on and right_on instead of on, for example pd.merge(a, b, left_on="dept_id", right_on="department_id"). The rows match correctly, but the result carries both key columns, so a quick drop(columns="department_id") tidies it up. Passing on= with a name that does not exist in both frames raises a KeyError.

Q: When would you reach for concat instead of merge?

Use concat when you are stacking tables that already share the same columns, such as combining twelve monthly CSV files into one year with pd.concat(frames, ignore_index=True). Merge is for matching related but different columns on a key. A rule of thumb: if you are not matching on a value, you probably want concat.

Q: Your nightly job merges an orders table with a customers table, and one morning the total revenue has doubled with no code change. What do you check first?

Suspect duplicate keys on one side of the merge. When both tables have repeated values in the key column, merge pairs every matching left row with every matching right row, so two duplicates on each side become four rows and totals inflate. Check for duplicates with customers["cust_id"].duplicated().any(), and add validate="many_to_one" (or the relationship you expect) so pandas raises a MergeError instead of silently multiplying rows.

Q: A teammate named Aditi merges two DataFrames with pd.concat(axis=1) and the values end up next to the wrong rows. What went wrong?

Concat with axis=1 lines rows up purely by position and index, not by any key, so if the two frames are not already aligned row for row (or one has been filtered and has a gappy index), values land against the wrong rows. The fix is to reset both indexes first, or better, use pd.merge on a real key column so the rows are matched by value rather than by position.

Q: How is df.join related to pd.merge under the hood?

df.join is a convenience wrapper that calls merge with left_index=True and right_index=True, so it matches on the row index by default. It is the cleanest choice when both tables already share a meaningful index (an employee id, a timestamp). If your data is keyed by a normal column instead, use merge directly.

Further reading: for the full reference, see pandas official documentation.

Related Posts

Previous: Pandas: Data Transformation with apply, groupby, pivot_table

Next: Pandas Project: Clean a Messy Real-World Dataset

Series Home: Python + AI/ML Tutorial Series