Pandas aggregations and groupby operations

To extract meaningful insights from raw data, we need to summarise it - calculating totals, averages, counts, and other statistics that reveal patterns. This process is called aggregation.

Pandas aggregation methods operate column-wise by default. When applied to a DataFrame, they produce a Series where each value summarises one column. When applied to a single Series, they return a scalar value.

The most commonly used aggregation methods:

• .sum() - column totals
• .mean() - arithmetic average
• .count() - number of non-missing values
• .min() / .max() - smallest / largest values
• .std() / .var() - standard deviation / variance

These methods automatically skip missing values (NaN) and only work on columns with compatible data types.

Let's work with a sales dataset to see aggregation in action.

import pandas as pd
import numpy as np

data = {
    "product": ["Widget A", "Widget B", "Gadget X", "Widget A", "Gadget X", "Widget B"],
    "month": ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb"],
    "units_sold": [120, 85, 200, 135, 180, 92],
    "revenue": [2400.0, 1275.0, 6000.0, 2700.0, 5400.0, 1380.0],
    "profit_margin": [0.25, 0.30, 0.15, 0.28, 0.18, 0.32]
}
df = pd.DataFrame(data)

    product month  units_sold  revenue  profit_margin
0  Widget A   Jan         120   2400.0           0.25
1  Widget B   Jan          85   1275.0           0.30
2  Gadget X   Jan         200   6000.0           0.15
3  Widget A   Feb         135   2700.0           0.28
4  Gadget X   Feb         180   5400.0           0.18
5  Widget B   Feb          92   1380.0           0.32

Aggregating a DataFrame

Calling .sum() on the numeric columns produces a Series whose labels are the column names:

numeric_columns = ["units_sold", "revenue", "profit_margin"]
print(df[numeric_columns].sum())

units_sold         812.00
revenue          19155.00
profit_margin        1.48
dtype: float64

Aggregating a Series

Applying an aggregation to a single Series returns a scalar value:

print(df["units_sold"].sum())  # np.int64(812)
print(df["product"].count())  # np.int64(6)

While overall summaries are useful, the real analytical power comes from calculating statistics by group - for example, sales by product, by month, or by region. This is where .groupby() becomes essential.

The .groupby() method implements the split-apply-combine pattern:

1. Split - the DataFrame is divided into groups based on the values in one or more columns.
2. Apply - an aggregation function (like .sum() or .mean()) is applied to each group separately.
3. Combine - the results from all groups are assembled into a new DataFrame or Series.

The syntax follows this pattern:

df.groupby("grouping_column")["columns_to_aggregate"].aggregation_method()

The grouping column becomes the index of the result. We typically select which columns to aggregate, preventing Pandas from attempting meaningless operations like summing text columns.

Let's use a sales dataset to explore grouping operations.

import pandas as pd

data = {
    "product": ["Widget A", "Widget B", "Gadget X", "Widget A", "Gadget X",
                "Widget B", "Widget A", "Widget B", "Gadget X"],
    "region": ["North", "North", "South", "South", "North", "South",
               "North", "North", "South"],
    "month": ["Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Feb", "Feb", "Feb"],
    "units_sold": [120, 85, 200, 135, 180, 92, 110, 88, 195],
    "revenue": [2400, 1275, 6000, 2700, 5400, 1380, 2200, 1320, 5850]
}
df = pd.DataFrame(data)

    product region month  units_sold  revenue
0  Widget A  North   Jan         120     2400
1  Widget B  North   Jan          85     1275
2  Gadget X  South   Jan         200     6000
3  Widget A  South   Jan         135     2700
4  Gadget X  North   Jan         180     5400
5  Widget B  South   Jan          92     1380
6  Widget A  North   Feb         110     2200
7  Widget B  North   Feb          88     1320
8  Gadget X  South   Feb         195     5850

Grouping with multiple aggregated columns

Grouping by product and summing the numeric columns produces a DataFrame:

numeric_columns = ["units_sold", "revenue"]
product_totals = df.groupby("product")[numeric_columns].sum()
print(product_totals)

          units_sold  revenue
product
Gadget X         575    17250
Widget A         365     7300
Widget B         265     3975

The grouping column ("product") becomes the index of the result. This breakdown reveals that Gadget X has the highest total sales across all regions and months.

Grouping with a single aggregated column

Aggregating only one column produces a Series instead:

print(df.groupby("product")["units_sold"].mean())

product
Gadget X    191.666667
Widget A    121.666667
Widget B     88.333333
Name: units_sold, dtype: float64

We can group by multiple columns simultaneously by passing a list. This allows for analysis like "revenue by product and month" or "survival rate by class and sex".

The syntax extends naturally from single-column grouping:

df.groupby(["col_1", "col_2"])["cols_to_aggregate"].aggregation_method()

When we group by multiple columns, Pandas creates a multi-level index where each unique combination of grouping values identifies a row in the result.

Using the same sales dataset, we can group by both region and product simultaneously.

import pandas as pd

data = {
    "product": ["Widget A", "Widget B", "Gadget X", "Widget A", "Gadget X",
                "Widget B", "Widget A", "Widget B", "Gadget X"],
    "region": ["North", "North", "South", "South", "North", "South",
               "North", "North", "South"],
    "month": ["Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Feb", "Feb", "Feb"],
    "units_sold": [120, 85, 200, 135, 180, 92, 110, 88, 195],
    "revenue": [2400, 1275, 6000, 2700, 5400, 1380, 2200, 1320, 5850]
}
df = pd.DataFrame(data)

Passing a list of columns to .groupby() creates a multi-level breakdown:

numeric_columns = ["units_sold", "revenue"]
region_product_summary = df.groupby(["region", "product"])[numeric_columns].sum()
print(region_product_summary)

                 units_sold  revenue
region product
North  Gadget X         180     5400
       Widget A         230     4600
       Widget B         173     2595
South  Gadget X         395    11850
       Widget A         135     2700
       Widget B          92     1380

The result has a multi-level index with one level per grouping column. This breakdown reveals that Gadget X performs much better in the South, while Widget A is more balanced across regions.

region_product_flat = region_product_summary.reset_index()
print(region_product_flat)

   region   product  units_sold  revenue
0  North  Gadget X         180     5400
1  North  Widget A         230     4600
2  North  Widget B         173     2595
3  South  Gadget X         395    11850
4  South  Widget A         135     2700
5  South  Widget B          92     1380