Visual walkthrough — Pandas — Series, DataFrame, indexing, groupby, merge, pivot

We will use one tiny table the entire way, so you can hold it in your head:

import pandas as pd
df = pd.DataFrame({
    "region": ["N", "N", "S", "S", "S"],
    "rev":    [10,  30,  5,  15,  10]})

Everything below is a picture of what happens to these five rows.

Step 1 — What a table even is (the raw material)

WHAT. Before we group anything, we must agree on what we are grouping. A DataFrame is a grid of cells. Every cell has two coordinates: a row label (the index) and a column label.

WHY. GroupBy works by reading one column (here region) and using its values as a rule to sort the row labels into buckets. So the two things we must be able to point at are: (1) the row labels 0,1,2,3,4, and (2) the column we will group by. If you cannot see those, nothing later makes sense.

PICTURE. Look at the figure. The far-left grey strip is the index (row labels). Each row is one observation — one measurement that belongs together. The orange column region is the key column; the blue column rev is the payload we will summarise.

Step 2 — Split: read the key, build the bucket map

WHAT. Pandas walks down the region column top to bottom and records which row labels carry each distinct key value. It never moves the data yet — it only builds a little lookup:

$\text{groups}=\{\underset{\text{key value}}{\underbrace{\text{"N"}}}:\underset{\text{row labels with that key}}{\underbrace{[0,1]}},\text{"S"}:[2,3,4]\}$

Read that symbol by symbol: the thing on the left of each colon is a value that appeared in the key column; the list on the right is every row label whose region equals that value.

WHY THIS TOOL — a hash map, not a sort. You might expect pandas to sort the rows to bring equal keys together. It does something cheaper: it uses a hash table. Each key value ("N", "S") is hashed to a slot, and the row labels pile up in that slot as they are seen. This answers the question "where does row r live?" in constant time, and it is why groupby scales to millions of rows. (This is the concrete meaning of split.)

PICTURE. The arrows show each row label flying from the table into its bucket. Rows 0 and 1 both say "N", so both arrows land in the N-bucket; rows 2, 3, 4 say "S" and land in the S-bucket. Nothing is added yet — we have only sorted addresses, not values.

Step 3 — Pick the payload inside each bucket

WHAT. We wrote .groupby("region")["rev"]. The ["rev"] says: inside every bucket, keep only the rev values. So the address lists from Step 2 get turned into value lists:

$\text{"N"}\to [10,30],\text{"S"}\to [5,15,10]$

Term by term: 10 and 30 are the rev cells at row labels 0 and 1 (the N-bucket's addresses); 5, 15, 10 are the rev cells at labels 2, 3, 4.

WHY. A reducer like mean needs numbers, not row addresses. This step is where the addresses [0,1] are cashed in for the actual values [10, 30]. Choosing ["rev"] here (instead of grouping the whole frame) is just a promise that we only care about that one payload column — it keeps the picture small.

PICTURE. Same two buckets as Step 2, but now each holds a short vertical stack of blue numbers — the raw revenues that belong to that region.

Step 4 — Apply: collapse each bucket to one number

WHAT. Now the reducer runs, once per bucket, independently:

$\text{mean}(N)=\frac{10+30}{2}=20.0,\text{mean}(S)=\frac{5+15+10}{3}=10.0$

Reading the mean formula: the numerator is the sum of every value in that one bucket; the denominator is how many values are in that same bucket (2 for N, 3 for S). Nothing from the N-bucket ever mixes with the S-bucket — that isolation is the whole point of splitting first.

WHY THIS TOOL — mean as an example of a "reducer". A reducer answers "summarise this list as a single scalar." sum, count, max, mean are all reducers; they differ only in the arithmetic. We chose mean because the parent example asked "average revenue per region". Swap in .sum() and only the arrow's arithmetic in the picture changes — the machine is identical.

PICTURE. Each bucket's stack of numbers funnels through a labelled "mean" gate and comes out as a single number on the right.

Step 5 — Combine: glue the answers into a new labelled Series

WHAT. The two scalars from Step 4 are stacked into a fresh Series whose index is the group keys:

region
N    20.0
S    10.0
Name: rev, dtype: float64

WHY. The original index 0,1,2,3,4 is gone — it described individual observations, and we no longer have individual observations, we have per-region summaries. The natural new label for each row is the key that produced it. That is why after a groupby your index changes to the grouping column: it is the only label that still means anything. (This new frame is now tidy: one row per region, one column of the summarised quantity.)

PICTURE. The two answer-numbers slide into a two-row table; the grey index strip now reads N, S instead of 0..4.

Step 6 — Edge case: an empty group and a missing value

WHAT. Two degenerate things can happen, and the machine must survive both.

1. A key that has zero rows (e.g. you .groupby() a categorical that lists region "E" but no row uses it). That bucket is empty: mean([]). Pandas returns NaN, not a crash.
2. A NaN inside the payload. Say row 3's rev were missing. Then the S-bucket is [5, NaN, 10]. By default mean skips NaN: it divides by the count of present values, so $\frac{5+10}{2}=7.5$, not $\frac{5+10}{3}$.

WHY. These are the two "no data" corners of every real dataset (see Missing Data / NaN). GroupBy handles them by the rule "reduce what you have; if you have nothing, the answer is NaN". Knowing this stops you from mistaking a NaN cell for a bug — it is the machine telling you a bucket was empty or a value was absent.

PICTURE. Left panel: an empty bucket → the mean gate emits NaN. Right panel: the S-bucket with a greyed-out NaN value being skipped before the division.

The one-picture summary

Here is the whole pipeline on one canvas: raw table → hash into buckets (split) → reduce each bucket (apply) → restack under key labels (combine). Trace the five original rows all the way to the two final numbers.