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.