pandas DataFrame.join: indexes, on and suffixes

When the key is already an index, I often find left.join(right) a convenient way to add columns. First, though, I check what that index represents. Two files can contain identical row numbers while listing customers in entirely different orders. Successfully joining those tables does not establish that the combined information belongs to the same customer or product.

The interface is DataFrame.join, a method called on a DataFrame. I do not use pd.join(...). These synthetic examples follow documentation without claiming execution. The main suffix example uses the current pandas 3.1 syntax, with an earlier 3.x version shown separately. The distinction helps when reading older examples alongside a newer installation.

The default join rule

The DataFrame.join reference describes index-to-index matching and a default of how='left'. That differs from the default inner join in merge. I often specify how even when it equals the default, making the base population visible to someone reading the code.

With a valid right-hand lookup, left rows remain and receive matching columns. A repeated key can create multiple pairs, however, so “left” alone does not guarantee an unchanged row count. I start with unique indexes to separate the choice of population from the relationship between the tables.

A corrected example with overlapping names

import pandas as pd

left = pd.DataFrame({
    'A': ['A0', 'A1', 'A2'],
    'B': ['B0', 'B1', 'B2'],
}, index=[0, 1, 2])
right = pd.DataFrame({
    'B': ['B3', 'B4', 'B5'],
}, index=[1, 2, 3])

result = left.join(
    right,
    how='left',
    suffixes=('_left', '_right'),
    validate='one_to_one',
)

Index zero has no right match. Index one receives B3, and two receives B4. Index three is absent because it exists only on the right. The old English article included that row despite the default left join, effectively displaying a different join type. “Missing” in the table describes the absent value without depending on its console representation.

Suffixes in pandas 3.1 and earlier versions

Both tables contain B. suffixes distinguishes it as B_left and B_right without changing the key or join type. From pandas 3.1, the documentation deprecates lsuffix and rsuffix. In code for pandas 3.0, the earlier form is:

legacy_result = left.join(
    right,
    how='left',
    lsuffix='_left',
    rsuffix='_right',
    validate='one_to_one',
)

The matching logic is unchanged. Choose syntax supported by your installed version rather than copying a new parameter into an older environment. When columns represent different concepts, I also consider descriptive names before the join. That makes the output understandable outside the expression that created it.

Inner and outer with the same inputs

common = left.join(right, how='inner', suffixes=('_left', '_right'))
all_keys = left.join(right, how='outer', suffixes=('_left', '_right'))

The inner result retains indexes one and two. The outer result includes zero, one, two and three. In the extra row three, left A and left B are missing while right B contains B5. That row belongs in a union of indexes. It should not be added to the left-join output merely because it exists in the right-hand input.

In real data, the question determines the join type. A report about the left population may deliberately retain a left join. A report meant to expose all keys from both sources needs a broader set. Neither choice establishes whether a missing value means an absent event, unavailable information or a preparation problem.

on matches a left column to the right index

This separate example uses an ordinary customer_id column on the left. The orders table needs no MultiIndex:

orders = pd.DataFrame({
    'order_id': [101, 102, 103],
    'customer_id': [2, 1, 3],
})
lookup = pd.DataFrame({
    'customer_name': ['Ada', 'Ben'],
}, index=[1, 2])

enriched = orders.join(
    lookup,
    on='customer_id',
    how='left',
    validate='many_to_one',
)

Customer two matches Ben despite appearing first in the orders table. Customer one matches Ada, while three has no match. Values in customer_id are matched to the index of lookup, rather than to another column with that name. That makes inspecting the right-hand side essential. Several fields in on require a corresponding right-hand MultiIndex.

If both keys are columns and I want to keep that structure, I usually choose pandas.merge. I do not claim a general performance advantage for join. Readability depends on the data structure, while execution-time comparisons would need measurements under specified conditions.

Join a list of objects

The other argument can also be a list of DataFrames or named Series. This example uses base column A and two different pieces of information indexed in the same way:

base = left[['A']]
extra = right.rename(columns={'B': 'C'})
flags = pd.DataFrame({'reviewed': [True, False]}, index=[0, 2])
combined = base.join([extra, flags], how='left')

The result still has indexes zero, one and two. At zero, reviewed is true and C is missing. At one, C contains B3 while the flag is missing. At two, C contains B4 and reviewed is false. A single joined Series needs a name to become the column name. A list does not make unrelated key domains compatible.

Lists do not support on or suffix parameters. I therefore prepare distinct names beforehand instead of leaving overlapping columns. That is an important limit compared with joining one object. When several sources need different matching conditions, I prefer separate, readable stages over trying to conceal the whole process inside one call.

Check the result before accepting it

In the orders example, validate='many_to_one' checks right-key uniqueness. It does not confirm that customer three exists or that the names are correct. I also inspect unmatched records and the meaning of the data. The pandas merging guide explains how repeated keys can create a many-to-many relationship that may or may not match the intended model.

Missing keys require attention too. Pandas matching can differ from an ordinary SQL comparison involving NULL, as the merge reference explains. If an index or key column contains missing values, I decide how to handle them before joining. Two unknown identifiers should not automatically become the same business entity in my interpretation.

An exercise with three decisions

In the orders example, decide whether an order without a customer name should remain. Then consider a repeated index one in the lookup: which validation should reject it? Finally, choose whether to keep the key as a column and use on, or deliberately make it an index. Each decision concerns data meaning, so it should be explainable before choosing the syntax.

Return to pandas.Series for index foundations. For a broader discussion of key-based operations and relationship checks, continue with merge. A consistent key and a clear output population help with either expression.