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

GroupBy Aggregation in Pandas

Learn how to calculate multiple statistics for groups, use named aggregation, summarize multiple columns, and create clean reports from grouped data.

1. Learning Objectives

By the end of this lesson, you will be able to:
  • Understand aggregation after grouping.
  • Calculate totals, averages, counts, minimums, and maximums by group.
  • Apply several aggregation functions in one operation.
  • Use named aggregation to create readable output columns.
  • Aggregate different columns using different functions.
  • Group data using multiple keys.
  • Sort and format aggregated results.
  • Handle missing values and interpret grouped summaries.

2. What Is GroupBy Aggregation?

GroupBy aggregation combines two operations. First, Pandas separates rows into groups using one or more keys. Then, it calculates a summary for each group.

For example, a sales dataset can be grouped by city and aggregated to calculate total revenue, average revenue, and the number of transactions in each city.

Sales Records Pune: 1000 Mumbai: 2000 Pune: 1500 Individual rows GroupBy + agg() sum() mean() count() Summary Report Pune: 2500 Mumbai: 2000 One row per group Multiple statistics

Grouping defines categories; aggregation calculates statistics for each category.

3. Prepare a Sample Dataset

We will use a sales dataset throughout this lesson.

import pandas as pd

sales = pd.DataFrame({
    "City": [
        "Pune", "Mumbai", "Pune",
        "Nashik", "Mumbai", "Pune",
        "Nashik"
    ],
    "Product": [
        "Laptop", "Mouse", "Keyboard",
        "Laptop", "Keyboard", "Mouse",
        "Mouse"
    ],
    "Revenue": [50000, 12000, 18000, 45000, 20000, 10000, 8000],
    "Units": [5, 12, 9, 4, 10, 10, 8]
})

print(sales)
Index City Product Revenue Units
0PuneLaptop500005
1MumbaiMouse1200012
2PuneKeyboard180009
3NashikLaptop450004
4MumbaiKeyboard2000010
5PuneMouse1000010
6NashikMouse80008

4. Aggregate with a Single Function

The simplest grouped aggregation applies one function to one selected column.

Total revenue per city

result = sales.groupby("City")["Revenue"].sum()

print(result)

Expected output:

City
Mumbai    32000
Nashik    53000
Pune      78000
Name: Revenue, dtype: int64

Average revenue per city

result = sales.groupby("City")["Revenue"].mean()

print(result)

Expected output:

City
Mumbai    16000.0
Nashik    26500.0
Pune      26000.0
Name: Revenue, dtype: float64

Number of transactions per city

result = sales.groupby("City").size()

print(result)

Expected output:

City
Mumbai    2
Nashik    2
Pune      3
dtype: int64

5. Apply Multiple Functions with agg()

The agg() method allows you to calculate multiple statistics for the same column in one operation.

summary = sales.groupby("City")["Revenue"].agg(
    ["sum", "mean", "min", "max", "count"]
)

print(summary)

Expected output:

          sum     mean    min    max  count
City
Mumbai  32000  16000.0  12000  20000      2
Nashik  53000  26500.0   8000  45000      2
Pune    78000  26000.0  10000  50000      3

Each row represents a city, and each summary column represents a different aggregation function.

Tip: Use agg() when a report requires several statistics. It is clearer than repeatedly grouping the same data for every statistic.

6. Aggregate Different Columns with Different Functions

You can assign different functions to different columns by passing a dictionary to agg().

summary = sales.groupby("City").agg({
    "Revenue": ["sum", "mean"],
    "Units": ["sum", "max"]
})

print(summary)

The output contains a hierarchical column structure because each source column has multiple statistics.

Expected values:

City Revenue sum Revenue mean Units sum Units max
Mumbai32000160002212
Nashik5300026500128
Pune78000260002410

This approach is useful when different business metrics require different statistical summaries.

7. Named Aggregation

Named aggregation lets you specify meaningful output column names. It is especially useful for creating clean reports and exporting summary tables.

summary = sales.groupby("City").agg(
    TotalRevenue=("Revenue", "sum"),
    AverageRevenue=("Revenue", "mean"),
    MinimumRevenue=("Revenue", "min"),
    MaximumRevenue=("Revenue", "max"),
    TotalUnits=("Units", "sum"),
    TransactionCount=("Product", "count")
)

print(summary)

Expected output:

        TotalRevenue  AverageRevenue  MinimumRevenue  MaximumRevenue  TotalUnits  TransactionCount
City
Mumbai         32000         16000.0           12000           20000          22                 2
Nashik         53000         26500.0            8000           45000          12                 2
Pune           78000         26000.0           10000           50000          24                 3

Each named aggregation uses this pattern:

OutputColumn=("SourceColumn", "aggregation_function")

For example, TotalRevenue=("Revenue", "sum") creates a column named TotalRevenue containing the sum of Revenue values in each group.

8. Group by Multiple Columns

Grouping by more than one key creates a separate group for every unique combination of the selected columns.

summary = sales.groupby(
    ["City", "Product"],
    as_index=False
).agg(
    TotalRevenue=("Revenue", "sum"),
    TotalUnits=("Units", "sum")
)

print(summary)

Expected output:

     City   Product  TotalRevenue  TotalUnits
0  Mumbai  Keyboard         20000          10
1  Mumbai     Mouse         12000          12
2  Nashik    Laptop         45000           4
3  Nashik     Mouse          8000           8
4    Pune  Keyboard         18000           9
5    Pune    Laptop         50000           5
6    Pune     Mouse         10000          10

This report shows each city's performance for each product. It can help identify which products contribute the most revenue in different locations.

9. Use as_index=False for a Flat Result

By default, group keys commonly become index labels in aggregated results. Use as_index=False to keep the group keys as ordinary columns.

Default behavior

result = sales.groupby("City")["Revenue"].sum()

print(result.index)

The city names are stored in the result's index.

Keep City as a column

result = sales.groupby(
    "City",
    as_index=False
)["Revenue"].sum()

print(result.columns.tolist())

Expected output:

['City', 'Revenue']
Practical tip: A flat DataFrame is often easier to export, merge with another table, or pass to a plotting library.

10. Sort Aggregated Results

After calculating group statistics, sort the results to rank categories.

summary = (
    sales.groupby("City", as_index=False)
    .agg(TotalRevenue=("Revenue", "sum"))
    .sort_values("TotalRevenue", ascending=False)
)

print(summary)

Expected output:

     City  TotalRevenue
2    Pune         78000
1  Nashik         53000
0  Mumbai         32000

The cities are now ranked by total revenue from highest to lowest.

11. Handling Missing Values

Numeric aggregation methods such as sum() and mean() generally skip missing values by default. Missing grouping keys are a separate concern.

data = pd.DataFrame({
    "City": ["Pune", "Pune", "Mumbai", "Mumbai"],
    "Revenue": [1000, None, 2000, 3000]
})

summary = data.groupby("City").agg(
    RevenueTotal=("Revenue", "sum"),
    RevenueAverage=("Revenue", "mean"),
    RevenueCount=("Revenue", "count"),
    RowCount=("Revenue", "size")
)

print(summary)

Expected output:

        RevenueTotal  RevenueAverage  RevenueCount  RowCount
City
Mumbai        5000.0          2500.0              2         2
Pune          1000.0          1000.0              1         2

Pune has two rows but only one recorded revenue value. Therefore, its row count is two and its non-missing revenue count is one.

Important: A total calculated while values are missing may not represent the complete total. Check data completeness before using grouped summaries for financial or operational decisions.

12. Practical Example: Student Performance Report

Samadhan is preparing a report that compares average marks, highest marks, and the number of students in each class.

students = pd.DataFrame({
    "Name": [
        "Samadhan", "Pradnya", "Samiksha",
        "Aarav", "Meera", "Rohan"
    ],
    "Class": ["A", "A", "B", "B", "A", "B"],
    "Marks": [92, 85, 78, 67, 88, 90],
    "StudyHours": [4, 3, 2, 2, 5, 4]
})

summary = students.groupby("Class").agg(
    AverageMarks=("Marks", "mean"),
    HighestMarks=("Marks", "max"),
    LowestMarks=("Marks", "min"),
    AverageStudyHours=("StudyHours", "mean"),
    StudentCount=("Name", "count")
)

print(summary)

Expected output:

       AverageMarks  HighestMarks  LowestMarks  AverageStudyHours  StudentCount
Class
A          88.333333            92           85           4.000000             3
B          78.333333            90           67           2.666667             3

The report shows differences between classes across several metrics. These summaries describe the dataset; they do not establish that study hours caused a difference in marks.

13. Common Aggregation Functions

Function Purpose Typical example
sum Calculates a total. Total revenue by city.
mean Calculates an average. Average marks by class.
median Calculates the middle value. Median salary by department.
min Finds the smallest value. Lowest price by category.
max Finds the largest value. Highest marks by class.
count Counts non-missing values. Recorded transactions by city.
std Calculates standard deviation. Variation in marks by class.
nunique Counts distinct non-missing values. Distinct products sold by city.

14. Common Mistakes and Solutions

Mistake 1: Calling groupby() without an aggregation

Solution: Add a method such as sum(), mean(), or agg() to calculate the summary.

Mistake 2: Forgetting to select the intended column

Solution: Use an expression such as df.groupby("City")["Revenue"].sum() when only revenue should be aggregated.

Mistake 3: Confusing count() with size()

Solution: Remember that count() excludes missing values in the selected column, whereas size() counts rows in each group.

Mistake 4: Producing hierarchical columns unintentionally

Solution: Use named aggregation when you want descriptive, flat column names.

Mistake 5: Forgetting to sort the final summary

Solution: Apply sort_values() to the aggregated result when you need a ranking.

15. Revision Questions

  1. What does aggregation mean in GroupBy operations?
  2. How do you calculate total revenue by city?
  3. How can you apply several functions to one grouped column?
  4. What is named aggregation?
  5. How can you aggregate different columns using different functions?
  6. What is the benefit of as_index=False?
  7. How can you rank groups by their aggregated revenue?
  8. What is the difference between count() and size()?
  9. How are missing values handled in grouped aggregation?

16. Hands-On Exercises

Exercise 1: Sales Summary

  • Create a sales DataFrame with city, product, revenue, and quantity.
  • Calculate total and average revenue by city.
  • Find the highest and lowest transaction values per city.

Exercise 2: Multiple Aggregations

  • Use agg() to calculate sum, mean, minimum, and maximum.
  • Apply different aggregation functions to different columns.
  • Display the result as a clean summary table.

Exercise 3: Named Aggregation

  • Create output columns named TotalRevenue, AverageRevenue, and TotalUnits.
  • Group by city and generate the report.
  • Sort the report by TotalRevenue in descending order.

Exercise 4: Student Performance

  • Create a dataset containing class, student name, and marks.
  • Calculate the average, highest, and lowest marks by class.
  • Count students in each class.

17. Quick Knowledge Check

Question 1

Which method applies several aggregation functions?

  • A. agg()
  • B. rename()
  • C. drop_duplicates()

Answer: A. agg()

Question 2

Which option is used for named aggregation?

  • A. Total=("Revenue", "sum")
  • B. Total="Revenue"
  • C. sum("Total", "Revenue")

Answer: A. Total=("Revenue", "sum")

Question 3

Which argument keeps group keys as regular columns in applicable aggregation results?

  • A. sort=False
  • B. as_index=False
  • C. dropna=False

Answer: B. as_index=False

Question 4

Which method counts rows in each group?

  • A. size()
  • B. mean()
  • C. idxmax()

Answer: A. size()

Summary

GroupBy aggregation allows you to calculate statistics for categories rather than for an entire dataset at once. Use sum(), mean(), min(), max(), count(), and agg() to summarize groups.

Named aggregation creates readable output columns, while grouping by multiple keys produces summaries for unique combinations of values. Use as_index=False for flat results and sort_values() to rank the final report.

In the next lesson, you will learn how to filter entire groups using conditions, rather than filtering individual rows.