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

Grouping Data in Pandas

Learn how to create groups using one or multiple columns, inspect groups, select grouped columns, and summarize records by category.

1. Learning Objectives

By the end of this lesson, you will be able to:
  • Create groups using groupby().
  • Group records by one column or multiple columns.
  • Understand how grouping keys determine group membership.
  • Inspect group names and retrieve individual groups.
  • Select columns before performing group-level calculations.
  • Use as_index, sort, and dropna.
  • Calculate totals, averages, and counts for groups.
  • Understand common GroupBy mistakes and their solutions.

2. What Does Grouping Mean?

Grouping means organizing rows that share the same value in a chosen column. For example, a sales dataset may contain transactions from Pune, Mumbai, and Nashik. Grouping by city organizes transactions into three city-based groups.

Once the groups are created, you can calculate statistics for each group separately instead of calculating one statistic for the entire dataset.

Original Rows Pune · 1000 Mumbai · 2000 Pune · 1500 Nashik · 800 Mumbai · 900 Groups by City Pune 1000, 1500 Mumbai 2000, 900 Nashik 800

Rows with the same city value belong to the same group, even when they are not adjacent in the original DataFrame.

3. Create a Sample DataFrame

We will use a sales dataset containing city, product, department, revenue, and units sold.

import pandas as pd

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

print(sales)
Index City Product Department Revenue Units
0PuneLaptopTechnology500005
1MumbaiMouseAccessories1200012
2PuneKeyboardAccessories180009
3NashikLaptopTechnology450004
4MumbaiKeyboardAccessories2000010
5PuneMouseAccessories1000010
6NashikMouseAccessories80008

4. Group by One Column

Pass the name of the column to groupby() to create groups using that column.

grouped = sales.groupby("City")

print(type(grouped))

The expression creates a GroupBy object. It does not immediately print a grouped summary because the calculation has not yet been requested.

Calculate total revenue by city

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

print(result)

Expected output:

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

Calculate average revenue by 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

5. Group by Multiple Columns

Use a list of column names to group records by more than one key. Pandas creates a separate group for each unique combination of values.

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

For example, Pune has separate groups for Laptop, Keyboard, and Mouse. Mumbai has separate groups for Keyboard and Mouse.

Remember: Grouping by City produces one result per city. Grouping by City and Product produces one result per city-product combination.

6. Keep Grouping Columns as Regular Columns

By default, grouping keys commonly become index labels in aggregation results. Use as_index=False when you prefer a regular tabular result.

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

print(result)

Expected output:

     City  Revenue
0  Mumbai    32000
1  Nashik    53000
2    Pune    78000

This is convenient when exporting results to CSV, creating charts, or passing summaries to other data-processing steps.

Multiple grouping keys with as_index=False

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

print(result)

The result contains regular City, Product, and Revenue columns instead of a MultiIndex.

7. Control the Order of Groups

The sort parameter controls whether grouping keys are sorted in the output. By default, Pandas sorts group keys when appropriate for the grouping dtype.

Sort group keys

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

print(result)

Group keys appear in sorted order. For string city names, this normally means alphabetical order.

Keep groups in order of first appearance

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

print(result)

Expected output:

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

Here, Pune appears first in the original dataset, followed by Mumbai and Nashik. Setting sort=False avoids sorting the group keys; it does not sort the rows within each group.

8. Inspect Group Names

The groups attribute provides a mapping from group keys to the index labels of rows belonging to those groups.

grouped = sales.groupby("City")

print(grouped.groups)

The output is a dictionary-like mapping. Its values identify the original DataFrame index labels associated with each city.

For this dataset, the groups contain these row labels:

Group key Original row indices
Mumbai1, 4
Nashik3, 6
Pune0, 2, 5

Count groups

print(grouped.ngroups)

Expected output:

3

9. Retrieve a Specific Group

Use get_group() to retrieve the rows belonging to a particular group key.

grouped = sales.groupby("City")

pune_sales = grouped.get_group("Pune")

print(pune_sales)

Expected output:

   City   Product Department  Revenue  Units
0  Pune    Laptop Technology    50000      5
2  Pune  Keyboard Accessories    18000      9
5  Pune     Mouse Accessories    10000     10

The returned DataFrame contains only rows whose City value is Pune. Their original index labels are preserved.

Important: The requested group key must exist. Attempting to retrieve a non-existent group can raise a KeyError.

10. Iterate Through Groups

You can loop through a GroupBy object. Each iteration provides the group key and the DataFrame containing that group's rows.

for city, group in sales.groupby("City"):
    print("City:", city)
    print(group[["Product", "Revenue"]])
    print()

This is useful when you need to process each group separately, inspect records, or create a report for each category.

For straightforward totals or averages, built-in GroupBy aggregations are usually clearer and more efficient than writing a loop.

11. Select Columns Before Aggregating

Select the relevant column after grouping when you only need one statistic. This makes your intention explicit.

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

print(result)

Expected output:

Department
Accessories    68000
Technology     95000
Name: Revenue, dtype: int64

The expression groups rows by department and sums the Revenue values within each department.

Select several columns

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

print(result)

Expected output:

        Revenue  Units
City
Mumbai    32000     22
Nashik    53000     12
Pune      78000     24

12. Count Rows and Non-Missing Values

The size() and count() methods answer different questions.

Method What it counts Typical use
size() Rows in each group. Count transactions per city.
count() Non-missing values in selected columns. Count recorded revenue values per city.
print(sales.groupby("City").size())

print(sales.groupby("City")["Revenue"].count())

Since the current Revenue column has no missing values, the two results contain the same counts. A dataset with missing Revenue values may produce different results.

13. Handle Missing Grouping Keys

A grouping column can contain missing values. By default, groupby() generally excludes missing grouping keys. Use dropna=False when you want missing keys to form their own group.

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

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

With the default behavior, only Pune and Mumbai appear in the result.

Include the missing-key group

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

print(result)

Expected output:

City
Mumbai    2000
Pune      4000
NaN       2000
Name: Revenue, dtype: int64

The missing-key group contains two rows with revenue values of 1500 and 500, giving a total of 2000.

14. Use GroupBy with Multiple Aggregations

Once grouping is understood, use agg() to produce multiple summaries from each group.

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

Named aggregation makes the output easier to understand by giving each result column a descriptive name.

15. Common Grouping Parameters

Parameter Purpose Example
by Specifies the grouping key or keys. groupby("City")
as_index Controls whether group keys become the result index for applicable aggregations. as_index=False
sort Controls whether group keys are sorted. sort=False
dropna Controls whether missing group keys are excluded. dropna=False
observed Controls which categorical groups are returned for categorical grouping keys. observed=True
Version note: Some GroupBy behaviors and defaults, particularly for categorical columns, can vary between Pandas versions. Check the documentation for your installed version when using observed or other less common options.

16. Common Mistakes and Solutions

Mistake 1: Expecting groupby() to print a summary

Solution: Follow the grouping expression with an operation such as sum(), mean(), or agg().

Mistake 2: Grouping by the wrong key

Solution: Confirm whether the report needs one result per city, product, department, or combination of keys.

Mistake 3: Confusing size() and count()

Solution: Use size() for rows and count() for non-missing values in a selected column.

Mistake 4: Ignoring missing grouping values

Solution: Consider dropna=False when missing categories need to be included in the analysis.

Mistake 5: Assuming group order is the same as row order

Solution: Use sort to control the order of group keys, and sort the aggregated result separately when ranking the final statistics.

17. Revision Questions

  1. What is the purpose of the groupby() method?
  2. How do you group a DataFrame by two columns?
  3. What is the difference between a GroupBy object and an aggregated result?
  4. What does as_index=False do?
  5. How can you retrieve all rows belonging to a specific group?
  6. What information is provided by the groups attribute?
  7. How does sort=False affect group ordering?
  8. What is the difference between size() and count()?
  9. How can you include missing grouping keys?

18. Hands-On Exercises

Exercise 1: Group Sales by City

  • Create a DataFrame with at least six sales records.
  • Group the data by city.
  • Calculate total revenue and transaction counts.
  • Return the result with city as a regular column.

Exercise 2: Group by Department and Role

  • Create an employee DataFrame with Department, Role, and Salary.
  • Group by Department and Role.
  • Calculate the average salary for each combination.

Exercise 3: Inspect Individual Groups

  • Create a dataset containing records from Pune, Mumbai, and Nashik.
  • Inspect the grouping keys and number of groups.
  • Retrieve the Pune group using get_group().
  • Iterate over the groups and print each group's records.

Exercise 4: Missing Categories

  • Add missing values to the grouping column.
  • Compare the default grouping behavior with dropna=False.
  • Explain how missing grouping keys affect the totals.

19. 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 argument keeps grouping keys as regular columns in applicable aggregation results?

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

Answer: B. as_index=False

Question 3

Which method retrieves the rows for a specific group?

  • A. get_group()
  • B. idxmax()
  • C. nunique()

Answer: A. get_group()

Question 4

How can missing grouping keys be included?

  • A. dropna=True
  • B. ignore_missing=True
  • C. dropna=False

Answer: C. dropna=False

Summary

The Pandas groupby() method organizes rows into groups based on one or more columns. After grouping, you can calculate totals, averages, counts, minimums, maximums, and other statistics.

Use as_index=False for regular tabular output, sort=False to avoid sorting group keys, and dropna=False to include a group for missing grouping keys. The groups attribute, ngroups, and get_group() help you inspect and retrieve groups.

In the next lesson, you will study GroupBy aggregation in greater depth and learn to produce concise, multi-statistic summary reports.