Outer join behavior in Pandas - Step-by-Step Execution

Concept Flow - Outer join behavior

Start with two tables

↓

Match rows on key

↓

Keep all rows from both tables

↓

Fill missing values with NaN

↓

Result: Combined table with all rows

Outer join combines two tables by matching keys and keeps all rows from both tables, filling missing matches with NaN.

Execution Sample

Pandas

import pandas as pd

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

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

This code merges two dataframes on 'key' using an outer join, keeping all keys from both.

Execution Table

Step	Action	df1 rows	df2 rows	Matched keys	Result rows	Notes
1	Start with df1 and df2	A, B	B, C	None yet	None yet	Initial tables
2	Match keys between df1 and df2	A, B	B, C	B	None yet	Only 'B' matches
3	Combine matched rows	B row	B row	B	1	Row with key 'B' combined
4	Add unmatched rows from df1	A row	-	-	1 + 1 = 2	'A' from df1 added with NaN for df2 columns
5	Add unmatched rows from df2	-	C row	-	2 + 1 = 3	'C' from df2 added with NaN for df1 columns
6	Final result	A, B	B, C	B	3	All keys from both tables included

💡 All rows from both tables included; unmatched keys filled with NaN

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
result	empty	1 row (key B combined)	2 rows (added key A with NaN)	3 rows (added key C with NaN)	3 rows (keys A, B, C)

Key Moments - 2 Insights

Why does the result have NaN values?

Because outer join keeps all rows from both tables, rows without a matching key in the other table get NaN for missing columns, as shown in steps 4 and 5.

Why is the key 'B' row combined but 'A' and 'C' are not?

Only 'B' exists in both tables, so it combines. 'A' and 'C' exist in only one table each, so they appear with NaN for the other table's columns (see step 2 and 3).

Visual Quiz - 3 Questions

Look at the execution table, how many rows does the result have after step 5?

A 2

B 1

C 3

D 4

💡 Hint

Check the 'Result rows' column at step 5 in the execution_table.

At which step are unmatched rows from df1 added to the result?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look for the step mentioning adding unmatched rows from df1 in the execution_table.

If df2 had an extra key 'D', what would happen to the number of rows in the final result?

A It would stay the same

B It would increase by 1

C It would decrease by 1

D It would double

💡 Hint

Outer join keeps all keys from both tables, so adding a new key in df2 adds a new row (see variable_tracker).

Concept Snapshot

Outer join in pandas merges two tables keeping all rows from both.
Syntax: pd.merge(df1, df2, on='key', how='outer')
Matched keys combine rows; unmatched keys fill missing columns with NaN.
Result includes all keys from both tables.
Useful to keep full data when keys don't fully overlap.

Full Transcript

Outer join behavior in pandas means combining two tables by matching keys and keeping all rows from both tables. When keys match, rows merge. When keys don't match, rows from each table still appear with missing values filled as NaN. The example code merges two dataframes on 'key' using an outer join. The execution table shows step-by-step how matched rows combine and unmatched rows add with NaN. Variable tracking shows the result grows from empty to three rows including all keys. Key moments clarify why NaN appears and why only matching keys combine. The visual quiz tests understanding of row counts and steps where unmatched rows add. The snapshot summarizes syntax and behavior for quick reference.

Practice

1. What does an outer join do when merging two DataFrames in pandas?

easy

A. Keeps only rows from the right DataFrame

B. Keeps only rows that match in both DataFrames

C. Keeps all rows from both DataFrames, filling missing values with NaN

D. Keeps only rows from the left DataFrame

Solution

1. Step 1: Understand the join types in pandas

   Inner join keeps only matching rows, left join keeps all from left, right join keeps all from right, outer join keeps all from both.

2. Step 2: Identify behavior of outer join

   Outer join includes all rows from both DataFrames and fills missing matches with NaN.

3. Final Answer:

   Keeps all rows from both DataFrames, filling missing values with NaN -> Option C

4. Quick Check:

   Outer join = all rows kept with NaN for missing [OK]

Hint: Outer join keeps all rows, fills missing with NaN [OK]

Common Mistakes:

• Confusing outer join with inner join
• Thinking outer join keeps only left or right rows
• Assuming missing data is dropped instead of NaN

2. Which of the following is the correct syntax to perform an outer join on DataFrames df1 and df2 using pandas?

easy

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

B. pd.merge(df1, df2, join='outer')

C. df1.join(df2, join='outer')

D. df1.merge(df2, type='outer')

Solution

1. Step 1: Recall the pandas merge syntax

   The function pd.merge() takes parameters including left DataFrame, right DataFrame, and how to join.

2. Step 2: Identify correct parameter for outer join

   The parameter to specify join type is how='outer'. Other options like join='outer' or type='outer' are invalid.

3. Final Answer:

   pd.merge(df1, df2, how='outer') -> Option A

4. Quick Check:

   Use how='outer' in pd.merge() [OK]

Hint: Use how='outer' in pd.merge() for outer join [OK]

Common Mistakes:

• Using join='outer' instead of how='outer'
• Using type='outer' which is invalid
• Using df1.join() without specifying correct parameters

3. Given the following DataFrames:

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

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

medium

A. {'key': ['A', 'B'], 'val1': [1, 2], 'val2': [NaN, 3]}

B. {'key': ['A', 'B', 'C'], 'val1': [1, 2, NaN], 'val2': [NaN, 3, 4]}

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

D. {'key': ['B', 'C'], 'val1': [2, NaN], 'val2': [3, 4]}

Solution

1. Step 1: Identify keys and values in both DataFrames

   df1 keys: A, B with val1 1, 2; df2 keys: B, C with val2 3, 4.

2. Step 2: Perform outer join on 'key'

   Outer join keeps all keys: A, B, C. For A val2 is missing (NaN), for C val1 is missing (NaN), B has both values.

3. Final Answer:

   {'key': ['A', 'B', 'C'], 'val1': [1, 2, NaN], 'val2': [NaN, 3, 4]} -> Option B

4. Quick Check:

   Outer join keeps all keys with NaN for missing values [OK]

Hint: Outer join keys = union of keys, missing values become NaN [OK]

Common Mistakes:

• Confusing outer join with inner join (only B)
• Missing keys from either DataFrame
• Not filling missing values with NaN

4. You wrote this code but got an error:

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

What is the likely cause of the error?

medium

A. Cannot use both 'on' and 'left_index=True' together in merge

B. Missing 'right_index=True' parameter

C. The 'how' parameter should be 'outerjoin' not 'outer'

D. DataFrames must have the same columns to merge

Solution

1. Step 1: Understand parameters 'on' and 'left_index'

   'on' specifies column to join on; 'left_index=True' uses index for join. Both cannot be used simultaneously.

2. Step 2: Identify conflict causing error

   Using 'on' and 'left_index=True' together causes pandas to raise an error because join keys are ambiguous.

3. Final Answer:

   Cannot use both 'on' and 'left_index=True' together in merge -> Option A

4. Quick Check:

   'on' and 'left_index=True' conflict in merge [OK]

Hint: Don't mix 'on' with 'left_index=True' in merge [OK]

Common Mistakes:

• Assuming 'how'='outerjoin' is valid
• Forgetting that 'on' and 'left_index' conflict
• Thinking DataFrames must have same columns to merge

5. You have two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'score': [10, 20, 30]})
df2 = pd.DataFrame({'id': [2, 3, 4], 'grade': ['B', 'A', 'C']})

You want to merge them with an outer join but replace all NaN in 'score' with 0 and in 'grade' with 'N/A'. Which code correctly does this?

hard

A. result = pd.merge(df1, df2, on='id', how='outer').fillna({'score': 0})

B. result = pd.merge(df1, df2, on='id', how='outer').fillna(0).fillna('N/A')

C. result = pd.merge(df1, df2, on='id', how='outer').fillna({'score': 'N/A', 'grade': 0})

D. result = pd.merge(df1, df2, on='id', how='outer').fillna({'score': 0, 'grade': 'N/A'})

Solution

1. Step 1: Perform outer join on 'id'

   Outer join keeps all ids: 1, 2, 3, 4. Missing 'score' or 'grade' will be NaN.

2. Step 2: Replace NaN with correct values per column

   Use fillna with a dictionary to specify 0 for 'score' and 'N/A' for 'grade'. Other options either fill all with 0 or wrong values.

3. Final Answer:

   result = pd.merge(df1, df2, on='id', how='outer').fillna({'score': 0, 'grade': 'N/A'}) -> Option D

4. Quick Check:

   Use fillna with dict for column-specific NaN replacement [OK]

Hint: Use fillna({'col1': val1, 'col2': val2}) for different columns [OK]

Common Mistakes:

• Using fillna(0) for all columns, replacing strings incorrectly
• Swapping fillna values between columns
• Not filling NaN for all missing columns