Pivot with aggregation functions in Pandas - Mini Project: Build & Apply

Pivot with aggregation functions

📖 Scenario: You work in a small store that sells fruits. You have a list of sales records showing the fruit type, the day of the week, and the quantity sold. You want to organize this data to see the total quantity sold for each fruit on each day.

🎯 Goal: Create a pivot table using pandas that shows the total quantity of each fruit sold per day of the week.

📋 What You'll Learn

Create a pandas DataFrame with the given sales data

Create a list of days to use as columns in the pivot table

Use pandas pivot_table with aggregation function sum to calculate total quantities

Print the resulting pivot table

💡 Why This Matters

🌍 Real World

Pivot tables help organize and summarize sales data to understand trends and performance by categories and time periods.

💼 Career

Data analysts and business intelligence professionals use pivot tables to quickly summarize and report data for decision making.

1

Create the sales data DataFrame

Create a pandas DataFrame called sales_data with these exact columns and rows:
Fruit: ['Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana']
Day: ['Monday', 'Monday', 'Tuesday', 'Tuesday', 'Wednesday', 'Wednesday']
Quantity: [10, 5, 15, 7, 10, 8]

Pandas

import pandas as pd
# Create the sales_data DataFrame with Fruit, Day, and Quantity 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 list of days for columns

Create a list called days with these exact values: ['Monday', 'Tuesday', 'Wednesday']

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Fruit': ['Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana'],
    'Day': ['Monday', 'Monday', 'Tuesday', 'Tuesday', 'Wednesday', 'Wednesday'],
    'Quantity': [10, 5, 15, 7, 10, 8]
})
# Create the list days with Monday, Tuesday, Wednesday
# Your code here

Need a hint?

Just create a list with the three day names in order.

3

Create the pivot table with sum aggregation

Create a pivot table called pivot_table from sales_data using pd.pivot_table.
Use Fruit as the index, Day as the columns, Quantity as the values, and sum as the aggregation function.
Also, use the days list as the columns order.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Fruit': ['Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana'],
    'Day': ['Monday', 'Monday', 'Tuesday', 'Tuesday', 'Wednesday', 'Wednesday'],
    'Quantity': [10, 5, 15, 7, 10, 8]
})
days = ['Monday', 'Tuesday', 'Wednesday']
# Create the pivot_table using pd.pivot_table with index='Fruit', columns='Day', values='Quantity', aggfunc='sum'
# Your code here

Need a hint?

Use pd.pivot_table with the right parameters and reorder columns with .reindex(columns=days).

4

Print the pivot table

Print the pivot_table variable to see the total quantity of each fruit sold per day.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Fruit': ['Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana'],
    'Day': ['Monday', 'Monday', 'Tuesday', 'Tuesday', 'Wednesday', 'Wednesday'],
    'Quantity': [10, 5, 15, 7, 10, 8]
})
days = ['Monday', 'Tuesday', 'Wednesday']
pivot_table = pd.pivot_table(sales_data, index='Fruit', columns='Day', values='Quantity', aggfunc='sum').reindex(columns=days)
# Print the pivot_table
# Your code here

Need a hint?

Use print(pivot_table) to display the pivot table.