Merging on multiple keys in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of merging two DataFrames on multiple keys

What is the output DataFrame after merging df1 and df2 on columns 'key1' and 'key2' using an inner join?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({
    'key1': ['A', 'B', 'C', 'A'],
    'key2': [1, 2, 3, 2],
    'value1': [10, 20, 30, 40]
})

df2 = pd.DataFrame({
    'key1': ['A', 'B', 'A', 'D'],
    'key2': [1, 2, 2, 4],
    'value2': [100, 200, 300, 400]
})

result = pd.merge(df1, df2, on=['key1', 'key2'], how='inner')
print(result)

A

  key1  key2  value1  value2
0    A     1      10     100
1    B     2      20     200
2    D     4      40     400

B

  key1  key2  value1  value2
0    A     1      10     100
1    B     2      20     200
2    C     3      30     300

C

  key1  key2  value1  value2
0    A     1      10     100
1    B     2      20     200
2    A     2      40     300

D

  key1  key2  value1  value2
0    A     1      10     100
1    B     2      20     200
2    A     2      40     400

💡 Hint

Look for rows where both 'key1' and 'key2' match in both DataFrames.

The inner merge keeps only rows where both keys match in df1 and df2. The matching pairs are ('A',1), ('B',2), and ('A',2). The values from both DataFrames are combined for these keys.

❓ data_output

intermediate

Number of rows after merging on multiple keys with outer join

After merging df1 and df2 on ['key1', 'key2'] using an outer join, how many rows does the resulting DataFrame have?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({
    'key1': ['X', 'Y', 'Z'],
    'key2': [1, 2, 3],
    'val1': [5, 10, 15]
})

df2 = pd.DataFrame({
    'key1': ['X', 'Y', 'W'],
    'key2': [1, 4, 3],
    'val2': [50, 40, 30]
})

merged = pd.merge(df1, df2, on=['key1', 'key2'], how='outer')
print(len(merged))

A 3

B 5

C 4

D 6

💡 Hint

Count all unique pairs of keys from both DataFrames combined.

The unique key pairs are ('X',1), ('Y',2), ('Z',3), ('Y',4), and ('W',3), totaling 5 unique pairs.

🔧 Debug

advanced

Identify the error in merging on multiple keys

What error will this code raise when trying to merge df1 and df2 on ['key1', 'key3']?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({
    'key1': ['A', 'B'],
    'key2': [1, 2],
    'value': [100, 200]
})

df2 = pd.DataFrame({
    'key1': ['A', 'B'],
    'key3': [1, 2],
    'value': [300, 400]
})

result = pd.merge(df1, df2, on=['key1', 'key3'])

A KeyError: 'key3'

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

C ValueError: columns overlap but no suffix specified

D No error, merge runs successfully

💡 Hint

Check if both DataFrames have all columns specified in 'on'.

df1 does not have a column named 'key3', so pandas raises a KeyError when trying to merge on it.

🚀 Application

advanced

Result of merging with suffixes on overlapping columns

What will be the output DataFrame after merging df1 and df2 on ['id', 'date'] with suffixes ('_left', '_right')?

Data Analysis Python

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]
})

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

A

   id        date  value_right  value
0   1  2023-01-01         100     10
1   2  2023-01-02         200     20

B

   id        date  value  value
0   1  2023-01-01     10    100
1   2  2023-01-02     20    200

C

   id        date  value_left  value
0   1  2023-01-01          10    100
1   2  2023-01-02          20    200

D

   id        date  value_left  value_right
0   1  2023-01-01          10          100
1   2  2023-01-02          20          200

💡 Hint

Suffixes are added to overlapping column names except the keys.

Since 'value' exists in both DataFrames and is not a key, suffixes '_left' and '_right' are added to distinguish them.

🧠 Conceptual

expert

Understanding merge behavior with duplicate keys

Given df1 and df2 below, how many rows will the merged DataFrame have after merging on ['key1', 'key2'] with an inner join?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({
    'key1': ['A', 'A'],
    'key2': [1, 1],
    'val1': [10, 20]
})

df2 = pd.DataFrame({
    'key1': ['A', 'A'],
    'key2': [1, 1],
    'val2': [100, 200]
})

merged = pd.merge(df1, df2, on=['key1', 'key2'], how='inner')
print(len(merged))

A 4

B 3

C 1

D 2

💡 Hint

Think about how many combinations are formed when keys are duplicated in both DataFrames.

Each row in df1 with key ('A',1) matches with each row in df2 with the same key, resulting in 2*2=4 rows in the merged DataFrame.