Data aggregation reporting in Pandas - Step-by-Step Execution

Concept Flow - Data aggregation reporting

Load DataFrame

↓

Choose columns to group

↓

Apply aggregation functions

↓

Create summary report

↓

Display aggregated results

The flow shows loading data, grouping by columns, applying aggregation functions, and producing a summary report.

Execution Sample

Pandas

import pandas as pd

data = {'Team': ['A', 'A', 'B', 'B'], 'Points': [10, 15, 10, 20]}
df = pd.DataFrame(data)
report = df.groupby('Team').agg({'Points': ['sum', 'mean']})
print(report)

This code groups data by 'Team' and calculates sum and mean of 'Points' for each team.

Execution Table

Step	Action	DataFrame State	Result
1	Create DataFrame	{'Team': ['A', 'A', 'B', 'B'], 'Points': [10, 15, 10, 20]}	DataFrame with 4 rows
2	Group by 'Team'	Groups: A, B	Two groups created
3	Aggregate 'Points' with sum and mean	Group A: Points=[10,15], Group B: Points=[10,20]	Sum and mean calculated per group
4	Create report DataFrame	Aggregated sums and means	Report with index Team and columns Points sum, mean
5	Print report	Report DataFrame	Output: Points sum mean Team A 25 12.5 B 30 15.0

💡 Aggregation complete and report displayed

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
df	None	{'Team': ['A', 'A', 'B', 'B'], 'Points': [10, 15, 10, 20]}	Same	Same	Same	Same
groups	None	None	Groups: A, B	Same	Same	Same
report	None	None	None	Sum and mean per group	Aggregated DataFrame	Printed output

Key Moments - 3 Insights

Why do we use groupby before aggregation?

Grouping splits data into subsets by 'Team' so aggregation functions like sum and mean apply to each group separately, as shown in execution_table step 2 and 3.

What does the agg({'Points': ['sum', 'mean']}) do exactly?

It tells pandas to calculate both sum and mean of the 'Points' column for each group, producing multiple summary statistics in one step (see execution_table step 3).

Why is the report indexed by 'Team'?

Because groupby uses 'Team' as the grouping key, the resulting aggregated DataFrame uses 'Team' as its index to label each group's summary (execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution_table step 3, what are the 'Points' values for group B before aggregation?

A [15, 10]

B [10, 20]

C [10, 15]

D [20, 25]

💡 Hint

Check the 'DataFrame State' column in step 3 of execution_table.

At which step is the aggregation result stored in the 'report' variable?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look for when 'report' is assigned the aggregated DataFrame in execution_table.

If we changed aggregation to only sum, how would the report DataFrame change?

A It would show mean only

B It would show sum and mean as before

C It would show only sum of Points per Team

D It would show count of Points

💡 Hint

Refer to the aggregation function in execution_sample code and its effect on report.

Concept Snapshot

Data aggregation reporting with pandas:
- Use df.groupby('column') to group data
- Apply .agg() with dict to specify aggregation functions
- Result is a summary DataFrame indexed by group keys
- Common aggregations: sum, mean, count
- Useful for quick summary reports

Full Transcript

This visual execution shows how to create a data aggregation report using pandas. First, a DataFrame is created with team names and points. Then, the data is grouped by the 'Team' column. Aggregation functions sum and mean are applied to the 'Points' column for each group. The result is a new DataFrame showing total and average points per team. Finally, the report is printed. Variables like df, groups, and report change as the code runs. Key moments include understanding why grouping is needed before aggregation, what the agg function does, and why the report is indexed by team. The quiz tests understanding of group values, assignment steps, and aggregation effects. This method helps summarize data quickly and clearly.

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