Transforming Columns: vectorization, map and apply

Transform pandas columns the fast way: vectorized math, np.where and np.select, map for lookups, assign for chaining, and apply only when you must.

• Intermediate
• 16 min read
• 4 objectives

Before this lessonLesson 4: Cleaning Data

Most analysis work is deriving new columns: a total from price and quantity, a tier from a score, a region from a country code. pandas gives you several ways to do this, and they are not equal. Some run in optimized C code over the whole column at once; others quietly fall back to a Python loop that can be a hundred times slower.

This lesson ranks the options from fastest and most idiomatic to slowest, so you reach for the right one first. The rule of thumb: operate on whole columns, not on individual values.

Why vectorization wins

A Python for loop handles one value at a time, checking types and creating objects on every step. A vectorized expression like df["price"] * df["qty"] hands both columns to NumPy, which multiplies them in a tight compiled loop. The code is also shorter and says what you mean.

import pandas as pd

orders = pd.DataFrame({
    "item": ["mouse", "desk", "cable", "chair"],
    "price": [25.0, 300.0, 8.0, 150.0],
    "qty": [2, 1, 5, 2],
})

orders["total"] = orders["price"] * orders["qty"]
orders["total_with_tax"] = (orders["total"] * 1.2).round(2)
orders["share"] = (orders["total"] / orders["total"].sum()).round(3)
print(orders)

    item  price  qty  total  total_with_tax  share
0  mouse   25.0    2   50.0            60.0  0.072
1   desk  300.0    1  300.0           360.0  0.435
2  cable    8.0    5   40.0            48.0  0.058
3  chair  150.0    2  300.0           360.0  0.435

Arithmetic, comparisons, .round(), .abs(), .clip(), the .str and .dt accessors and every NumPy function (np.log, np.sqrt) are all vectorized. If you can express a transformation with them, do.

Conditional columns: np.where and np.select

"If this, then that, otherwise something else" is the most common reason people reach for a loop. You do not need one. np.where(condition, a, b) handles two outcomes. For several, np.select takes a list of conditions and a list of choices, and picks the first condition that is true for each row.

import numpy as np
import pandas as pd

orders = pd.DataFrame({
    "item": ["mouse", "desk", "cable", "chair"],
    "total": [50.0, 300.0, 40.0, 300.0],
})

orders["shipping"] = np.where(orders["total"] >= 100, 0, 7.5)

conditions = [
    orders["total"] >= 250,
    orders["total"] >= 50,
]
choices = ["large", "medium"]
orders["size"] = np.select(conditions, choices, default="small")
print(orders)

    item  total  shipping    size
0  mouse   50.0       7.5  medium
1   desk  300.0       0.0   large
2  cable   40.0       7.5   small
3  chair  300.0       0.0   large

For numeric ranges, pd.cut is even clearer: give it bin edges and labels. Note that bins are right-inclusive by default, so (0, 50] includes 50.

import pandas as pd

scores = pd.Series([35, 50, 72, 88, 99], name="score")
grades = pd.cut(scores, bins=[0, 50, 75, 90, 100], labels=["F", "C", "B", "A"])
print(pd.DataFrame({"score": scores, "grade": grades}))

   score grade
0     35     F
1     50     F
2     72     C
3     88     B
4     99     A

map: lookups and one-to-one replacements

Series.map takes a dictionary (or another Series) and replaces each value with its match. It is the natural way to translate codes into labels or to attach a value from a small lookup table. Anything not found in the dictionary becomes missing, which is a handy way to spot unexpected values.

import pandas as pd

users = pd.DataFrame({"name": ["Ada", "Grace", "Linus", "Ken"],
                      "country": ["GB", "US", "FI", "BR"]})

region = {"GB": "EMEA", "FI": "EMEA", "US": "AMER"}
users["region"] = users["country"].map(region)
print(users)
print(users["region"].isna().sum(), "unmapped")

    name country region
0    Ada      GB   EMEA
1  Grace      US   AMER
2  Linus      FI   EMEA
3    Ken      BR    NaN
1 unmapped

If you want unknown values to stay as they were, use .replace(region) instead, or fill afterwards with .map(region).fillna("OTHER"). For lookups against a larger table with several columns, a merge (covered in the grouping lesson) is the better tool.

assign: transformations you can chain

df.assign() returns a new DataFrame with extra columns, so it fits into method chains. Passing a lambda lets a new column refer to one created earlier in the same call.

import pandas as pd

orders = pd.DataFrame({"price": [25.0, 300.0, 8.0], "qty": [2, 1, 5]})

result = (
    orders
    .assign(
        total=lambda d: d["price"] * d["qty"],
        is_big=lambda d: d["total"] > 45,
    )
    .sort_values("total", ascending=False)
)
print(result)

   price  qty  total  is_big
1  300.0    1  300.0    True
0   25.0    2   50.0    True
2    8.0    5   40.0   False

apply: the flexible last resort

Series.apply(func) calls a Python function on every value, and df.apply(func, axis=1) calls it on every row. That flexibility is useful when the logic truly cannot be vectorized, for example calling a parser from another library. But it is a Python loop in disguise, and on large data it is slow.

import time
import numpy as np
import pandas as pd

df = pd.DataFrame({"price": np.arange(200_000) % 500 + 1.0,
                   "qty": np.arange(200_000) % 7 + 1})

start = time.perf_counter()
slow = df.apply(lambda row: row["price"] * row["qty"], axis=1)
t_apply = time.perf_counter() - start

start = time.perf_counter()
fast = df["price"] * df["qty"]
t_vec = time.perf_counter() - start

print("same result:", slow.equals(fast))
print("vectorized is faster:", t_vec < t_apply)

same result: True
vectorized is faster: True

On a typical laptop the row-wise apply above takes around a second while the vectorized version takes about a millisecond. The exact numbers vary by machine, but the gap is usually two or three orders of magnitude.

A good use of apply is on a small result, such as a grouped summary with a few dozen rows, or on a column of values that need a function from another library. Keep it off the hot path of million-row tables.

Recap

• Vectorized column expressions run in compiled code and are the default choice.
• Use np.where for two outcomes, np.select for several, and pd.cut for numeric bins.
• map translates values with a dictionary; unmatched values become missing.
• assign with lambdas keeps multi-step transformations readable and chainable.
• apply is a Python loop; use it for small data or logic that truly cannot be vectorized.

# Write your solution here