Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

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

By the end of this lesson, you will be able to:
  • 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?
Input Data 78 92 85 Multiple values Aggregation sum() mean() Summary Total: 255 Mean: 85 Compact results for decision-making

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
0Samadhan92214
1Pradnya85203
2Samiksha78202
3Aarav67192
4Meera88225
Important: Aggregation functions are generally applied to numeric columns when calculating statistics such as mean, median, variance, and standard deviation.

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
Use case: The mean is useful when you need an overall average, but remember that unusually large or small values can influence it.

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.
axis=0: Column Totals A B C 10 1 5 20 2 5 30 3 5 Sum each column axis=1: Row Totals A B C Total 10 1 5 16 20 2 5 27 30 3 5 38 Sum across each 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.

Be careful with totals: A sum can be returned even when some observations are missing. Before reporting totals, check whether missing values should be ignored, filled, or investigated for your use case.

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

  1. What is aggregation in Pandas?
  2. What is the difference between mean and median?
  3. How do you calculate the total of a column?
  4. What is the difference between count() and len()?
  5. What do std() and var() measure?
  6. How do you apply multiple aggregation functions to a column?
  7. What does axis=0 mean for DataFrame aggregation?
  8. How are missing values treated by common numeric aggregations?
  9. What do idxmax() and idxmin() 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.