Merging on index in Pandas

Introduction

Merging on index helps you combine two tables by matching their row labels instead of columns. It is useful when the row labels carry important information.

You have two tables with related data and their row labels (indexes) match.

You want to join data based on unique IDs stored as row indexes.

You want to combine time series data where dates are the index.

You want to add columns from one table to another using the index as the key.

Syntax

Pandas

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

Set left_index=True and right_index=True to merge on the indexes of both tables.

The 'how' parameter controls the type of join: 'inner', 'left', 'right', or 'outer'.

Examples

Merge two dataframes on their indexes using an inner join by default.

Pandas

pd.merge(df1, df2, left_index=True, right_index=True)

Merge two dataframes on their indexes including all rows from both (outer join).

Pandas

pd.merge(df1, df2, left_index=True, right_index=True, how='outer')

Merge two dataframes on their indexes keeping all rows from the left dataframe.

Pandas

pd.merge(df1, df2, left_index=True, right_index=True, how='left')

Sample Program

This example merges two dataframes on their indexes. Only rows with matching indexes 'b' and 'c' appear in the result.

Pandas

import pandas as pd

# Create first dataframe with index
df1 = pd.DataFrame({'A': [1, 2, 3]}, index=['a', 'b', 'c'])

# Create second dataframe with index
df2 = pd.DataFrame({'B': [4, 5, 6]}, index=['b', 'c', 'd'])

# Merge on index with inner join
result = pd.merge(df1, df2, left_index=True, right_index=True, how='inner')

print(result)

Important Notes

Merging on index is useful when the index uniquely identifies rows.

If indexes do not match, the result depends on the 'how' parameter.

You can reset the index to merge on columns instead if needed.

Summary

Merging on index joins tables by their row labels.

Use left_index=True and right_index=True to merge on indexes.

The 'how' parameter controls which rows appear in the result.