ZetCode

Pandas join Function

last modified November 17, 2026 Python 3.15

The join method combines two DataFrames by matching their index labels. It is a convenience wrapper around merging: where merge looks for a shared column, join lines rows up by their index by default. That makes it a natural fit when two tables already share the same labels, such as measurements taken at the same times.

The examples below were verified with pandas 3.1.1. For the fuller set of database-style options see Pandas merging data.

Joining on the index

A join is an operation that combines rows from two tables based on matching labels. With no extra arguments, DataFrame.join matches the index of the calling frame with the index of the other frame and returns a table with their columns side by side.

join_index.py
import pandas as pd

left = pd.DataFrame({'value': [10, 20]}, index=['a', 'b'])
right = pd.DataFrame({'other': [30, 40]}, index=['a', 'b'])
print(left.join(right))

Both frames carry the labels a and b, so the two value columns are placed together under those labels. Only labels present in both frames appear in the result.

$ python join_index.py
   value  other
a     10     30
b     20     40

Joining on a column with on=

If the left frame's join key is a column rather than its index, on= names that column. Pandas uses the values in that column to look up labels in the right frame's index. The right-hand table keeps its index; the left-hand table keeps its usual row numbers.

join_on.py
import pandas as pd

left = pd.DataFrame({'key': ['a', 'b'], 'value': [10, 20]})
right = pd.DataFrame({'other': [30, 40]}, index=['a', 'b'])
print(left.join(right, on='key'))

The key column of the left frame contains a and b, which match the right frame's index labels. The other column is pulled in, and the result is indexed 0 and 1 like the original left frame.

$ python join_on.py
  key  value  other
0   a     10     30
1   b     20     40

Handling overlapping column names

When both frames have a column with the same name, Pandas must tell them apart. Older code used the separate lsuffix and rsuffix arguments for the left and right frames; these are deprecated in pandas 3.1. The modern replacement is suffixes, a pair of strings appended to the two names.

join_suffixes.py
import pandas as pd

left = pd.DataFrame({'value': [10, 20]}, index=['a', 'b'])
right = pd.DataFrame({'value': [30, 40]}, index=['a', 'b'])
print(left.join(right, suffixes=('_left', '_right')))

Both frames have a value column, so the result contains value_left and value_right. Under the old names this would be written lsuffix='_left', rsuffix='_right', which still works but warns. Without any suffix argument the duplicate names would raise an error.

$ python join_suffixes.py
   value_left  value_right
a          10           30
b          20           40

Choosing how to join

The how parameter controls which labels survive, much like a SQL join. 'left' keeps every label from the left frame, 'inner' keeps only labels found in both, 'right' keeps every label from the right frame, and 'outer' keeps all labels from either. Missing combinations are filled with NaN.

join_how.py
import pandas as pd

left = pd.DataFrame({'value': [10, 20]}, index=['a', 'b'])
right = pd.DataFrame({'other': [30, 40]}, index=['a', 'c'])
for how in ['left', 'inner', 'right', 'outer']:
    print(f"how='{how}':")
    print(left.join(right, how=how))
    print()

The left frame has labels a and b, the right frame a and c. The left join keeps b with a missing other; the inner join keeps only a; the right join keeps c with a missing value; the outer join keeps all three labels. The decimal values in some outputs come from missing values forcing a column to a numeric dtype that can hold NaN.

$ python join_how.py
how='left':
   value  other
a     10   30.0
b     20    NaN

how='inner':
   value  other
a     10     30

how='right':
   value  other
a   10.0     30
c    NaN     40

how='outer':
   value  other
a   10.0   30.0
b   20.0    NaN
c    NaN   40.0

Joining several frames at once

join accepts a list of DataFrames, not just one. All of them are joined to the caller in a single step, matching indexes. This is a compact way to attach several sets of columns that share the same labels.

join_multiple.py
import pandas as pd

left = pd.DataFrame({'a': [1, 2]}, index=['x', 'y'])
r1 = pd.DataFrame({'b': [3, 4]}, index=['x', 'y'])
r2 = pd.DataFrame({'c': [5, 6]}, index=['x', 'y'])
print(left.join([r1, r2]))

The left frame contributes a, and the two right frames contribute b and c. Since all three share the index labels x and y, the result has two complete rows with no missing values.

$ python join_multiple.py
   a  b  c
x  1  3  5
y  2  4  6

Difference from merge

The key difference is the default key: join matches on the index, while merge matches on a column named by on= (or on all shared column names). Both support the same four how strategies and both can produce the same result after set_index lines up the keys.

join_vs_merge.py
import pandas as pd

left = pd.DataFrame({'key': ['a', 'b', 'c'], 'v1': [1, 2, 3]})
right = pd.DataFrame({'key': ['a', 'b', 'd'], 'v2': [4, 5, 6]})
print("merge:")
print(pd.merge(left, right, on='key', how='left'))
print()
print("join on key:")
print(left.set_index('key').join(right.set_index('key'), how='left'))

The two calls produce the same rows: keys a and b have matching values in both frames, while c and d get NaN on the side where they are absent. merge keeps the key as a column; join uses it as the index. Reach for join when indexes already align, and merge when you need to match on columns directly. See Pandas merging data for more.

$ python join_vs_merge.py
merge:
  key  v1   v2
0   a   1  4.0
1   b   2  5.0
2   c   3  NaN

join on key:
     v1   v2
key         
a     1  4.0
b     2  5.0
c     3  NaN

Source

Pandas DataFrame.join Documentation, Pandas Merge, join, concatenate and compare Documentation, and Pandas merge Documentation

In this article we have joined DataFrames on their index, used on= for a column key, resolved name clashes with suffixes, chosen among the four how strategies, joined several frames at once, and compared join with merge. For other ways to combine tables see Pandas concatenating data and Pandas merging data.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Pandas tutorials.