merge() for SQL-style joins in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of inner join with merge()

What is the output DataFrame after performing an inner join using merge() on these two DataFrames by column key?

Data Analysis Python

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  val1  val2
0   A     1     4
1   B     2     5
2   C     3     6

B

  key  val1  val2
0   B     2     4
1   C     3     5

C

  key  val1  val2
0   B     2     5
1   C     3     6
2   D   NaN     6

D

  key  val1  val2
0   A     1   NaN
1   B     2   NaN
2   C     3   NaN

💡 Hint

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

An inner join returns rows where the key exists in both DataFrames. Here, keys 'B' and 'C' are common, so only those rows appear with their respective values.

❓ data_output

intermediate

Number of rows after left join

After performing a left join with merge() on these DataFrames by id, how many rows will the resulting DataFrame have?

Data Analysis Python

import pandas as pd

df_left = pd.DataFrame({'id': [1, 2, 3, 4], 'val_left': ['a', 'b', 'c', 'd']})
df_right = pd.DataFrame({'id': [3, 4, 5], 'val_right': ['x', 'y', 'z']})

result = pd.merge(df_left, df_right, on='id', how='left')

A 4

B 3

C 5

D 7

💡 Hint

Left join keeps all rows from the left DataFrame.

A left join keeps all rows from the left DataFrame regardless of matches in the right. Since df_left has 4 rows, the result has 4 rows.

🔧 Debug

advanced

Identify the error in merge() usage

What error will this code raise when trying to merge these DataFrames?

Data Analysis Python

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 ValueError: No common columns to perform merge on

B No error, merge runs successfully

C TypeError: merge() missing 1 required positional argument

D KeyError: 'A'

💡 Hint

Check if the column used for merging exists in both DataFrames.

Column 'A' exists only in df1, not in df2. Trying to merge on 'A' causes a ValueError.

❓ visualization

advanced

Visualize the result of an outer join

Which option shows the correct DataFrame output after performing an outer join on these DataFrames by key?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({'key': ['K0', 'K1', 'K2'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['K1', 'K2', 'K3'], 'val2': [4, 5, 6]})

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

A

  key  val1  val2
0  K0   NaN   NaN
1  K1   2.0   4.0
2  K2   3.0   5.0
3  K3   6.0   NaN

B

  key  val1  val2
0  K1   2.0   4.0
1  K2   3.0   5.0

C

  key  val1  val2
0  K0   1.0   NaN
1  K1   2.0   4.0
2  K2   3.0   5.0
3  K3   NaN   6.0

D

  key  val1  val2
0  K0   1.0   4.0
1  K1   2.0   5.0
2  K2   3.0   6.0

💡 Hint

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

Outer join includes all keys from both DataFrames. Keys K0 and K3 appear only in one DataFrame, so their missing values are NaN.

🚀 Application

expert

Combine DataFrames with multiple keys and suffixes

Given these DataFrames, what is the output of merging on columns city and year with suffixes _left and _right?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'LA'],
    'year': [2020, 2020, 2021, 2021],
    'pop': [8.3, 4.0, 8.4, 4.1]
})

df2 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'LA'],
    'year': [2020, 2020, 2021, 2021],
    'pop': [8.5, 4.1, 8.6, 4.2]
})

result = pd.merge(df1, df2, on=['city', 'year'], suffixes=('_left', '_right'))
print(result)

A

  city  year  pop_left
0   NY  2020       8.3
1   LA  2020       4.0
2   NY  2021       8.4
3   LA  2021       4.1

B

  city  year  pop
0   NY  2020  8.5
1   LA  2020  4.1
2   NY  2021  8.6
3   LA  2021  4.2

C

  city  year  pop_left  pop_right
0   NY  2020       8.5        8.3
1   LA  2020       4.1        4.0
2   NY  2021       8.6        8.4
3   LA  2021       4.2        4.1

D

  city  year  pop_left  pop_right
0   NY  2020       8.3        8.5
1   LA  2020       4.0        4.1
2   NY  2021       8.4        8.6
3   LA  2021       4.1        4.2

💡 Hint

Suffixes are added to overlapping column names except the keys.

Since both DataFrames have a 'pop' column, suffixes '_left' and '_right' are added to distinguish them in the merged DataFrame.

Practice

1. What does the how parameter in merge() control when joining two DataFrames?

easy

A. The data types of the columns

B. The columns to join on

C. The order of rows in the result

D. The type of join: inner, left, right, or outer

Solution

1. Step 1: Understand the role of how in merge()

   The how parameter specifies the type of join to perform, such as inner, left, right, or outer join.

2. Step 2: Differentiate from other parameters

   Other parameters like on specify columns to join on, but how controls how rows are matched and combined.

3. Final Answer:

   The type of join: inner, left, right, or outer -> Option D

4. Quick Check:

   how = join type [OK]

Hint: Remember: 'how' means join type like SQL joins [OK]

Common Mistakes:

• Confusing 'how' with 'on' parameter
• Thinking 'how' controls column selection
• Assuming 'how' changes data types

2. Which of the following is the correct syntax to merge two DataFrames df1 and df2 on the column 'id' using an inner join?

easy

A. pd.merge(df1, df2, join='id', how='inner')

B. pd.merge(df1, df2, on='id', how='inner')

C. df1.merge(df2, on='id', type='inner')

D. pd.merge(df1, df2, on='id', join='inner')

Solution

1. Step 1: Identify correct parameter names

   The correct parameters for merging are on for the column and how for join type.

2. Step 2: Check syntax correctness

   pd.merge(df1, df2, on='id', how='inner') uses pd.merge(df1, df2, on='id', how='inner'), which is the correct syntax. Other options use incorrect parameter names like join or type.

3. Final Answer:

   pd.merge(df1, df2, on='id', how='inner') -> Option B

4. Quick Check:

   Correct parameters: on, how [OK]

Hint: Use 'on' for columns and 'how' for join type [OK]

Common Mistakes:

• Using 'join' instead of 'how'
• Using 'type' instead of 'how'
• Mixing parameter names

3. Given the following code, what is the output of result?

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)

medium

A. key val1 val2
0 B 2 4
1 C 3 5

B. key val1 val2
0 A 1 4
1 B 2 5
2 C 3 6

C. key val1 val2
0 A 1 NaN
1 B 2 4
2 C 3 5

D. key val1 val2
0 B 2 NaN
1 C 3 NaN
2 D NaN 6

Solution

1. Step 1: Understand inner join on 'key'

   Inner join keeps only rows with keys present in both DataFrames: 'B' and 'C'.

2. Step 2: Match rows and combine columns

   For 'B': val1=2, val2=4; for 'C': val1=3, val2=5. Rows with 'A' and 'D' are excluded.

3. Final Answer:

   key val1 val2 0 B 2 4 1 C 3 5 -> Option A

4. Quick Check:

   Inner join = intersection keys [OK]

Hint: Inner join keeps only common keys [OK]

Common Mistakes:

• Including keys not in both DataFrames
• Confusing inner with outer join
• Misaligning values for keys

4. What is wrong with this code snippet?

import pandas as pd

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

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

medium

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

B. The 'how' parameter cannot be 'left'

C. The merge function is missing the 'left_on' and 'right_on' parameters

D. Column 'id' does not exist in both DataFrames

Solution

1. Step 1: Check columns used for merging

   The code tries to merge on column 'id', but df2 has 'key' column instead of 'id'.

2. Step 2: Understand merge requirements

   For merging on different column names, left_on and right_on must be used, or columns must have the same name.

3. Final Answer:

   The merge function is missing the 'left_on' and 'right_on' parameters -> Option C

4. Quick Check:

   Merge columns must exist in both DataFrames or be specified [OK]

Hint: Check column names exist in both DataFrames or specify left_on/right_on [OK]

Common Mistakes:

• Assuming merge works with different column names without specifying
• Thinking 'how' parameter is invalid
• Believing row counts must match

5. You have two DataFrames: orders with columns order_id, customer_id, and amount, and customers with columns cust_id and name. How do you merge them to get all orders with customer names, even if some customers are missing in customers DataFrame?

hard

A. pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left')

B. pd.merge(orders, customers, on='customer_id', how='inner')

C. pd.merge(orders, customers, left_on='cust_id', right_on='customer_id', how='right')

D. pd.merge(orders, customers, on='cust_id', how='outer')

Solution

1. Step 1: Identify join keys with different column names

   Orders use 'customer_id', customers use 'cust_id', so use left_on and right_on to specify.

2. Step 2: Choose join type to keep all orders

   Use how='left' to keep all rows from orders, even if no matching customer exists.

3. Final Answer:

   pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left') -> Option A

4. Quick Check:

   Left join keeps all left rows [OK]

Hint: Use left_on/right_on for different keys and 'left' to keep all left rows [OK]

Common Mistakes:

• Using 'on' with different column names
• Using inner join and losing unmatched orders
• Swapping left_on and right_on