join() is essentially a convenience wrapper around merge() specifically optimized for the common case of combining DataFrames by their index rather than by an arbitrary column, defaulting to how='left', unlike merge()'s default of 'inner', which keeps every row of the calling DataFrame and fills in NaN for any unmatched rows from the other one. Passing the on parameter lets you join on a regular column of the calling DataFrame against the other DataFrame's index instead of index-to-index, a common pattern for attaching lookup data.

1Understanding df.join()

💡

join() defaults to how='left' and joins on the index, while merge() defaults to how='inner' and joins on columns by default — remember these different defaults, since assuming one function's behavior for the other is an easy mistake.

editor.html

import pandas as pd

df1 = pd.DataFrame({"name": ["Alice", "Bob"]}, index=[1, 2])
df2 = pd.DataFrame({"score": [90, 85]}, index=[1, 2])
print(df1.join(df2))

localhost:3000

2Practical Example

Here is a real-world application of df.join() showing how it is used in production Pandas code.

editor.html

import pandas as pd

orders = pd.DataFrame({"customer_id": [1, 2, 3], "total": [50, 30, 20]})
customers = pd.DataFrame({"name": ["Alice", "Bob"]}, index=[1, 2])
print(orders.join(customers, on="customer_id"))

localhost:3000

3Best Practices

Follow these guidelines when working with df.join():

1. Use join() specifically for the common case of combining DataFrames by their index, since it's more concise than the equivalent merge() call

2. Use merge() instead of join() when combining on regular columns rather than the index, since that's what merge() is more naturally suited for

3. Set an appropriate index before using join(), if the DataFrames aren't already indexed by the key you want to combine on

⚠️

Tip: join() defaults to how='left' and joins on the index, while merge() defaults to how='inner' and joins on columns by default — remember these different defaults, since assuming one function's behavior for the other is an easy mistake.

editor.html

import pandas as pd

df1 = pd.DataFrame({"name": ["Alice", "Bob"]}, index=[1, 2])
df2 = pd.DataFrame({"score": [90, 85]}, index=[1, 2])
print(df1.join(df2))

localhost:3000

Examples

Example 01Basic Usage

import pandas as pd

df1 = pd.DataFrame({"name": ["Alice", "Bob"]}, index=[1, 2])
df2 = pd.DataFrame({"score": [90, 85]}, index=[1, 2])
print(df1.join(df2))

Example 02Advanced Example

import pandas as pd

orders = pd.DataFrame({"customer_id": [1, 2, 3], "total": [50, 30, 20]})
customers = pd.DataFrame({"name": ["Alice", "Bob"]}, index=[1, 2])
print(orders.join(customers, on="customer_id"))

Best Practices

Use join() specifically for the common case of combining DataFrames by their index, since it's more concise than the equivalent merge() call
Use merge() instead of join() when combining on regular columns rather than the index, since that's what merge() is more naturally suited for
Set an appropriate index before using join(), if the DataFrames aren't already indexed by the key you want to combine on

Interview Question

What's the key difference in default behavior between df.join() and pd.merge() when combining two DataFrames?▼

Hint: Think about both what they join on by default, and how they handle unmatched rows by default.

join() defaults to combining DataFrames based on their index, and defaults to a left join, keeping every row of the calling DataFrame and filling in NaN for anything unmatched on the other side. merge() instead defaults to combining based on shared column names, and defaults to an inner join, keeping only rows that have a match on both sides. Both differences matter: using join() when you actually meant to combine on a column, or assuming merge()'s inner-join default when you actually wanted to keep every row, are both common sources of unexpected results.

Exercises

MediumPractice using df.join() in a real scenario.

View Solution

import pandas as pd

df1 = pd.DataFrame({"name": ["Alice", "Bob"]}, index=[1, 2])
df2 = pd.DataFrame({"score": [90, 85]}, index=[1, 2])
print(df1.join(df2))

Frequently Asked Questions

What's the key difference in default behavior between df.join() and pd.merge() when combining two DataFrames?▼

Related Functions

pd-mergepd-indexdf-index