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

Need a 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

Need a 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

Need a 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

Need a hint?

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