Pandas Merge and Join Explained

Real datasets rarely live in one table. Customers sit in one file, orders in another, products in a third. Answering almost any interesting question means combining them, and in pandas that combining is done with merge. If you know SQL joins, merge is the same idea; if you do not, this tutorial gives you the mental model from scratch.

Merge matches rows from two DataFrames that share a value in a key column, and stitches their columns together into one wider table. The only real decision is which unmatched rows to keep, and that is what the four join types control.

Two tables and a shared key

Here are two small illustrative sample tables that share an emp_id column:

import pandas as pd

employees = pd.DataFrame({
    "emp_id": [1, 2, 3, 4],
    "name": ["Aarti", "Bhaskar", "Chitra", "Devan"],
    "dept_id": [10, 10, 20, 30],
})

departments = pd.DataFrame({
    "dept_id": [10, 20, 40],
    "dept_name": ["Sales", "Tech", "Legal"],
})

Notice dept_id 30 has no matching department, and department 40 has no employee. Those mismatches are exactly what the join types handle differently.

Inner join: keep only matches

An inner join keeps only rows whose key exists in both tables:

print(pd.merge(employees, departments, on="dept_id", how="inner"))

   emp_id     name  dept_id dept_name
0       1    Aarti       10     Sales
1       2  Bhaskar       10     Sales
2       3   Chitra       20      Tech

Devan (dept 30) drops out because there is no department 30, and Legal drops out because no employee belongs to it. Inner is the default how, and it is the right choice when you only care about fully matched records.

Left join: keep every left row

A left join keeps all rows from the left table and fills in NaN where the right table has no match:

print(pd.merge(employees, departments, on="dept_id", how="left"))

   emp_id     name  dept_id dept_name
0       1    Aarti       10     Sales
1       2  Bhaskar       10     Sales
2       3   Chitra       20      Tech
3       4    Devan       30       NaN

Devan stays, but his dept_name is NaN because department 30 does not exist. Left join is the workhorse of analysis: you keep your primary table intact and enrich it with attributes from a lookup table, accepting that some may be missing.

Right and outer joins

A right join mirrors a left join, keeping all rows from the right table. An outer join keeps everything from both sides, filling gaps on either side with NaN:

print(pd.merge(employees, departments, on="dept_id", how="outer"))

   emp_id     name  dept_id dept_name
0     1.0    Aarti       10     Sales
1     2.0  Bhaskar       10     Sales
2     3.0   Chitra       20      Tech
3     4.0    Devan       30       NaN
4     NaN      NaN       40     Legal

Now both the unmatched employee and the unmatched department appear. Outer joins are useful for reconciliation, such as finding records present in one system but not another.

Keys with different names

When the key columns are named differently in each table, use left_on and right_on:

pd.merge(orders, customers, left_on="cust_id", right_on="id", how="left")

This is common when tables come from different systems with their own naming conventions.

Merge versus concat

Merge and concat solve different problems. Merge combines tables side by side by matching a key. concat stacks tables, usually adding rows to union two datasets with the same columns:

q1 = pd.DataFrame({"month": ["Jan", "Feb"], "sales": [100, 120]})
q2 = pd.DataFrame({"month": ["Mar", "Apr"], "sales": [140, 90]})
print(pd.concat([q1, q2], ignore_index=True))

  month  sales
0   Jan    100
1   Feb    120
2   Mar    140
3   Apr     90

Reach for merge to join related information and concat to append or union datasets that share a structure.

Practical usage

The everyday pattern is a left join from a fact table to one or more dimension tables: orders enriched with customer names, product categories and region labels. Analysts often merge, then group the enriched table to answer a question the raw fact table could not, such as revenue by product category. Getting the join type right is a correctness issue, not a style choice: an inner join silently drops unmatched rows, which can quietly understate totals if you did not intend it.

Common mistakes

• Wrong join type dropping data. Using an inner join when you meant left can silently remove rows that lack a match, understating counts and sums. Default to left when you want to keep your primary table whole.
• Duplicate keys multiplying rows. If the key is not unique in the right table, each left row matches several rights and the result balloons. Check uniqueness with value_counts() before merging.
• Mismatched key types. Merging an integer id against a string "id" matches nothing. Confirm both key columns share a dtype first.
• Overlapping non-key columns. If both tables have a column of the same name that is not the key, pandas appends suffixes _x and _y. Rename or drop columns, or set the suffixes argument, to keep the output readable.

In interviews

Join questions are guaranteed in any role that touches SQL or pandas. Interviewers ask you to describe inner versus left versus outer, and often present a scenario, "keep all customers even those with no orders," expecting you to choose a left join and explain why. A classic follow-up is why a join produced more rows than the input, testing whether you understand one-to-many relationships and duplicate keys. Connecting pandas merge to SQL joins shows you understand the concept rather than one library's syntax.

Where this fits in your learning path

Merging is a pivotal skill in the data analytics path because it unlocks multi-table analysis. It pairs naturally with groupby, since you usually join first and summarize second, and with pivot tables for reshaping the combined result. If joins feel abstract, revisit the pandas introduction to solidify the DataFrame model. Combining tables confidently is a core requirement of the data analyst role.