Pandas
Intermediate 15 min read Pandas

Merging and Joining

Combining tables like a database

pd.merge and .join combine frames on keys with inner/outer/left/right semantics, then .concat stacks by rows or columns — the join vocabulary is shared with SQL.

mergejoininnerleftconcaton keysuffixes
Prerequisites: GroupBy and Aggregation
Before we start

What you will learn

  • Choose merge vs concat by task
  • Understand join types and row-fanning
  • Spot key-alignment and duplicate-key bugs
Introduction

What is this?

pd.merge(left, right, on='user_id', how='inner') combines two frames by key columns, with how ∈ {inner, outer, left, right}. pd.concat(stack, axis=0) stacks rows or axis=1 glues columns. Merge is how facts get attached to dimensions; fan-out (duplicate keys → many rows) and mismatched names are the classic footguns.

Motivation

Why do you need it?

  • Master tables and transaction tables are never in one place — you routinely join facts to dims.
  • A correctly-named merge replaces fragile hand-keyed dict lookups.
  • SQL's JOIN vocabulary transfers wholesale — pandas is the same algebra in Python.
  • Understanding fan-out prevents silently multiplied row counts that poison every downstream stat.
Think about it

Mental model

Imagine

Equipping a list of students (facts) with their gradebook records (dims)

Think of

Merging tables on a key

You line up student_id on both sides, attach grades, and decide what to do with students lacking a row (left join keeps facts) or grades with no student (inner drops).

Mechanics

How it works

  • Merge on explicit keys: pd.merge(a, b, on='id', how='inner') matches key values.
  • how='left' keeps ALL rows of the left frame, filling unmatched right with NaN.
  • Duplicate keys multiply rows — one left row with 3 matching right rows becomes 3 rows.
  • Renaming collides: suffixes=('_x','_y') resolves same-named non-key columns.
  • concat obeys axis: axis=0 stacks records, axis=1 stacks side-by-side (positions, not keys).

Join semantics (Venn of keys).

inner
left
right
outer
fan-out risk
Hands-on

Examples

Merge and concat side by side

Merge and concat side by side
import pandas as pd
users = pd.DataFrame({'id':[1,2,3], 'name':['a','b','c']})
purch = pd.DataFrame({'id':[1,1,3], 'amt':[10,5,30]})
inner = pd.merge(users, purch, on='id', how='inner')
print(inner)               # user 1 appears TWICE (two purchases) — fan-out
stacked = pd.concat([users, pd.DataFrame({'id':[4],'name':['d']})], ignore_index=True)
print(stacked.tail(1))

User 1 bought twice, so inner produces two rows — a healthy fan-out the analyst MUST notice before counting. concat stacks records straight, ignoring keys, for append-style jobs.

Real world

In production

  • A daily job merges the user table to purchases on user_id (how='left') so every user appears with purchases attached.
  • An analytics surface left-joins a dates table to enrollment rows to flag missing registration data.
  • A data-audit pipeline compares two days' snapshots via an outer merge to find added/removed rows.
Guidelines

Best practices

  • Always specify on= and how= explicitly; defaults surprise.
  • Check for duplicates in the key before merging — df.duplicated(subset='id').any().
  • Verify row count after merge: len(inner) vs expected (fan-out check).
  • Drop stray non-key shared columns or set suffixes clearly.
Watch out

Common mistakes

  • Implicit column-name collisions producing _x/_y — or worse, silently wrong matches.
  • Forgot the key dtype mismatch (int vs str id) — merge returns empty or error.
  • Forgetting how='left' and silently dropping unmatched facts.
  • Winging concat on keys instead of merge — appending instead of aligning.
When things break

Debugging tips

  • Empty result? Print both key dtypes and value samples — mismatch is the norm.
  • Row count changed a lot? Check duplicate keys on both sides.
  • For column collisions console in a suffix and scan the schema after merge.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

A left merge keeps:

2

A one-to-many merge silently multiplies row counts (fan-out).

Go deeper

Official documentation

Merge, join, concatenate
docs
pd.merge API
docs