Merging on index in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of merging two DataFrames on index with inner join

Given two DataFrames df1 and df2 indexed by id, what is the result of merging them with how='inner' on their indexes?

Pandas

import pandas as pd

df1 = pd.DataFrame({'value1': [10, 20, 30]}, index=['a', 'b', 'c'])
df2 = pd.DataFrame({'value2': [100, 200, 300]}, index=['b', 'c', 'd'])

result = df1.merge(df2, left_index=True, right_index=True, how='inner')
print(result)

A {'b': {'value1': 20, 'value2': 100}, 'c': {'value1': 30, 'value2': 200}}

B {'a': {'value1': 10, 'value2': 100}, 'b': {'value1': 20, 'value2': 200}, 'c': {'value1': 30, 'value2': 300}}

C {'a': {'value1': 10}, 'b': {'value1': 20, 'value2': 100}, 'c': {'value1': 30, 'value2': 200}, 'd': {'value2': 300}}

D {'b': {'value1': 20, 'value2': 200}, 'c': {'value1': 30, 'value2': 300}}

💡 Hint

Inner join keeps only the indexes present in both DataFrames.

Only indexes 'b' and 'c' are common to both DataFrames, so the merged DataFrame contains rows for these indexes with columns from both.

❓ query_result

intermediate

Result of merging on index with left join

What is the output of merging df1 and df2 on their indexes using how='left'?

Pandas

import pandas as pd

df1 = pd.DataFrame({'value1': [1, 2, 3]}, index=['x', 'y', 'z'])
df2 = pd.DataFrame({'value2': [10, 20]}, index=['y', 'z'])

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

A {'x': {'value1': 1, 'value2': None}, 'y': {'value1': 2, 'value2': 10}, 'z': {'value1': 3, 'value2': 20}}

B {'y': {'value1': 2, 'value2': 10}, 'z': {'value1': 3, 'value2': 20}}

C {'x': {'value1': 1}, 'y': {'value1': 2, 'value2': 10}, 'z': {'value1': 3, 'value2': 20}}

D {'x': {'value1': 1, 'value2': 10}, 'y': {'value1': 2, 'value2': 20}, 'z': {'value1': 3, 'value2': None}}

💡 Hint

Left join keeps all indexes from the left DataFrame and fills missing values from the right with NaN.

Index 'x' is only in df1, so value2 is NaN there. Indexes 'y' and 'z' have matching rows in both DataFrames.

📝 Syntax

advanced

Identify the syntax error in merging on index

Which option contains a syntax error when merging two DataFrames on their indexes?

Pandas

import pandas as pd

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

A df1.merge(df2, left_index=True, right_index=True, how='right')

B df1.merge(df2, left_index=True right_index=True, how='inner')

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

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

💡 Hint

Check for missing commas between arguments.

Option B is missing a comma between left_index=True and right_index=True, causing a syntax error.

❓ optimization

advanced

Optimizing merge on index for large DataFrames

You have two large DataFrames indexed by the same column. Which approach is fastest to merge them on their indexes?

A Convert indexes to columns, merge, then set index back

B Reset indexes on both DataFrames and merge on the index column as a regular column

C Use <code>df1.merge(df2, left_index=True, right_index=True)</code> without resetting indexes

D Use <code>pd.concat([df1, df2], axis=1)</code> without merge

💡 Hint

Merging directly on indexes is usually faster than resetting indexes.

Option C merges directly on indexes, which is optimized in pandas. Resetting indexes or concatenating may add overhead.

🧠 Conceptual

expert

Understanding merge behavior with duplicate indexes

Consider two DataFrames with duplicate indexes. What happens when you merge them on their indexes?

Pandas

import pandas as pd

df1 = pd.DataFrame({'val1': [1,2,3]}, index=['a','a','b'])
df2 = pd.DataFrame({'val2': [10,20]}, index=['a','b'])

result = df1.merge(df2, left_index=True, right_index=True, how='inner')
print(result)

A Rows with index 'a' in df1 are matched with the single 'a' in df2, resulting in multiple rows for 'a' in the output

B Only the first occurrence of 'a' in df1 is matched with df2, others are dropped

C Merge fails with a ValueError due to duplicate indexes

D Duplicates in indexes are ignored and only unique indexes are merged

💡 Hint

Think about how pandas handles many-to-one merges on indexes.

Pandas performs a many-to-one merge, matching each duplicate index row in df1 with the single matching row in df2, resulting in multiple rows for 'a'.