Introduction

To merge two pandas DataFrames by index, use either .join() or pd.merge(..., left_index=True, right_index=True). Both align rows using the index instead of matching on named columns.

The important complication is duplicate index values. If either side contains duplicates, the merge can produce more rows than you expect because pandas matches every left duplicate with every right duplicate that shares the same index label.

That behavior is correct for relational joins, but it often surprises people who assumed the index was unique when it was not.

Basic Merge by Index

Here is the direct approach with join:

1import pandas as pd
2
3left = pd.DataFrame(
4    {"sales": [100, 200]},
5    index=["A", "B"],
6)
7
8right = pd.DataFrame(
9    {"region": ["East", "West"]},
10    index=["A", "B"],
11)
12
13result = left.join(right)
14print(result)

Output:

   sales region
A    100   East
B    200   West

The equivalent merge form is:

result = pd.merge(left, right, left_index=True, right_index=True, how="inner")

Use whichever style you find clearer. join is shorter when index alignment is the primary goal.

What Happens with Duplicate Indices

Duplicate indices change the semantics. Suppose both DataFrames contain repeated labels:

1left = pd.DataFrame(
2    {"sales": [100, 150]},
3    index=["A", "A"],
4)
5
6right = pd.DataFrame(
7    {"region": ["East", "North"]},
8    index=["A", "A"],
9)
10
11result = left.join(right)
12print(result)

Output:

1   sales region
2A    100   East
3A    100  North
4A    150   East
5A    150  North

This is not a pandas bug. It is a many-to-many join on the index label A, so every matching pair is returned.

Choose the Right Join Type

As with column-based merges, you can control which index labels survive:

outer_result = left.join(right, how="outer")
left_result = left.join(right, how="left")
inner_result = left.join(right, how="inner")

The join type controls which labels are kept, but it does not change the duplicate-expansion behavior. If duplicates exist on both sides, matching rows still multiply.

Aggregate Before Joining If Duplicates Are Not Meaningful

If duplicate index values should really represent one logical row per label, aggregate first:

1left_agg = left.groupby(level=0).sum()
2right_agg = right.groupby(level=0).first()
3
4result = left_agg.join(right_agg)
5print(result)

This is often the correct fix when duplicates came from preprocessing steps such as concatenation, resampling, or accidental index reuse.

Reset the Index When the Index Is Not a Real Key

Sometimes you are trying to merge by row position or by a hidden logical column, and the index is not trustworthy. In that case, reset it and merge on an explicit column instead:

1left_reset = left.reset_index().rename(columns={"index": "key"})
2right_reset = right.reset_index().rename(columns={"index": "key"})
3
4result = pd.merge(left_reset, right_reset, on="key", how="inner")

This makes the join key visible and easier to validate.

It also makes debugging easier, because you can inspect the key as an ordinary column and check for duplicates with duplicated() before merging.

Common Pitfalls

• Assuming duplicate indices will behave like unique keys. They do not; they create many-to-many matches.
• Using .join() without checking whether the index is actually the right merge key.
• Confusing row position with index label. Pandas aligns by label, not by physical row order.
• Forgetting to choose the correct how value when labels do not overlap perfectly.
• Trying to "fix" duplicate expansion after the merge instead of addressing duplicates before joining.

Summary

• Merge by index with .join() or pd.merge(..., left_index=True, right_index=True).
• Duplicate index values produce repeated matches, which can multiply rows.
• Use how to control label retention, not duplicate behavior.
• Aggregate or clean duplicates first if you expect one row per index label.
• Reset the index and merge on an explicit key when the index is not the real identifier.