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.