Left join behavior in Pandas - Mini Project: Build & Apply

Understanding Left Join Behavior with pandas

📖 Scenario: You work in a small bookstore. You have two lists of information: one list has book titles and their authors, and another list has book titles and their prices. You want to combine these lists to see all books with their authors and prices, but some books might not have prices yet.

🎯 Goal: Build a pandas DataFrame that shows all books with their authors and prices using a left join. This means every book from the first list appears, and prices are added when available.

📋 What You'll Learn

Create a pandas DataFrame called books with columns title and author using the exact data provided.

Create a pandas DataFrame called prices with columns title and price using the exact data provided.

Create a variable called merged_books that contains the result of a left join of books with prices on the title column.

Use pandas merge function with how='left' to perform the join.

💡 Why This Matters

🌍 Real World

Combining data from different sources is common in business, like merging customer info with purchase records.

💼 Career

Data analysts and scientists often join tables to prepare data for reports and insights.

1

Create the books DataFrame

Create a pandas DataFrame called books with these exact entries: titles 'The Hobbit', '1984', 'Python 101', and authors 'J.R.R. Tolkien', 'George Orwell', 'Michael Driscoll'. Use columns named title and author.

Pandas

import pandas as pd
# Create the books DataFrame with the exact titles and authors
# 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 prices DataFrame

Create a pandas DataFrame called prices with these exact entries: titles 'The Hobbit', 'Python 101', and prices 10.99, 29.99. Use columns named title and price.

Pandas

import pandas as pd

books = pd.DataFrame({
    'title': ['The Hobbit', '1984', 'Python 101'],
    'author': ['J.R.R. Tolkien', 'George Orwell', 'Michael Driscoll']
})

# Create the prices DataFrame with the exact titles and prices
# Your code here

Need a hint?

Use pd.DataFrame with a dictionary for titles and prices.

3

Perform the left join

Create a variable called merged_books that contains the result of left joining books with prices on the title column using pandas merge function with how='left'.

Pandas

import pandas as pd

books = pd.DataFrame({
    'title': ['The Hobbit', '1984', 'Python 101'],
    'author': ['J.R.R. Tolkien', 'George Orwell', 'Michael Driscoll']
})

prices = pd.DataFrame({
    'title': ['The Hobbit', 'Python 101'],
    'price': [10.99, 29.99]
})

# Perform a left join of books with prices on 'title'
# Your code here

Need a hint?

Use pd.merge() with on='title' and how='left' to keep all books and add prices where available.

4

Complete the DataFrame setup

Add a line to set the index of merged_books to the title column using the set_index method with inplace=True.

Pandas

import pandas as pd

books = pd.DataFrame({
    'title': ['The Hobbit', '1984', 'Python 101'],
    'author': ['J.R.R. Tolkien', 'George Orwell', 'Michael Driscoll']
})

prices = pd.DataFrame({
    'title': ['The Hobbit', 'Python 101'],
    'price': [10.99, 29.99]
})

merged_books = pd.merge(books, prices, on='title', how='left')

# Set the index of merged_books to the title column
# Your code here

Need a hint?

Use set_index with inplace=True to change the DataFrame index without creating a new one.