Outer join in Data Analysis Python - Mini Project: Build & Apply

Outer Join with Python Data Analysis

📖 Scenario: You work in a small company that keeps customer and order information in separate tables. You want to combine these tables to see all customers and their orders, including customers who have not placed any orders yet.

🎯 Goal: Build a Python script that performs an outer join on two data tables (dictionaries) to combine customer and order data, showing all customers and their orders, including those without orders.

📋 What You'll Learn

Create two dictionaries: customers and orders with given data

Create a list all_customer_ids that contains all unique customer IDs from both dictionaries

Use a for loop to perform an outer join by iterating over all_customer_ids

Create a final dictionary customer_orders that maps each customer ID to their order or None if no order exists

💡 Why This Matters

🌍 Real World

Combining customer and order data is common in business to understand sales and customer activity.

💼 Career

Data analysts and database professionals often perform joins to merge data from different sources for reporting and analysis.

1

Create customer and order data dictionaries

Create a dictionary called customers with these exact entries: 1: 'Alice', 2: 'Bob', 3: 'Charlie'. Also create a dictionary called orders with these exact entries: 2: 'Order1001', 3: 'Order1002', 4: 'Order1003'.

Data Analysis Python

# Create the customers and orders dictionaries
# Your code here

Hint

Use curly braces {} to create dictionaries with the exact keys and values given.

2

Create a list of all unique customer IDs

Create a list called all_customer_ids that contains all unique keys from both customers and orders dictionaries combined. Use the set() function and union() method to combine keys.

Data Analysis Python

customers = {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
orders = {2: 'Order1001', 3: 'Order1002', 4: 'Order1003'}
# Create all_customer_ids list with unique keys from both dictionaries
# Your code here

Hint

Use set(customers.keys()) and set(orders.keys()) to get keys, then combine with union().

3

Perform the outer join using a for loop

Create an empty dictionary called customer_orders. Use a for loop with variable customer_id to iterate over all_customer_ids. Inside the loop, add an entry to customer_orders with key customer_id and value as a tuple of the customer name from customers (or None if missing) and the order from orders (or None if missing). Use the get() method for safe access.

Data Analysis Python

customers = {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
orders = {2: 'Order1001', 3: 'Order1002', 4: 'Order1003'}
all_customer_ids = list(set(customers.keys()).union(orders.keys()))
# Create customer_orders dictionary and fill it with outer join results
# Your code here

Hint

Use dict.get(key) to get values safely, returning None if the key is missing.

4

Complete the outer join dictionary

Add a final line that assigns the customer_orders dictionary to a variable called final_result. This completes the data structure for further use.

Data Analysis Python

customers = {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
orders = {2: 'Order1001', 3: 'Order1002', 4: 'Order1003'}
all_customer_ids = list(set(customers.keys()).union(orders.keys()))
customer_orders = {}
for customer_id in all_customer_ids:
    customer_orders[customer_id] = (customers.get(customer_id), orders.get(customer_id))
# Assign customer_orders to final_result
# Your code here

Hint

Simply assign the dictionary customer_orders to final_result.

Practice

1. What does an outer join do when combining two tables?

easy

A. Keeps all rows from both tables, filling missing matches with nulls

B. Keeps only rows that match in both tables

C. Keeps only rows from the first table

D. Keeps only rows from the second table

Solution

1. Step 1: Understand join types

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

2. Step 2: Define outer join behavior

   Outer join fills missing matches with null (or NaN) values to keep all data.

3. Final Answer:

   Keeps all rows from both tables, filling missing matches with nulls -> Option A

4. Quick Check:

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

Hint: Outer join keeps all rows from both tables with nulls for missing [OK]

Common Mistakes:

• Confusing outer join with inner join
• Thinking outer join keeps only one table's rows
• Assuming missing matches are dropped

2. Which of the following is the correct syntax to perform a full outer join using pandas?

easy

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

B. pd.join(df1, df2, type='full')

C. df1.outer_join(df2)

D. pd.merge(df1, df2, how='fulljoin')

Solution

1. Step 1: Recall pandas merge syntax

   Use pd.merge() with how='outer' for full outer join.

2. Step 2: Check options for correctness

   pd.merge(df1, df2, how='outer') matches correct syntax; others use invalid methods or parameters.

3. Final Answer:

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

4. Quick Check:

   pandas full outer join = merge with how='outer' [OK]

Hint: Use pd.merge with how='outer' for full outer join in pandas [OK]

Common Mistakes:

• Using pd.join instead of pd.merge for outer join
• Wrong parameter names like 'fulljoin' or 'type'
• Calling non-existent methods like outer_join

3. Given these two DataFrames:

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

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

medium

A. [{'id':1, 'val1':'A'}, {'id':2, 'val1':'B'}]

B. [{'id':2, 'val1':'B', 'val2':'X'}]

C. [{'id':1, 'val1':'A', 'val2':NaN}, {'id':2, 'val1':'B', 'val2':'X'}, {'id':3, 'val1':NaN, 'val2':'Y'}]

D. [{'id':3, 'val2':'Y'}]

Solution

1. Step 1: Identify keys and join type

   Joining on 'id' with full outer join keeps all ids from both DataFrames.

2. Step 2: Combine rows with matching and missing data

   id=1 only in df1, id=2 in both, id=3 only in df2; missing values become NaN.

3. Final Answer:

   [{'id':1, 'val1':'A', 'val2':NaN}, {'id':2, 'val1':'B', 'val2':'X'}, {'id':3, 'val1':NaN, 'val2':'Y'}] -> Option C

4. Quick Check:

   Outer join keeps all ids with NaN for missing columns [OK]

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

Common Mistakes:

• Expecting only matching rows (inner join)
• Ignoring NaN for missing matches
• Confusing left or right join results

4. What is wrong with this code snippet for an outer join?

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

medium

A. df1 and df2 must have the same columns

B. The 'on' parameter should be a list, not a string

C. pd.merge cannot perform outer joins

D. The 'how' parameter value 'outside' is invalid

Solution

1. Step 1: Check 'how' parameter values

   Valid values are 'left', 'right', 'inner', 'outer'; 'outside' is invalid.

2. Step 2: Validate other parameters

   'on' can be string or list; pd.merge supports outer joins; columns can differ.

3. Final Answer:

   The 'how' parameter value 'outside' is invalid -> Option D

4. Quick Check:

   Invalid join type causes error [OK]

Hint: Check 'how' parameter spelling for join type [OK]

Common Mistakes:

• Using invalid join type strings
• Thinking 'on' must be a list always
• Assuming pd.merge can't do outer joins

5. You have two DataFrames:

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

You want to perform a full outer join on 'id' and get all combinations of matching rows (including duplicates). Which code correctly achieves this?

hard

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

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

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

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

Solution

1. Step 1: Understand join type needed

   Full outer join keeps all rows from both DataFrames, including duplicates.

2. Step 2: Check code options for duplicates

   pd.merge(df1, df2, on='id', how='outer') keeps duplicates; pd.merge(df1, df2, on='id', how='outer').drop_duplicates() removes duplicates; pd.merge(df1, df2, on='id', how='inner') is inner join; pd.merge(df1, df2, on='id', how='outer', indicator=True) adds indicator but doesn't affect duplicates.

3. Final Answer:

   pd.merge(df1, df2, on='id', how='outer') -> Option B

4. Quick Check:

   Full outer join keeps duplicates unless dropped [OK]

Hint: Full outer join keeps duplicates; don't drop them unless needed [OK]

Common Mistakes:

• Using inner join instead of outer join
• Dropping duplicates unintentionally
• Confusing indicator parameter with join behavior