Merging on index in Pandas - Step-by-Step Execution

Concept Flow - Merging on index

Start with two DataFrames

↓

Identify index columns

↓

Merge on index

↓

Create merged DataFrame

↓

Output

We start with two tables, identify their index columns, merge them using these indexes, and produce a combined table.

Execution Sample

Pandas

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2]}, index=['x', 'y'])
df2 = pd.DataFrame({'B': [3, 4]}, index=['x', 'y'])

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

This code merges two DataFrames on their index labels, combining columns side by side.

Execution Table

Step	Action	df1 Index	df1 Columns	df2 Index	df2 Columns	Merge Condition	Merged Output
1	Create df1	['x', 'y']	['A']	N/A	N/A	N/A	{'x': {'A':1}, 'y': {'A':2}}
2	Create df2	N/A	N/A	['x', 'y']	['B']	N/A	{'x': {'B':3}, 'y': {'B':4}}
3	Merge on index	['x', 'y']	['A']	['x', 'y']	['B']	left_index=True, right_index=True	{'x': {'A':1, 'B':3}, 'y': {'A':2, 'B':4}}
4	Output merged DataFrame	['x', 'y']	['A', 'B']	['x', 'y']	['B']	Indexes matched	A B x 1 3 y 2 4

💡 All index labels matched, merge completed successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
df1	undefined	{'x':1, 'y':2} with column A	{'x':1, 'y':2} with column A	{'x':1, 'y':2} with column A	{'x':1, 'y':2} with column A
df2	undefined	undefined	{'x':3, 'y':4} with column B	{'x':3, 'y':4} with column B	{'x':3, 'y':4} with column B
merged	undefined	undefined	undefined	{'x': {'A':1, 'B':3}, 'y': {'A':2, 'B':4}}	{'x': {'A':1, 'B':3}, 'y': {'A':2, 'B':4}}

Key Moments - 2 Insights

Why do we use left_index=True and right_index=True in merge?

Because we want to join the two DataFrames based on their index labels, not on columns. The execution_table row 3 shows the merge condition using these parameters.

What happens if the indexes do not match exactly?

Rows with non-matching indexes will be excluded by default in inner merge. This is implied in the exit_note where all indexes matched, so all rows appear in the output.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the merge condition used?

A left_index=True, right_index=True

B on='A'

C on='B'

D left_on='A', right_on='B'

💡 Hint

Check the 'Merge Condition' column at step 3 in execution_table.

At which step does the merged DataFrame first appear?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Merged Output' column in execution_table to see when merged data is created.

If df2 had an index label 'z' not in df1, what would happen to that row in the merged DataFrame?

A It would appear with NaN values for df1 columns

B It would cause an error

C It would be excluded from the merged DataFrame

D It would replace df1 rows

💡 Hint

By default, merge on index is inner join, so only matching indexes appear (see exit_note).

Concept Snapshot

Merging on index in pandas:
Use df1.merge(df2, left_index=True, right_index=True)
This joins DataFrames by their index labels.
Only rows with matching indexes appear by default.
Result combines columns side by side.
Useful when index is meaningful key.

Full Transcript

This visual execution shows how to merge two pandas DataFrames using their index labels. First, two DataFrames df1 and df2 are created with indexes 'x' and 'y'. Then, the merge function is called with left_index=True and right_index=True to join them on their indexes. The merged DataFrame contains columns from both, aligned by index. The execution table traces each step, showing the indexes and columns involved, the merge condition, and the resulting merged output. Key moments clarify why the index parameters are used and what happens if indexes do not match. The quiz tests understanding of merge conditions, step when merge occurs, and behavior with unmatched indexes. The snapshot summarizes the syntax and behavior for quick reference.