Joining DataFrames with pd.DataFrame.join
While pd.merge is the most common approach for merging two different pd.DataFrame objects, the lesser used yet functionally similar pd.DataFrame.join method is another viable option. Stylistically, you can think of pd.DataFrame.join as a shortcut for when you want to augment an existing pd.DataFrame with a few more columns; by contrast, pd.merge defaults to treating both pd.DataFrame objects with equal importance.
How to do it
To drive home the point about pd.DataFrame.join being a shortcut to augment an existing pd.DataFrame, let’s imagine a sales table where the row index corresponds to a salesperson but uses a surrogate key instead of a natural key:
sales = pd.DataFrame(
[[1000], [2000], [4000]],
columns=["sales"],
index=pd.Index([42, 555, 9000], name="salesperson_id")
)
sales = sales.convert_dtypes(dtype_backend="numpy_nullable")
sales
sales
salesperson_id
42 ...
