merge() for SQL-like joins in Pandas - Mini Project: Build & Apply

Using merge() for SQL-like joins in pandas

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

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

📋 What You'll Learn

Create two pandas DataFrames with exact data

Set a variable for the join key column

Use pandas.merge() to join the DataFrames on the key

Print the merged DataFrame

💡 Why This Matters

🌍 Real World

Combining data from different sources is common in business, like joining sales records with product details to analyze performance.

💼 Career

Data scientists and analysts often use joins to prepare data for reports, dashboards, and machine learning models.

1

Create the book details DataFrame

Create a pandas DataFrame called books with these exact columns and data: book_id as [101, 102, 103], title as ['Python Basics', 'Data Science 101', 'Machine Learning'], and price as [25.0, 30.0, 45.0].

Pandas

# Create the books DataFrame with book_id, title, and price columns
# Your code here

Need a hint?

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

2

Create the sales DataFrame and set join key

Create a pandas DataFrame called sales with columns book_id as [101, 103, 104] and copies_sold as [150, 200, 50]. Then create a variable called join_key and set it to the string 'book_id'.

Pandas

import pandas as pd

books = pd.DataFrame({
    'book_id': [101, 102, 103],
    'title': ['Python Basics', 'Data Science 101', 'Machine Learning'],
    'price': [25.0, 30.0, 45.0]
})

# Create the sales DataFrame with book_id and copies_sold columns
# Create the join_key variable with value 'book_id'
# Your code here

Need a hint?

Use pd.DataFrame again for sales. Then assign join_key = 'book_id'.

3

Merge the DataFrames using merge()

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

Pandas

import pandas as pd

books = pd.DataFrame({
    'book_id': [101, 102, 103],
    'title': ['Python Basics', 'Data Science 101', 'Machine Learning'],
    'price': [25.0, 30.0, 45.0]
})

sales = pd.DataFrame({
    'book_id': [101, 103, 104],
    'copies_sold': [150, 200, 50]
})

join_key = 'book_id'

# Merge books and sales on join_key using pandas.merge()
# Your code here

Need a hint?

Use pd.merge(books, sales, on=join_key) and assign it to merged_data.

4

Print the merged DataFrame

Print the variable merged_data to see the combined table.

Pandas

import pandas as pd

books = pd.DataFrame({
    'book_id': [101, 102, 103],
    'title': ['Python Basics', 'Data Science 101', 'Machine Learning'],
    'price': [25.0, 30.0, 45.0]
})

sales = pd.DataFrame({
    'book_id': [101, 103, 104],
    '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

Need a hint?

Use print(merged_data) to show the combined table.