Aggregate Functions

A season table of IPL batters has hundreds of numbers. Questions like "How many runs were scored in total?", "What is the average score?" or "Who scored the most?" all need the same thing: squeeze many values into one summary value. That is exactly what aggregate functions do.

Here is the small squad we will use in this whole chapter:

import pandas as pd

df = pd.DataFrame({
    "player":  ["Kohli", "Rohit", "Dhoni", "Gill", "Jadeja", "Bumrah", "Faf", "Surya"],
    "team":    ["RCB", "MI", "CSK", "GT", "CSK", "MI", "RCB", "MI"],
    "runs":    [741, 417, 161, 890, 280, 15, 730, 605],
    "wickets": [0, 0, 0, 0, 20, 20, 0, 0],
    "matches": [15, 14, 14, 17, 16, 13, 14, 16],
})
print(df)
print(df.dtypes)

   player team  runs  wickets  matches
0   Kohli  RCB   741        0       15
1   Rohit   MI   417        0       14
2   Dhoni  CSK   161        0       14
3    Gill   GT   890        0       17
4  Jadeja  CSK   280       20       16
5  Bumrah   MI    15       20       13
6     Faf  RCB   730        0       14
7   Surya   MI   605        0       16
player       str
team         str
runs       int64
wickets    int64
matches    int64
dtype: object

Note (pandas 3): text columns now show the dedicated str dtype. Old tutorials (pandas 1/2) show object here.

Definition

An aggregate function (also called a reduction) takes many values — a whole column or a whole row — and returns one single value that summarises them, such as their total, average, middle value, smallest, largest, count or spread.

• Many values → one value — a Series of 8 runs becomes one number (3839). On a DataFrame, each column is reduced separately, so you get one value per column (a Series).
• Summarises — the result describes the data (centre, size, spread) instead of listing it.
• Reduction — the dimension is reduced: a 1D Series becomes a scalar, a 2D DataFrame becomes a 1D Series.

The main aggregate functions

Tip: mean is pulled by extreme values (one 890 lifts the average a lot); median is not. When data has outliers, report the median too.

Syntax

Series.sum(skipna=True)
DataFrame.sum(axis=0, skipna=True, numeric_only=False)
DataFrame.mean(axis=0, skipna=True, numeric_only=False)   # same for median, min, max
DataFrame.count(axis=0, numeric_only=False)
DataFrame.std(axis=0, skipna=True, ddof=1, numeric_only=False)
DataFrame.agg(func)                                        # many aggregates at once

Example — one column

print(df["runs"].sum())
print(df["runs"].mean())
print(df["runs"].median())
print(df["runs"].min(), df["runs"].max())
print(df["runs"].count())
print(df["runs"].std())

3839
479.875
511.0
15 890
8
310.8285873504651

How it works: each call walks through all 8 values of the runs Series and returns one number. median sorts the values (15, 161, 280, 417, 605, 730, 741, 890) and averages the two middle ones: (417 + 605) / 2 = 511.

Example — axis=0 vs axis=1

stats = df[["runs", "wickets", "matches"]]
print(stats.sum())          # axis=0 (default): one total per COLUMN
print(stats.sum(axis=1))    # axis=1: one total per ROW

runs       3839
wickets      40
matches     119
dtype: int64
0    756
1    431
2    175
3    907
4    316
5     48
6    744
7    621
dtype: int64

axis=0  (down the columns)          axis=1  (across the rows)
        runs wickets matches                runs wickets matches
          |     |       |           row 0:  741 + 0 + 15  --> 756
          |     |       |           row 1:  417 + 0 + 14  --> 431
          v     v       v           ...
        3839   40     119

How it works: axis=0 collapses the rows, so the column labels survive as the result's index. axis=1 collapses the columns, so the row labels (0–7) survive. Remember it as: "the axis you name is the one that disappears." You can also write axis="index" and axis="columns" — e.g. stats.max(axis="columns") gives each player's largest number.

Example — text columns and numeric_only

print(df.mean(numeric_only=True))
print(df.sum())

runs       479.875
wickets      5.000
matches     14.875
dtype: float64
player     KohliRohitDhoniGillJadejaBumrahFafSurya
team                          RCBMICSKGTCSKMIRCBMI
runs                                          3839
wickets                                         40
matches                                        119
dtype: object

How it works: numeric_only=True skips player and team. Without it, sum() "adds" strings by joining them (rarely what you want), and df.mean() raises TypeError: Cannot perform reduction 'mean' with string dtype.

Example — missing values

import numpy as np
s = pd.Series([50, np.nan, 30, 80, np.nan])
print(s.count(), len(s), s.size)
print(s.sum(), s.mean())
print(s.sum(skipna=False))

3 5 5
160.0 53.333333333333336
nan

How it works: count() counts only the 3 real values, while len()/.size count all 5 slots. mean() divides by the count (160 / 3), not by 5. With skipna=False, a single NaN poisons the result.

Example — many aggregates at once with agg()

print(stats.agg(["min", "max", "mean"]))

         runs  wickets  matches
min    15.000      0.0   13.000
max   890.000     20.0   17.000
mean  479.875      5.0   14.875

agg() (alias aggregate()) runs every function in the list on every column and stacks the answers as rows. describe() is a ready-made version that gives count, mean, std, min, 25%, 50%, 75% and max in one go.

std: sample vs population

print(df["runs"].std(), df["runs"].std(ddof=0), np.std(df["runs"].to_numpy()))

310.8285873504651 290.75351997009426 290.75351997009426

Note: pandas std() divides by N − 1 (sample standard deviation, ddof=1), but NumPy's np.std() divides by N (ddof=0). That is why the same data gives two different answers. Pass ddof=0 to pandas to match NumPy.

Common mistakes

• ❌ df.mean() on a frame with text columns → TypeError. ✅ df.mean(numeric_only=True) or select the number columns first.
• ❌ Expecting count() to equal the number of rows. ✅ count() skips NaN; use len(df) for rows.
• ❌ Mixing up axes: df.sum(axis=1) when you wanted column totals. ✅ axis=0 (default) = per column, axis=1 = per row.
• ❌ Comparing pandas std() with NumPy np.std() and thinking one is wrong. ✅ They use different ddof defaults (1 vs 0).
• ❌ Using max() to find who scored most. ✅ idxmax() gives the row label: df.loc[df["runs"].idxmax(), "player"] → 'Gill'.

Interview tip

Q: What is the difference between df.sum(axis=0) and df.sum(axis=1)? A: axis=0 aggregates down the rows and returns one value per column (indexed by column names). axis=1 aggregates across the columns and returns one value per row (indexed by row labels). The named axis is the one that gets collapsed.