Bezen

Pandas · Module 14 of 22 · 5 concepts · about 6 min

Combining DataFrames

Putting frames together without quietly changing how many rows you have.

At work: Bringing together data that lives in different systems: orders with customers, clicks with campaigns, trades with prices.

Join semantics: inner, left, right, outer

What it is

how= decides which non-matching rows survive. A left join keeps every row of the left frame and fills unmatched right-hand columns with NaN.

Why it matters

Choosing inner where you meant left silently drops the rows that failed to match — frequently the exact rows worth investigating.

In the real world: Attaching customer details to every order with a left join, so orders from guest checkouts are kept rather than silently dropped.

Visual example

Joins

A left join keeps every left row; where the right side has no match, the new columns are NaN.

orders
ordercust
1c1
2c9
how="left"
customers — no c9
custname
c1Ann
left join
ordercustname
1c1Ann
2c9NaN

Rows kept by each join

how="left" → 2 rows

how="inner" → 1 row

Why this matters

The unmatched rows are usually the interesting ones — orders from customers you have no record of. An inner join hides them.

Common misconception

Wrong idea: a merge only adds columns.

Correct idea: an inner join can silently drop rows, and a left join adds NaN. Compare len() before and after.

  1. 1Match keysc1 finds Ann; c9 finds nothing.
  2. 2Keep left rowsWith how="left" every order stays.
  3. 3Fill the gapNo match means NaN in the new columns.
before = len(orders)
joined = orders.merge(customers, on="customer_id", how="left")
assert len(joined) == before          # a left join must not change the count
joined["customer_name"].isna().sum()  # how many orders had no customer row

Reading a shrunken row count as 'the join worked'. Compare counts before and after, deliberately.

Practise it: Attach Customer Names to Orders, Join Without Letting a Dirty Dimension Table Multiply Your Rows, Pad Each Store's Own Calendar, Not the Global One

Fan-out, and validate=

What it is

If the right-hand key is not unique, every duplicate multiplies the matching left rows. validate="m:1" makes Pandas raise instead of fanning out silently.

Why it matters

Fan-out is the most destructive join bug, because it inflates every downstream sum while leaving the data looking structurally fine.

In the real world: Joining orders to a customer table that accidentally lists some customers twice doubles their orders and inflates revenue. validate catches it.

Visual example

Joins

One duplicate key on the right turns one order into two — no error, just more rows.

orders — 2 rows
ordercust
1c1
2c2
merge(on="cust")
customers — c1 twice
custname
c1Ann
c1Ann
c2Bo
result — 3 rows
ordercustname
1c1Ann
1c1Ann
2c2Bo

Row counts

orders: 2 rows

after merge: 3 rows

Why this matters

The frame still looks fine, and every sum downstream is now inflated. validate= turns this into an error instead.

Common misconception

Wrong idea: a merge cannot change how many orders there are.

Correct idea: each duplicate key on the right multiplies the matching rows. validate="many_to_one" makes it raise.

orders.merge(customers, on="customer_id", how="left", validate="m:1")
# MergeError if customers has duplicate customer_id values

Trusting that a lookup table is unique because it is supposed to be. Asserting it costs nothing and turns silent corruption into an exception.

Practise it: Attach Customer Names to Orders

Joining on mismatched keys

What it is

left_on/right_on join columns with different names; on requires the same name in both. Key dtypes must match — an int64 will not join to an object of digits.

Why it matters

Dtype mismatch is the most common cause of a join that produces zero matches against data you can see lines up.

In the real world: The orders system calls it cust_id, the CRM calls it customer_id, and one stores it as text: all of it has to line up before the join works.

print(orders["customer_id"].dtype, customers["id"].dtype)   # int64 object
customers["id"] = customers["id"].astype("int64")
orders.merge(customers, left_on="customer_id", right_on="id", how="left")

Whitespace or case differences in string keys. The join reports no matches and the data looks identical on screen.

concat

What it is

Stacks frames vertically (axis=0) or side by side (axis=1), aligning on the other axis.

Why it matters

Vertical concat is how you combine partitions — a file per day, a shard per region — into one frame.

In the real world: Stacking twelve monthly sales files into one year of data for an annual report.

parts = [pd.read_parquet(p) for p in paths]
all_days = pd.concat(parts, ignore_index=True)
assert len(all_days) == sum(len(p) for p in parts)

Concatenating vertically without ignore_index=True leaves duplicate index labels, which then makes .loc return multiple rows for one label.

merge_asof and point-in-time joins

What it is

Joins on the nearest key at or before each row's key — the as-of join used to attach the value that was current at the moment of an event. Both frames must be sorted on the key.

Why it matters

It is the correct tool for building training data from a feature store: each row sees only what was knowable at its own timestamp.

In the real world: Attaching the exchange rate that applied at the moment of each payment, not today's rate, so historical revenue is stated correctly.

pd.merge_asof(
    events.sort_values("ts"),
    features.sort_values("ts"),
    on="ts",
    by="user_id",
    direction="backward",     # never look forward
    tolerance=pd.Timedelta("7D"),
)

Using a plain merge on a date column instead, which happily attaches a value computed after the event. The model then trains on the future and looks excellent offline.

Practise it: Attach Customer Names to Orders, Join Without Letting a Dirty Dimension Table Multiply Your Rows, Point-in-Time Feature Join