Why combining datasets creates complete pictures in Data Analysis Python - See It in Action

Why combining datasets creates complete pictures

📖 Scenario: Imagine you work at a small bookstore. You have two lists: one with book titles and their authors, and another with book titles and their prices. To understand your inventory better, you want to combine these lists to see each book's title, author, and price together.

🎯 Goal: Build a combined dataset that shows each book's title, author, and price by joining two separate datasets.

📋 What You'll Learn

Create a dictionary called books_authors with book titles as keys and authors as values.

Create a dictionary called books_prices with book titles as keys and prices as values.

Create a list of dictionaries called combined_books where each dictionary has keys title, author, and price.

Print the combined_books list to show the complete picture.

💡 Why This Matters

🌍 Real World

Combining datasets helps businesses see all related information in one place, like matching customer info with their orders.

💼 Career

Data analysts often combine data from different sources to create reports and insights that help companies make decisions.

1

Create the books and authors dataset

Create a dictionary called books_authors with these exact entries: 'The Hobbit': 'J.R.R. Tolkien', '1984': 'George Orwell', 'Pride and Prejudice': 'Jane Austen'.

Data Analysis Python

# Create the books_authors dictionary with titles and authors
# Your code here

Hint

Use curly braces {} to create a dictionary with book titles as keys and authors as values.

2

Create the books and prices dataset

Create a dictionary called books_prices with these exact entries: 'The Hobbit': 15.99, '1984': 12.50, 'Pride and Prejudice': 9.99.

Data Analysis Python

books_authors = {
    'The Hobbit': 'J.R.R. Tolkien',
    '1984': 'George Orwell',
    'Pride and Prejudice': 'Jane Austen'
}
# Create the books_prices dictionary with titles and prices
# Your code here

Hint

Use curly braces {} to create a dictionary with book titles as keys and prices as values.

3

Combine the datasets into one list

Create a list called combined_books that contains dictionaries for each book. Each dictionary should have keys 'title', 'author', and 'price'. Use a for loop with variables title and author to iterate over books_authors.items() and get the price from books_prices.

Data Analysis Python

books_authors = {
    'The Hobbit': 'J.R.R. Tolkien',
    '1984': 'George Orwell',
    'Pride and Prejudice': 'Jane Austen'
}

books_prices = {
    'The Hobbit': 15.99,
    '1984': 12.50,
    'Pride and Prejudice': 9.99
}

# Create combined_books list by joining books_authors and books_prices
# Your code here

Hint

Use a for loop to go through books_authors. For each book, get the price from books_prices and add a dictionary to combined_books.

4

Print the combined dataset

Write a print statement to display the combined_books list.

Data Analysis Python

books_authors = {
    'The Hobbit': 'J.R.R. Tolkien',
    '1984': 'George Orwell',
    'Pride and Prejudice': 'Jane Austen'
}

books_prices = {
    'The Hobbit': 15.99,
    '1984': 12.50,
    'Pride and Prejudice': 9.99
}

combined_books = []
for title, author in books_authors.items():
    price = books_prices[title]
    combined_books.append({'title': title, 'author': author, 'price': price})

# Print the combined_books list
# Your code here

Hint

Use print(combined_books) to show the final combined list.

Practice

1. Why do we combine datasets in data analysis?

easy

A. To make data smaller by removing columns

B. To delete unnecessary data permanently

C. To get a more complete view by joining related information

D. To create random data for testing

Solution

1. Step 1: Understand the purpose of combining datasets

   Combining datasets helps bring together related pieces of information from different sources.

2. Step 2: Recognize the benefit of combining

   This creates a fuller, more complete picture for analysis.

3. Final Answer:

   To get a more complete view by joining related information -> Option C

4. Quick Check:

   Combining datasets = fuller picture [OK]

Hint: Combining means joining info to see the whole story [OK]

Common Mistakes:

• Thinking combining deletes data
• Confusing combining with filtering
• Believing combining creates random data

2. Which of the following is the correct syntax to merge two pandas DataFrames df1 and df2 on a common column 'id'?

easy

A. pd.merge(df1, df2, on='id')

B. df1.merge(df2, by='id')

C. merge(df1, df2, key='id')

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

Solution

1. Step 1: Recall pandas merge syntax

   The correct function is pd.merge() with parameters for the two DataFrames and the column name using on='id'.

2. Step 2: Check each option

   pd.merge(df1, df2, on='id') uses correct function and parameter names. Others use incorrect method names or parameters.

3. Final Answer:

   pd.merge(df1, df2, on='id') -> Option A

4. Quick Check:

   Correct merge syntax = pd.merge(df1, df2, on='id') [OK]

Hint: Use pd.merge() with on='column' to combine DataFrames [OK]

Common Mistakes:

• Using wrong method like df1.merge with 'by' instead of 'on'
• Using pd.join which does not exist
• Passing wrong parameter names like 'key'

3. Given these two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'value1': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'value2': ['X', 'Y', 'Z']})
result = pd.merge(df1, df2, on='id', how='inner')

What is the content of result?

medium

A. {'id': [2, 3], 'value1': ['B', 'C'], 'value2': ['X', 'Y']}

B. {'id': [1, 2, 3, 4], 'value1': ['A', 'B', 'C', NaN], 'value2': [NaN, 'X', 'Y', 'Z']}

C. {'id': [1, 2, 3], 'value1': ['A', 'B', 'C'], 'value2': [NaN, 'X', 'Y']}

D. {'id': [2, 3, 4], 'value1': ['B', 'C', NaN], 'value2': ['X', 'Y', 'Z']}

Solution

1. Step 1: Understand inner join behavior

   Inner join keeps only rows with matching 'id' in both DataFrames. Here, ids 2 and 3 appear in both.

2. Step 2: Match rows and combine columns

   Rows with id 2 and 3 combine values from both DataFrames. Others are dropped.

3. Final Answer:

   {'id': [2, 3], 'value1': ['B', 'C'], 'value2': ['X', 'Y']} -> Option A

4. Quick Check:

   Inner join = only matching ids kept [OK]

Hint: Inner join keeps only rows with matching keys in both sets [OK]

Common Mistakes:

• Confusing inner join with outer join
• Expecting all ids to appear
• Mixing up which columns get NaN

4. You try to merge two DataFrames df1 and df2 on column 'key' using pd.merge(df1, df2, on='key') but get a KeyError: 'key'. What is the likely cause?

medium

A. You must use how='outer' to avoid KeyError

B. The column 'key' does not exist in one or both DataFrames

C. You forgot to import pandas as pd

D. The DataFrames have different numbers of rows

Solution

1. Step 1: Understand KeyError in merge context

   A KeyError for the merge column means that column is missing in one or both DataFrames.

2. Step 2: Check other options

   Using different join types or import issues do not cause KeyError on column name. Row counts do not matter for this error.

3. Final Answer:

   The column 'key' does not exist in one or both DataFrames -> Option B

4. Quick Check:

   KeyError means missing column [OK]

Hint: KeyError on merge means missing column in data [OK]

Common Mistakes:

• Thinking join type fixes KeyError
• Assuming import error causes KeyError
• Believing row count mismatch causes KeyError

5. You have two datasets:

sales = pd.DataFrame({'store_id': [1, 2, 3], 'sales': [100, 200, 300]})
stores = pd.DataFrame({'store_id': [2, 3, 4], 'location': ['NY', 'LA', 'SF']})

You want a combined table showing all stores with their sales if available, and location if available. Which merge command gives this complete picture?

hard

A. pd.merge(sales, stores, on='store_id', how='inner')

B. pd.merge(sales, stores, on='store_id', how='left')

C. pd.merge(sales, stores, on='store_id', how='right')

D. pd.merge(sales, stores, on='store_id', how='outer')

Solution

1. Step 1: Understand join types and goal

   You want all stores from both datasets, showing sales and location where available.

2. Step 2: Choose join type that keeps all rows

   Outer join keeps all rows from both DataFrames, filling missing values with NaN.

3. Step 3: Verify options

   Inner join keeps only matching stores. Left or right join keep only one side's stores. Outer join keeps all.

4. Final Answer:

   pd.merge(sales, stores, on='store_id', how='outer') -> Option D

5. Quick Check:

   Outer join = all data combined [OK]

Hint: Use outer join to keep all rows from both datasets [OK]

Common Mistakes:

• Using inner join and losing unmatched rows
• Confusing left/right join with full outer join
• Expecting missing data to be dropped automatically