Outer join behavior in Pandas - Mini Project: Build & Apply

Understanding Outer Join Behavior with pandas

📖 Scenario: You work in a small company that keeps two separate lists: one for employees and one for their assigned projects. Sometimes, employees may not have projects yet, and sometimes projects may not have assigned employees. You want to combine these lists to see all employees and projects together, even if some don't match.

🎯 Goal: Build a pandas DataFrame that shows all employees and all projects combined using an outer join. This means you will see every employee and every project, matched where possible, and with missing data filled with NaN.

📋 What You'll Learn

Create two pandas DataFrames: employees and projects with exact data.

Create a variable join_key with the column name to join on.

Use pandas merge function with how='outer' to join the DataFrames on join_key.

Store the result in a variable called combined.

💡 Why This Matters

🌍 Real World

Combining employee and project data from separate sources to get a complete view of assignments.

💼 Career

Data analysts and database professionals often need to join tables to create comprehensive reports.

1

Create the employees DataFrame

Create a pandas DataFrame called employees with these exact columns and data: 'emp_id' with values [1, 2, 3] and 'emp_name' with values ['Alice', 'Bob', 'Charlie'].

Pandas

import pandas as pd
# Create the employees DataFrame with emp_id and emp_name columns
# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of data.

2

Create the projects DataFrame

Create a pandas DataFrame called projects with these exact columns and data: 'emp_id' with values [2, 3, 4] and 'project_name' with values ['Project X', 'Project Y', 'Project Z'].

Pandas

import pandas as pd

employees = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'emp_name': ['Alice', 'Bob', 'Charlie']
})

# Create the projects DataFrame with emp_id and project_name columns
# Your code here

Hint

Use pd.DataFrame with a dictionary for the projects data.

3

Set the join key

Create a variable called join_key and set it to the string 'emp_id'. This will be the column used to join the DataFrames.

Pandas

import pandas as pd

employees = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'emp_name': ['Alice', 'Bob', 'Charlie']
})

projects = pd.DataFrame({
    'emp_id': [2, 3, 4],
    'project_name': ['Project X', 'Project Y', 'Project Z']
})

# Create the join_key variable with value 'emp_id'
# Your code here

Hint

Just assign the string 'emp_id' to the variable join_key.

4

Perform the outer join

Use the pandas merge function to join employees and projects on the column join_key with how='outer'. Store the result in a variable called combined.

Pandas

import pandas as pd

employees = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'emp_name': ['Alice', 'Bob', 'Charlie']
})

projects = pd.DataFrame({
    'emp_id': [2, 3, 4],
    'project_name': ['Project X', 'Project Y', 'Project Z']
})

join_key = 'emp_id'

# Use pd.merge with how='outer' to join employees and projects on join_key
# Your code here

Hint

Use pd.merge(employees, projects, on=join_key, how='outer') and assign it to combined.

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