Left and right joins in Data Analysis Python - Mini Project: Build & Apply

Left and Right Joins in Data Analysis

📖 Scenario: You work in a small store that keeps two lists: one for products and one for sales. You want to combine these lists to see which products sold and which did not.

🎯 Goal: Learn how to use left join and right join to combine two tables (dataframes) in Python using pandas. You will create two dataframes, set a join key, and then join them to see all products with their sales and all sales with their product details.

📋 What You'll Learn

Create two pandas DataFrames with exact data

Create a variable for the join key column name

Use pandas merge with how='left' and how='right' to join dataframes

Print the joined DataFrames to see the results

💡 Why This Matters

🌍 Real World

Stores and businesses often have separate lists for products and sales. Joining these lists helps understand which products sold and which did not.

💼 Career

Data analysts and scientists use joins to combine data from different sources to create meaningful reports and insights.

1

Create product and sales DataFrames

Import pandas as pd. Create a DataFrame called products with columns 'product_id' and 'product_name' containing these rows: (1, 'Apple'), (2, 'Banana'), (3, 'Carrot'). Create another DataFrame called sales with columns 'product_id' and 'quantity' containing these rows: (1, 10), (2, 5), (4, 7).

Data Analysis Python

import pandas as pd
# Create the products DataFrame
# Create the sales DataFrame

Hint

Use pd.DataFrame with a dictionary of lists to create each DataFrame.

2

Set the join key variable

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

Data Analysis Python

import pandas as pd
products = pd.DataFrame({
    'product_id': [1, 2, 3],
    'product_name': ['Apple', 'Banana', 'Carrot']
})
sales = pd.DataFrame({
    'product_id': [1, 2, 4],
    'quantity': [10, 5, 7]
})
# Create the join_key variable

Hint

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

3

Perform left and right joins

Use pd.merge to create a DataFrame called left_joined by joining products and sales on join_key with how='left'. Then create another DataFrame called right_joined by joining products and sales on join_key with how='right'.

Data Analysis Python

import pandas as pd
products = pd.DataFrame({
    'product_id': [1, 2, 3],
    'product_name': ['Apple', 'Banana', 'Carrot']
})
sales = pd.DataFrame({
    'product_id': [1, 2, 4],
    'quantity': [10, 5, 7]
})
join_key = 'product_id'
# Create left_joined using pd.merge with how='left'
# Create right_joined using pd.merge with how='right'

Hint

Use pd.merge() with the on parameter set to join_key and how set to 'left' or 'right'.

4

Print the joined DataFrames

Print the left_joined DataFrame. Then print the right_joined DataFrame.

Data Analysis Python

import pandas as pd
products = pd.DataFrame({
    'product_id': [1, 2, 3],
    'product_name': ['Apple', 'Banana', 'Carrot']
})
sales = pd.DataFrame({
    'product_id': [1, 2, 4],
    'quantity': [10, 5, 7]
})
join_key = 'product_id'
left_joined = pd.merge(products, sales, on=join_key, how='left')
right_joined = pd.merge(products, sales, on=join_key, how='right')
# Print left_joined
# Print right_joined

Hint

Use print() to show both DataFrames.

Practice

1. What does a left join do when combining two tables in data analysis?

easy

A. Keeps all rows from both tables, matching or not.

B. Keeps all rows from the left table and adds matching rows from the right table.

C. Keeps only rows that match in both tables.

D. Keeps all rows from the right table and adds matching rows from the left table.

Solution

1. Step 1: Understand the role of the left table in a left join

   A left join keeps every row from the left table regardless of matches in the right table.

2. Step 2: Understand how matching rows from the right table are added

   Only rows from the right table that match the left table's keys are added; unmatched right rows are ignored.

3. Final Answer:

   Keeps all rows from the left table and adds matching rows from the right table. -> Option B

4. Quick Check:

   Left join = all left rows + matching right rows [OK]

Hint: Left join keeps all left rows, adds right matches [OK]

Common Mistakes:

• Confusing left join with right join
• Thinking left join keeps unmatched right rows
• Believing left join only keeps matching rows

2. Which of the following is the correct syntax for a left join using pandas in Python?

easy

A. pd.merge(df1, df2, how='left', on='key')

B. pd.merge(df1, df2, how='right', on='key')

C. pd.join(df1, df2, how='left')

D. df1.left_join(df2, on='key')

Solution

1. Step 1: Recall pandas merge syntax for joins

   In pandas, the function to join tables is pd.merge(), specifying the join type with the 'how' parameter.

2. Step 2: Identify the correct parameters for a left join

   Using how='left' and on='key' correctly performs a left join on the specified key column.

3. Final Answer:

   pd.merge(df1, df2, how='left', on='key') -> Option A

4. Quick Check:

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

Hint: Use pd.merge with how='left' for left join [OK]

Common Mistakes:

• Using pd.join instead of pd.merge
• Wrong 'how' parameter like 'right' or missing
• Calling non-existent left_join method

3. Given these two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'value': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'score': [90, 80, 70]})

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

medium

A. [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80}]

B. [{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80},{'id':4,'value':NaN,'score':70}]

C. [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80},{'id':4,'value':NaN,'score':70}]

D. [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':NaN},{'id':3,'value':'C','score':NaN}]

Solution

1. Step 1: Identify the left and right tables

   df1 is the left table with ids 1, 2, 3; df2 is the right table with ids 2, 3, 4.

2. Step 2: Perform left join on 'id'

   All rows from df1 appear. For id=1, no match in df2, so score is NaN. For ids 2 and 3, scores 90 and 80 are added. id=4 from df2 is ignored.

3. Final Answer:

   [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80}] -> Option A

4. Quick Check:

   Left join keeps all df1 rows + matching df2 rows [OK]

Hint: Left join keeps all left rows, unmatched right rows ignored [OK]

Common Mistakes:

• Including unmatched right rows in output
• Replacing unmatched left values with NaN incorrectly
• Confusing left and right join results

4. You run this code but get an error:

pd.merge(df1, df2, how='right', key='id')

What is the error and how to fix it?

medium

A. Error: DataFrames must have same columns. Fix by renaming columns.

B. Error: 'how' must be 'left' or 'inner'. Fix by changing 'right' to 'left'.

C. Error: 'key' is not a valid argument. Fix by using 'on='id'' instead.

D. No error, code runs fine.

Solution

1. Step 1: Identify the incorrect parameter name

   The parameter to specify join keys in pd.merge is 'on', not 'key'.

2. Step 2: Correct the parameter name

   Replace 'key='id'' with 'on='id'' to fix the error.

3. Final Answer:

   Error: 'key' is not a valid argument. Fix by using 'on='id'' instead. -> Option C

4. Quick Check:

   Use 'on' for join keys in pd.merge [OK]

Hint: Use 'on' not 'key' for join columns in pd.merge [OK]

Common Mistakes:

• Using 'key' instead of 'on' parameter
• Changing 'how' incorrectly
• Assuming no error occurs

5. You have two DataFrames:

df_customers = pd.DataFrame({'customer_id': [1, 2, 3], 'name': ['Ann', 'Bob', 'Cara']})
df_orders = pd.DataFrame({'order_id': [101, 102], 'customer_id': [2, 4], 'amount': [250, 300]})

You want to list all orders with customer names, including orders without matching customers. Which join should you use and why?

hard

A. Full outer join to keep all customers and all orders.

B. Left join on 'customer_id' to keep all customers and add matching orders.

C. Inner join on 'customer_id' to keep only matching customers and orders.

D. Right join on 'customer_id' to keep all orders and add matching customer names.

Solution

1. Step 1: Identify which table to keep all rows from

   You want all orders listed, even if no matching customer exists, so keep all rows from df_orders.

2. Step 2: Choose join type to keep all right table rows

   df_orders is the right table in the join. Using a right join keeps all rows from df_orders and adds matching customer names from df_customers.

3. Final Answer:

   Right join on 'customer_id' to keep all orders and add matching customer names. -> Option D

4. Quick Check:

   Right join keeps all right table rows [OK]

Hint: Use right join to keep all orders, add matching customers [OK]

Common Mistakes:

• Using left join keeps all customers, not all orders
• Using inner join drops unmatched orders
• Using full join adds unmatched customers unnecessarily