Stop 06 · Python for Data AnalysisTopics · 6/12

Merging & Joining DataFrames

In short

Orders live in one file, customers in another. The interesting questions need both. A merge joins them, and one wrong setting can quietly double your revenue.

Pandas' equivalent of SQL joins — combining two DataFrames on a shared key using pd.merge(), with the same inner/left/right/outer logic from Phase 3.

The jaadu matches cards from two stacks into pairs on a table.

Think of it like this

Hostel room list meets mess fee list

The warden has a room list by roll number. The mess has a fee list by roll number. Match them on roll number and you see who lives where and who has paid. A student missing from the fee list still shows up, with 'fees' left blank. That's a left join.

Watch it happen

Left merge of orders onto customers

1/5These are customer IDs in the customers table. This is the left side, so every one stays.

The big ideas

What you need to know

on= is the shared key

The column both tables have, like customer_id. Use left_on and right_on if names differ.

how= is the join type

inner keeps matches only, left keeps all left rows, outer keeps everything. Same logic as SQL.

Watch for duplicate keys

If the right table has a key twice, left rows get copied. That's how revenue doubles by mistake.

validate= catches it

validate="many_to_one" raises an error if the right side has repeated keys. Cheap insurance.

See it in code

Walk through the code

python

1import pandas as pd2 3merged = pd.merge(4    orders,5    customers,6    on="customer_id",7    how="left",8    validate="many_to_one",9    indicator=True,10)11 12print(len(orders), len(merged))13print(merged["_merge"].value_counts())

1/4Orders on the left, customers on the right, matched on customer_id. Every order is kept.

Like SELECT ... FROM orders LEFT JOIN customers USING (customer_id), with two safety checks added.

Save yourself the pain

Common mistakes

Oops

Merging and summing revenue without checking row counts.

Do this instead

Compare len() before and after. If it grew, a key is duplicated on the right side.

Oops

Joining on a key that is int in one table and text in the other.

Do this instead

Make both the same type first, or the merge finds zero matches and you won't get an error.

Your mission

Join and verify

~30 min

Quick check

orders has 1,000 rows. After a left merge with customers you get 1,040. What most likely happened?

Go deeper

1Practice a left merge between two related DataFrames (e.g. orders and customers).
2Compare the Pandas merge syntax directly to the SQL JOIN you'd write for the same result.
3Learn to check for unexpected row-count changes after a merge (a common bug source).