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'.

Practice

1. What does setting left_index=True and right_index=True do when merging two pandas DataFrames?

easy

A. It merges the DataFrames by ignoring the indexes completely.

B. It merges the DataFrames using their column names as the key.

C. It merges the DataFrames by concatenating columns without any key.

D. It merges the DataFrames using their row indexes as the key.

Solution

1. Step 1: Understand the role of indexes in merge

   Indexes are row labels in pandas DataFrames. Using left_index=True and right_index=True tells pandas to join on these row labels instead of columns.

2. Step 2: Effect of these parameters in merge

   When both are True, pandas matches rows based on their index values, combining rows with the same index from both DataFrames.

3. Final Answer:

   It merges the DataFrames using their row indexes as the key. -> Option D

4. Quick Check:

   Merging on indexes = left_index=True and right_index=True [OK]

Hint: Indexes join rows by labels, not columns [OK]

Common Mistakes:

• Thinking merge always uses columns as keys
• Confusing index merge with concatenation
• Ignoring the need to set both left_index and right_index

2. Which of the following is the correct syntax to merge two DataFrames df1 and df2 on their indexes with an inner join?

easy

A. pd.merge(df1, df2, on='index', how='inner')

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

C. pd.merge(df1, df2, index=True, how='inner')

D. pd.merge(df1, df2, left_on='index', right_on='index', how='inner')

Solution

1. Step 1: Identify correct parameters for index merge

   To merge on indexes, pandas requires left_index=True and right_index=True. Using on='index' or left_on='index' is incorrect because 'index' is not a column name.

2. Step 2: Confirm the join type

   The how='inner' parameter specifies an inner join, which keeps only matching index rows.

3. Final Answer:

   pd.merge(df1, df2, left_index=True, right_index=True, how='inner') -> Option B

4. Quick Check:

   Use left_index and right_index for index merge [OK]

Hint: Use left_index and right_index, not 'on' for index merge [OK]

Common Mistakes:

• Using on='index' instead of left_index=True
• Confusing index merge with column merge
• Forgetting to set both left_index and right_index

3. Given the following DataFrames:

df1:
       A
idx
1      10
2      20
3      30


df2:
       B
idx
2     200
3     300
4     400

What is the result of pd.merge(df1, df2, left_index=True, right_index=True, how='inner')?

medium

A. {'idx': [2, 3], 'A': [20, 30], 'B': [200, 300]}

B. {'idx': [1, 2, 3], 'A': [10, 20, 30], 'B': [null, 200, 300]}

C. {'idx': [1, 2, 3, 4], 'A': [10, 20, 30, null], 'B': [null, 200, 300, 400]}

D. {'idx': [2, 3, 4], 'A': [20, 30, null], 'B': [200, 300, 400]}

Solution

1. Step 1: Understand inner join on indexes

   An inner join keeps only rows where the index exists in both DataFrames. Here, indexes 2 and 3 are common.

2. Step 2: Combine matching rows

   Rows with index 2 have A=20 and B=200, index 3 have A=30 and B=300. Rows with index 1 and 4 are excluded.

3. Final Answer:

   {'idx': [2, 3], 'A': [20, 30], 'B': [200, 300]} -> Option A

4. Quick Check:

   Inner join keeps only common indexes [OK]

Hint: Inner join keeps only matching index rows [OK]

Common Mistakes:

• Including rows with indexes not in both DataFrames
• Confusing inner join with outer join
• Expecting nulls in inner join result

4. Consider this code snippet:

import pandas as pd

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

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

What is the error in this code?

medium

A. No error; the code runs and merges correctly.

B. Missing 'on' parameter causes a KeyError.

C. left_index and right_index cannot be used together.

D. DataFrames must have the same index values for merge.

Solution

1. Step 1: Check parameters used in merge

   The code uses left_index=True and right_index=True correctly to merge on indexes with how='outer'.

2. Step 2: Understand outer join behavior

   Outer join includes all indexes from both DataFrames, filling missing values with NaN. This is valid and runs without error.

3. Final Answer:

   No error; the code runs and merges correctly. -> Option A

4. Quick Check:

   left_index and right_index merge works fine [OK]

Hint: Outer join merges all indexes, no error with index merge [OK]

Common Mistakes:

• Assuming missing 'on' causes error when using index merge
• Thinking left_index and right_index cannot be combined
• Believing indexes must match exactly for merge

5. You have two DataFrames:

df1:
       Score
Student
Alice     85
Bob       90
Charlie   78


df2:
       Grade
Student
Bob       B
Charlie   C
David     A

You want to merge them so that all students from df1 appear, and matching grades from df2 are added. Missing grades should show as NaN. Which code achieves this?

hard

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

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

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

D. pd.merge(df1, df2, left_on='Student', right_on='Student', how='outer')

Solution

1. Step 1: Identify merge type for all df1 rows

   To keep all rows from df1 and add matching data from df2, use a left join.

2. Step 2: Merge on indexes

   Both DataFrames use 'Student' as index, so set left_index=True and right_index=True to merge on indexes.

3. Final Answer:

   pd.merge(df1, df2, left_index=True, right_index=True, how='left') -> Option C

4. Quick Check:

   Left join on indexes keeps all df1 rows [OK]

Hint: Left join keeps all left DataFrame rows [OK]

Common Mistakes:

• Using inner join which drops unmatched df1 rows
• Using right join which keeps df2 rows instead
• Merging on columns instead of indexes