Outer join in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this left outer join?

Given two dataframes df1 and df2:

df1 = pd.DataFrame({"id": [1, 2, 3], "value1": [10, 20, 30]})
df2 = pd.DataFrame({"id": [2, 3, 4], "value2": [200, 300, 400]})

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

What will be printed?

Data Analysis Python

import pandas as pd
df1 = pd.DataFrame({"id": [1, 2, 3], "value1": [10, 20, 30]})
df2 = pd.DataFrame({"id": [2, 3, 4], "value2": [200, 300, 400]})
result = pd.merge(df1, df2, on="id", how="left")
print(result)

A

   id  value1  value2
0   1      10   100.0
1   2      20   200.0
2   3      30   300.0

B

   id  value1  value2
0   1      10     NaN
1   2      20   200.0
2   3      30   300.0

C

   id  value1  value2
0   1      10   200.0
1   2      20   300.0
2   3      30   400.0

D

   id  value1  value2
0   2      20   200.0
1   3      30   300.0
2   4     NaN   400.0

💡 Hint

Remember, a left join keeps all rows from the left dataframe and matches rows from the right dataframe where possible.

The left join keeps all rows from df1. For id=1, there is no matching id in df2, so value2 is NaN. For id=2 and id=3, matching rows exist, so values from df2 are included.

❓ query_result

intermediate

What is the output of this full outer join?

Given two dataframes df1 and df2:

df1 = pd.DataFrame({"key": ["A", "B", "C"], "val1": [1, 2, 3]})
df2 = pd.DataFrame({"key": ["B", "C", "D"], "val2": [20, 30, 40]})

result = pd.merge(df1, df2, on="key", how="outer")
print(result)

What will be printed?

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": [20, 30, 40]})
result = pd.merge(df1, df2, on="key", how="outer")
print(result)

A

  key  val1  val2
0   A   1.0   NaN
1   B   2.0  20.0
2   C   3.0  30.0
3   D   NaN  40.0

B

  key  val1  val2
0   A   1.0  20.0
1   B   2.0  30.0
2   C   3.0  40.0

C

  key  val1  val2
0   B   2.0  20.0
1   C   3.0  30.0
2   D   NaN  40.0

D

  key  val1  val2
0   A   NaN   NaN
1   B   2.0  20.0
2   C   3.0  30.0
3   D   40.0  40.0

💡 Hint

Full outer join keeps all rows from both dataframes, filling missing values with NaN.

The full outer join includes all keys from both df1 and df2. For key='A', only df1 has a value, so val2 is NaN. For key='D', only df2 has a value, so val1 is NaN. Keys B and C appear in both.

📝 Syntax

advanced

Which option produces a syntax error in this outer join code?

Consider the following code snippet to perform an outer join in pandas:

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

Which of the following options will cause a syntax error?

Data Analysis Python

import pandas as pd
pd.merge(df1, df2, on='id', how='outer')

A pd.merge(df1, df2, on='id', how='outer')

B )'retuo'=woh ,'di'=no ,2fd ,1fd(egrem.dp

C pd.merge(df1, df2, on='id' how='outer')

D d.merge(df1, df2, on='id', how='outer')

💡 Hint

Check for missing commas between arguments.

Option C is missing a comma between on='id' and how='outer', causing a syntax error. The other options have correct syntax.

❓ optimization

advanced

Which option is the most efficient way to perform a full outer join on large dataframes?

You have two large dataframes df1 and df2 with millions of rows. You want to perform a full outer join on column key. Which option is the most efficient?

A pd.merge(df1, df2, on='key', how='inner', sort=False)

B pd.merge(df1, df2, on='key', how='outer', sort=True)

C pd.merge(df1, df2, on='key', how='outer').sort_values('key')

D pd.merge(df1, df2, on='key', how='outer', sort=False)

💡 Hint

Sorting during merge can slow down performance on large data.

Option D disables sorting during the merge, which improves performance on large dataframes. Option D sorts during merge, slowing it down. Option D sorts after merge, adding extra cost. Option D uses inner join, which is not the requested full outer join.

🧠 Conceptual

expert

What is the number of rows in the result of a full outer join?

Given two tables T1 and T2 with unique keys, the number of rows in a full outer join on the key column is:

A The sum of the number of rows in <code>T1</code> and <code>T2</code> minus the number of keys common to both

B The number of rows in <code>T1</code> only

C The number of rows in <code>T2</code> only

D The product of the number of rows in <code>T1</code> and <code>T2</code>

💡 Hint

Think about how full outer join combines all unique keys from both tables.

A full outer join includes all rows from both tables. Rows with matching keys appear once, so the total rows equal the sum of rows minus the overlap (common keys).