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.

Practice

1.

What does merging on multiple keys in pandas allow you to do?

Choose the best explanation.

easy

A. Join dataframes by adding new columns without matching rows.

B. Merge dataframes only by index without considering columns.

C. Sort dataframes based on multiple columns before merging.

D. Combine two dataframes using more than one column to match rows.

Solution

1. Step 1: Understand merging concept

   Merging combines rows from two dataframes based on matching column values.

2. Step 2: Identify multiple keys usage

   Using multiple keys means matching rows where all specified columns have the same values.

3. Final Answer:

   Combine two dataframes using more than one column to match rows. -> Option D

4. Quick Check:

   Merging on multiple keys = combine on several columns [OK]

Hint: Think of matching rows by multiple columns at once [OK]

Common Mistakes:

• Confusing merge with concatenation
• Using only one column when multiple are needed
• Merging without specifying keys
• Assuming merge sorts data automatically

2.

Which of the following is the correct syntax to merge two pandas DataFrames df1 and df2 on columns 'city' and 'year'?

import pandas as pd

# Assume df1 and df2 are defined
merged_df = 

easy

A. pd.merge(df1, df2, on=['city', 'year'])

B. pd.merge(df1, df2, keys=['city', 'year'])

C. df1.merge(df2, by=['city', 'year'])

D. df1.join(df2, on=['city', 'year'])

Solution

1. Step 1: Recall pandas merge syntax

   The correct parameter to specify columns for merging is on with a list of column names.

2. Step 2: Check each option

   pd.merge(df1, df2, on=['city', 'year']) uses on=['city', 'year'], which is correct. Others use wrong parameters like keys or by.

3. Final Answer:

   pd.merge(df1, df2, on=['city', 'year']) -> Option A

4. Quick Check:

   Use on for columns list in merge [OK]

Hint: Use 'on' parameter with list of columns for multiple keys [OK]

Common Mistakes:

• Using 'keys' instead of 'on'
• Using 'by' instead of 'on'
• Using join() instead of merge() for multiple keys
• Passing columns as a string instead of list

3.

Given the following dataframes:

import pandas as pd

df1 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'SF'],
    'year': [2020, 2020, 2021, 2020],
    'value1': [10, 20, 30, 40]
})

df2 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'SF'],
    'year': [2020, 2021, 2021, 2020],
    'value2': [100, 200, 300, 400]
})

merged = pd.merge(df1, df2, on=['city', 'year'], how='inner')
print(merged)

What will be the output?

medium

A. All rows from df1 with matching rows from df2 or NaN if no match

B. All rows from df2 with matching rows from df1 or NaN if no match

C. Rows where city and year match in both dfs: NY-2020, NY-2021, SF-2020

D. Concatenation of df1 and df2 ignoring keys

Solution

1. Step 1: Understand inner merge on multiple keys

   Inner merge returns rows where all keys match in both dataframes.

2. Step 2: Identify matching rows

   Matching pairs are: ('NY', 2020), ('NY', 2021), and ('SF', 2020). 'LA' with year 2020 or 2021 does not match in both.

3. Final Answer:

   Rows where city and year match in both dfs: NY-2020, NY-2021, SF-2020 -> Option C

4. Quick Check:

   Inner merge = only matching keys rows [OK]

Hint: Inner merge keeps only rows matching all keys [OK]

Common Mistakes:

• Confusing inner with outer merge
• Expecting all rows from one dataframe
• Ignoring that both keys must match
• Assuming concatenation instead of merge

4.

Consider this code snippet:

import pandas as pd

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

merged = pd.merge(df1, df2, on=['A', 'B'])
print(merged)

What is the issue if the output is an empty dataframe?

medium

A. The values in column 'B' do not match exactly in both dataframes for all rows.

B. The merge syntax is incorrect; 'on' should be 'by'.

C. DataFrames must have the same number of rows to merge.

D. You cannot merge on multiple columns with the same names.

Solution

1. Step 1: Check merge keys values

   Rows merge only if all key columns match exactly. Here, df1 has B=4 in second row, df2 has B=5.

2. Step 2: Understand empty result cause

   Because second rows differ in 'B', only first rows match. First rows have A=1 and B=3 in both, so one row should merge. If output is empty, likely no exact matches.

3. Final Answer:

   The values in column 'B' do not match exactly in both dataframes for all rows. -> Option A

4. Quick Check:

   Exact key match needed for merge rows [OK]

Hint: Check all key columns have matching values for merge [OK]

Common Mistakes:

• Thinking merge needs same row count
• Using wrong parameter name 'by'
• Assuming merge works without exact key matches
• Believing multiple keys with same names cause error

5.

You have two dataframes:

df_sales = pd.DataFrame({
    'store': ['A', 'B', 'A', 'B'],
    'month': [1, 1, 2, 2],
    'sales': [100, 200, 150, 250]
})

df_targets = pd.DataFrame({
    'store': ['A', 'B'],
    'month': [1, 2],
    'target': [120, 230]
})

You want to merge these to compare sales with targets for matching store and month. Which merge type should you use to keep all sales data and add targets where available?

hard

A. Use how='outer' to keep all rows from both dataframes.

B. Use how='left' to keep all sales rows and add matching targets.

C. Use how='right' to keep all targets and add matching sales.

D. Use how='inner' to keep only rows with matching store and month in both.

Solution

1. Step 1: Identify main dataframe to keep

   You want to keep all sales data, so df_sales is the left/main dataframe.

2. Step 2: Choose merge type

   Left merge keeps all rows from left dataframe and adds matching rows from right. Missing matches get NaN.

3. Final Answer:

   Use how='left' to keep all sales rows and add matching targets. -> Option B

4. Quick Check:

   Left merge = keep left all, add right matches [OK]

Hint: Left merge keeps all left rows, adds matching right rows [OK]

Common Mistakes:

• Using inner merge and losing unmatched sales
• Using right merge and losing unmatched sales
• Using outer merge and getting extra unmatched rows
• Confusing which dataframe is left or right