concat vs merge vs join

You now know three ways to combine DataFrames. They overlap a little, which makes it easy to pick the wrong one — and the wrong one often runs without an error but gives wrong numbers. This topic puts them side by side (the summary table in notebook 12 does the same).

Definition

pd.concat stacks objects along an axis (rows under rows, or columns beside columns) aligning on labels; merge matches rows by the values of key columns (database join); join matches rows by index labels (a merge shortcut for indexed data).

In one line each:

• concat — "put these pieces together" (same kind of data, more rows).
• merge — "look up information from another table using a key".
• join — "attach columns from tables that share my index".

Comparison table

Decision guide

 Do the tables have the SAME columns and you want MORE ROWS?
   └─ yes → pd.concat([a, b], ignore_index=True)
   └─ no  → you want MORE COLUMNS. Is there a key that identifies rows?
              ├─ key is a COLUMN (player, id, team)   → a.merge(b, on="key", how=...)
              ├─ key is the INDEX on both sides        → a.join(b, how=...)
              └─ no key, rows already in same order    → pd.concat([a, b], axis=1)
                                                          (only if you are SURE of the order)

Example — the same goal, three tools

runs = pd.DataFrame({"player": ["Kohli", "Gill", "Faf"], "runs": [639, 890, 730]})
sixes = pd.DataFrame({"player": ["Gill", "Faf", "Kohli"], "sixes": [33, 31, 24]})   # different order!

print(pd.concat([runs, sixes], axis=1))                 # WRONG: aligned by position/label
print(runs.merge(sixes, on="player"))                    # right: matched by key
print(runs.set_index("player").join(sixes.set_index("player")))   # right: matched by index

  player  runs player  sixes
0  Kohli   639   Gill     33
1   Gill   890    Faf     31
2    Faf   730  Kohli     24
  player  runs  sixes
0  Kohli   639     24
1   Gill   890     33
2    Faf   730     31
        runs  sixes
player
Kohli    639     24
Gill     890     33
Faf      730     31

How it works: concat(axis=1) glued row 0 to row 0 — Kohli got Gill's 33 sixes and the result even has two player columns. merge and join matched by player, so they are correct regardless of order.

Classic mistakes

1. Duplicate keys exploding rows. If the lookup table repeats a key, every match is duplicated:

players = pd.DataFrame({"player": ["Kohli", "Gill"], "runs": [741, 426]})
teams = pd.DataFrame({"player": ["Kohli", "Kohli", "Gill"], "team": ["RCB", "RCB", "GT"]})   # Kohli twice
m = players.merge(teams, on="player", how="left")
print(m)
print("total runs before:", players["runs"].sum(), "after:", m["runs"].sum())

  player  runs team
0  Kohli   741  RCB
1  Kohli   741  RCB
2   Gill   426   GT
total runs before: 1167 after: 1908

Kohli's 741 is now counted twice. ✅ Fix: teams.drop_duplicates("player"), and guard with validate="many_to_one".

2. Losing rows with the default inner merge. ✅ Use how="left" when the left table is your "master" list, and check indicator=True.

3. Using concat(axis=1) instead of a key-based merge. ✅ Only use it when the index already identifies the rows (then it aligns correctly).

4. Forgetting ignore_index=True after stacking rows. ✅ Duplicate labels make .loc return several rows.

5. Key mismatches — spaces, capital letters, int vs str dtypes. ✅ Clean with .str.strip(), .str.title(), astype() before combining.

6. Overlapping column names → _x/_y (merge) or an error (join). ✅ Give meaningful suffixes / lsuffix+rsuffix, or drop unneeded columns first.

Sanity checklist after combining

Interview tip

Q: Explain the difference between concat, merge and join in one sentence each. A: concat stacks DataFrames along rows or columns by aligning labels; merge combines two DataFrames by matching values in key columns (SQL-style, inner by default); join combines by index labels (left by default) and is a convenience wrapper over merge.