Merge on Keys and Indexes
Joining by columns or by index.
Merge on Keys and Indexes is a free Data Science Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Data Science Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Same Name, One Word
When both tables share a column with the same name, on is all you need to merge them together.
orders.merge(customers, on="customer_id")Join on Many Keys
Sometimes one column is not enough. Pass a list to on and pandas matches rows on every key at once.
sales.merge(targets, on=["region", "month"])Different Names, No Problem
When key columns are named differently, use left_on and right_on to tell pandas which column maps to which.
orders.merge(users, left_on="uid", right_on="id")A Leftover Column
With left_on and right_on, both key columns survive in the result. You often drop the duplicate afterward.
merged.drop(columns="id")The Index Can Be a Key Too
If your key lives in the index instead of a column, set left_index or right_index to True to join on it.
orders.merge(prices, left_on="sku", right_index=True)join for Index Merges
For two index-aligned tables, the join method is shorter. It merges on the index by default.
left.join(right)Handle Overlapping Names
When both tables have a column with the same name, pandas adds suffixes so the two versions stay distinct.
a.merge(b, on="id", suffixes=("_a", "_b"))Keys Must Share a Type
A common surprise: a key stored as a string on one side and an integer on the other will simply never match.
Mind Duplicate Keys
If a key value repeats on both sides, merge produces every matching pair. Rows can multiply unexpectedly.
join Defaults to Left
Unlike merge, the join method uses a left join by default. Pass how to change it when you need to.
left.join(right, how="outer")Pick the Right Tool
Use merge for column keys with full control, and join when both tables are already aligned on their index. 🔧
Quick Check
Your key columns have different names in each table. What do you use?
Recap: Matching the Keys
You can merge on shared columns, mismatched names, or the index, and use suffixes to keep overlapping columns clear. Nicely done!
Frequently asked questions
Is the “Merge on Keys and Indexes” lesson free?
Yes — the full text of “Merge on Keys and Indexes” is free to read here on the web, and the Data Science Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Data Science Academy course, upgrade to CoddyKit PRO.
What will I learn in “Merge on Keys and Indexes”?
Joining by columns or by index. You practise Data Science Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Data Science Academy?
No prior experience is required. Data Science Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Merge on Keys and Indexes” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Data Science Academy lesson?
Yes. Every Data Science Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Inner, Left, Right, and Outer
- Merge on Keys and Indexes
- concat to Stack and Append
- Diagnose Bad Joins