Vectorized Pandas: Replacing Loops With Set-Based Thinking

The loop that shouldn't be there

Analysts arriving at pandas from Excel or plain Python write code that looks like this:

margins = []
for i in range(len(df)):
    row = df.iloc[i]
    if row['revenue'] > 0:
        margins.append((row['revenue'] - row['cost']) / row['revenue'])
    else:
        margins.append(0)
df['margin'] = margins

It is correct. On 50,000 rows it takes about eight seconds. The equivalent vectorized version takes about four milliseconds:

df['margin'] = np.where(
    df['revenue'] > 0,
    (df['revenue'] - df['cost']) / df['revenue'],
    0,
)

Two thousand times faster, and shorter. The speedup isn't the interesting part, though — the interesting part is why, because once you understand the reason you stop writing the first version by instinct.

Why the loop is slow

A pandas column is a NumPy array: one contiguous block of memory holding values of a single type. Operations on it run in compiled C over that whole block.

df.iloc[i] breaks that. It constructs a new Series object for every row, boxing each value into a Python object with its own type checks and reference counting. You pay that construction cost 50,000 times, and every arithmetic operation happens in the interpreter rather than in C.

So the rule is not "loops are slow." It's: every time you cross from the array into Python object space, you pay. Vectorizing means staying on the array side.

Callout: df.iterrows() has the same problem and is the most common way people hit it. itertuples() is roughly ten times faster because it doesn't build a Series per row — but it's still a Python-level loop, so it's a fallback, not a fix.

Conditionals become masks

The mental shift is to stop thinking "for each row, decide" and start thinking "for each condition, select."

A boolean mask is just an array of True/False the same length as your data:

high_value = df['revenue'] > 1000

You combine masks with &, |, and ~ — not and, or, not, which only work on single values:

target = (df['revenue'] > 1000) & (df['country'] == 'BD')
df.loc[target, 'segment'] = 'priority'

The parentheses are mandatory. & binds tighter than > in Python, so without them the expression is parsed wrongly and you get a confusing error.

For two outcomes, use np.where. For more than two, np.select keeps things readable:

conditions = [
    df['revenue'] >= 10_000,
    df['revenue'] >= 1_000,
    df['revenue'] > 0,
]
labels = ['enterprise', 'mid_market', 'smb']
 
df['tier'] = np.select(conditions, labels, default='inactive')

Conditions are evaluated in order and the first match wins, exactly like a SQL case expression. That ordering matters: put the most specific condition first, or it will never be reached.

Lookups become joins

This pattern shows up constantly:

def get_region(country):
    return region_lookup.get(country, 'Unknown')
 
df['region'] = df['country'].apply(get_region)

apply is a Python loop wearing a costume. For a straight key-to-value mapping, map does it natively:

df['region'] = df['country'].map(region_lookup).fillna('Unknown')

And when the lookup is a DataFrame rather than a dict, it's a merge:

df = df.merge(regions, on='country', how='left')

If you find yourself writing a function whose body is a dictionary lookup or a table lookup, you're writing a join by hand.

Group-wise math without the loop

Here's the pattern that trips up people who are otherwise comfortable with groupby. You want each row's share of its country's total:

totals = {}
for country in df['country'].unique():
    totals[country] = df[df['country'] == country]['revenue'].sum()
df['share'] = df.apply(lambda r: r['revenue'] / totals[r['country']], axis=1)

That's a full table filter per country, then a row-wise apply. transform does the whole thing in one pass:

df['share'] = df['revenue'] / df.groupby('country')['revenue'].transform('sum')

The distinction worth memorizing: agg collapses, transform broadcasts. agg gives you one row per group. transform gives you back a result aligned to the original index, so it drops straight into a column. It's the same idea as a SQL window function versus a group by.

transform also handles running calculations and group-relative ranking:

df['running_total'] = df.groupby('country')['revenue'].cumsum()
df['rank_in_country'] = df.groupby('country')['revenue'].rank(ascending=False)

Strings and dates vectorize too

Both have accessors that operate on the whole column in one go:

df['domain'] = df['email'].str.split('@').str[1]
df['clean'] = df['name'].str.strip().str.lower()
df['is_gov'] = df['domain'].str.endswith('.gov.bd')
 
df['date'] = pd.to_datetime(df['date_string'], format='%Y-%m-%d')
df['month'] = df['date'].dt.to_period('M')
df['is_weekend'] = df['date'].dt.dayofweek >= 5

One detail worth the keystrokes: pass an explicit format to to_datetime. Without it pandas infers, which is slower and — with ambiguous dates like 03/04/2025 — can silently pick the wrong interpretation on some rows.

When a loop is genuinely fine

Vectorization is a default, not a religion.

Keep the loop when the operation is inherently sequential and each step depends on the previous result in a way cumsum and friends can't express. Keep it when you're iterating over a handful of groups rather than thousands of rows — looping over 12 months to write 12 files is fine. And keep it when the vectorized version would be so contorted that nobody could maintain it; a readable apply over 500 rows costs nothing.

The cost only matters at scale, and the scale is set by row count, not by how clever the code looks.

A checklist

When you next open a slow notebook, scan for these:

• iterrows() or range(len(df)) — replace with column operations
• apply(..., axis=1) — usually np.where, np.select, or arithmetic on columns
• apply on a single column doing a dict lookup — that's map
• a loop over unique() values — that's groupby plus transform
• df[df[col] == x] inside a loop — that's a merge or a groupby
• repeated pd.concat inside a loop — build a list, concat once at the end

Fixing those five patterns usually accounts for most of the runtime in an analyst's notebook, and the resulting code is shorter than what it replaced. That's the real argument for vectorizing: the fast version is also the one that reads like what you meant.