pivot_table() for summarization in Pandas - Mini Project: Build & Apply

Summarize Sales Data Using pivot_table()

📖 Scenario: You work in a small store that sells different products in various cities. You have sales data for a week and want to understand total sales by city and product.

🎯 Goal: Build a summary table using pandas pivot_table() to see total sales for each product in each city.

📋 What You'll Learn

Create a pandas DataFrame with sales data

Define a pivot table configuration variable

Use pivot_table() to summarize total sales by city and product

Print the resulting pivot table

💡 Why This Matters

🌍 Real World

Stores and businesses often need to summarize sales data quickly to understand which products sell best in which locations.

💼 Career

Data analysts and business intelligence professionals use pivot tables to create clear summaries and reports from raw data.

1

Create the sales data DataFrame

Import pandas as pd and create a DataFrame called sales_data with these exact columns and rows:
City: 'New York', 'New York', 'Los Angeles', 'Los Angeles', 'Chicago', 'Chicago'
Product: 'Apples', 'Bananas', 'Apples', 'Bananas', 'Apples', 'Bananas'
Sales: 100, 150, 200, 250, 300, 350

Pandas

import pandas as pd
# Create the sales_data DataFrame with the exact data
# Your code here

Need a hint?

Use pd.DataFrame() with a dictionary of lists for columns.

2

Set up pivot table configuration

Create a variable called pivot_config as a dictionary with keys:
index set to 'City',
columns set to 'Product',
and values set to 'Sales'.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'City': ['New York', 'New York', 'Los Angeles', 'Los Angeles', 'Chicago', 'Chicago'],
    'Product': ['Apples', 'Bananas', 'Apples', 'Bananas', 'Apples', 'Bananas'],
    'Sales': [100, 150, 200, 250, 300, 350]
})
# Create pivot_config dictionary with index, columns, and values keys
# Your code here

Need a hint?

Use a dictionary with keys exactly as 'index', 'columns', and 'values'.

3

Create the pivot table

Use pd.pivot_table() with sales_data and the pivot_config dictionary unpacked to create a variable called summary_table.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'City': ['New York', 'New York', 'Los Angeles', 'Los Angeles', 'Chicago', 'Chicago'],
    'Product': ['Apples', 'Bananas', 'Apples', 'Bananas', 'Apples', 'Bananas'],
    'Sales': [100, 150, 200, 250, 300, 350]
})
pivot_config = {
    'index': 'City',
    'columns': 'Product',
    'values': 'Sales'
}
# Create summary_table using pd.pivot_table with sales_data and pivot_config
# Your code here

Need a hint?

Use pd.pivot_table(data, **config) to unpack the dictionary.

4

Display the pivot table

Print the summary_table variable to show the total sales by city and product.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'City': ['New York', 'New York', 'Los Angeles', 'Los Angeles', 'Chicago', 'Chicago'],
    'Product': ['Apples', 'Bananas', 'Apples', 'Bananas', 'Apples', 'Bananas'],
    'Sales': [100, 150, 200, 250, 300, 350]
})
pivot_config = {
    'index': 'City',
    'columns': 'Product',
    'values': 'Sales'
}
summary_table = pd.pivot_table(sales_data, **pivot_config)
# Print the summary_table
# Your code here

Need a hint?

Use print(summary_table) to display the pivot table.