Stop 03 · Data Manipulation & VisualizationTopics · 5/10

Merging & Joining Datasets

In short

Training data rarely lives in one file. Users in one table, orders in another, reviews in a third. Merging is how you stitch them into one table a model can learn from.

Combining multiple DataFrames via merges and joins (inner, outer, left, right) — how you'll build a single training table from scattered data sources.

The purple blob connects matching coloured cards on two paper strips with red thread.

Think of it like this

Matching two lists by roll number

The office has an attendance list and a marks list. Both have roll numbers. To make report cards, you match each roll number on one list to the same number on the other and tie them with a thread. Unmatched names need a decision: keep them blank or drop them.

Watch it happen

students LEFT JOIN marks on roll

101

102

103

104

1/5students has rolls 101 to 104. marks only has rows for 101, 103 and 105.

The big ideas

What you need to know

Join on a key

Pick the column both tables share, like user_id. Check it has the same type and format in both.

Four kinds of join

inner keeps only matches. left keeps every left row. right keeps every right row. outer keeps everything.

Watch your row count

If one key appears many times on the right, left rows get duplicated. Compare len(df) before and after.

Let pandas check for you

validate='one_to_one' or 'many_to_one' raises an error if the keys aren't what you expect.

See it in code

Walk through the code

python

1import pandas as pd2 3students = pd.DataFrame({"roll": [101, 102, 103, 104],4                         "name": ["Asha", "Bilal", "Charu", "Dev"]})5marks = pd.DataFrame({"roll": [101, 103, 105], "marks": [88, 72, 95]})6 7inner = students.merge(marks, on="roll", how="inner")   # 2 rows8left = students.merge(marks, on="roll", how="left")     # 4 rows, 2 NaN9outer = students.merge(marks, on="roll", how="outer", indicator=True)10print(outer[["roll", "_merge"]])    # both / left_only / right_only11 12safe = students.merge(marks, on="roll", how="left", validate="one_to_one")13print(len(students), len(safe))     # 4 4

1/4Two tables that share a roll column, with some rolls on only one side.

indicator=True adds a _merge column that tells you where each row came from. It's the fastest way to see what didn't match.

Save yourself the pain

Common mistakes

Oops

Merging on a key that's an int in one table and a string in the other.

Do this instead

Convert both to the same type first with astype. Otherwise nothing matches, or pandas raises an error.

Oops

Not checking row counts, then training on accidentally duplicated rows.

Do this instead

Print len before and after every merge, and use validate when you know the relationship.

Your mission

Build a training table

~20 min

Quick check

You left-join 1,000 users with an orders table where some users have 3 orders. How many rows can the result have?

Go deeper

1Practice all four join types on two related DataFrames and inspect the row counts.
2Learn to spot join issues: duplicate keys causing row explosion.
3Merge two real datasets on a shared key end-to-end.