Pandas
Intermediate 13 min read Pandas

Joins and Concatenation

Combine tables the way SQL does — in-memory

Merging, joining and concatenating combine tables by key or by stacking — the mechanism behind enriched datasets: revenue + customers, events + entities, monthly files into one history.

mergejoinconcatleftinnerhowkeys
Prerequisites: Pandas Grouping and Aggregation
Before we start

What you will learn

  • Merge frames on keys with inner/left/outer semantics
  • Concatenate rows and columns with concat
  • Diagnose fan-out and key-collision bugs
Introduction

What is this?

pd.merge(left, right, on='key', how='inner'|'left'|'right'|'outer') glues frames by matching key columns, replicating SQL joins. join() operates on indexes. concat() stacks rows (axis=0) or columns (axis=1), honoring or ignoring indexes. Combined, they assemble every 'one big table' analyses depend on.

Motivation

Why do you need it?

  • Enriched analysis is usually two frames joined on a customer_id or campaign_id.
  • Jan+Feb+Mar files become one history with pd.concat.
  • Left joins are the safest default — you keep the population you started from.
  • Joining correctly beats VLOOKUP past tens of thousands of rows.
Think about it

Mental model

Imagine

Merging two attendance lists by student ID

Think of

pd.merge

Inner keeps only students on both lists; left keeps everyone from list A and fills blanks for B; outer keeps everyone from both. The student ID is the key — and if two students share an ID, the lists fan out into multiple rows.

Mechanics

How it works

  • merge(left, right, on='customer_id') requires shared column names; merge by left_on/right_on when they differ.
  • how='left' keeps all rows of the left frame, filling unmatched right values with NaN (a many-to-one enrich).
  • how='inner' keeps only matching keys; how='outer' keeps both sides with nulls.
  • A one-to-many merge duplicates left rows — the classic fan-out.
  • concat([a, b]) stacks rows and aligns column names; join='inner' or keys= parameterize it.
  • Merging on indexes via .join(right, on='key') is a convenience wrapper over merge.

Join semantics in one picture.

Left frame
How
Key match
Combined
Check
Hands-on

Examples

Every leave + merge the right way

Every leave + merge the right way
import pandas as pd
users = pd.DataFrame({"user_id": [1, 2, 3], "plan": ["pro", "basic", "pro"]})
events = pd.DataFrame({"user_id": [1, 1, 2], "evt": ["login", "pay", "login"]})
 
enriched = users.merge(events, on="user_id", how="left")
print(enriched)                     # user 3 appears with NaN event
print(enriched["evt"].isna().sum()) # count unmatched = 1
print(pd.concat([users.iloc[:1], users.iloc[1:]], ignore_index=True))

A left merge keeps every user, filling missing events; concat stacks sliced frames back into one history.

Real world

In production

  • A cohort analysis left-merges user attributes onto event rows so every event carries its plan at time of event.
  • A BI extract concatenates 24 hourly files into one daily grain with concat + dedupe keys.
  • A reconciliation diffs two systems by full outer join on invoice_id and flags rows present on one side only.
Guidelines

Best practices

  • Always verify row count before and after the join; fan-out is the top silent bug.
  • Prefer left joins to preserve the analysis population.
  • Validate = drop_duplicates on the key before a many-to-one assumption.
  • Name merged frames and keep suffix rules visible (validate= flag the mismatch!).
Watch out

Common mistakes

  • Assuming one-to-one when one-to-many duplicates rows quietly.
  • Merging on a non-unique key twice, compounding the blowup.
  • A key with different dtypes (int vs string '1') matching nothing.
  • concat on objects with mismatched columns silently filling NaN.
When things break

Debugging tips

  • Row explosion after merge → check key uniqueness with duplicated().any() on both sides.
  • Empty result → dtype mismatch on the key; compare dtypes explicitly.
  • Nulls suddenly appearing → that's the left-join fill signal, not data loss.
  • If two merges disagree, inspect the intersection and symmetric difference of the keys.
Interviews

Interview checkpoint

Practice

Hands-on lab

Enrich Events with User Attributes
30 min · intermediate
In progress
Objectives
  • Left-merge user attributes onto event rows
  • Detect and diagnose join fan-out
  • Concatenate monthly files into one history

Enrich Events with User Attributes

30 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

A left join preserves:

2

Row count exploded after a merge. First check:

Go deeper

Official documentation

pandas: Merge, join and concatenate
docs