join(): Index-Based Joining

When your tables are already indexed by the key (player name as the index — common after set_index or groupby), there is a shorter tool than merge: DataFrame.join(). It lines rows up by index label and defaults to a left join.

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

Definition

DataFrame.join() adds the columns of one or more other DataFrames/named Series to the calling DataFrame by matching index labels (or a column of the caller against the other's index with on=). It is a convenience wrapper around merge(left_index=True, right_index=True) with how="left" as the default.

• Calling DataFrame — the one before the dot (runs.join(...)) is the left table.
• Index labels — the keys are the row labels, not a column.
• Left by default — all rows of the caller are kept (merge's default is inner!).

Syntax

df.join(other, on=None, how="left", lsuffix="", rsuffix="", sort=False, validate=None)

Example — default left join on the index

print(runs.join(team))

        runs team
player
Kohli    741  RCB
Rohit    417   MI
Gill     890   GT
Pant     446  NaN

How it works: every row of runs is kept; team is looked up by index label. Pant has no label in team → NaN; Bumrah is ignored. Other how values behave exactly as in merge:

print(runs.join(team, how="outer"))

         runs team
player
Bumrah    NaN   MI
Gill    890.0   GT
Kohli   741.0  RCB
Pant    446.0  NaN
Rohit   417.0   MI

Example — overlapping columns need lsuffix / rsuffix

r24 = pd.DataFrame({"runs": [741, 426]}, index=["Kohli", "Gill"])
r23 = pd.DataFrame({"runs": [639, 890]}, index=["Kohli", "Gill"])
r24.join(r23)

ValueError: columns overlap but no suffix specified: Index(['runs'], dtype='str')

print(r24.join(r23, lsuffix="_2024", rsuffix="_2023"))

       runs_2024  runs_2023
Kohli        741        639
Gill         426        890

Unlike merge (which adds _x/_y automatically), join refuses to guess — you must supply suffixes.

Example — on=: a column of the caller vs the other's index

bat = pd.DataFrame({"player": ["Kohli", "Rohit", "Pant"], "runs": [741, 417, 446]})
print(bat.join(team, on="player"))

  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI
2   Pant   446  NaN

The caller keeps its normal 0, 1, 2 index; its player column is matched against team's index.

Example — several frames (and Series) at once

sixes = pd.DataFrame({"sixes": [38, 23, 33]}, index=["Kohli", "Rohit", "Gill"])
print(runs.join([team, sixes]))

matches = pd.Series([15, 14, 17], index=["Kohli", "Rohit", "Gill"], name="matches")
print(runs.join(matches))

         runs team  sixes
player
Kohli   741.0  RCB   38.0
Rohit   417.0   MI   23.0
Gill    890.0   GT   33.0
Pant    446.0  NaN    NaN
        runs  matches
player
Kohli    741     15.0
Rohit    417     14.0
Gill     890     17.0
Pant     446      NaN

A list joins everything in one call (here runs happened to come back as float). A Series must have a name — it becomes the new column's name.

Notebook 10's example — default RangeIndex

df1 = pd.DataFrame({"Name": ["Aman", "Rohit"], "Marks": [80, 85]})
df2 = pd.DataFrame({"City": ["Delhi", "Mumbai"]})
print(df1.join(df2))

    Name  Marks    City
0   Aman     80   Delhi
1  Rohit     85  Mumbai

Both frames have the default index 0, 1, so row 0 meets row 0. This only works because both are in the same order — with real data, join on a meaningful index (or merge on a key column).

join vs merge

runs.join(team, how="left") equals runs.merge(team, left_index=True, right_index=True, how="left").

Common mistakes

• ❌ Calling join on frames with a default 0..n-1 index and assuming it matches by player. ✅ set_index("player") first, or use merge(on="player").
• ❌ Forgetting lsuffix/rsuffix with overlapping columns → ValueError. ✅ Supply both.
• ❌ Expecting join to be inner like merge. ✅ join defaults to how="left".
• ❌ Joining an unnamed Series. ✅ Give it a name (s.rename("matches")).

Interview tip

Q: When would you choose join over merge? A: When the key is already the index on both sides (e.g. results of groupby), or you want to attach several indexed tables in one call. join is index-based and left by default; merge is column-based, inner by default, and offers indicator.