All lessons

Module 8: Analyses employers ask for · Lesson 8.8 · about 15 min

Fast pandas: vectorise, avoid loops

Make pandas code fast: whole-column (vectorised) operations instead of loops and apply, np.where and np.select for conditions, categories for repeated text, reading only the columns you need, and timing code to see the difference.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 15 minutes · uses Modules 2 and 3. You’ll be able to spot slow pandas code and rewrite it.

pandas is fast when it works on whole columns at once. A Python loop over rows does the same job one row at a time, often 100 times slower.

In everyday terms: a supermarket checkout: scanning a full basket at once versus walking back to the shelf for each item.

1. Loop, apply, vectorised

Pythonorders.csv

Same result, very different speed: the vectorised line is usually hundreds of times faster. Times here are in your browser; on a laptop all three are faster, but the order is the same.

2. Conditions without loops

Pythonorders.csv

Rules of thumb:

  • Arithmetic and comparisons on columns: always vectorised.
  • if per row: use np.where or np.select.
  • A lookup: map or merge, never a loop.
  • Text: the .str methods.
  • Only when nothing else fits: apply, and preferably on a groupby, not on every row.

3. Memory: categories and fewer columns

Pythonorders.csv

Read only the columns you need (usecols), and store repeated text as category. On a file with millions of rows this is the difference between “fits in memory” and “crashes”.

Vectorised
df.qty * df.price: one operation on the whole column, in compiled code.
apply(axis=1)
A Python function per row: convenient but slow.
np.where / np.select
If-then rules on whole columns.
astype('category')
Stores repeated text once; less memory, faster groupby.
Physical meaning
A loop pays Python's overhead on every row; a column operation pays it once and does the rest in C. On a million rows that is the difference between a blink and a coffee break.

“apply is vectorised.” It still calls your Python function once per row. Use it when no column operation exists, not by default.

Watch it happen, one step at a time:

The task. 10 % off when qty is 3 or more.
Loop. for i, row in df.iterrows(): ... one row at a time.
Vectorised. df['pay'] = np.where(df.qty >= 3, df.price * 0.9, df.price)
Same answer.
qtypricepay
11010.0
3109.0
52018.0
Timing on 1 million rows. Loop: seconds to minutes. np.where: milliseconds.

4. Your turn

Your turnorders.csv
How it works

Principle: a pandas column is a NumPy array: numbers packed side by side in memory. A vectorised operation hands the whole array to compiled C code, which runs through it in one tight loop. A Python loop instead does a slow lookup, type check and object creation for every row:

Tloop≈n⋅(cpython+cwork),Tvector≈csetup+n⋅cwork,cpython≫cwork

When pandas is too slow (tens of millions of rows): Polars, DuckDB (SQL on files) or Spark (Data at Scale course) use the same ideas across many cores or machines.

5. Practice

  1. Replace df.apply(lambda r: r.a + r.b, axis=1).
  2. A loop builds a list of strings with if/elif/else. What do you use instead?
  3. Why does usecols speed up read_csv?
Answers
  1. df['a'] + df['b'].
  2. np.select([cond1, cond2], [val1, val2], default=val3).
  3. pandas skips parsing the other columns and never stores them.

Try it on your own data: big-O plotter shows how loop costs grow with the number of rows.

New words: vectorised, iterrows, apply, np.where, category dtype, usecols.

  • Prefer column operations over loops and apply.
  • np.where for one rule, np.select for several.
  • category dtype for repeated text.
  • Read only the columns you need (usecols=).

Check yourself

1Why is df.qty * df.unit_price faster than a for loop over rows?
2What is usually the fastest way to add a column from a condition?
3When does the category dtype save memory?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.