merge(): Joining on Key Columns

One table has each batter's runs; another has each player's team. To answer "total runs per team" you need both facts in one row per player — matched by player name, not by row position. That lookup is a merge — pandas' version of a SQL JOIN (notebook 11: "merge() SQL join jaisa hota hai").

bat = pd.DataFrame({"player": ["Kohli", "Rohit", "Gill", "Pant"], "runs": [741, 417, 890, 446]})
info = pd.DataFrame({"player": ["Kohli", "Rohit", "Gill", "Bumrah"], "team": ["RCB", "MI", "GT", "MI"]})
print(bat)
print(info)

  player  runs
0  Kohli   741
1  Rohit   417
2   Gill   890
3   Pant   446
   player team
0   Kohli  RCB
1   Rohit   MI
2    Gill   GT
3  Bumrah   MI

Definition

merge() combines two DataFrames side by side by matching rows whose key column(s) have equal values, exactly like a database JOIN. Each output row is a pair of one left row and one right row that share the same key; the how argument decides what happens to rows without a match.

• Key column — the column used to match rows (player). It plays the role of an ID.
• Left / right — the first DataFrame is the left table, the second is the right table. The order matters for how="left"/"right".
• Matching values — unlike concat(axis=1), the row order does not matter: Gill in row 2 of one table matches Gill in row 0 of the other.
• Default — how="inner" (keep only keys present in both) and on= all columns with the same name in both tables.

Syntax

pd.merge(left, right, how="inner", on=None, left_on=None, right_on=None,
         left_index=False, right_index=False, sort=False,
         suffixes=("_x", "_y"), indicator=False, validate=None)
left.merge(right, ...)          # same thing as a method

Example — the simplest merge

print(pd.merge(bat, info))
print(bat.merge(info, on="player"))

  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI
2   Gill   890   GT
  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI
2   Gill   890   GT

How it works: with no on, pandas uses every column name the two tables share — here only player. It is an inner join, so only the three players in both tables survive: Pant (no team info) and Bumrah (no runs) are dropped. Always write on= explicitly — if the tables later gain another shared column (like season), the implicit merge silently changes.

 bat                    info                        result (inner)
 player runs            player team                 player runs team
 Kohli  741  <------->  Kohli  RCB                  Kohli  741  RCB
 Rohit  417  <------->  Rohit  MI                   Rohit  417  MI
 Gill   890  <------->  Gill   GT                   Gill   890  GT
 Pant   446   (no match)  Bumrah MI  (no match)     -- both dropped --

Example — keys with different names: left_on / right_on

squad = pd.DataFrame({"player": ["Kohli", "Rohit", "Gill"], "team_code": ["RCB", "MI", "GT"]})
teams = pd.DataFrame({"code": ["MI", "RCB", "GT"],
                      "team_name": ["Mumbai Indians", "Royal Challengers Bengaluru", "Gujarat Titans"]})
r = squad.merge(teams, left_on="team_code", right_on="code")
print(r)
print(r.drop(columns="code"))

  player team_code code                    team_name
0  Kohli       RCB  RCB  Royal Challengers Bengaluru
1  Rohit        MI   MI               Mumbai Indians
2   Gill        GT   GT               Gujarat Titans
  player team_code                    team_name
0  Kohli       RCB  Royal Challengers Bengaluru
1  Rohit        MI               Mumbai Indians
2   Gill        GT               Gujarat Titans

How it works: team_code (left) is matched with code (right). Both key columns are kept in the result because they have different names — drop the duplicate afterwards. Alternative: rename first, teams.rename(columns={"code": "team_code"}), then merge on="team_code".

Example — merging on several keys

a = pd.DataFrame({"season": [2023, 2023, 2024], "player": ["Kohli", "Gill", "Kohli"], "runs": [639, 890, 741]})
b = pd.DataFrame({"season": [2023, 2024, 2024], "player": ["Kohli", "Kohli", "Gill"], "sixes": [24, 38, 12]})
print(a.merge(b, on=["season", "player"], how="left"))

   season player  runs  sixes
0    2023  Kohli   639   24.0
1    2023   Gill   890    NaN
2    2024  Kohli   741   38.0

How it works: with a list, a row matches only when all keys are equal — (2023, Kohli) matches (2023, Kohli) but not (2024, Kohli). Gill 2023 has no partner (only Gill 2024 exists), so sixes is NaN.

Example — merging on the index

ii = info.set_index("player")
print(bat.merge(ii, left_on="player", right_index=True, how="left"))

  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI
2   Gill   890   GT
3   Pant   446  NaN

right_index=True uses ii's index (player names) as its key. When both keys are indexes, left_index=True, right_index=True — or the shortcut join() (covered later).

Keys must really be equal

y2 = pd.DataFrame({"player": ["Kohli ", "rohit"], "team": ["RCB", "MI"]})
print(bat.merge(y2, on="player"))
y2["player"] = y2["player"].str.strip().str.title()
print(bat.merge(y2, on="player"))

Empty DataFrame
Columns: [player, runs, team]
Index: []
  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI

"Kohli " (trailing space) and "rohit" (lower case) do not equal "Kohli"/"Rohit" — the inner merge is empty. Clean keys with the string methods (.str.strip(), .str.title()) first. Different dtypes also fail: merging an int64 id with a str id raises ValueError: You are trying to merge on int64 and str columns for key 'id'... — convert with astype.

Common mistakes

• ❌ Relying on the implicit on (all shared column names). ✅ Always pass on= (or left_on/right_on).
• ❌ Expecting unmatched rows to stay with the default merge. ✅ Default is how="inner" — use how="left" to keep every left row.
• ❌ Keys with extra spaces / different case / different dtypes → empty or partial result. ✅ Clean and convert keys before merging.
• ❌ Keeping both team_code and code after left_on/right_on. ✅ Drop one, or rename before merging.

Interview tip

Q: How is pd.merge different from pd.concat(axis=1)? A: merge matches rows by the values of key columns (like SQL JOIN), so row order does not matter. concat(axis=1) just aligns rows by index label — it is glue, not a lookup.