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

Inner Join with Python Dictionaries

📖 Scenario: You are working with two small datasets representing customers and their orders in a simple store system. You want to combine these datasets to see which customers placed which orders.

🎯 Goal: Build a Python script that performs an inner join on two dictionaries based on a common key, customer_id, to combine customer names with their order details.

📋 What You'll Learn

Create two dictionaries: customers and orders with exact entries

Create a list variable joined_data to store the inner join results

Use a for loop to iterate over orders and match customer_id with customers

Append tuples of (customer_name, order_id, product) to joined_data

💡 Why This Matters

🌍 Real World

Inner joins are used in databases and data analysis to combine related data from different tables based on common keys, like customer IDs.

💼 Career

Understanding inner joins is essential for data analysts, database administrators, and software developers working with relational data.

1

Create the customers dictionary

Create a dictionary called customers with these exact entries: 1: 'Alice', 2: 'Bob', 3: 'Charlie'.

Data Analysis Python

# Create the customers dictionary
# Your code here

Hint

Use curly braces {} to create a dictionary with integer keys and string values.

2

Create the orders dictionary

Create a dictionary called orders with these exact entries: 101: {'customer_id': 1, 'product': 'Book'}, 102: {'customer_id': 2, 'product': 'Pen'}, 103: {'customer_id': 4, 'product': 'Notebook'}.

Data Analysis Python

customers = {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
# Create the orders dictionary
# Your code here

Hint

Use nested dictionaries for each order with keys customer_id and product.

3

Create the joined_data list and loop to join

Create an empty list called joined_data. Use a for loop with variables order_id and order_info to iterate over orders.items(). Inside the loop, check if order_info['customer_id'] is in customers. If yes, append a tuple (customers[order_info['customer_id']], order_id, order_info['product']) to joined_data.

Data Analysis Python

customers = {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
orders = {101: {'customer_id': 1, 'product': 'Book'}, 102: {'customer_id': 2, 'product': 'Pen'}, 103: {'customer_id': 4, 'product': 'Notebook'}}
# Create joined_data list and perform inner join
# Your code here

Hint

Use orders.items() to get both order ID and info, then check if the customer ID exists in customers.

4

Complete the inner join by verifying joined_data

Add a final line that assigns result to joined_data to complete the inner join data structure.

Data Analysis Python

customers = {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
orders = {101: {'customer_id': 1, 'product': 'Book'}, 102: {'customer_id': 2, 'product': 'Pen'}, 103: {'customer_id': 4, 'product': 'Notebook'}}
joined_data = []
for order_id, order_info in orders.items():
    if order_info['customer_id'] in customers:
        joined_data.append((customers[order_info['customer_id']], order_id, order_info['product']))
# Assign result to joined_data
# Your code here

Hint

Simply assign the joined_data list to a new variable result to finalize.

Practice

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

easy

A. Returns only rows with matching keys in both tables

B. Returns all rows from the first table, matching or not

C. Returns all rows from the second table, matching or not

D. Returns all rows from both tables, matching or not

Solution

1. Step 1: Understand join types

   Inner join returns rows where keys match in both tables only.

2. Step 2: Compare options with definition

   The option 'Returns only rows with matching keys in both tables' describes this behavior correctly.

3. Final Answer:

   Returns only rows with matching keys in both tables -> Option A

4. Quick Check:

   Inner join = matching rows only [OK]

Hint: Inner join keeps only matching rows from both tables [OK]

Common Mistakes:

• Confusing inner join with left or right join
• Thinking inner join returns unmatched rows
• Mixing up join types and their results

2. Which of the following is the correct syntax to perform an inner join of two pandas DataFrames df1 and df2 on column 'id'?

easy

A. df1.join(df2, on='id', how='outer')

B. pd.concat([df1, df2], axis=1, join='inner')

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

D. df1.merge(df2, on='id', how='left')

Solution

1. Step 1: Identify pandas inner join syntax

   The correct function is pd.merge() with how='inner' and on='id'.

2. Step 2: Check each option

   pd.merge(df1, df2, on='id', how='inner') matches this exactly; others use wrong methods or join types.

3. Final Answer:

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

4. Quick Check:

   pd.merge + how='inner' = inner join [OK]

Hint: Use pd.merge with how='inner' for inner joins [OK]

Common Mistakes:

• Using join() instead of merge() for inner join
• Using wrong join type like 'outer' or 'left'
• Using concat which does not perform join

3. Given two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'val1': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'val2': ['X', 'Y', 'Z']})
result = pd.merge(df1, df2, on='id', how='inner')

What is the content of result?

medium

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

B. {'id': [1, 4], 'val1': ['A', None], 'val2': [None, 'Z']}

C. {'id': [2, 3, 4], 'val1': ['B', 'C', None], 'val2': ['X', 'Y', 'Z']}

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

Solution

1. Step 1: Identify matching keys for inner join

   Keys in both df1 and df2 are 2 and 3 only.

2. Step 2: Merge rows with matching keys

   Rows with id 2 and 3 combine val1 and val2 columns.

3. Final Answer:

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

4. Quick Check:

   Inner join keeps ids 2 and 3 only [OK]

Hint: Inner join keeps only common keys in both DataFrames [OK]

Common Mistakes:

• Including keys not present in both tables
• Confusing inner join with outer join
• Assuming all rows are kept

4. You wrote this code to inner join two DataFrames:

result = pd.merge(df1, df2, how='inner')

But it raises an error. What is the likely cause?

medium

A. Using 'how' parameter incorrectly

B. Missing the 'on' parameter to specify join key

C. DataFrames must be converted to lists first

D. Inner join is not supported by pandas

Solution

1. Step 1: Check required parameters for pd.merge

   pd.merge requires 'on' or 'left_on'/'right_on' to specify join keys.

2. Step 2: Identify missing 'on' parameter

   Code misses 'on' parameter, causing error.

3. Final Answer:

   Missing the 'on' parameter to specify join key -> Option B

4. Quick Check:

   pd.merge needs 'on' key [OK]

Hint: Always specify 'on' column for merge keys [OK]

Common Mistakes:

• Forgetting to specify join keys
• Assuming default join key exists
• Trying to join incompatible data types

5. You have two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 2, 3], 'val1': ['A', 'B', 'C', 'D']})
df2 = pd.DataFrame({'id': [2, 2, 3], 'val2': ['X', 'Y', 'Z']})
result = pd.merge(df1, df2, on='id', how='inner')

How many rows will result contain after the inner join?

hard

A. 6

B. 4

C. 3

D. 5

Solution

1. Step 1: Identify matching keys and duplicates

   Keys 2 and 3 appear multiple times in both DataFrames.

2. Step 2: Calculate Cartesian product for duplicates

   For id=2: df1 has 2 rows, df2 has 2 rows -> 2*2=4 rows.
   For id=3: df1 has 1 row, df2 has 1 row -> 1*1=1 row.
   Total rows = 4 + 1 = 5 rows.

3. Step 3: Recount carefully

   Recount confirms 5 rows total.

4. Final Answer:

   5 -> Option D

5. Quick Check:

   Duplicates multiply rows in inner join [OK]

Hint: Duplicates multiply rows in inner join result [OK]

Common Mistakes:

• Ignoring duplicate keys causing more rows
• Counting unique keys only
• Assuming one-to-one join always