Learn AI with Python · Lesson

Advanced Merging and Joining

pd.merge() with how, on, left_on/right_on, suffixes, merge_asof for time-based joins.

Advanced Merging and Joining is a free Learn AI with Python lesson on CoddyKit — lesson 3 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 Learn AI with Python learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Combining DataFrames

pd.merge joins two DataFrames on shared keys, exactly like a SQL JOIN. Mastering the how options is essential for relational data work.

import pandas as pd
left = pd.DataFrame({"id": [1, 2, 3], "name": ["a", "b", "c"]})
right = pd.DataFrame({"id": [2, 3, 4], "score": [90, 80, 70]})

Inner Join (default)

how="inner" keeps only keys present in BOTH frames. It is the default.

print(pd.merge(left, right, on="id", how="inner"))
#    id name  score
# 0   2    b     90
# 1   3    c     80

Left Join

how="left" keeps every row from the left frame; unmatched right columns become NaN. The most common join in analytics.

print(pd.merge(left, right, on="id", how="left"))
#    id name  score
# 0   1    a    NaN
# 1   2    b   90.0
# 2   3    c   80.0

Outer Join

how="outer" keeps all keys from both sides, filling gaps with NaN. Use it to see the full union of records.

print(pd.merge(left, right, on="id", how="outer"))
# includes id 1 (no score) and id 4 (no name)

Joining on Different Column Names

When key columns are named differently, use left_on and right_on instead of on.

r2 = right.rename(columns={"id": "user_id"})
print(pd.merge(left, r2, left_on="id", right_on="user_id"))

Suffixes for Overlapping Columns

If both frames share a non-key column name, pandas appends _x and _y. Override with suffixes for clarity.

a = pd.DataFrame({"id": [1], "val": [10]})
b2 = pd.DataFrame({"id": [1], "val": [99]})
print(pd.merge(a, b2, on="id", suffixes=("_old", "_new")))

The indicator Column

indicator=True adds a _merge column showing the source of each row: left_only, right_only, or both. Invaluable for auditing joins.

print(pd.merge(left, right, on="id", how="outer", indicator=True))
# _merge tells you where each row came from

Diagnosing Join Problems

Filtering on the indicator quickly reveals unmatched keys, a common cause of silently dropped data.

m = pd.merge(left, right, on="id", how="outer", indicator=True)
print(m[m["_merge"] != "both"])   # rows that did not match

merge_asof for Time Joins

pd.merge_asof joins on the NEAREST key rather than an exact match. It is purpose-built for aligning time series, for example matching each trade to the latest preceding quote. Both frames must be sorted on the key.

trades = pd.DataFrame({"time": [1, 3, 7], "price": [10, 11, 12]})
quotes = pd.DataFrame({"time": [0, 2, 5], "bid": [9, 10, 11]})
print(pd.merge_asof(trades, quotes, on="time"))

merge_asof Direction

By default merge_asof looks BACKWARD (latest key at or before). Use direction="forward" or "nearest" to change the matching rule.

print(pd.merge_asof(trades, quotes, on="time", direction="nearest"))

Choosing the Right Join

Use inner to keep only matches, left to enrich a primary table, outer for the full union, and merge_asof for time-aligned (approximate-key) joins.

Quick Check

Test your join knowledge.

Recap

Merging toolkit:

how= inner / left / outer controls which keys survive
on vs left_on/right_on for key columns
suffixes disambiguate overlapping names
indicator=True audits row sources
pd.merge_asof for nearest-key time joins (sort first)

Frequently asked questions

Is the “Advanced Merging and Joining” lesson free?

Yes — the full text of “Advanced Merging and Joining” is free to read here on the web, and the Learn AI with Python 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 Learn AI with Python course, upgrade to CoddyKit PRO.

What will I learn in “Advanced Merging and Joining”?

pd.merge() with how, on, left_on/right_on, suffixes, merge_asof for time-based joins. You practise Learn AI with Python 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 Learn AI with Python?

No prior experience is required. Learn AI with Python on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Advanced Merging and Joining” 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 Learn AI with Python lesson?

Yes. Every Learn AI with Python 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

← Back to Learn AI with Python