merge() for SQL-style joins in Data Analysis Python - Mini Project: Build & Apply

Using merge() for SQL-style joins in Python

📖 Scenario: You work in a small bookstore. You have two lists of data: one with book details and another with sales information. You want to combine these lists to see which books sold and their prices.

🎯 Goal: Learn how to use the merge() function in pandas to join two tables like in SQL. You will combine book details with sales data to get a full view of sales.

📋 What You'll Learn

Create two pandas DataFrames with exact data

Create a variable for the join key column

Use merge() to join the DataFrames on the key

Print the merged DataFrame to see the combined data

💡 Why This Matters

🌍 Real World

Combining data from different sources is common in business. For example, joining customer info with purchase history helps understand sales.

💼 Career

Data analysts and scientists often merge datasets to prepare data for analysis and reporting.

1

Create the book details DataFrame

Create a pandas DataFrame called books with these exact columns and rows:
book_id: 101, 102, 103, 104
title: 'Python Basics', 'Data Science 101', 'Machine Learning', 'Deep Learning'
price: 25, 30, 45, 50

Data Analysis Python

import pandas as pd
# Create the books DataFrame with columns book_id, title, price
# Your code here

Hint

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

2

Create the sales DataFrame and join key

Create a pandas DataFrame called sales with these exact columns and rows:
book_id: 101, 103, 105
copies_sold: 150, 200, 50
Then create a variable called join_key and set it to the string 'book_id'.

Data Analysis Python

import pandas as pd
books = pd.DataFrame({
    'book_id': [101, 102, 103, 104],
    'title': ['Python Basics', 'Data Science 101', 'Machine Learning', 'Deep Learning'],
    'price': [25, 30, 45, 50]
})
# Create the sales DataFrame with columns book_id and copies_sold
# Create the join_key variable with value 'book_id'
# Your code here

Hint

Use the same method as before to create sales. Then assign the string 'book_id' to join_key.

3

Merge the DataFrames using merge()

Use the pandas merge() function to join books and sales on the column stored in join_key. Store the result in a new DataFrame called merged_data. Use an inner join (default).

Data Analysis Python

import pandas as pd
books = pd.DataFrame({
    'book_id': [101, 102, 103, 104],
    'title': ['Python Basics', 'Data Science 101', 'Machine Learning', 'Deep Learning'],
    'price': [25, 30, 45, 50]
})
sales = pd.DataFrame({
    'book_id': [101, 103, 105],
    'copies_sold': [150, 200, 50]
})
join_key = 'book_id'
# Merge books and sales on join_key using merge()
# Your code here

Hint

Use pd.merge(books, sales, on=join_key) to join the tables.

4

Print the merged DataFrame

Print the merged_data DataFrame to see the combined book and sales information.

Data Analysis Python

import pandas as pd
books = pd.DataFrame({
    'book_id': [101, 102, 103, 104],
    'title': ['Python Basics', 'Data Science 101', 'Machine Learning', 'Deep Learning'],
    'price': [25, 30, 45, 50]
})
sales = pd.DataFrame({
    'book_id': [101, 103, 105],
    'copies_sold': [150, 200, 50]
})
join_key = 'book_id'
merged_data = pd.merge(books, sales, on=join_key)
# Print the merged_data DataFrame
# Your code here

Hint

Use print(merged_data) to display the combined table.

Practice

1. What does the how parameter in merge() control when joining two DataFrames?

easy

A. The data types of the columns

B. The columns to join on

C. The order of rows in the result

D. The type of join: inner, left, right, or outer

Solution

1. Step 1: Understand the role of how in merge()

   The how parameter specifies the type of join to perform, such as inner, left, right, or outer join.

2. Step 2: Differentiate from other parameters

   Other parameters like on specify columns to join on, but how controls how rows are matched and combined.

3. Final Answer:

   The type of join: inner, left, right, or outer -> Option D

4. Quick Check:

   how = join type [OK]

Hint: Remember: 'how' means join type like SQL joins [OK]

Common Mistakes:

• Confusing 'how' with 'on' parameter
• Thinking 'how' controls column selection
• Assuming 'how' changes data types

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

easy

A. pd.merge(df1, df2, join='id', how='inner')

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

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

D. pd.merge(df1, df2, on='id', join='inner')

Solution

1. Step 1: Identify correct parameter names

   The correct parameters for merging are on for the column and how for join type.

2. Step 2: Check syntax correctness

   pd.merge(df1, df2, on='id', how='inner') uses pd.merge(df1, df2, on='id', how='inner'), which is the correct syntax. Other options use incorrect parameter names like join or type.

3. Final Answer:

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

4. Quick Check:

   Correct parameters: on, how [OK]

Hint: Use 'on' for columns and 'how' for join type [OK]

Common Mistakes:

• Using 'join' instead of 'how'
• Using 'type' instead of 'how'
• Mixing parameter names

3. Given the following code, what is the output of result?

import pandas as pd

df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['B', 'C', 'D'], 'val2': [4, 5, 6]})

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

medium

A. key val1 val2
0 B 2 4
1 C 3 5

B. key val1 val2
0 A 1 4
1 B 2 5
2 C 3 6

C. key val1 val2
0 A 1 NaN
1 B 2 4
2 C 3 5

D. key val1 val2
0 B 2 NaN
1 C 3 NaN
2 D NaN 6

Solution

1. Step 1: Understand inner join on 'key'

   Inner join keeps only rows with keys present in both DataFrames: 'B' and 'C'.

2. Step 2: Match rows and combine columns

   For 'B': val1=2, val2=4; for 'C': val1=3, val2=5. Rows with 'A' and 'D' are excluded.

3. Final Answer:

   key val1 val2 0 B 2 4 1 C 3 5 -> Option A

4. Quick Check:

   Inner join = intersection keys [OK]

Hint: Inner join keeps only common keys [OK]

Common Mistakes:

• Including keys not in both DataFrames
• Confusing inner with outer join
• Misaligning values for keys

4. What is wrong with this code snippet?

import pandas as pd

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

result = pd.merge(df1, df2, on='id', how='left')
print(result)

medium

A. DataFrames must have the same number of rows to merge

B. The 'how' parameter cannot be 'left'

C. The merge function is missing the 'left_on' and 'right_on' parameters

D. Column 'id' does not exist in both DataFrames

Solution

1. Step 1: Check columns used for merging

   The code tries to merge on column 'id', but df2 has 'key' column instead of 'id'.

2. Step 2: Understand merge requirements

   For merging on different column names, left_on and right_on must be used, or columns must have the same name.

3. Final Answer:

   The merge function is missing the 'left_on' and 'right_on' parameters -> Option C

4. Quick Check:

   Merge columns must exist in both DataFrames or be specified [OK]

Hint: Check column names exist in both DataFrames or specify left_on/right_on [OK]

Common Mistakes:

• Assuming merge works with different column names without specifying
• Thinking 'how' parameter is invalid
• Believing row counts must match

5. You have two DataFrames: orders with columns order_id, customer_id, and amount, and customers with columns cust_id and name. How do you merge them to get all orders with customer names, even if some customers are missing in customers DataFrame?

hard

A. pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left')

B. pd.merge(orders, customers, on='customer_id', how='inner')

C. pd.merge(orders, customers, left_on='cust_id', right_on='customer_id', how='right')

D. pd.merge(orders, customers, on='cust_id', how='outer')

Solution

1. Step 1: Identify join keys with different column names

   Orders use 'customer_id', customers use 'cust_id', so use left_on and right_on to specify.

2. Step 2: Choose join type to keep all orders

   Use how='left' to keep all rows from orders, even if no matching customer exists.

3. Final Answer:

   pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left') -> Option A

4. Quick Check:

   Left join keeps all left rows [OK]

Hint: Use left_on/right_on for different keys and 'left' to keep all left rows [OK]

Common Mistakes:

• Using 'on' with different column names
• Using inner join and losing unmatched orders
• Swapping left_on and right_on