Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 64 ยท Transforming Data

Vectorised Operations

Learn vectorised pandas operations: column arithmetic, np.where and np.select, clip, round, cumsum, diff, pct_change, shift, and why Python loops are slow.

A vectorised operation works on a whole column at once. Instead of writing a loop that visits every row, you write df["price"] * df["qty"] and pandas applies the multiplication to every value inside fast compiled code. The result is shorter code that is usually dozens of times faster than the same loop in plain Python.

This lesson is the toolbox you will reach for in almost every analysis: arithmetic between columns, choosing values by condition, limiting ranges, running totals, and comparing each row with the one before it. At the end you will time a loop against a vectorised expression and see the gap for yourself. If you have not yet added columns to a frame, read Creating and Changing Columns first.

Column arithmetic

The usual operators + - * / // % ** work between columns, between a column and a single number, and between a column and a Series. Comparisons (>, ==, ...) produce a Boolean column the same way.

Python Arithmetic on whole columns

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 last line is the one to remember. shuffled holds the same kind of numbers, but its labels are in a different order, and pandas matches by label: row 0 of orders gets the value labelled 0 (which is 2.0), so the first result is -0.75, not 0.25. This index alignment is powerful when you want it and a surprise when you do not. If one side has a label the other lacks, the result for that label is missing (NaN).

The diagram shows why a loop and a vectorised call differ so much in cost.

flowchart LR
    subgraph Loop["Python loop"]
        direction TB
        L1["row 0: look up, multiply, store"] --> L2["row 1: look up, multiply, store"] --> L3["... one Python step per row"]
    end
    subgraph Vec["Vectorised"]
        direction TB
        V1["price column (one array)"] --> V2["one compiled loop<br/>multiplies every pair"] --> V3["result column"]
        V4["qty column (one array)"] --> V2
    end

A loop pays Python's overhead once per row; a vectorised call pays it once per column.

Choosing values with np.where

Often a new column depends on a condition: "pass if the score is at least 60". np.where(condition, if_true, if_false) evaluates the whole Boolean column and picks from the two options row by row. It comes from NumPy, which pandas is built on.

For more than two outcomes, np.select(conditions, choices, default=...) takes a list of Boolean columns and a list of results. It checks the conditions in order and uses the first one that is true, so list the strictest condition first.

Python Two outcomes and many outcomes

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.

Series.where(cond, other) keeps values where cond is true and replaces the others. Many people read it the other way round ("where the condition holds, replace"). When you are unsure, print a tiny example, or use mask, which replaces the values where the condition is true.

For a plain "if/else" the diagram below is how np.select walks through your list. A row never reaches a later condition once an earlier one matched.

flowchart TD
    A["score"] --> B{">= 90?"}
    B -- yes --> GA["A"]
    B -- no --> C{">= 75?"}
    C -- yes --> GB["B"]
    C -- no --> D{">= 60?"}
    D -- yes --> GC["C"]
    D -- no --> GF["default: F"]

np.select tests the conditions in the order you list them and stops at the first match.

Clip, abs and round

Three small methods tidy numbers without any loop:

  • clip(lower, upper) forces values into a range. Anything below lower becomes lower; anything above upper becomes upper. Either bound may be left out.
  • abs() removes the sign.
  • round(n) rounds to n decimal places (default 0). Like Python's round, it rounds exact halves to the nearest even number, so 0.5 becomes 0.0 and 1.5 becomes 2.0.
Python Limiting and tidying values

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.

clip is the standard way to cap extreme values; you will meet it again in Duplicates and Outliers.

Cumulative functions

The cum family returns a Series of the same length where each value summarises everything up to that row: cumsum, cumprod, cummax, cummin. They are how you build running totals and "best so far" columns.

Comparing rows: diff, pct_change and shift

Time-ordered data constantly asks "how does this row compare with the one before?". Three tools answer that:

  • shift(k) moves the values down by k rows (up if k is negative). The vacated rows become NaN. It lines up yesterday's value next to today's.
  • diff(k) is value - value.shift(k): the change since k rows ago.
  • pct_change(k) is the change as a fraction of the earlier value: (value - previous) / previous.
Python Running totals and day-over-day change

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 first row of change, pct_change and yesterday is NaN because nothing precedes it. That is correct, not a bug; decide whether to leave it, drop it, or fill it (see Missing Data). These methods work on the row order, so sort by time first. Grouped versions that restart for each customer or product appear in agg, transform and filter.

A frequent pattern: df["growth"] = df["revenue"] / df["revenue"].shift(1) - 1 does the same job as pct_change() but lets you shift by a different amount, for example shift(7) to compare with the same weekday last week.

Why loops are slow

Each pass of a Python loop does extra work: it looks up the row, creates a Python number object, runs the multiplication, and stores the answer. With 200,000 rows that overhead is paid 200,000 times. A vectorised expression hands the two whole arrays to compiled code that multiplies them in one tight loop.

The next example times both ways. Timings differ on every machine, so the code prints only comparisons that stay true: both methods agree, and the loop is far slower.

Python Loop versus vectorised

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.

That loop is the worst kind: it also pays for .iloc on every pass. A loop over plain Python lists is faster than that, but still well behind the vectorised version. The rule of thumb: if you are about to write for row in df.iterrows(), first ask which vectorised method does the same thing. The speed chapter, Writing Fast pandas Code, goes deeper.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does df['a'] + df['b'] do when the two columns have the same index labels in different orders?

    pandas aligns on the index. Values with the same label are added together, wherever they sit. Labels present on only one side give NaN.
  2. 2. Which call gives the grade 'A' to scores of 90 and above, 'B' to 75 and above, and 'F' otherwise?

    np.select takes a list of conditions and a list of results, tests them in order, and uses the default when none match.
  3. 3. What is the first value of s.diff()?

    diff subtracts the previous row, and the first row has no previous row, so the result is missing.
  4. 4. Why is a Python for loop over the rows usually much slower than df['price'] * df['qty']?

    The work per number is the same, but a Python loop adds lookups and object creation for every row. The vectorised call does the whole column in compiled code.
  5. 5. Which method returns the change of each value as a fraction of the previous value?

    pct_change computes (value - previous) / previous. diff gives the absolute difference, and shift only moves values.

Summary

  • Arithmetic and comparisons on columns apply to every row at once, and Series line up by index label.
  • np.where picks between two values; np.select handles many conditions in order with a default.
  • Series.where keeps values where the condition is true; mask replaces them.
  • clip, abs and round reshape values without loops; round sends exact halves to the even neighbour.
  • cumsum, cummax, diff, pct_change and shift build running totals and row-to-row comparisons, leaving NaN where no earlier row exists.
  • Prefer a vectorised expression to a Python loop; the loop pays its overhead once per row.

Next: apply, map and replace, for the cases where no ready-made vectorised method exists.