Merging on different column names in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of merging two DataFrames on different column names

What is the output DataFrame after merging df1 and df2 on df1's 'key1' and df2's 'key2' columns?

Pandas

import pandas as pd

df1 = pd.DataFrame({'key1': ['A', 'B', 'C'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key2': ['B', 'C', 'D'], 'val2': [4, 5, 6]})

result = pd.merge(df1, df2, left_on='key1', right_on='key2')
print(result)

A

  key1  val1 key2  val2
0    A     1    B     4
1    B     2    C     5
2    C     3    D     6

B

  key1  val1 key2  val2
0    B     2    B     4
1    C     3    C     5

C

  key1  val1 key2  val2
0    A     1    A     4
1    B     2    B     5
2    C     3    C     6

D

Empty DataFrame
Columns: [key1, val1, key2, val2]
Index: []

💡 Hint

Remember that merging on different column names requires specifying left_on and right_on parameters.

The merge matches rows where df1's 'key1' equals df2's 'key2'. Only 'B' and 'C' appear in both, so only those rows merge.

❓ data_output

intermediate

Number of rows after merging on different column names with inner join

How many rows will the resulting DataFrame have after this merge operation?

Pandas

import pandas as pd

df1 = pd.DataFrame({'id1': [1, 2, 3, 4], 'value1': ['a', 'b', 'c', 'd']})
df2 = pd.DataFrame({'id2': [3, 4, 5, 6], 'value2': ['x', 'y', 'z', 'w']})

merged = pd.merge(df1, df2, left_on='id1', right_on='id2', how='inner')
print(len(merged))

A 2

B 4

C 6

D 0

💡 Hint

Inner join keeps only rows with matching keys in both DataFrames.

Only ids 3 and 4 appear in both DataFrames, so the merged DataFrame has 2 rows.

🔧 Debug

advanced

Identify the error in merging on different column names

What error will this code raise when trying to merge df1 and df2?

Pandas

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'C': [1, 2], 'D': [5, 6]})

result = pd.merge(df1, df2, on='A')

A KeyError: 'A'

B ValueError: columns overlap but no suffix specified

C TypeError: merge() got an unexpected keyword argument 'on'

D No error, merge runs successfully

💡 Hint

Check if both DataFrames have the column named in 'on' parameter.

df2 does not have column 'A', so merge raises a KeyError.

🚀 Application

advanced

Merging DataFrames with different column names and suffixes

After merging df1 and df2 on different column names, what will be the column names in the result?

Pandas

import pandas as pd

df1 = pd.DataFrame({'user_id': [1, 2], 'score': [10, 20]})
df2 = pd.DataFrame({'id': [1, 3], 'score': [15, 25]})

result = pd.merge(df1, df2, left_on='user_id', right_on='id', suffixes=('_left', '_right'))
print(result.columns.tolist())

A ['user_id', 'score', 'id', 'score']

B ['user_id', 'score_x', 'id', 'score_y']

C ['user_id', 'score_left', 'id', 'score_right']

D ['user_id', 'score_left', 'score_right']

💡 Hint

Suffixes are added to overlapping column names except the join keys.

Both DataFrames have 'score' column. After merge, suffixes '_left' and '_right' are added to distinguish them.

🧠 Conceptual

expert

Choosing merge parameters for different column names

You have two DataFrames: dfA with column 'emp_id' and dfB with column 'employee'. You want to merge them to keep all rows from dfA and matching rows from dfB. Which merge parameters are correct?

A pd.merge(dfA, dfB, on=['emp_id', 'employee'], how='inner')

B pd.merge(dfA, dfB, on='emp_id', how='left')

C pd.merge(dfA, dfB, left_on='employee', right_on='emp_id', how='right')

D pd.merge(dfA, dfB, left_on='emp_id', right_on='employee', how='left')

💡 Hint

Use left_on and right_on when column names differ. 'how'='left' keeps all rows from left DataFrame.

Option D correctly specifies different column names and uses left join to keep all rows from dfA.

Practice

1.

What does the left_on and right_on parameters do in pandas.merge()?

easy

A. They specify columns with different names to join on from left and right DataFrames.

B. They rename columns in the DataFrames before merging.

C. They filter rows based on column values before merging.

D. They sort the DataFrames before merging.

Solution

1. Step 1: Understand merge parameters

   The left_on and right_on parameters specify which columns to join on when the column names differ between DataFrames.

2. Step 2: Clarify their purpose

   They do not rename or filter data but tell pandas which columns to match for merging.

3. Final Answer:

   They specify columns with different names to join on from left and right DataFrames. -> Option A

4. Quick Check:

   left_on/right_on = specify join columns [OK]

Hint: Use left_on and right_on to join different column names [OK]

Common Mistakes:

• Thinking they rename columns
• Confusing with filtering rows
• Assuming they sort data

2.

Which of the following is the correct syntax to merge df1 and df2 on columns 'id' in df1 and 'user_id' in df2?

df1 = pd.DataFrame({'id': [1, 2], 'val': ['A', 'B']})
df2 = pd.DataFrame({'user_id': [1, 3], 'score': [10, 20]})

easy

A. pd.merge(df1, df2, left_on='user_id', right_on='id')

B. pd.merge(df1, df2, on='id', right_on='user_id')

C. pd.merge(df1, df2, left_on='id', right_on='user_id')

D. pd.merge(df1, df2, on=['id', 'user_id'])

Solution

1. Step 1: Identify correct parameters

   Since columns have different names, use left_on='id' and right_on='user_id' to specify join keys.

2. Step 2: Check syntax correctness

   pd.merge(df1, df2, left_on='id', right_on='user_id') correctly uses pd.merge(df1, df2, left_on='id', right_on='user_id'). Others misuse on or swap keys.

3. Final Answer:

   pd.merge(df1, df2, left_on='id', right_on='user_id') -> Option C

4. Quick Check:

   left_on='id', right_on='user_id' = correct syntax [OK]

Hint: Use left_on for left, right_on for right column names [OK]

Common Mistakes:

• Using on with different column names
• Swapping left_on and right_on
• Passing both columns in on as a list

3.

What is the output of the following code?

import pandas as pd

df1 = pd.DataFrame({'key1': ['A', 'B', 'C'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key2': ['B', 'C', 'D'], 'val2': [4, 5, 6]})

result = pd.merge(df1, df2, left_on='key1', right_on='key2', how='inner')
print(result)

medium

A. key1 val1 key2 val2
0 A 1 A 4
1 B 2 B 5

B. key1 val1 key2 val2
0 A 1 B 4
1 B 2 C 5

C. Empty DataFrame
Columns: [key1, val1, key2, val2]
Index: []

D. key1 val1 key2 val2
0 B 2 B 4
1 C 3 C 5

Solution

1. Step 1: Understand merge keys and type

   We merge on key1 from df1 and key2 from df2 with how='inner', so only matching keys appear.

2. Step 2: Find matching keys

   Matching keys are 'B' and 'C'. Rows with 'A' and 'D' are excluded.

3. Final Answer:

   key1 val1 key2 val2 0 B 2 B 4 1 C 3 C 5 -> Option D

4. Quick Check:

   Inner join on matching keys = rows with B and C [OK]

Hint: Inner merge keeps only matching keys from both sides [OK]

Common Mistakes:

• Expecting all rows to appear
• Mixing up left_on and right_on
• Confusing inner with outer join

4.

What is wrong with this code snippet?

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'X': [1, 3], 'Y': [5, 6]})

merged = pd.merge(df1, df2, left_on='A', right_on='B')
print(merged)

medium

A. It will raise a KeyError because 'B' is not in df2.

B. It uses wrong parameter names for merge.

C. It tries to merge on columns that do not match logically.

D. It merges correctly without errors.

Solution

1. Step 1: Check columns in each DataFrame

   df1 has columns 'A' and 'B'. df2 has columns 'X' and 'Y'. The code tries to merge on left_on='A' and right_on='B'.

2. Step 2: Identify error cause

   Since df2 does not have column 'B', pandas will raise a KeyError.

3. Final Answer:

   It will raise a KeyError because 'B' is not in df2. -> Option A

4. Quick Check:

   right_on column must exist in right DataFrame [OK]

Hint: Check if merge columns exist in respective DataFrames [OK]

Common Mistakes:

• Assuming columns exist in both DataFrames
• Confusing left_on and right_on columns
• Ignoring error messages

5.

You have two DataFrames:

df_customers = pd.DataFrame({'cust_id': [101, 102, 103], 'name': ['Alice', 'Bob', 'Charlie']})
df_orders = pd.DataFrame({'customer': [102, 101, 104], 'order_id': [5001, 5002, 5003]})

You want to merge these to get customer names with their orders, including customers without orders and orders without customers. Which code correctly does this?

hard

A. pd.merge(df_customers, df_orders, left_on='cust_id', right_on='customer', how='inner')

B. pd.merge(df_customers, df_orders, left_on='cust_id', right_on='customer', how='outer')

C. pd.merge(df_customers, df_orders, left_on='name', right_on='customer', how='left')

D. pd.merge(df_customers, df_orders, on='cust_id', how='inner')

Solution

1. Step 1: Identify columns to join on

   Customer IDs are cust_id in df_customers and customer in df_orders. So use left_on='cust_id' and right_on='customer'.

2. Step 2: Choose join type for all data

   To include all customers and all orders (even unmatched), use how='outer' for full outer join.

3. Final Answer:

   pd.merge(df_customers, df_orders, left_on='cust_id', right_on='customer', how='outer') -> Option B

4. Quick Check:

   Outer join + left_on/right_on = all data combined [OK]

Hint: Use how='outer' with left_on/right_on for full merge [OK]

Common Mistakes:

• Using inner join loses unmatched rows
• Merging on wrong columns
• Using name column instead of ID