Skip to content
Tutorials Center
My learning

Topics

Lesson 50 of 64 ยท Performance and Scale

Writing Fast pandas Code

Make pandas code faster. Replace loops and iterrows with vectorised operations, avoid repeated concat, use query and eval, time code with perf_counter.

pandas is fast when you let it work on whole columns at once, and slow when you hand it one row at a time. The difference is not small: the same calculation can take 50 milliseconds as a column operation and 10 seconds as a Python loop. Most of "making pandas faster" is learning which style of code stays inside the fast, compiled part of the library.

In this lesson you will time loops against vectorised code, avoid the classic repeated-concat trap, use query() and eval(), drop to NumPy when it helps, and build a simple habit of measuring before changing anything. The timing helper we write prints comparisons, not raw milliseconds, because timings differ from machine to machine and between your computer and the browser.

Why vectorised code is fast

A pandas column is a NumPy array: one block of memory holding values of the same type. An operation such as df["price"] * df["qty"] runs as a single compiled loop over that block. A Python for loop has to do the same job one value at a time, creating Python objects, checking types and calling functions on every step.

flowchart LR
    subgraph Slow["Row loop"]
        A1["Python for loop"] --> A2["build one row object"]
        A2 --> A3["Python arithmetic"]
        A3 --> A1
    end
    subgraph Fast["Vectorised"]
        B1["column.__mul__(other)"] --> B2["one compiled loop<br/>over a NumPy array"]
    end

The slow path crosses between Python and the data on every row; the fast path crosses once for the whole column.

Timing code

Before comparing, we need a stopwatch. time.perf_counter() returns a high-resolution clock. Timing one run is noisy, so take the best of a few runs:

Python
import time

def best_of(function, repeat=3):
    best = float("inf")
    for _ in range(repeat):
        start = time.perf_counter()
        function()
        best = min(best, time.perf_counter() - start)
    return best

In a notebook %timeit does this for you. We will use best_of in the examples and print only which version is faster and by roughly how much.

Loops, iterrows, apply and vectorised

Here are four ways to compute a total price with a discount. Three of them look reasonable. One is much better.

Python Four ways to compute the same column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

iterrows() builds a full Series object for every row, which is the slowest option. apply(..., axis=1) also calls a Python function per row. The vectorised version uses np.where() for the condition and two column multiplications. Whenever you write for over rows, ask whether a column operation, np.where, np.select, map or groupby can do it. Vectorised Operations shows these tools in detail.

If a row-wise function is truly unavoidable, itertuples() is much faster than iterrows(), and a list comprehension over zip(df["a"], df["b"]) is faster still. Try the vectorised route first.

Do not grow a DataFrame in a loop

Every pd.concat([df, new_row]) creates a brand new DataFrame and copies all the old rows. Doing that inside a loop copies the data again and again, so the work grows with the square of the number of pieces. Collect the pieces in a Python list and call concat once.

Python Repeated concat versus one concat

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same advice applies to building rows by hand: append plain dictionaries or tuples to a list and create the DataFrame at the end with pd.DataFrame(rows). There is more on combining frames in Concatenating DataFrames.

query and eval

DataFrame.query() filters with a string expression, and DataFrame.eval() computes columns from one. They are often shorter to read, particularly with long conditions:

Python query() and eval()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

@min_price reads a Python variable from the surrounding code. Column names must be valid identifiers; use backticks (`unit price`) for names with spaces. The main benefit of query() and eval() is readability. Speed gains appear only on very large frames when the optional numexpr package is installed, so do not choose them for speed alone.

Using NumPy underneath

Sometimes the quickest route is to leave pandas for the hot spot. df["col"].to_numpy() gives the array without copying (when the dtype allows), and NumPy functions have no index alignment to pay for. Convert, compute, and wrap the result back:

Python Computing on the array directly

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The index is the price you pay for convenience in pandas: every operation between two Series aligns their labels first. If your two columns already line up, working on the arrays skips that step. Do this only in measured bottlenecks; otherwise plain pandas is easier to read.

Method chaining and temporary frames

A long pipeline written as one chain is easy to read and avoids many intermediate variables:

Python
summary = (
    orders
    .query("status == 'paid'")
    .assign(total=lambda d: d["price"] * d["qty"])
    .groupby("region", as_index=False)["total"].sum()
    .sort_values("total", ascending=False)
)

Chaining does not make code faster by itself, but with copy-on-write in pandas 3 it does not make it slower either: each step shares memory with the previous result until something is changed. Filter as early as possible so later steps process fewer rows, and select only the columns you need before expensive steps such as groupby or merge.

A profiling mindset

  1. Make it correct, then measure. Time the whole program, then the suspicious piece. Guessing is often wrong.
  2. Change one thing at a time and compare against the previous timing and result.
  3. Check the answer every time. A faster function that returns different numbers is a bug.
  4. Mind the size. An approach that is fine for 10,000 rows can be unusable at 10 million; test with realistic data.
  5. Stop when it is fast enough. Readable code is worth keeping.

On your own machine, %timeit and %prun in notebooks, or the cProfile module in scripts, show where time goes.

pyarrow-backed dtypes (read-only)

pandas can store columns in Apache Arrow memory instead of NumPy. With the pyarrow package installed you can ask for it per column or when reading:

Python
# needs: pip install pyarrow   (not available in the browser editors)
df = pd.read_csv("big.csv", dtype_backend="pyarrow")
df["name"] = df["name"].astype("string[pyarrow]")
df["qty"] = df["qty"].astype("int64[pyarrow]")

Arrow columns handle missing values natively, store text compactly and speed up many string operations and file reads. They are not always faster, and a few features behave differently, so benchmark with your own data. The editors in this course run without pyarrow, so these lines are not runnable here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is a vectorised column operation faster than a Python loop over rows?

    A column operation stays inside compiled code for the whole array, while a Python loop creates objects and runs interpreter steps for every row.
  2. 2. What is wrong with calling pd.concat([result, new_piece]) inside a loop 10,000 times?

    Every concat builds a new DataFrame. Collect the pieces in a list and concatenate once at the end.
  3. 3. Which statement about query() is correct?

    query() returns a filtered copy and reads outer variables with @. It mainly improves readability, not speed.
  4. 4. Which should you do before optimising a slow pandas script?

    Measuring finds the real bottleneck, and a reference result lets you confirm that a faster version still gives the same answer.
  5. 5. Which is usually the slowest way to compute a new column from other columns?

    iterrows builds a Series object for every row, which makes it the slowest of the common approaches.

Summary

  • Vectorised operations run in compiled code; row loops, iterrows() and apply(axis=1) run in Python and are much slower.
  • Collect pieces in a list and call pd.concat() once; never grow a DataFrame row by row.
  • query() and eval() make long conditions and formulas easier to read; speed gains need numexpr and large frames.
  • Use to_numpy() for a measured hot spot when index alignment is not needed.
  • Chain steps, filter early and select only needed columns before heavy operations.
  • Time with time.perf_counter() (best of a few runs), change one thing at a time and verify results.
  • pyarrow-backed dtypes can help with text and file reads but need the pyarrow package.

Next: When Data Gets Big