How to Use Join in pandas: Syntax, Examples, and Tips

In pandas, you use DataFrame.join() to combine two DataFrames based on their index or a key column. It merges columns from another DataFrame into the calling DataFrame, aligning rows by index or specified keys.

Syntax

The basic syntax of DataFrame.join() is:

• other: The DataFrame to join with.
• on: Column or index level name(s) to join on (optional).
• how: Type of join - 'left', 'right', 'outer', or 'inner' (default is 'left').
• lsuffix and rsuffix: Suffixes to apply to overlapping column names.

python

DataFrame.join(other, on=None, how='left', lsuffix='', rsuffix='', sort=False)

Example

This example shows how to join two DataFrames by their index using join(). It adds columns from df2 to df1 based on matching index values.

python

import pandas as pd

# Create first DataFrame
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2']}, index=[0, 1, 2])

# Create second DataFrame
df2 = pd.DataFrame({'C': ['C0', 'C1', 'C2'], 'D': ['D0', 'D1', 'D2']}, index=[1, 2, 3])

# Join df2 to df1 on index (default how='left')
result = df1.join(df2)

print(result)

Output

    A   B    C    D
0  A0  B0  NaN  NaN
1  A1  B1   C0   D0
2  A2  B2   C1   D1

Common Pitfalls

Common mistakes when using join() include:

• Not aligning indexes properly, which leads to many NaN values.
• Forgetting to set how parameter correctly, causing unexpected rows to appear or disappear.
• Overlapping column names without suffixes, causing column name conflicts.

Always check your indexes and consider using reset_index() or specifying on if joining on columns.

python

import pandas as pd

# DataFrames with overlapping columns
left = pd.DataFrame({'key': ['K0', 'K1'], 'A': ['A0', 'A1']})
right = pd.DataFrame({'key': ['K0', 'K1'], 'A': ['B0', 'B1']})

# Wrong: join without suffixes causes duplicate column names
try:
    left.set_index('key').join(right.set_index('key'))
except Exception as e:
    print(f'Error: {e}')

# Right: use suffixes to avoid conflicts
joined = left.set_index('key').join(right.set_index('key'), lsuffix='_left', rsuffix='_right')
print(joined)

Output

Error: columns overlap but no suffix specified: Index(['A'], dtype='object')
       A_left A_right
key                
K0       A0      B0
K1       A1      B1

Quick Reference

Parameter	Description	Default
other	DataFrame to join with	None
on	Column or index level name(s) to join on	None
how	Type of join: 'left', 'right', 'outer', 'inner'	'left'
lsuffix	Suffix for overlapping columns in left DataFrame	''
rsuffix	Suffix for overlapping columns in right DataFrame	''
sort	Sort the join keys lexicographically	False

Key Takeaways

Use DataFrame.join() to combine DataFrames by index or key columns easily.

Set the 'how' parameter to control which rows appear in the result.

Use suffixes to avoid column name conflicts when joining DataFrames with overlapping columns.

Ensure indexes align or specify 'on' to join on columns instead of index.

Check for NaN values after join to verify correct alignment.