Fast pandas: vectorise and use the right dtypes

For 100,000 order lines, pandas is fast when the work runs inside its compiled column operations, and slow when Python runs once for every row. Two habits make most analysis code fast. Use column expressionscolumn expression: A calculation written on whole columns or arrays, with values and row identity governed by the operation's contract.: write each calculation as an expression on whole columns, such as df["qty"] * df["price"], and turn branching rules into np.where, np.select or pd.cut instead of apply(axis=1) or a loop. Use the right dtypes: keep repeated text as category, whole numbers in the smallest integer type that holds them, and read only the columns you need, so the same table fits in a fraction of the memory.

A column expression avoids per-row Python work

In this lesson’s measured runs, the vectorised form of one calculation on 100,000 rows was more than a thousand times faster than apply(axis=1), and a 200,000-row order table shrank from 16.59 MB to 6.40 MB. You will also see when apply is perfectly fine, and how to measure time and memory so that the numbers mean something.

A pandas column is a typed array: 100,000 quantities sit side by side in memory as 64-bit integers. An expression such as df["qty"] * df["price"] makes one call into compiled code, which runs a tight loop over both arrays. apply(axis=1) does the opposite. For every row it builds a new Series holding that row’s values, calls your Python function, reads the values back out of the Series one at a time and collects the results. Each step is quick, but it happens 100,000 times, and none of it is the arithmetic you asked for.

# One calculation written six ways, timed on 100,000 order lines: the amount of each line is quantity × price,
# with 10 % off for 10 or more units. Each version is timed with timeit (the best of several runs, so other
# programs on the computer disturb it least), and all six results are compared at the end.
import timeit

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
n = 100_000
orders = pd.DataFrame({"qty": rng.integers(1, 21, n), "price": rng.integers(50, 5000, n) / 10})


def with_iterrows(df):
    out = []
    for _, row in df.iterrows():  # builds a Series for every row
        out.append(row["qty"] * row["price"] * (0.9 if row["qty"] >= 10 else 1.0))
    return pd.Series(out, index=df.index)


def with_apply(df):
    return df.apply(lambda row: row["qty"] * row["price"] * (0.9 if row["qty"] >= 10 else 1.0), axis=1)


def with_itertuples(df):
    return pd.Series([r.qty * r.price * (0.9 if r.qty >= 10 else 1.0) for r in df.itertuples(index=False)], index=df.index)


def with_list_comprehension(df):
    pairs = zip(df["qty"].tolist(), df["price"].tolist())
    return pd.Series([q * p * (0.9 if q >= 10 else 1.0) for q, p in pairs], index=df.index)


def vectorised(df):
    return df["qty"] * df["price"] * np.where(df["qty"] >= 10, 0.9, 1.0)


def with_numpy(df):
    q = df["qty"].to_numpy()
    return pd.Series(q * df["price"].to_numpy() * np.where(q >= 10, 0.9, 1.0), index=df.index)


ways = [
    ("iterrows loop", with_iterrows),
    ("apply(axis=1)", with_apply),
    ("itertuples", with_itertuples),
    ("list comprehension", with_list_comprehension),
    ("vectorised pandas", vectorised),
    ("NumPy arrays", with_numpy),
]
timings = {}
results = {}
for name, func in ways:
    start = timeit.default_timer()
    results[name] = func(orders)
    first = timeit.default_timer() - start
    if first > 0.2:  # a slow version: a single call is long enough to measure
        timings[name] = first
    else:  # a fast one: enough calls for 0.2 s per measurement, best of 5
        timer = timeit.Timer(lambda: func(orders))
        number, _ = timer.autorange()
        timings[name] = min(timer.repeat(repeat=5, number=number)) / number

base = timings["vectorised pandas"]
print(f"{n} order lines: amount = qty x price, 10 % off from 10 units")
for name, _ in ways:
    ms = timings[name] * 1000
    shown = f"{ms:,.0f} ms" if ms >= 10 else f"{ms:.2f} ms"
    print(f"{name:<20}{shown:>12}   {timings[name] / base:>8,.0f} x the vectorised time")
reference = results["vectorised pandas"]
print("all six results equal:", all(np.allclose(r, reference) for r in results.values()))

100000 order lines: amount = qty x price, 10 % off from 10 units
iterrows loop           7,958 ms     22,544 x the vectorised time
apply(axis=1)           1,929 ms      5,465 x the vectorised time
itertuples                 54 ms        152 x the vectorised time
list comprehension         14 ms         40 x the vectorised time
vectorised pandas        0.35 ms          1 x the vectorised time
NumPy arrays             0.37 ms          1 x the vectorised time
all six results equal: True

All six versions return the same amounts, as the last line confirms, and they fall into two groups. The four that run Python once per row are slow in proportion to how much pandas does for each row: iterrows and apply(axis=1) build a Series per row and took seconds; itertuples builds a lighter named tuple; the list comprehension over two plain Python lists does least and is the fastest of the four. The two vectorised versions took under a millisecond. Calling to_numpy() and working on NumPy arrays changed little once the work was vectorised: the big step is leaving per-row Python, not leaving pandas. The pandas guide to user-defined functions makes the same point: most tasks can be written with built-in methods and vectorised operations, and those are what pandas can optimise.

Compare the same 100,000 discount calculations before using their timing ratios.

Measure the actual task at realistic sizes

Two numbers decide whether a change was worth it, the time per run and the memory, and both are easy to get wrong. The timing boundarytiming boundary: The operations included in a measurement, such as imports, input reads, computation, conversions and result materialisation. says which work is measured. For time, Python’s timeit module runs a statement repeatedly: Timer.autorange() picks a number of runs that takes at least 0.2 seconds, and repeat() measures that several times. The timeit documentation recommends the lowest of those measurements, because the slower ones are mostly other programs competing for the processor, not your code. In a notebook, %timeit does the same work. Then measure at realistic sizes:

# Every pandas operation costs a fixed amount of work before it touches the data (checking types, aligning the
# index, building the result). This example times the same multiplication, written as a list comprehension and
# as a vectorised expression, on tables from 10 to 1,000,000 rows: the best of 5 measurements of at least 0.2 s.
import timeit

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)


def per_call(func):
    timer = timeit.Timer(func)
    number, _ = timer.autorange()
    return min(timer.repeat(repeat=5, number=number)) / number


def shown(seconds):
    us = seconds * 1e6
    return f"{us:,.0f} µs" if us >= 100 else f"{us:.1f} µs"


print(f"{'rows':>9}  {'list comprehension':>18}  {'vectorised':>11}  vectorised is")
for n in [10, 1_000, 100_000, 1_000_000]:
    df = pd.DataFrame({"qty": rng.integers(1, 21, n), "price": rng.integers(50, 5000, n) / 10})

    def loop():
        return pd.Series([q * p for q, p in zip(df["qty"].tolist(), df["price"].tolist())], index=df.index)

    def vectorised():
        return df["qty"] * df["price"]

    a, b = per_call(loop), per_call(vectorised)
    ratio = a / b
    verdict = f"{ratio:,.0f} x faster" if ratio >= 1.5 else "slower" if ratio <= 0.67 else "about the same"
    print(f"{n:>9,}  {shown(a):>18}  {shown(b):>11}  {verdict}")

     rows  list comprehension   vectorised  vectorised is
       10             62.7 µs      80.8 µs  about the same
    1,000              207 µs      93.0 µs  2 x faster
  100,000           13,614 µs       210 µs  65 x faster
1,000,000          179,312 µs     1,062 µs  169 x faster

At 10 rows the list comprehension is as quick as the vectorised expression, or quicker, because every pandas operation first spends some tens of microseconds checking types, aligning indexes and building its result, whatever the size of the data. That fixed cost is why a speed-up measured on a small sample says little. As the rows grow, the per-row cost of Python takes over: at a million rows the vectorised expression was more than a hundred times faster than even the leanest loop. Time your code on data the size of production, and check that the new version returns the same values before you keep it.

Keep the original autorange protocol while comparing the ten-row and million-row measurements.

The first matching condition controls the fee

Priority is a rule, not a property inferred from the output values. Decide whether express status overrides an amount-based discount before choosing the condition order. Test a row satisfying both conditions, a row satisfying neither and each exact boundary. That makes an incorrectly reordered rule observable even when ordinary rows still produce plausible fees.

Most apply(axis=1) code exists because of an if statement. NumPy and pandas have a vectorised form for each common kind of branch:

# Branching logic without a Python call per row. Eight orders get a delivery fee, a size label and a region:
# np.where chooses between two values, np.select between several (the first condition that is true wins),
# pd.cut puts numbers into ranges and Series.map looks values up in a dictionary.
import numpy as np
import pandas as pd

orders = pd.DataFrame(
    {
        "city": ["Pune", "Kochi", "Delhi", "Pune", "Agra", "Kochi", "Delhi", "Agra"],
        "amount": [180.0, 950.0, 499.0, 500.0, 2400.0, 75.0, 1200.0, 640.0],
        "express": [False, True, False, True, False, False, True, True],
    }
)

# Two outcomes: free delivery from 500, otherwise 40.
orders["fee"] = np.where(orders["amount"] >= 500, 0, 40)

# Several outcomes, checked in order: express costs 99 whatever the amount, then the free-delivery rule.
conditions = [orders["express"], orders["amount"] >= 500]
orders["fee_v2"] = np.select(conditions, [99, 0], default=40)

# Numeric ranges: right=False makes each range include its left edge, [0, 250), [250, 1000), [1000, inf).
orders["size"] = pd.cut(orders["amount"], bins=[0, 250, 1000, np.inf], labels=["small", "medium", "large"], right=False)

# A lookup: cities missing from the dictionary become NaN, so fill them on purpose.
region = {"Pune": "west", "Delhi": "north", "Kochi": "south"}
orders["region"] = orders["city"].map(region).fillna("other")

print(orders.to_string())

    city  amount  express  fee  fee_v2    size region
0   Pune   180.0    False   40      40   small   west
1  Kochi   950.0     True    0      99  medium  south
2  Delhi   499.0    False   40      40  medium  north
3   Pune   500.0     True    0      99  medium   west
4   Agra  2400.0    False    0       0   large  other
5  Kochi    75.0    False   40      40   small  south
6  Delhi  1200.0     True    0      99   large  north
7   Agra   640.0     True    0      99  medium  other

• np.where(condition, a, b) chooses between two values for all rows at once: free delivery from 500, otherwise 40.
• np.select(conditions, choices, default=...) handles several outcomes. It checks the conditions in order and takes the first that is true, so the most specific rule goes first: in fee_v2, express orders pay 99 even above 500, as rows 1, 3, 6 and 7 show.

Check the inclusive 500 boundary and express priority on the original eight orders.

Ranges and lookup keys need explicit boundaries

Keep missing and unknown inputs distinct from valid bins and known keys. A label is a reporting decision, so check both numeric endpoints and an absent lookup key before relying on the formatted output. Retain the source values separately when later code needs to distinguish an unknown city from the explicit other category.

• pd.cut turns numbers into labelled ranges. With right=False each range includes its left edge, so an amount of exactly 250 or 500 is medium, and the edges are written once instead of in a chain of comparisons.
• Series.map(dict) replaces each value with its entry in a dictionary. Values without an entry become NaN, which is why the example fills Agra with “other” on purpose.

Place 250 and 1,000 in their bins before interpreting Agra’s lookup fallback.

Missing input must be handled before a default branch

The original example uses ordinary numeric values, so each valid condition is a Boolean. A nullable predicate is a different input representation and can remain missing. State whether such a row is rejected, flagged or assigned a value before selecting fees. A fallback should record a defined decision rather than silently turn missing data into a valid-looking result.

Missing values need one decision of their own. Ordered and equality comparisons with a native NaN are false, while != is true; nullable pandas missing predicates can instead remain missing, so a row with a missing amount falls through every condition to the default of np.select. Fill or flag missing values before the rules run, so that the default means “no rule matched” rather than “the data was missing”.

Distinguish a false comparison from a missing predicate before selecting a default fee.

Reuse a pure result once per distinct input value

Some logic has no vectorised form: a library function that accepts one value at a time, a rule another team wrote as a Python function, or a clean-up that combines several string steps. The pandas guide to user-defined functions names such cases (custom computations, functions from other libraries, unusual group operations) as the reasons to use one. Even then, Python does not have to run once per row. When values repeat, run it once per distinct value:

# When the logic needs plain Python, call it once per distinct value instead of once per row. 500,000 city names
# arrive in 24 spellings; normalise() trims, folds case and replaces old names. Each way is timed (the best of
# 3 runs, conversions included) and counts how often normalise() ran.
import timeit

import numpy as np
import pandas as pd

ALIASES = {"bombay": "mumbai", "bangalore": "bengaluru", "madras": "chennai", "calcutta": "kolkata", "poona": "pune"}
calls = 0


def normalise(name):
    global calls
    calls += 1
    key = " ".join(name.split()).casefold()
    return ALIASES.get(key, key).title()


spellings = ["Mumbai", " mumbai", "Bombay", "MUMBAI ", "Bengaluru", "Bangalore", "bangalore ", "Chennai", "Madras",
             "chennai", "Kolkata", "Calcutta", "kolkata ", "Pune", "Poona", "PUNE", "Delhi", "delhi ", "New  Delhi",
             "Jaipur", "jaipur", "Kochi", "KOCHI", "Kochi "]
rng = np.random.default_rng(5)
cities = pd.Series(rng.choice(spellings, 500_000))


def per_row():
    return cities.map(normalise)


def per_distinct_value():
    lookup = {name: normalise(name) for name in cities.unique()}
    return cities.map(lookup)


def via_category():
    return cities.astype("category").map(normalise)  # a categorical Series maps its categories, not its rows


reference = per_row()
print(f"{'way':<31}{'calls':>9}{'time':>11}{'speed-up':>10}")
base = None
for label, func in [("one call per row", per_row), ("one call per distinct value", per_distinct_value),
                    ("convert to category, then map", via_category)]:
    calls = 0
    result = func()
    used = calls
    seconds = min(timeit.repeat(func, repeat=3, number=1))
    base = base or seconds
    assert result.astype(str).equals(reference), label
    print(f"{label:<31}{used:>9,}{seconds * 1000:>8.1f} ms{base / seconds:>8.0f} x")
print(f"all three agree: {len(spellings)} spellings become {reference.nunique()} cities:", ", ".join(sorted(reference.unique())))

way                                calls       time  speed-up
one call per row                 500,000   214.9 ms       1 x
one call per distinct value           24    75.6 ms       3 x
convert to category, then map         24    30.1 ms       7 x
all three agree: 24 spellings become 9 cities: Bengaluru, Chennai, Delhi, Jaipur, Kochi, Kolkata, Mumbai, New Delhi, Pune

Mapping the names row by row called normalise() 500,000 times. A dictionary built from the 24 distinct spellings needs only 24 calls for this value-dependent normaliser; reuse is valid only when the returned result does not depend on row context or call order, and converting the column to category first was faster still, because Series.map on a categorical column applies the function to the categories only, as its documentation states. The pandas guide to categorical data gives the same advice for .str methods on columns with many repeated values. apply is also fine when the table is small, as the 10-row line of the previous example showed, or when it runs once per group and there are only a few groups. In pandas 3, Series.map also takes an engine argument that hands a numeric function to a just-in-time compiler such as Numba; that needs the extra package and a function whose types stay the same from call to call.

Follow the 24 spellings into nine city names before comparing the three measured methods.

Column memory and whole-process memory answer different questions

Use the same accounting method and units for the before-and-after table. Deep object accounting estimates the referenced values but does not identify every shared allocation uniquely across a process. Parser buffers, library imports and other live objects can increase process memory without appearing in the finished DataFrame. A process-peak measurement therefore complements the column report rather than replacing its per-column detail.

Memory decides how large a table you can load, and less memory also means less to copy, so the diet speeds things up too. Use column memory accountingcolumn memory accounting: A report of the data representation's storage, distinct from the peak live memory of the whole process. with memory_usage(deep=True). Without deep=True, pandas counts only the 8-byte pointer of each value in an object column and none of the strings behind the pointers, so text looks far smaller than it is. Then give each column the smallest dtype that keeps every value:

# A memory diet for a 200,000-row order table: measure every column with memory_usage(deep=True), then give
# each column the smallest dtype that keeps its values. Seeded, so every run builds the same table.
import numpy as np
import pandas as pd

rng = np.random.default_rng(11)
n = 200_000
cities = ["Mumbai", "Delhi", "Bengaluru", "Hyderabad", "Chennai", "Kolkata", "Pune", "Ahmedabad", "Jaipur", "Lucknow",
          "Surat", "Kochi", "Indore", "Bhopal", "Nagpur", "Patna", "Chandigarh", "Coimbatore", "Vadodara", "Guwahati"]
orders = pd.DataFrame(
    {
        "order_id": np.arange(10_000_001, 10_000_001 + n),
        "customer": [f"C{v:07d}" for v in rng.integers(0, 10_000_000, n)],
        "city": rng.choice(cities, n),
        "channel": rng.choice(["app", "web", "store"], n, p=[0.6, 0.3, 0.1]),
        "status": rng.choice(["delivered", "returned", "cancelled", "pending"], n, p=[0.85, 0.06, 0.05, 0.04]),
        "qty": rng.integers(1, 21, n),
        "price": rng.integers(5_000, 5_000_000, n) / 100,
    }
)


def mb(nbytes):
    return f"{nbytes / 1e6:.2f} MB"


before = orders.memory_usage(deep=True)
as_objects = orders.astype({c: object for c in ["customer", "city", "channel", "status"]}).memory_usage(deep=True)

slim = orders.copy()
for col in ["city", "channel", "status"]:  # few distinct values: store each once
    slim[col] = slim[col].astype("category")
for col in ["order_id", "qty"]:  # whole numbers: the smallest unsigned type that holds them
    slim[col] = pd.to_numeric(slim[col], downcast="unsigned")
after = slim.memory_usage(deep=True)

print(f"{'column':<10}{'unique':>8}  {'as Python objects':>17}  {'pandas 3 default':>16}  {'after the diet':>14}  dtype after")
for col in ["Index", *orders.columns]:
    unique = "" if col == "Index" else f"{orders[col].nunique():,}"
    obj = mb(as_objects[col])
    dtype = "" if col == "Index" else str(slim[col].dtype)
    print(f"{col:<10}{unique:>8}  {obj:>17}  {mb(before[col]):>16}  {mb(after[col]):>14}  {dtype}".rstrip())
print(f"{'total':<10}{'':>8}  {mb(as_objects.sum()):>17}  {mb(before.sum()):>16}  {mb(after.sum()):>14}")
print(f"the diet kept {after.sum() / before.sum():.0%} of the default and {after.sum() / as_objects.sum():.0%} of the object table")

# Two columns that did not go on the diet, and why.
as_category = orders["customer"].astype("category").memory_usage(deep=True)
print(f"customer as category: {mb(as_category)} (almost every value is different)")
price32 = orders["price"].astype("float32")
changed = price32.astype("float64") != orders["price"]
largest = (price32.astype("float64") - orders["price"]).abs().max()
total_gap = abs(float(price32.sum()) - orders["price"].sum())
print(f"price as float32: {int(changed.sum()):,} of {n:,} prices change (by up to {largest:.4f}),"
      f" and the float32 total is off by {total_gap:.2f}")
print("to_numeric(price, downcast='float') keeps", pd.to_numeric(orders["price"], downcast="float").dtype)
print("to_numeric(halves, downcast='float') gives", pd.to_numeric(pd.Series([0.5, 1.5, 2.0]), downcast="float").dtype)

# Small integer types are for storage: arithmetic stays in the small type and wraps around past its maximum.
qty = pd.Series([12, 13, 20], dtype="uint8")
print("uint8 quantities 12, 13, 20 times 20:", (qty * 20).tolist(), "| after astype('int64'):", (qty.astype("int64") * 20).tolist())

column      unique  as Python objects  pandas 3 default  after the diet  dtype after
Index                         0.00 MB           0.00 MB         0.00 MB
order_id   200,000            1.60 MB           1.60 MB         0.80 MB  uint32
customer   197,938           11.40 MB           3.20 MB         3.20 MB  str
city            20           11.18 MB           2.98 MB         0.20 MB  category
channel          3           10.44 MB           2.24 MB         0.20 MB  category
status           4           11.57 MB           3.37 MB         0.20 MB  category
qty             20            1.60 MB           1.60 MB         0.20 MB  uint8
price      196,077            1.60 MB           1.60 MB         1.60 MB  float64
total                        49.39 MB          16.59 MB         6.40 MB
the diet kept 39% of the default and 13% of the object table
customer as category: 3.99 MB (almost every value is different)
price as float32: 191,928 of 200,000 prices change (by up to 0.0019), and the float32 total is off by 194.12
to_numeric(price, downcast='float') keeps float64
to_numeric(halves, downcast='float') gives float32
uint8 quantities 12, 13, 20 times 20: [240, 4, 144] | after astype('int64'): [240, 260, 400]

Keep the original three memory totals separate from a process-peak measurement.

A smaller dtype must preserve the required values

Read the table from left to right. With its text held as Python objects, which was the pandas 2 default, the order table took 49.39 MB, almost all of it in the four text columns. The pandas 3 default str dtype keeps the characters in one Arrow buffer and brings that to 16.59 MB without any work on your part. The diet then took it to 6.40 MB, 39 % of the default:

• category for text with few distinct values. city has 20 values, channel 3 and status 4, so each name is stored once and every row keeps a 1-byte code: city fell from 2.98 MB to 0.20 MB.
• The smallest integer type that holds the values. pd.to_numeric(..., downcast="unsigned") chose uint32 for the order IDs, which stay below 4.29 billion, and uint8 for quantities from 1 to 20, so 8 bytes a value became 4 and 1.

The last lines of the output show why two columns stayed as they were. customer holds 197,938 different values in 200,000 rows, and as a category it grew from 3.20 MB to 3.99 MB: when almost every value is distinct, the codes are added on top of the values, as the pandas guide to categorical data warns. price holds amounts with paise or cents, and float32 cannot store most of them exactly: 191,928 of the 200,000 prices changed, and the float32 total was off by 194.12. pd.to_numeric(..., downcast="float") left these prices as float64, while the halves in the next line became float32. Float64 reduces the demonstrated float32 error but is still approximate decimal arithmetic. For exact money, choose validated integer minor units or decimal arithmetic with an explicit scale and rounding rule.

Compare the repeated city column with the nearly unique customer and the changed price values.

Small integer storage can wrap during arithmetic

The smallest storage type need not be the computation type. Determine the largest intermediate product or sum the calculation can produce, including repeated addition and signed inputs. Then choose or validate a computation type before the first arithmetic step. Converting an already wrapped number only changes its representation and cannot restore the mathematical result.

Computation dtype rangecomputation dtype range: The values a type can represent during a calculation; it must cover intermediate results as well as stored inputs. must cover the arithmetic result. Small integer types are for storing values, not for arithmetic on them. Under NumPy’s promotion rules (NEP 50), a uint8 column multiplied by 20 stays uint8, so 13 × 20 wrapped around to 4 and 20 × 20 to 144 without an error, as the final line shows. Convert to a wider type with astype("int64") before arithmetic that can leave the small type’s range.

Follow 13 × 20 through the original wrapped value of four and the widened value of 260.

Read the needed columns with declared types

The cheapest column is the one you never load. read_csv accepts usecols, the columns to keep, and dtype, the types to give them while parsing, so a column never exists in its expensive form at all:

# Read only what the analysis needs. The example writes a 200,000-row CSV with 12 columns, then reads it three
# ways: everything, only 4 columns, and 4 columns with their dtypes given to the reader. Memory comes from
# memory_usage(deep=True); each time is the best of 3 reads.
import timeit

import numpy as np
import pandas as pd

rng = np.random.default_rng(3)
n = 200_000
cities = ["Mumbai", "Delhi", "Bengaluru", "Hyderabad", "Chennai", "Kolkata", "Pune", "Jaipur"]
pd.DataFrame(
    {
        "order_id": np.arange(1, n + 1),
        "order_date": pd.date_range("2025-04-01", periods=n, freq="2min").strftime("%Y-%m-%d %H:%M"),
        "customer": [f"C{v:07d}" for v in rng.integers(0, 10_000_000, n)],
        "city": rng.choice(cities, n),
        "pincode": rng.integers(110_001, 855_118, n),
        "channel": rng.choice(["app", "web", "store"], n),
        "product": [f"P{v:05d}" for v in rng.integers(0, 40_000, n)],
        "category": rng.choice(["grocery", "fashion", "mobiles", "home", "beauty"], n),
        "qty": rng.integers(1, 21, n),
        "price": rng.integers(5_000, 500_000, n) / 100,
        "coupon": rng.choice(["", "FEST10", "NEW50", "SAVE5"], n, p=[0.7, 0.1, 0.1, 0.1]),
        "note": rng.choice(["", "leave at door", "call first", "gift wrap"], n, p=[0.8, 0.1, 0.05, 0.05]),
    }
).to_csv("orders.csv", index=False)

wanted = ["city", "category", "qty", "price"]
ways = {
    "every column": dict(),
    "usecols (4 columns)": dict(usecols=wanted),
    "usecols + dtype": dict(usecols=wanted, dtype={"city": "category", "category": "category", "qty": "uint8"}),
}
print(f"{'read_csv with':<22}{'columns':>8}{'memory':>11}{'time':>10}")
for label, kwargs in ways.items():
    df = pd.read_csv("orders.csv", **kwargs)
    seconds = min(timeit.repeat(lambda: pd.read_csv("orders.csv", **kwargs), repeat=3, number=1))
    print(f"{label:<22}{df.shape[1]:>8}{df.memory_usage(deep=True).sum() / 1e6:>8.2f} MB{seconds * 1000:>7.0f} ms")

read_csv with          columns     memory      time
every column                12   29.32 MB    305 ms
usecols (4 columns)          4    8.97 MB    101 ms
usecols + dtype              4    2.20 MB     93 ms

Reading the 4 columns the analysis needs, instead of all 12, cut the frame’s memory by more than two thirds and the read time with it, because pandas skips the other columns while it parses. Giving the two repeated text columns category and the quantity uint8 in the same call shrank the frame further. Do the same with every file format that allows it: read_parquet takes columns=, and a database query should select only the columns it needs.

Compare the twelve-column result with the four selected columns and the original typed result.

A fast fee function keeps the rules and order labels

delivery_fees(orders) returns the right fee for every order, but it calls a Python function per row. Rewrite it so that it is at least 20 times faster without changing a single fee. The rules are in the docstring: the base fee is 0 from an amount of 1,000, otherwise 40 in a metro city and 60 elsewhere; every whole kilogram above 2 kg adds 15; no fee is above 200. The checks compare your fees with the original rules on 8 edge cases and 5,000 random orders, check that every fee stays in its order’s row, and time both versions on 50,000 orders. The starter passes 2 of the 3 checks.

# The starter code of the exercise: correct fees, one Python call per order. Make delivery_fees() at least 20
# times faster without changing a single fee, then run check_delivery.py.
import pandas as pd


def delivery_fees(orders: pd.DataFrame) -> pd.Series:
    """The delivery fee of every order.

    The base fee is 0 from an amount of 1,000, otherwise 40 in a metro city and 60 elsewhere. Every whole
    kilogram above 2 kg adds 15, and no fee is above 200.
    """

    def fee(row):
        if row["amount"] >= 1000:
            base = 0
        elif row["metro"]:
            base = 40
        else:
            base = 60
        extra_kg = max(0, int(row["weight_kg"]) - 2)
        return min(base + 15 * extra_kg, 200)

    return orders.apply(fee, axis=1)

# Sample checks for delivery_task.py: the same fee as the rules for every order, the input's index kept, and at
# least 20 times faster than the original on 50,000 orders (both timed here, the best of 3 runs each).
import timeit

import numpy as np
import pandas as pd

from delivery_task import delivery_fees


def original(orders):
    def fee(row):
        base = 0 if row["amount"] >= 1000 else (40 if row["metro"] else 60)
        return min(base + 15 * max(0, int(row["weight_kg"]) - 2), 200)

    return orders.apply(fee, axis=1)


def random_orders(n, seed):
    rng = np.random.default_rng(seed)
    return pd.DataFrame(
        {
            "amount": rng.integers(0, 300_000, n) / 100,
            "metro": rng.random(n) < 0.5,
            "weight_kg": rng.integers(1, 250, n) / 10,
        },
        index=pd.RangeIndex(1000, 1000 + n, name="order_id"),
    )


edges = pd.DataFrame(
    {
        "amount": [1000.0, 999.99, 999.99, 0.0, 5000.0, 250.0, 250.0, 1500.0],
        "metro": [False, True, False, True, False, False, True, True],
        "weight_kg": [2.0, 2.99, 3.0, 0.1, 30.0, 14.5, 25.0, 1.0],
    },
    index=[7, 3, 9, 1, 4, 8, 2, 6],  # not sorted: the fees must stay with their orders
)


def same_fees(orders):
    got, want = delivery_fees(orders), original(orders)
    return got.index.equals(want.index) and np.array_equal(got.to_numpy(dtype=float), want.to_numpy(dtype=float))


results = []
results.append(("edge cases: the same fees, in the same rows", same_fees(edges)))
results.append(("5,000 random orders: the same fees", same_fees(random_orders(5_000, seed=1))))
big = random_orders(50_000, seed=2)
yours = min(timeit.repeat(lambda: delivery_fees(big), repeat=3, number=1))
theirs = min(timeit.repeat(lambda: original(big), repeat=3, number=1))
results.append((f"50,000 orders: at least 20 x faster than the original (it is {theirs / yours:.1f} x)", theirs / yours >= 20))

for i, (what, ok) in enumerate(results, start=1):
    print(f"{i}. {'passed' if ok else 'failed'}: {what}")
print(f"{sum(ok for _, ok in results)} of {len(results)} checks passed")

1. passed: edge cases: the same fees, in the same rows
2. passed: 5,000 random orders: the same fees
3. failed: 50,000 orders: at least 20 x faster than the original (it is 1.2 x)
2 of 3 checks passed

Start from the three parts of the rule. The base fee has three outcomes, which is a job for np.select with the amount rule first. The extra kilograms are np.floor(weight_kg) - 2, never below zero (clip(lower=0)), which matches int() for positive weights. The cap is clip(upper=200). Build the result from the columns of orders so that it keeps their index.

Preserve the original eight edge rows, random fixtures and 20-times threshold while changing the fee implementation.

Practise the preserved performance contracts

Interview questions

Fresher · Why is a vectorised pandas expression faster than the same calculation in apply(axis=1)?

apply(axis=1) calls a Python function once per row, and for every row pandas builds a Series holding that row’s values, which the function then reads one at a time. That per-row work, not the arithmetic, is what takes the time. A vectorised expression such as df["qty"] * df["price"] makes one call into compiled code that loops over typed arrays stored side by side in memory. On 100,000 rows the gap in this lesson’s run was more than a thousand times. On a very small table the difference disappears, because each pandas operation has a fixed cost of some tens of microseconds, so measure at realistic sizes. Branching rules have vectorised forms too: np.where, np.select and pd.cut.

Key takeaways

• Per-row Python (iterrows, apply(axis=1), loops) is slow because of the work done for every row; one expression on whole columns runs in compiled code.
• Time with timeit and report the lowest of several runs; measure on realistic sizes, and check that the faster version returns the same values.
• np.where, np.select (the first true condition wins), pd.cut and Series.map replace most if statements inside apply.
• When Python per row is unavoidable, call it once per distinct value; on a categorical column, Series.map does that for you.
• memory_usage(deep=True) accounts for the DataFrame’s own data and index, while the whole process peak needs separate measurement. Use category for text with few distinct values, the smallest integer type for storage (widen it before arithmetic), a declared exact or approximate money representation, and usecols with dtype when you read.