Data aggregation reporting in Pandas - Mini Project: Build & Apply

Data Aggregation Reporting

📖 Scenario: You work in a small retail store. You have sales data for different products sold over several days. You want to find out the total sales for each product to understand which products sell the most.

🎯 Goal: Create a program that uses pandas to aggregate sales data by product and report the total sales for each product.

📋 What You'll Learn

Create a pandas DataFrame with sales data for products and their sales amounts.

Create a variable to hold the column name to group by.

Use pandas aggregation to sum sales for each product.

Print the aggregated total sales per product.

💡 Why This Matters

🌍 Real World

Retail stores and businesses often need to summarize sales data to understand product performance and make decisions.

💼 Career

Data aggregation is a fundamental skill for data analysts and data scientists to prepare reports and insights from raw data.

1

Create the sales data DataFrame

Create a pandas DataFrame called sales_data with two columns: 'Product' and 'Sales'. Use these exact rows: 'Apple', 10, 'Banana', 15, 'Apple', 5, 'Orange', 8, 'Banana', 7.

Pandas

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

Hint

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

2

Set the grouping column

Create a variable called group_column and set it to the string 'Product' to specify the column to group by.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Apple', 'Banana', 'Apple', 'Orange', 'Banana'],
    'Sales': [10, 15, 5, 8, 7]
})
# Create the group_column variable with value 'Product'
# Your code here

Hint

Just assign the string 'Product' to the variable group_column.

3

Aggregate total sales by product

Create a new DataFrame called total_sales by grouping sales_data using group_column and summing the 'Sales' values for each product.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Apple', 'Banana', 'Apple', 'Orange', 'Banana'],
    'Sales': [10, 15, 5, 8, 7]
})
group_column = 'Product'
# Group sales_data by group_column and sum the Sales column into total_sales
# Your code here

Hint

Use groupby() on sales_data with group_column, then select 'Sales' and apply sum().

4

Print the total sales report

Print the total_sales DataFrame to display the total sales for each product.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Apple', 'Banana', 'Apple', 'Orange', 'Banana'],
    'Sales': [10, 15, 5, 8, 7]
})
group_column = 'Product'
total_sales = sales_data.groupby(group_column)['Sales'].sum().reset_index()
# Print the total_sales DataFrame
# Your code here

Hint

Use print(total_sales) to show the result.

Practice

1. What is the main purpose of using groupby() in pandas for data aggregation reporting?

easy

A. To filter rows based on a condition

B. To sort data in ascending order

C. To split data into groups based on column values

D. To merge two dataframes

Solution

1. Step 1: Understand the role of groupby()

   The groupby() function splits the dataframe into groups based on unique values in one or more columns.

2. Step 2: Identify the purpose in aggregation

   After grouping, aggregation functions like sum or mean can be applied to each group separately.

3. Final Answer:

   To split data into groups based on column values -> Option C

4. Quick Check:

   groupby() splits data for aggregation [OK]

Hint: Remember: groupby splits data, agg summarizes groups [OK]

Common Mistakes:

• Confusing groupby with sorting
• Thinking groupby filters rows
• Mixing groupby with merging dataframes

2. Which of the following is the correct syntax to calculate the mean of column 'Sales' grouped by 'Region' in pandas?

easy

A. df.groupby('Region').agg('Sales'.mean)

B. df.group_by('Region').mean('Sales')

C. df.groupby('Sales')['Region'].mean()

D. df.groupby('Region')['Sales'].mean()

Solution

1. Step 1: Identify correct groupby syntax

   The correct method is groupby('Region') to group by 'Region'.

2. Step 2: Select column and apply mean

   Use ['Sales'].mean() to calculate mean of 'Sales' per group.

3. Final Answer:

   df.groupby('Region')['Sales'].mean() -> Option D

4. Quick Check:

   Correct method chaining for groupby and mean [OK]

Hint: Use df.groupby('col')['col2'].mean() for grouped mean [OK]

Common Mistakes:

• Using group_by instead of groupby
• Swapping group and aggregation columns
• Incorrect agg syntax with string method

3. Given the dataframe:

import pandas as pd
data = {'Team': ['A', 'A', 'B', 'B', 'C'], 'Points': [10, 15, 10, 20, 30]}

What is the output of:

df = pd.DataFrame(data)
df.groupby('Team').agg({'Points': 'sum'})

medium

A. {'Team': ['A', 'B', 'C'], 'Points': [25, 30, 30]}

B. {'Team': ['A', 'B', 'C'], 'Points': [10, 10, 30]}

C. {'Team': ['A', 'B', 'C'], 'Points': [15, 20, 30]}

D. Error: agg() requires a list of functions

Solution

1. Step 1: Group data by 'Team'

   Teams are A, B, and C. Grouping splits rows accordingly.

2. Step 2: Sum 'Points' per group

   For A: 10+15=25, B: 10+20=30, C: 30.

3. Final Answer:

   {'Team': ['A', 'B', 'C'], 'Points': [25, 30, 30]} -> Option A

4. Quick Check:

   Sum points per team = [25, 30, 30] [OK]

Hint: Sum points after groupby sums values per group [OK]

Common Mistakes:

• Summing only first value per group
• Confusing sum with mean
• Expecting agg() needs list always

4. You run this code:

df.groupby('Category').agg('sum', 'Price')

But get an error. What is the problem?

medium

A. agg() expects a dictionary or list, not multiple arguments

B. groupby() requires numeric columns only

C. sum is not a valid aggregation function

D. Price column does not exist

Solution

1. Step 1: Check agg() function signature

   The agg() method expects a single function, list of functions, or dict mapping columns to functions.

2. Step 2: Identify incorrect argument usage

   Passing two separate arguments ('sum', 'Price') is invalid syntax and causes error.

3. Final Answer:

   agg() expects a dictionary or list, not multiple arguments -> Option A

4. Quick Check:

   agg() needs one argument: function or dict [OK]

Hint: Use agg({'Price': 'sum'}) or agg('sum') only [OK]

Common Mistakes:

• Passing multiple arguments to agg()
• Assuming groupby needs numeric columns only
• Using invalid function names

5. You have a sales dataframe with columns 'Store', 'Product', and 'Revenue'. You want to find the total revenue per store and the average revenue per product within each store. Which pandas code correctly achieves this?

hard

A. df.groupby('Store').agg({'Revenue': ['sum', 'mean']})

B. df.groupby(['Store', 'Product']).agg({'Revenue': 'mean'}).groupby('Store').agg({'Revenue': 'sum'})

C. df.groupby('Store').agg({'Revenue': 'sum'}).groupby('Product').agg({'Revenue': 'mean'})

D. df.groupby('Product').agg({'Revenue': ['sum', 'mean']})

Solution

1. Step 1: Calculate average revenue per product within each store

   Group by both 'Store' and 'Product', then calculate mean revenue.

2. Step 2: Calculate total revenue per store

   Group the result by 'Store' and sum the mean revenues of products.

3. Final Answer:

   df.groupby(['Store', 'Product']).agg({'Revenue': 'mean'}).groupby('Store').agg({'Revenue': 'sum'}) -> Option B

4. Quick Check:

   Nested groupby for product mean then store sum [OK]

Hint: Group by both, then aggregate stepwise for nested summaries [OK]

Common Mistakes:

• Trying to do sum and mean in one groupby without nesting
• Grouping by wrong columns order
• Mixing aggregation levels incorrectly