Aggregation Operations in Pandas
Learn how to summarize datasets using sum, mean, median, minimum, maximum, count, standard deviation, variance, and multiple aggregation functions.
1. Learning Objectives
- Understand aggregation and why it is useful.
- Calculate totals, averages, medians, minimums, and maximums.
- Count valid values in a Series or DataFrame.
- Calculate standard deviation and variance.
- Aggregate individual columns and entire DataFrames.
- Apply multiple aggregation functions with
agg(). - Handle missing values when summarizing data.
- Use aggregation to answer practical business questions.
2. What Is Aggregation?
Aggregation is the process of summarizing many data values into one or more meaningful results. Instead of examining every row individually, we calculate statistics that describe the dataset.
For example, a student marks dataset can answer these questions:
- What is the total of all students' marks?
- What is the average mark?
- What is the highest mark?
- How many students have recorded marks?
Aggregation turns multiple observations into concise statistical summaries.
3. Create a Sample DataFrame
We will use this student dataset to learn the common aggregation functions.
import pandas as pd
students = pd.DataFrame({
"Name": ["Samadhan", "Pradnya", "Samiksha", "Aarav", "Meera"],
"Marks": [92, 85, 78, 67, 88],
"Age": [21, 20, 20, 19, 22],
"StudyHours": [4, 3, 2, 2, 5]
})
print(students)
| Index | Name | Marks | Age | StudyHours |
|---|---|---|---|---|
| 0 | Samadhan | 92 | 21 | 4 |
| 1 | Pradnya | 85 | 20 | 3 |
| 2 | Samiksha | 78 | 20 | 2 |
| 3 | Aarav | 67 | 19 | 2 |
| 4 | Meera | 88 | 22 | 5 |
4. The sum() Function
The sum() function calculates the total of numeric values.
It is useful for total sales, total marks, total expenses, and total quantities.
total_marks = students["Marks"].sum() print(total_marks)
Expected output:
410
The calculation is 92 + 85 + 78 + 67 + 88 = 410.
Sum a different column
total_study_hours = students["StudyHours"].sum() print(total_study_hours)
Expected output:
16
Sum several numeric columns
print(students[["Marks", "StudyHours"]].sum())
Expected output:
Marks 410 StudyHours 16 dtype: int64
5. The mean() Function
The mean() function calculates the arithmetic average.
Add the values and divide by the number of valid observations.
average_marks = students["Marks"].mean() print(average_marks)
Expected output:
82.0
The total marks are 410 and there are five students, so the mean is 410 divided by 5, which equals 82.
Calculate the average for each numeric column
print(students[["Marks", "Age", "StudyHours"]].mean())
Expected output:
Marks 82.0 Age 20.4 StudyHours 3.2 dtype: float64
6. The median() Function
The median is the middle value after values are arranged in sorted order. It is often useful when a dataset contains unusually high or low values.
median_marks = students["Marks"].median() print(median_marks)
The sorted marks are 67, 78, 85, 88, and 92. The middle value is 85.
Expected output:
85.0
Mean versus median
| Measure | Meaning | Student marks example |
|---|---|---|
| Mean | Total divided by the number of observations. | 82.0 |
| Median | Middle value after sorting. | 85.0 |
7. The min() and max() Functions
Use min() to find the smallest value and max()
to find the largest value.
print("Lowest marks:", students["Marks"].min())
print("Highest marks:", students["Marks"].max())
Expected output:
Lowest marks: 67 Highest marks: 92
Find the names of the students with the lowest and highest marks
highest_index = students["Marks"].idxmax() lowest_index = students["Marks"].idxmin() print(students.loc[highest_index, "Name"]) print(students.loc[lowest_index, "Name"])
Expected output:
Samadhan Aarav
Unlike max() and min(), which return values,
idxmax() and idxmin() return the index label
of the first occurrence of the maximum or minimum.
8. The count() and size Concepts
Use count() to count non-missing values in a Series or
separately in each DataFrame column.
print(students["Marks"].count()) print(students.count())
Expected output:
5 Name 5 Marks 5 Age 5 StudyHours 5 dtype: int64
In this dataset, every column has five non-missing values.
Difference between count() and len()
print("Rows:", len(students))
print("Non-missing marks:", students["Marks"].count())
len(students) counts rows, while
students["Marks"].count() counts non-missing values in
the Marks column. These results can differ when values are missing.
9. The std() and var() Functions
Standard deviation and variance describe how spread out numeric values are around their mean.
- Variance: Measures spread using squared deviations from the mean.
- Standard deviation: The square root of variance, expressed in the original units.
print("Standard deviation:", students["Marks"].std())
print("Variance:", students["Marks"].var())
Expected output, rounded for display:
Standard deviation: 10.246950765959598 Variance: 105.0
By default, Pandas calculates sample variance and sample standard deviation, using a degrees-of-freedom adjustment of 1.
Population standard deviation
population_std = students["Marks"].std(ddof=0) print(population_std)
Setting ddof=0 calculates the population standard deviation.
Use the population or sample version according to the statistical question.
10. Other Useful Statistical Functions
| Function | Purpose | Example |
|---|---|---|
sum() |
Calculates a total. | students["Marks"].sum() |
mean() |
Calculates the arithmetic average. | students["Marks"].mean() |
median() |
Returns the middle value. | students["Marks"].median() |
min() |
Returns the smallest value. | students["Marks"].min() |
max() |
Returns the largest value. | students["Marks"].max() |
count() |
Counts non-missing values. | students["Marks"].count() |
std() |
Calculates standard deviation. | students["Marks"].std() |
var() |
Calculates variance. | students["Marks"].var() |
nunique() |
Counts distinct non-missing values. | students["Age"].nunique() |
quantile() |
Calculates a requested quantile. | students["Marks"].quantile(0.75) |
Example: Count distinct ages
print(students["Age"].nunique())
Expected output:
4
The distinct ages are 19, 20, 21, and 22.
11. Using agg() for Multiple Aggregations
The agg() method allows you to apply multiple aggregation
functions to one or more columns in a single operation.
Example 1: Multiple functions on one column
result = students["Marks"].agg(
["sum", "mean", "median", "min", "max"]
)
print(result)
Expected output:
sum 410.0 mean 82.0 median 85.0 min 67.0 max 92.0 Name: Marks, dtype: float64
Example 2: Multiple functions on several columns
result = students[["Marks", "StudyHours"]].agg(
["min", "max", "mean"]
)
print(result)
Expected output:
Marks StudyHours min 67.0 2.0 max 92.0 5.0 mean 82.0 3.2
Each row represents an aggregation function, while each column represents one of the selected original columns.
Example 3: Different aggregations for different columns
result = students.agg({
"Marks": ["min", "max", "mean"],
"StudyHours": ["sum", "mean"]
})
print(result)
When different functions are requested for different columns, Pandas aligns the results by function name. Missing combinations appear as missing values in the resulting table.
12. Aggregation Across DataFrame Rows and Columns
The axis parameter determines the direction of aggregation
for DataFrame methods that support it.
numbers = pd.DataFrame({
"A": [10, 20, 30],
"B": [1, 2, 3],
"C": [5, 5, 5]
})
print(numbers.sum(axis=0))
print(numbers.sum(axis=1))
Expected output:
A 60 B 6 C 15 dtype: int64 0 16 1 27 2 38 dtype: int64
| Argument | Aggregation direction | Meaning |
|---|---|---|
axis=0 |
Down each column | Produces one result per column. |
axis=1 |
Across each row | Produces one result per row. |
Column aggregation summarizes each column; row aggregation summarizes each row.
13. Handling Missing Values During Aggregation
Pandas numeric aggregation functions generally skip missing values by default. The following example shows how this affects a total and average.
scores = pd.DataFrame({
"Name": ["Samadhan", "Pradnya", "Samiksha", "Meera"],
"Marks": [90, None, 80, 70]
})
print("Sum:", scores["Marks"].sum())
print("Mean:", scores["Marks"].mean())
print("Count:", scores["Marks"].count())
Expected output:
Sum: 240.0 Mean: 80.0 Count: 3
Only three marks are present, so the mean is calculated using 90, 80, and 70. The missing value is not treated as zero by the mean calculation.
14. Practical Example: Sales Report
Samiksha is analyzing monthly sales and wants a quick summary of revenue and units sold.
sales = pd.DataFrame({
"Product": ["Laptop", "Mouse", "Keyboard", "Monitor", "Webcam"],
"UnitsSold": [25, 80, 60, 30, 45],
"Revenue": [125000, 16000, 30000, 90000, 22500]
})
summary = sales[["UnitsSold", "Revenue"]].agg(
["sum", "mean", "min", "max"]
)
print(summary)
Expected output:
UnitsSold Revenue sum 240.0 283500.0 mean 48.0 56700.0 min 25.0 16000.0 max 80.0 125000.0
This summary provides the total, average, minimum, and maximum for both sales quantities and revenue. It can be used as the starting point for a more detailed business report.
15. Aggregation Compared with Related Operations
| Operation | Main purpose | Typical method |
|---|---|---|
| Aggregation | Summarizes multiple values into statistics. | sum(), mean(), agg() |
| Sorting | Reorders rows or columns. | sort_values(), sort_index() |
| Filtering | Selects rows that satisfy conditions. | df[df["Marks"] > 80] |
| Grouping | Splits observations into groups for analysis. | groupby() |
These operations often work together. For example, filter a dataset, group the remaining rows by department, aggregate salaries, and sort the summary to identify the highest-paying departments.
16. Common Mistakes
Mistake 1: Calculating the mean of a text column
A name column is not numeric. Select an appropriate numeric column before calculating statistics such as mean or standard deviation.
Mistake 2: Confusing count with row count
count() excludes missing values. Use len(df)
when you want the number of rows in a DataFrame.
Mistake 3: Confusing sample and population standard deviation
Pandas uses ddof=1 by default for std()
and var(). Use ddof=0 for population statistics.
Mistake 4: Assuming aggregation changes the original data
Aggregation methods generally return summary results. They do not ordinarily replace the original DataFrame.
Mistake 5: Using every aggregation on every column
Select columns that match the statistic you need. Numeric operations may not be meaningful for text or identifier columns.
17. Revision Questions
- What is aggregation in Pandas?
- What is the difference between mean and median?
- How do you calculate the total of a column?
- What is the difference between
count()andlen()? - What do
std()andvar()measure? - How do you apply multiple aggregation functions to a column?
- What does
axis=0mean for DataFrame aggregation? - How are missing values treated by common numeric aggregations?
- What do
idxmax()andidxmin()return?
18. Hands-On Exercises
Exercise 1: Student Marks
Create a DataFrame with the marks 75, 89, 94, 68, and 84.
- Calculate the total and average marks.
- Find the highest and lowest marks.
- Calculate the median, variance, and standard deviation.
- Display all five statistics using
agg().
Exercise 2: Employee Salaries
Create an employee dataset with names, departments, and salaries.
- Calculate the average salary.
- Find the minimum and maximum salaries.
- Count the non-missing salary values.
Exercise 3: Sales Summary
Create a DataFrame containing products, quantities, and revenue.
- Calculate total units sold and total revenue.
- Calculate the average revenue per product.
- Use
agg()to create a compact summary.
Exercise 4: Missing Data
Create a numeric column with at least one missing value. Compare its sum, mean, and count with the total number of rows.
19. Quick Knowledge Check
Question 1
Which function calculates the arithmetic average?
- A.
sum() - B.
mean() - C.
max()
Answer: B. mean()
Question 2
Which method applies multiple aggregation functions?
- A.
agg() - B.
sort_index() - C.
reset_index()
Answer: A. agg()
Question 3
Which function counts non-missing values in a Series?
- A.
count() - B.
median() - C.
var()
Answer: A. count()
Question 4
Which method returns the index label of the maximum value?
- A.
max() - B.
idxmax() - C.
nunique()
Answer: B. idxmax()
Summary
Aggregation helps turn detailed datasets into useful summaries.
Common functions include sum(), mean(),
median(), min(), max(),
count(), std(), and var().
Use agg() when you need several statistics in one operation.
For DataFrames, the axis parameter controls whether values
are summarized down columns or across rows. Remember to account for
missing data and choose the appropriate statistical definition.
In the next lesson, you will learn how to combine aggregation with
groupby() to summarize data separately for categories,
departments, cities, or other groups.