My sales report loop takes twenty minutes

See the problem

Every Monday you total last week’s sales by region, counting only orders of £50 or more. You load the CSV with pandas and loop over the rows, adding each big order to its region.

With 2,000 test rows it takes a second. The real week has 3 million rows, and the script runs for twenty minutes. The data fits in memory easily, so what is slow?

Define the task

• Input: a table of orders, with region and amount columns, and a minimum amount.
• Output: the total of orders at or above the minimum, for each region, biggest total first.
• Rules: regions with no big orders don’t appear.
• The hard part: doing it in whole-column steps, so the work happens in pandas’ fast C code, not in a Python loop.

Example: the orders are north 120, south 30, north 80, east 300 and south 95. With a minimum of 50, the result is east 300, north 200, south 95.

Pause and think

⏸ Pause and think

This is filter, then group, then sum, then sort. Which of those can pandas do to a whole column at once?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

iterrows() builds a new Series object for every row. That is the slow part.

Hint 2 of 3

orders["amount"] >= 50 is a column of True and False. Indexing orders with that mask keeps the True rows.

Hint 3 of 3

.groupby("region")["amount"].sum() totals every region in one pass.

Understand the idea

A DataFrame is a table stored column by column. Each column is a Series: one type of value, packed tightly in memory, like a NumPy array. Operations on a whole column run in C, over millions of values per second.

Step	pandas	What it does
Load	pd.read_csv("orders.csv")	Reads the file into a DataFrame.
Filter	df[df["amount"] >= 50]	The comparison gives a True/False column, a mask; indexing with it keeps the True rows.
Combine masks	df[(df.a > 1) & (df.b == "x")]	&, |, ~, with brackets. Not and or or.
Group	df.groupby("region")["amount"].sum()	Sorts rows into groups in one pass, then totals each.
Join	orders.merge(customers, on="cid")	Like SQL’s JOIN.
Missing values	NaN; .isna(), .fillna(0)	Sums and means skip NaN by default.

A simple rule: if you’re writing a for loop over rows, there’s almost always a column operation instead.

★Key insight

pandas is fast when you work on whole columns: a mask to filter, groupby to group, merge to join. A Python loop over rows, with iterrows or apply, throws that speed away.

Climb the ladder

Here n is the number of rows and r the number of regions. Constant factors matter here too: pandas’ column steps run in C, and a Python loop is often a hundred times slower per row.

Okay

iterrows

The idea: Loop over the rows, and add each big order to a dict of totals.

It reads like plain Python, and the answer is right.

import pandas as pd


def region_totals(orders: pd.DataFrame, minimum: float) -> pd.Series:
    totals: dict[str, float] = {}
    for _, row in orders.iterrows():  # builds a Series for every row: very slow
        if row["amount"] >= minimum:
            totals[row["region"]] = totals.get(row["region"], 0) + row["amount"]
    return pd.Series(totals).sort_values(ascending=False)

Time

O(n), slowly

Space

O(r)

Why

One pass, but iterrows builds a Python Series object for every row.

Where it breaks: every row becomes a new Series, and every step is a Python step. 3 million rows takes minutes.

Better

A mask, then one scan per region

The idea: Filter with a mask, then for each region, sum the rows that match it.

The waste in Okay: a Python step for every row.

The filter is now one fast column step.

Highlighted lines are new in this rung.

import pandas as pd


def region_totals(orders: pd.DataFrame, minimum: float) -> pd.Series:
    big = orders[orders["amount"] >= minimum]  # one fast filter for all rows
    totals = {}
    for region in big["region"].unique():  # but one full scan per region
        totals[region] = big.loc[big["region"] == region, "amount"].sum()
    return pd.Series(totals).sort_values(ascending=False)

Time

O(n × r)

Space

O(n)

Why

Each of the r regions compares all n rows again.

What changed from the previous rung

import pandas as pd


def region_totals(orders: pd.DataFrame, minimum: float) -> pd.Series:
    totals: dict[str, float] = {}
    for _, row in orders.iterrows():  # builds a Series for every row: very slow
        if row["amount"] >= minimum:
            totals[row["region"]] = totals.get(row["region"], 0) + row["amount"]
    big = orders[orders["amount"] >= minimum]  # one fast filter for all rows
    totals = {}
    for region in big["region"].unique():  # but one full scan per region
        totals[region] = big.loc[big["region"] == region, "amount"].sum()
    return pd.Series(totals).sort_values(ascending=False)

Where it breaks: each region scans every row again. With 5,000 regions, that’s 5,000 passes over the table.

Best

groupby

The idea: Mask, groupby, sum, sort_values: four column steps.

The waste in Better: it does a group-by by hand, once per group.

groupby puts every row in its group in one pass, in C.

Highlighted lines are new in this rung.

import pandas as pd


def region_totals(orders: pd.DataFrame, minimum: float) -> pd.Series:
    return (
        orders[orders["amount"] >= minimum]  # filter: a True/False mask
        .groupby("region")["amount"]  # group: one pass, rows sorted into regions
        .sum()
        .sort_values(ascending=False)
    )

Time

O(n + r log r)

Space

O(n)

Why

One pass to group and sum, then a sort of the r totals.

What changed from the previous rung

import pandas as pd


def region_totals(orders: pd.DataFrame, minimum: float) -> pd.Series:
    big = orders[orders["amount"] >= minimum]  # one fast filter for all rows
    totals = {}
    for region in big["region"].unique():  # but one full scan per region
        totals[region] = big.loc[big["region"] == region, "amount"].sum()
    return pd.Series(totals).sort_values(ascending=False)
    return (
        orders[orders["amount"] >= minimum]  # filter: a True/False mask
        .groupby("region")["amount"]  # group: one pass, rows sorted into regions
        .sum()
        .sort_values(ascending=False)
    )

When Best isn’t best: if the data doesn’t fit in memory, read it in chunks with read_csv(chunksize=…), or use a tool built for it, like DuckDB or Polars.

Write it yourself

The browser editor runs plain Python, without pandas. So here you write what groupby does inside: filter and group in one pass. rows is a list of dicts, one per order. Return (region, total) pairs, biggest first. The last test has 200,000 rows and 5,000 regions.

Totals per region, in one pass

Ctrl + Enter runs

from collections import defaultdict def region_totals(rows, minimum): return []

Run testsVisualizeReset codeLoads about 6 MB the first time. Best on Wi-Fi.

1. ○Not run yet: the five orders above, minimum 50 → [['east', 300], ['north', 200], ['south', 95]]
2. ○Not run yet: no order is big enough → []
3. ○Not run yet: 200,000 orders in 5,000 regions: [how many regions, the top one] → [5000, ['r4', 108]]

Try your own input

Arguments, separated by commas as in Python

region_totals()Run onceVisualize

Show an answer

totals.py

from collections import defaultdict


def region_totals(rows: list[dict], minimum: float) -> list[tuple[str, float]]:
    totals: defaultdict[str, float] = defaultdict(float)
    for row in rows:  # filter and group in one pass, like groupby does
        if row["amount"] >= minimum:
            totals[row["region"]] += row["amount"]
    return sorted(totals.items(), key=lambda pair: pair[1], reverse=True)

One pass, with a dict of totals: that is what groupby(...).sum() does, in C and on whole columns.

Compare

Way	Time	Speed per row	Use it when
Okay: iterrows	O(n)	Python, plus a Series per row	Never on big data
Better: one scan per group	O(n × r)	C, but r times	A handful of groups, once
Best: groupby	O(n + r log r)	C, once	Always

Common mistakes and edge cases

!and between masks

df[(df.a > 1) and (df.b < 5)] raises “The truth value of a Series is ambiguous”. Use & and |, and put each comparison in brackets: df[(df.a > 1) & (df.b < 5)].

• Chained assignment: df[df.a > 1]["b"] = 0 may change a copy, not df. Write df.loc[df.a > 1, "b"] = 0.
• apply is a loop too: df["x"].apply(f) calls a Python function per row. Look for a column method first, like .str.lower() or .dt.year.
• merge multiplies rows when a key repeats on both sides. Check with validate="many_to_one".
• Missing values are skipped: mean() ignores NaN, so a column that’s half missing still gives a mean. Count them with .isna().sum().
• Read types on purpose: read_csv(dtype={"zip": str}) keeps leading zeros; parse_dates=["day"] gives real dates.

Say it in the interview

Questions to ask first

• How big is the data, and does it fit in memory?
• What should missing amounts count as: zero, or skipped?

A 60-second walkthrough

“A DataFrame stores each column as a typed array, so whole-column operations run in C. I’d filter with a boolean mask, df[df['amount'] >= 50], then groupby('region')['amount'].sum(), then sort_values(ascending=False). That’s one pass to group plus a sort of the groups. Looping with iterrows builds a Series per row and is often a hundred times slower. Summing each region on its own rescans the table for every region. If the data didn’t fit in memory, I’d read it in chunks and add up partial totals, or use DuckDB.”

Likely follow-ups

• loc or iloc? loc picks by labels and masks; iloc by position.
• What does merge do with keys that don’t match? The default, how="inner", drops them; how="left" keeps every left row, with NaN for the missing columns.
• How do you add a column? df["total"] = df["qty"] * df["price"]: one column step, no loop.

Practice

Three problems, from easy to hard. The editors run plain Python, so each one has you build what a pandas step does inside. The answers show the pandas line too.

1. Average per group, skipping gaps (easy)

Write mean_by(rows, key, col), the mean of col for each value of key. Missing values are None: skip them, as pandas skips NaN. A group with no values at all gets None.

Hint 1 of 3

sum() of a list with None in it raises TypeError.

Hint 2 of 3

Treating None as 0 pulls the mean down.

Hint 3 of 3

Only append values that are not None. An empty group gives None.

Average per group, skipping gaps

Ctrl + Enter runs

def mean_by(rows, key, col): return {}

Run testsVisualizeReset codeLoads about 6 MB the first time. Best on Wi-Fi.

1. ○Not run yet: mean_by([{'t': 'a', 's': 2}, {'t': 'a', 's': 4}], 't', 's') → {'a': 3}
2. ○Not run yet: team a: 10, None, 20; team b: None → {'a': 15, 'b': None}
3. ○Not run yet: Extra test

Extra tests check tricky cases. You see their input only if they fail.

Try your own input

Arguments, separated by commas as in Python

mean_by()Run onceVisualize

Show the Okay, Better and Best answers

Okay

sum and len

The idea: Group the values, then sum(v) / len(v).

Right when nothing is missing.

def mean_by(rows: list[dict], key: str, col: str) -> dict:
    groups: dict = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row[col])
    return {k: sum(v) / len(v) for k, v in groups.items()}  # sum() fails on None

Time

O(n)

Space

O(n)

Why

Each value is grouped once and added once.

Where it breaks: one None makes sum() raise TypeError.

Better

None becomes 0

The idea: Store the value, or 0 when it is None.

The waste in Okay: one gap stops the whole report.

Highlighted lines are new in this rung.

def mean_by(rows: list[dict], key: str, col: str) -> dict:
    groups: dict = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row[col] or 0)  # missing becomes 0
    return {k: sum(v) / len(v) for k, v in groups.items()}

Time

O(n)

Space

O(n)

Why

Each value once.

What changed from the previous rung

def mean_by(rows: list[dict], key: str, col: str) -> dict:
    groups: dict = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row[col])
    return {k: sum(v) / len(v) for k, v in groups.items()}  # sum() fails on None
        groups.setdefault(row[key], []).append(row[col] or 0)  # missing becomes 0
    return {k: sum(v) / len(v) for k, v in groups.items()}

Where it breaks: a missing score counts as 0, so team a’s mean is 10, not 15, and team b’s is 0 when it should have none.

Best

Skip the gaps

The idea: Only keep values that are not None; a group with none left gets None.

The waste in Better: it invents data.

In pandas: df.groupby("team")["score"].mean(), which skips NaN the same way.

Highlighted lines are new in this rung.

def mean_by(rows: list[dict], key: str, col: str) -> dict:
    groups: dict = {}
    for row in rows:
        values = groups.setdefault(row[key], [])
        if row[col] is not None:  # skip missing values, like pandas skips NaN
            values.append(row[col])
    # A group with no values at all has no mean: None, like pandas' NaN.
    return {k: sum(v) / len(v) if v else None for k, v in groups.items()}

Time

O(n)

Space

O(n)

Why

Each value once.

What changed from the previous rung

def mean_by(rows: list[dict], key: str, col: str) -> dict:
    groups: dict = {}
    for row in rows:
        groups.setdefault(row[key], []).append(row[col] or 0)  # missing becomes 0
    return {k: sum(v) / len(v) for k, v in groups.items()}
        values = groups.setdefault(row[key], [])
        if row[col] is not None:  # skip missing values, like pandas skips NaN
            values.append(row[col])
    # A group with no values at all has no mean: None, like pandas' NaN.
    return {k: sum(v) / len(v) if v else None for k, v in groups.items()}

When Best isn’t best: sometimes missing does mean 0, like a day with no sales. Then fill on purpose: .fillna(0).

2. Join two tables (medium)

Write merge(left, right, on), an inner join of two lists of dicts on the column on, like left.merge(right, on=on). Each matching pair gives one combined row, in left order. A key that repeats in right gives one row per match. It must be fast on 20,000 rows each.

Hint 1 of 3

Checking every pair is len(left) × len(right) steps.

Hint 2 of 3

Index right by key in a dict, then look each left row up.

Hint 3 of 3

A dict of key → row keeps only the last row for a repeated key. Store a list of rows.

Join two tables

Ctrl + Enter runs

def merge(left, right, on): return []

Run testsVisualizeReset codeLoads about 6 MB the first time. Best on Wi-Fi.

1. ○Not run yet: orders 1, 2, 3 with customers 7, 8 and a second 8 → [{'order': 1, 'cid': 7, 'name': 'ana'}, {'order': 2, 'cid': 8, 'name': 'bo'}, {'order': 2, 'cid': 8, 'name': 'bo2'}]
2. ○Not run yet: no keys match → []
3. ○Not run yet: 20,000 rows each: how many joined rows? → 20000

Try your own input

Arguments, separated by commas as in Python

merge()Run onceVisualize

Show the Okay, Better and Best answers

Okay

Every pair

The idea: Two loops: combine each left and right row whose keys match.

Right, including repeated keys.

def merge(left: list[dict], right: list[dict], on: str) -> list[dict]:
    out = []
    for a in left:
        for b in right:  # every pair: len(left) x len(right) checks
            if a[on] == b[on]:
                out.append({**a, **b})
    return out

Time

O(n × m)

Space

O(k)

Why

Checks all n × m pairs to find the k matches.

Where it breaks: 20,000 × 20,000 is 400 million checks.

Better

A dict of rows

The idea: index = {row[on]: row}, then look up each left row.

The waste in Okay: searching instead of looking up.

Highlighted lines are new in this rung.

def merge(left: list[dict], right: list[dict], on: str) -> list[dict]:
    index = {b[on]: b for b in right}  # one row per key: a repeated key overwrites
    return [{**a, **index[a[on]]} for a in left if a[on] in index]

Time

O(n + m)

Space

O(m)

Why

One pass to index, one to look up.

What changed from the previous rung

def merge(left: list[dict], right: list[dict], on: str) -> list[dict]:
    out = []
    for a in left:
        for b in right:  # every pair: len(left) x len(right) checks
            if a[on] == b[on]:
                out.append({**a, **b})
    return out
    index = {b[on]: b for b in right}  # one row per key: a repeated key overwrites
    return [{**a, **index[a[on]]} for a in left if a[on] in index]

Where it breaks: a repeated key overwrites the row before it, so customer “bo” disappears and order 2 is joined once, not twice.

Best

A dict of lists

The idea: Index every right row under its key, then join each left row with each of its matches.

The waste in Better: it trusts keys to be unique.

This is a hash join, which is how merge and SQL databases usually join. In pandas: left.merge(right, on="cid").

Highlighted lines are new in this rung.

def merge(left: list[dict], right: list[dict], on: str) -> list[dict]:
    index: dict = {}
    for b in right:
        index.setdefault(b[on], []).append(b)  # keep every row for each key
    return [{**a, **b} for a in left for b in index.get(a[on], [])]

Time

O(n + m + k)

Space

O(m + k)

Why

Index, look up, and write the k joined rows.

What changed from the previous rung

def merge(left: list[dict], right: list[dict], on: str) -> list[dict]:
    index = {b[on]: b for b in right}  # one row per key: a repeated key overwrites
    return [{**a, **index[a[on]]} for a in left if a[on] in index]
    index: dict = {}
    for b in right:
        index.setdefault(b[on], []).append(b)  # keep every row for each key
    return [{**a, **b} for a in left for b in index.get(a[on], [])]

When Best isn’t best: if both tables are already sorted by the key, walking them side by side joins them in O(n + m) without the dict.

3. A rolling average (hard)

Write rolling_mean(values, window), the mean of each value and the window - 1 before it, like Series.rolling(window).mean(). The first window - 1 places have no full window: give None there, so the result is as long as the input. It must be fast with 200,000 values and a window of 10,000.

Hint 1 of 3

Summing each window from scratch is window steps per value.

Hint 2 of 3

Keep a running total: add the value coming in, subtract the one leaving.

Hint 3 of 3

Append None until the first full window, so the lengths match.

A rolling average

Ctrl + Enter runs

def rolling_mean(values, window): return []

Run testsVisualizeReset codeLoads about 6 MB the first time. Best on Wi-Fi.

1. ○Not run yet: rolling_mean([1, 2, 3, 4], 2) → [None, 1.5, 2.5, 3.5]
2. ○Not run yet: rolling_mean([5], 3) → [None]
3. ○Not run yet: 200,000 values, window 10,000: [length, place 9,998, the last] → [200000, None, 194999.5]

Try your own input

Arguments, separated by commas as in Python

rolling_mean()Run onceVisualize

Show the Okay, Better and Best answers

Okay

Sum each window

The idea: For each place, sum the last window values.

Right, and the Nones line up with pandas.

def rolling_mean(values: list[float], window: int) -> list[float | None]:
    out: list[float | None] = []
    for i in range(len(values)):
        if i + 1 < window:
            out.append(None)
        else:
            out.append(sum(values[i + 1 - window : i + 1]) / window)  # re-adds w values
    return out

Time

O(n × w)

Space

O(n)

Why

Each of the n places adds up w values.

Where it breaks: 200,000 places × 10,000 values is 2 billion additions.

Better

A running total

The idea: Add the new value, subtract the one leaving, and append once the window is full.

The waste in Okay: each window re-adds w − 1 values the last one already added.

Highlighted lines are new in this rung.

def rolling_mean(values: list[float], window: int) -> list[float]:
    out = []
    total = 0.0
    for i, v in enumerate(values):
        total += v
        if i >= window:
            total -= values[i - window]
        if i + 1 >= window:
            out.append(total / window)  # the first window - 1 days are left out
    return out

Time

O(n)

Space

O(n)

Why

Two steps per value.

What changed from the previous rung

def rolling_mean(values: list[float], window: int) -> list[float | None]:
    out: list[float | None] = []
    for i in range(len(values)):
        if i + 1 < window:
            out.append(None)
        else:
            out.append(sum(values[i + 1 - window : i + 1]) / window)  # re-adds w values
def rolling_mean(values: list[float], window: int) -> list[float]:
    out = []
    total = 0.0
    for i, v in enumerate(values):
        total += v
        if i >= window:
            total -= values[i - window]
        if i + 1 >= window:
            out.append(total / window)  # the first window - 1 days are left out
    return out

Where it breaks: it skips the first window - 1 places, so the result is shorter than the input and no longer lines up with the days.

Best

Running total, padded

The idea: The same running total, with None until the first full window.

The waste in Better: a result that doesn’t line up with its input.

In pandas: s.rolling(10_000).mean().

Highlighted lines are new in this rung.

def rolling_mean(values: list[float], window: int) -> list[float | None]:
    out: list[float | None] = []
    total = 0.0
    for i, v in enumerate(values):
        total += v  # add the day coming in
        if i >= window:
            total -= values[i - window]  # and drop the day going out
        out.append(total / window if i + 1 >= window else None)  # None, like NaN
    return out

Time

O(n)

Space

O(n)

Why

Two steps per value.

What changed from the previous rung

def rolling_mean(values: list[float], window: int) -> list[float]:
    out = []
def rolling_mean(values: list[float], window: int) -> list[float | None]:
    out: list[float | None] = []
    total = 0.0
    for i, v in enumerate(values):
        total += v
        total += v  # add the day coming in
        if i >= window:
            total -= values[i - window]
        if i + 1 >= window:
            out.append(total / window)  # the first window - 1 days are left out
            total -= values[i - window]  # and drop the day going out
        out.append(total / window if i + 1 >= window else None)  # None, like NaN
    return out

When Best isn’t best: a running total of floats slowly gathers rounding error over millions of steps. pandas handles that; for long runs by hand, re-add the window now and then.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Spot the bugThis line raises “The truth value of a Series is ambiguous”. What fixes it?

   big_north = df[(df.amount > 50) and (df.region == "north")]

   Drop the bracketsUse & instead of andUse df.amount.gt(50) onlyAdd .all()

   Check answer
2. Multiple choiceWhat is the fast way to total amount per region?

   Loop with iterrows and add to a dictFor each region, df[df.region == r].amount.sum()df.groupby("region")["amount"].sum()df.apply(sum, axis=1)

   Check answer
3. Predict the outputHow many rows does the merge give?

   orders = pd.DataFrame({"cid": [1, 2]})
   people = pd.DataFrame({"cid": [1, 1, 3], "name": ["a", "b", "c"]})
   print(len(orders.merge(people, on="cid")))

   1234

   Check answer
4. Multiple choiceWhat does df["score"].mean() do with NaN values?

   Counts them as 0Skips themReturns NaNRaises an error

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1Why is iterrows slow?Show answer
• Card 2How do you keep rows where amount is at least 50?Show answer
• Card 3How do you change some rows of a column safely?Show answer
• Card 4What does merge do when a key repeats on one side?Show answer

Add 4 cards to my review deck

Cheat sheet

• df = pd.read_csv(path); df.head(), df.info().
• Filter: df[(df.a > 1) & (df.b == "x")].
• Group: df.groupby("k")["v"].sum(), .mean(), .agg(["sum", "count"]).
• Join: a.merge(b, on="id", how="left").
• Change rows: df.loc[mask, "col"] = value.
• No iterrows; no apply when a column method exists.

Go deeper

• 10 minutes to pandas.
• Group by: split-apply-combine.
• Merge, join and concatenate.
• Before this: Comprehensions. Next: the Advanced level.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Load a CSV into a DataFrame and pick rows with a True/False mask
• Total or average per group with groupby, in one pass
• Join two tables with merge, and say what happens to repeated keys
• Say why iterrows and apply are slow, and what to use instead

✓Mark as completeI already know this

Status: Not started