merge() for SQL-like joins in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of inner join with pandas.merge()

What is the output DataFrame after performing an inner join on the 'key' column?

Pandas

import pandas as pd

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

result = pd.merge(df1, df2, on='key', how='inner')
print(result)

A {'key': ['A', 'B', 'C', 'D'], 'val1': [1, 2, 3, null], 'val2': [null, 4, 5, 6]}

B {'key': ['B', 'C', 'D'], 'val1': [2, 3, null], 'val2': [4, 5, 6]}

C {'key': ['B', 'C'], 'val1': [2, 3], 'val2': [4, 5]}

D {'key': ['A', 'B', 'C'], 'val1': [1, 2, 3], 'val2': [null, 4, 5]}

💡 Hint

Inner join keeps only keys present in both DataFrames.

Inner join returns rows where the key exists in both DataFrames. Here, keys 'B' and 'C' are common.

❓ data_output

intermediate

Number of rows after left join with pandas.merge()

How many rows will the resulting DataFrame have after performing a left join on 'key'?

Pandas

import pandas as pd

df1 = pd.DataFrame({'key': ['X', 'Y', 'Z'], 'val1': [10, 20, 30]})
df2 = pd.DataFrame({'key': ['Y', 'Z'], 'val2': [100, 200]})

result = pd.merge(df1, df2, on='key', how='left')
print(len(result))

A 3

B 2

C 4

D 1

💡 Hint

Left join keeps all rows from the left DataFrame.

Left join keeps all rows from df1 (3 rows), matching rows from df2 or NaN if no match.

🔧 Debug

advanced

Identify the error in merge() usage

What error will this code raise?

Pandas

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2], 'val': ['A', 'B']})
df2 = pd.DataFrame({'key': [1, 2], 'val2': ['X', 'Y']})

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

A ValueError: columns overlap but no suffix specified

B KeyError: 'id'

C TypeError: merge() missing 1 required positional argument

D No error, merge runs successfully

💡 Hint

Check if both DataFrames have the column used in 'on'.

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

❓ visualization

advanced

Visualize the result of an outer join

Which option shows the correct DataFrame after an outer join on 'key'?

Pandas

import pandas as pd

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

result = pd.merge(df1, df2, on='key', how='outer').sort_values('key').reset_index(drop=True)
print(result)

A

  key  val1  val2
0   A   1.0   NaN
1   B   2.0   3.0
2   C   NaN   4.0

B

  key  val1  val2
0   A   NaN   NaN
1   B   2.0   3.0
2   C   NaN   4.0

C

  key  val1  val2
0   A   1.0   3.0
1   B   2.0   4.0
2   C   NaN   NaN

D

  key  val1  val2
0   A   1.0   4.0
1   B   2.0   3.0
2   C   NaN   NaN

💡 Hint

Outer join keeps all keys from both DataFrames, filling missing values with NaN.

Outer join includes keys A, B, and C. Values missing in one DataFrame become NaN.

🚀 Application

expert

Combine DataFrames with multiple keys and suffixes

You have two DataFrames with overlapping column names besides the keys. Which code correctly merges them on 'id' and 'date' with suffixes to avoid column name conflicts?

Pandas

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2], 'date': ['2023-01-01', '2023-01-02'], 'value': [10, 20]})
df2 = pd.DataFrame({'id': [1, 2], 'date': ['2023-01-01', '2023-01-02'], 'value': [100, 200]})

A pd.merge(df1, df2, on=['id', 'date'], suffixes=['_left', '_right'])

B pd.merge(df1, df2, on=['id', 'date'], suffixes='_left_right')

C pd.merge(df1, df2, on=['id', 'date'], suffixes=('left', 'right'))

D pd.merge(df1, df2, on=['id', 'date'], suffixes=('_left', '_right'))

💡 Hint

Suffixes must be a tuple of strings.

The suffixes parameter requires a tuple of two strings to append to overlapping column names.