Outer join behavior in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this outer join?

Given two pandas DataFrames df1 and df2:

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

What is the result of pd.merge(df1, df2, on='key', how='outer')?

Pandas

import pandas as pd
df1 = pd.DataFrame({"key": [1, 2, 3], "val1": ["A", "B", "C"]})
df2 = pd.DataFrame({"key": [2, 3, 4], "val2": ["X", "Y", "Z"]})
result = pd.merge(df1, df2, on='key', how='outer')
print(result.sort_values('key').reset_index(drop=True))

A [{'key': 1, 'val1': 'A', 'val2': 'X'}, {'key': 2, 'val1': 'B', 'val2': 'Y'}, {'key': 3, 'val1': 'C', 'val2': 'Z'}]

B [{'key': 1, 'val1': 'A', 'val2': None}, {'key': 2, 'val1': 'B', 'val2': 'X'}, {'key': 3, 'val1': 'C', 'val2': 'Y'}, {'key': 4, 'val1': None, 'val2': 'Z'}]

C [{'key': 2, 'val1': 'B', 'val2': 'X'}, {'key': 3, 'val1': 'C', 'val2': 'Y'}]

D [{'key': 1, 'val1': 'A', 'val2': 'X'}, {'key': 4, 'val1': None, 'val2': 'Z'}]

💡 Hint

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

Outer join returns all rows from both DataFrames. For keys missing in one DataFrame, the corresponding columns are filled with None.

❓ query_result

intermediate

How many rows after outer join with duplicate keys?

Consider these DataFrames with duplicate keys:

df1 = pd.DataFrame({"key": [1, 2, 2], "val1": ["A", "B", "C"]})
df2 = pd.DataFrame({"key": [2, 2, 3], "val2": ["X", "Y", "Z"]})

How many rows will pd.merge(df1, df2, on='key', how='outer') have?

Pandas

import pandas as pd
df1 = pd.DataFrame({"key": [1, 2, 2], "val1": ["A", "B", "C"]})
df2 = pd.DataFrame({"key": [2, 2, 3], "val2": ["X", "Y", "Z"]})
result = pd.merge(df1, df2, on='key', how='outer')
print(len(result))

A 5

B 7

C 6

D 4

💡 Hint

Remember that duplicates cause a Cartesian product for matching keys.

For key=2, df1 has 2 rows and df2 has 2 rows, so 2*2=4 rows for key=2. Plus one row for key=1 and one for key=3, total 6 rows.

📝 Syntax

advanced

Which code snippet correctly performs an outer join on two DataFrames?

Choose the code that correctly performs an outer join on df1 and df2 using pandas.

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

B df1.merge(df2, how='outer')

C pd.concat([df1, df2], join='outer')

D df1.join(df2, how='outer')

💡 Hint

Check the correct function and parameters for outer join in pandas.

Option A uses pd.merge with how='outer' and on='key', which is the correct syntax for outer join on a key column.

Option A is valid but joins on index by default, and how='outer' is valid but without specifying keys it joins on index.

Option A uses pd.concat which concatenates rows or columns, not a join.

Option A is invalid because merge method on DataFrame requires on parameter to specify join keys.

🔧 Debug

advanced

Why does this outer join produce unexpected NaNs?

Given these DataFrames:

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

Why does pd.merge(df1, df2, on='key', how='outer') produce NaNs in all columns except the keys?

Pandas

import pandas as pd
df1 = pd.DataFrame({"key": [1, 2], "val1": ["A", "B"]})
df2 = pd.DataFrame({"key": ["1", "2"], "val2": ["X", "Y"]})
result = pd.merge(df1, df2, on='key', how='outer')
print(result)

A Because the key columns have different data types (int vs string), so no matches occur.

B Because outer join only keeps rows with matching keys, so unmatched rows get NaN.

C Because the DataFrames have different column names, causing merge to fail silently.

D Because the merge function requires specifying left_on and right_on for different column names.

💡 Hint

Check the data types of the key columns in both DataFrames.

The keys have different types: integers in df1 and strings in df2. This causes no matches during merge, so all rows appear with NaNs in the other DataFrame's columns.

🧠 Conceptual

expert

What is the effect of outer join on index alignment in pandas?

When performing an outer join with pd.merge on two DataFrames with overlapping and non-overlapping keys, how does pandas handle the index of the resulting DataFrame?

A The resulting DataFrame uses a MultiIndex combining indexes from both DataFrames.

B The resulting DataFrame preserves the index from the left DataFrame only.

C The resulting DataFrame preserves the index from the right DataFrame only.

D The resulting DataFrame has a new default integer index from 0 to n-1, ignoring original indexes.

💡 Hint

Think about how pandas resets or preserves indexes after merge.

By default, pd.merge returns a DataFrame with a new default integer index. It does not preserve the original indexes unless left_index=True and right_index=True are specified.