merge and join

merge combines two tables by matching values in a key column — a SQL join, with pandas syntax.

orders.merge(stalls, on="city")

Our two tables don't agree on their keys, which is the interesting case. Delhi and Mumbai are in both; Pune has orders but no stall record; Jaipur has a stall but no orders. What happens to those two rows is entirely down to how=.

The four hows

how="inner"   # keys in BOTH        — the default. Pune and Jaipur dropped
how="left"    # all of the left     — Pune kept, manager is NaN
how="right"   # all of the right    — Jaipur kept, cups is NaN
how="outer"   # everything          — both kept, sorted by key

One sentence covers all four: the named side is kept in full. Left keeps left, right keeps right, outer keeps both, inner keeps neither — only the overlap.

inner being the default matters, because it's the one that silently loses rows. Check the row count after every merge:

before = len(orders)
merged = orders.merge(stalls, on="city")
print(before, "->", len(merged))

Duplicate keys multiply

The failure that catches people out. If a key appears three times on the left and twice on the right, you get six rows for it, not three. Merge two tables that both have duplicates and the result can be far larger than either input.

Rather than discovering that from a memory error, state your assumption and let pandas check it:

orders.merge(stalls, on="city", validate="many_to_one")

That says "many order rows, at most one stall row per city" — and raises immediately if it isn't true. one_to_one, one_to_many and many_to_many are the other options. It costs one argument and turns a silent data corruption into an exception.

Seeing what matched

merged = orders.merge(stalls, on="city", how="outer", indicator=True)
merged["_merge"].value_counts()
# both / left_only / right_only

indicator=True adds a column recording where each row came from. It's the fastest way to answer "which of my rows didn't find a match?", and worth reaching for whenever a merge surprises you.

Column name clashes

If both frames have a column with the same name — and it isn't the key — pandas appends _x and _y. That's how revenue_x ends up in production. Name them yourself:

orders.merge(stalls, on="city", suffixes=("_order", "_stall"))

When the keys are named differently

orders.merge(stalls, left_on="city", right_on="location")
orders.merge(stalls, on=["city", "date"])       # a composite key
orders.merge(stalls, left_index=True, right_on="city")

join is merge on the index

df.join(other) is a shorthand that matches on the index rather than a column, and — unlike merge — defaults to how="left". Two different defaults for two similar-looking methods, so it's worth being explicit either way.