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 Overview in Pandas

Learn how to divide a dataset into groups, calculate summaries for each group, and combine the results into meaningful reports.

1. Learning Objectives

By the end of this lesson, you will be able to:
  • Understand the purpose of grouping data.
  • Use groupby() to group rows by a column.
  • Calculate group totals, averages, counts, minimums, and maximums.
  • Group data using multiple columns.
  • Apply multiple aggregation functions to each group.
  • Understand the split-apply-combine workflow.
  • Use GroupBy to answer practical business and student-performance questions.

2. What Is GroupBy?

GroupBy is a technique for organizing rows into categories and performing calculations separately for each category.

For example, a sales dataset might contain transactions from several cities. Instead of calculating one total for the entire dataset, we can calculate a separate total for Pune, Mumbai, and Nashik.

Questions GroupBy can answer

  • What is the total revenue for each city?
  • What is the average salary in each department?
  • How many students belong to each class?
  • Which product category has the highest average price?
  • How many transactions occurred in each region?
Original Data Pune: 100 Mumbai: 200 Pune: 150 Mumbai: 50 GroupBy Split by city Apply sum() Combine results Group Totals Mumbai: 250 Pune: 250 One result per group instead of one total

GroupBy splits rows into categories, applies calculations, and combines the results.

3. The Split-Apply-Combine Process

GroupBy follows three important steps.

Step Meaning Example
Split Divide the rows into groups according to one or more keys. Create a group for each city.
Apply Perform a calculation or transformation on each group. Calculate revenue totals.
Combine Collect the group results into a final output. Return a table of city totals.
Remember: GroupBy is not just about totals. You can use it for averages, counts, minimums, maximums, transformations, filtering, and many other group-level operations.

4. Create a Sample Dataset

We will use a sales dataset with cities, products, and revenue.

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

5. Group Data Using One Column

Use groupby() to group records by a column. The following expression creates a GroupBy object grouped by city.

grouped = sales.groupby("City")

print(type(grouped))

The result is a GroupBy object. It represents the groups but does not automatically display a summary table.

Important: Calling groupby() alone does not calculate a total or average. Follow it with an operation such as sum(), mean(), size(), or agg().

6. Calculate Total Revenue by City

To find the total revenue for each city, group by City and sum the Revenue column.

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

print(city_revenue)

Expected output:

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

The calculation adds all revenue values belonging to the same city.

Return the result as a DataFrame

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

print(city_revenue)

Expected output:

     City  Revenue
0  Mumbai    32000
1  Nashik    53000
2    Pune    78000

The as_index=False argument keeps the grouping key as a regular column instead of using it as the result index.

7. Calculate Average Revenue by City

Replace sum() with mean() to calculate the average revenue per transaction in each city.

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

print(average_revenue)

Expected output:

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

Pune's average is 78,000 divided by three transactions, which equals 26,000. Nashik's average is 53,000 divided by two transactions, which equals 26,500.

Business insight: A city with the highest total revenue does not necessarily have the highest average revenue per transaction. Compare both metrics before making decisions.

8. Count Records in Each Group

There are two common ways to count grouped records. Use size() to count rows in each group, or count() to count non-missing values in a selected column.

Count rows with size()

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

print(transaction_count)

Expected output:

City
Mumbai    2
Nashik    2
Pune      3
dtype: int64

Count non-missing revenue values

revenue_count = sales.groupby("City")["Revenue"].count()

print(revenue_count)

Because the current dataset contains no missing revenue values, both examples return the same counts. They can differ when the selected column contains missing data.

9. Find the Minimum and Maximum by Group

You can calculate the smallest and largest revenue values for each city.

city_minimum = sales.groupby("City")["Revenue"].min()
city_maximum = sales.groupby("City")["Revenue"].max()

print("Minimum revenue:")
print(city_minimum)

print("Maximum revenue:")
print(city_maximum)

Expected output:

Minimum revenue:
City
Mumbai    12000
Nashik     8000
Pune      10000
Name: Revenue, dtype: int64

Maximum revenue:
City
Mumbai    20000
Nashik    45000
Pune      50000
Name: Revenue, dtype: int64

10. Group by Multiple Columns

Grouping by multiple columns creates a separate group for each unique combination of grouping values.

For example, grouping by both City and Product calculates results separately for each city-product combination.

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

print(result)

Expected output:

City    Product
Mumbai  Keyboard    20000
        Mouse       12000
Nashik  Laptop      45000
        Mouse        8000
Pune    Keyboard    18000
        Laptop      50000
        Mouse       10000
Name: Revenue, dtype: int64

The output uses a MultiIndex because two columns are used as grouping keys. Each combination has its own result.

Keep both grouping columns in the result

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

print(result)

This returns a regular DataFrame with City, Product, and Revenue columns.

11. Apply Multiple Aggregations

Use agg() to calculate several statistics for each group 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

This creates a compact report showing total revenue, average revenue, minimum revenue, maximum revenue, and the number of non-missing revenue values for each city.

Aggregate multiple numeric columns

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

print(summary)

The output has hierarchical column labels because each original column has more than one requested statistic. This format is useful for multi-metric reporting.

12. GroupBy with a Named Aggregation

Named aggregation lets you assign clear names to output columns. This is particularly useful when preparing a report for another application or exporting a clean summary.

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

print(summary)

Expected output:

        TotalRevenue  AverageRevenue  TotalUnits  TransactionCount
City
Mumbai         32000         16000.0          22                 2
Nashik         53000         26500.0          12                 2
Pune           78000         26000.0          24                 3

Each named output column uses a tuple containing the source column and aggregation function. This produces readable column names without hierarchical output columns.

13. GroupBy and Missing Values

Missing values in a grouping key are generally excluded from groups by default. Use dropna=False if you need a separate group for missing grouping keys.

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

print(data.groupby("City")["Revenue"].sum())

By default, the missing city does not appear as a separate group.

Include missing grouping keys

result = data.groupby(
    "City",
    dropna=False
)["Revenue"].sum()

print(result)

The result includes a missing-key group representing the record whose city is not available. This can be useful for identifying incomplete records rather than silently excluding them from a report.

Do not confuse two types of missing data: dropna in groupby() controls missing grouping keys. Missing values in the aggregated column are handled according to the selected aggregation function.

14. Sort Grouped Results

After calculating group summaries, you can sort the results to identify the highest or lowest categories.

summary = (
    sales.groupby("City")["Revenue"]
    .sum()
    .sort_values(ascending=False)
)

print(summary)

Expected output:

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

Pune has the highest total revenue in this dataset, followed by Nashik and Mumbai.

15. Practical Example: Student Performance by Class

Pradnya is preparing a student report and wants to compare average marks and total study hours across classes.

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"),
    TotalStudyHours=("StudyHours", "sum"),
    StudentCount=("Name", "count")
)

print(summary)

Expected output:

       AverageMarks  HighestMarks  TotalStudyHours  StudentCount
Class
A          88.333333            92               12             3
B          78.333333            90                8             3

Class A has a higher average mark and more total study hours in this example. These are descriptive results; they do not by themselves establish that study hours caused the difference in marks.

16. Important GroupBy Methods

Method Purpose Example
sum() Calculates group totals. sales.groupby("City")["Revenue"].sum()
mean() Calculates group averages. sales.groupby("City")["Revenue"].mean()
count() Counts non-missing values per group. sales.groupby("City")["Revenue"].count()
size() Counts rows per group. sales.groupby("City").size()
min() Finds the minimum within each group. sales.groupby("City")["Revenue"].min()
max() Finds the maximum within each group. sales.groupby("City")["Revenue"].max()
agg() Applies one or more aggregation functions. sales.groupby("City").agg(...)
transform() Returns results aligned to the original rows. sales.groupby("City")["Revenue"].transform("sum")
filter() Keeps or removes entire groups based on a condition. grouped.filter(...)

The next lessons explore these operations in more detail, including aggregation, filtering groups, and transformations.

17. Common Mistakes

Mistake 1: Calling groupby() without an operation

groupby() creates a grouped object. Add an operation such as sum() or agg() to calculate a result.

Mistake 2: Confusing size() and count()

size() counts rows in each group. count() counts non-missing values in the selected column.

Mistake 3: Grouping by the wrong columns

Check whether you need a group per city, per product, or per city-product combination.

Mistake 4: Expecting all columns in the result

Aggregation returns the grouping keys and the selected summaries, not necessarily every original column.

Mistake 5: Ignoring missing grouping keys

If missing categories matter to your analysis, consider dropna=False and inspect the resulting missing-key group.

18. Revision Questions

  1. What is the purpose of groupby()?
  2. Explain the split-apply-combine process.
  3. How do you calculate total revenue for each city?
  4. What is the difference between size() and count()?
  5. How do you group data by two columns?
  6. Why would you use agg() after groupby()?
  7. What does as_index=False do?
  8. How can you include missing grouping keys?
  9. How do you sort a grouped revenue summary from highest to lowest?

19. Hands-On Exercises

Exercise 1: Sales by City

Create a sales DataFrame with city, product, and revenue columns.

  • Calculate total revenue for each city.
  • Calculate average revenue for each city.
  • Sort cities by total revenue in descending order.

Exercise 2: Department Salary Report

Create an employee DataFrame containing department, name, and salary.

  • Calculate the average salary by department.
  • Find the highest salary in each department.
  • Count employees in each department.

Exercise 3: Group by Multiple Columns

Group a sales dataset by both city and product. Calculate total revenue for every combination.

Exercise 4: Multiple Aggregations

Use agg() to calculate total revenue, average revenue, minimum revenue, and maximum revenue for each city.

Exercise 5: Missing Grouping Keys

Add a row with a missing city. Compare the grouped output with the default behavior and with dropna=False.

20. Quick Knowledge Check

Question 1

Which method creates groups based on column values?

  • A. groupby()
  • B. sort_values()
  • C. reset_index()

Answer: A. groupby()

Question 2

Which method counts rows in each group?

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

Answer: B. size()

Question 3

Which method is suitable for several statistics per group?

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

Answer: A. agg()

Question 4

What does grouping by City and Product create?

  • A. One group for the entire DataFrame
  • B. A group for each unique city-product combination
  • C. A group for every numeric column

Answer: B. A group for each unique city-product combination

Summary

GroupBy is a powerful Pandas technique for summarizing data by categories. It follows the split-apply-combine process: divide the rows into groups, perform calculations, and combine the results.

Use groupby() with functions such as sum(), mean(), min(), max(), size(), count(), and agg(). Grouping by multiple columns creates a separate group for every unique combination of the selected keys.

In the next lesson, you will explore GroupBy aggregation in greater depth, including named aggregations and building clean summary reports.