Replace Pandas Row Loops Without Changing the Answers

When a DataFrame loop is slow, first identify what the loop does: column arithmetic, conditional assignment, a lookup, a group calculation, or a genuinely sequential operation. For independent row rules, create boolean masks and assign complete columns. Preserve the original rule order, missing-value policy, and row identity before comparing speed.

This tutorial replaces a fictional order-classification loop with a complete vectorized function. The example was executed with pandas 2.2.3 and 3.0.6, using NumPy. It covers overlapping conditions, invalid numbers, nullable values, and nondefault indexes. It makes no universal speedup claim; a tiny teaching fixture cannot predict your production workload.

Choose a replacement that matches the operation

The SQL-to-pandas translation guide introduces the table operations. Here the distinct task is replacing an existing conditional loop without changing its answers. For a grouped result, use the separate agg and transform guide.

Changing iterrows() to apply(..., axis=1) still invokes a row function. It can improve readability without removing the same work. The pandas performance guide recommends simplifying Python work and considering vectorization before compiled extensions. Profile the complete pipeline, including file loading and joins, so you optimize the part that consumes the time.

Write the business rule before the implementation

Our fictional input has one row per order. Its amount arrives as text. We want these decisions, in this order:

1. An unknown or missing status requires review, regardless of amount.
2. A recognized status with an absent, unparseable, infinite, or negative amount requires review.
3. A valid refunded order remains refunded, even when its amount meets the priority threshold.
4. A valid paid order of at least 100 is priority; other valid paid orders are standard.

Zero is a valid amount. A blank is unknown, not zero. This example accepts ordinary numeric strings and scientific notation that pandas can parse, but rejects nonfinite results. If your feed forbids scientific notation, currency symbols, or separators, define that input contract and add a format check before numeric conversion. Do not silently rewrite ambiguous currency strings.

The review reason follows the same precedence: unknown status first, then invalid amount, then negative amount. Keeping the reason separate from the decision makes an unexpectedly large review queue explainable.

Copy the complete vectorized implementation

Run this block in a fresh Python session. It validates required columns and order keys, copies the input, normalizes text once, and builds masks from the same index. The raw amount remains available for investigation.

import numpy as np
import pandas as pd


def classify_orders(frame):
    required = {"order_id", "status", "amount_raw"}
    if not frame.columns.is_unique:
        raise ValueError("Duplicate column names")
    if not required.issubset(frame.columns):
        raise ValueError("Missing required columns")
    if frame["order_id"].isna().any() or not frame["order_id"].is_unique:
        raise ValueError("Each order_id must be present and unique")

    out = frame.copy()
    status = out["status"].astype("string").str.strip().str.casefold()
    amount = pd.to_numeric(
        out["amount_raw"].astype("string").str.strip(), errors="coerce"
    ).astype("Float64")
    finite = np.isfinite(amount.to_numpy(dtype="float64", na_value=np.nan))
    valid_amount = amount.notna() & finite
    known_status = status.isin(["paid", "refunded"])
    nonnegative = amount.ge(0).fillna(False)
    usable = known_status & valid_amount & nonnegative
    paid = usable & status.eq("paid").fillna(False)
    refunded = usable & status.eq("refunded").fillna(False)

    decision = pd.Series("review", index=out.index, dtype="string")
    decision.loc[paid] = "standard"
    decision.loc[paid & amount.ge(100).fillna(False)] = "priority"
    decision.loc[refunded] = "refunded"

    reason = pd.Series("", index=out.index, dtype="string")
    reason.loc[~known_status] = "unknown_status"
    reason.loc[known_status & ~valid_amount] = "missing_or_invalid_amount"
    reason.loc[known_status & valid_amount & ~nonnegative] = "negative_amount"
    return out.assign(amount=amount, decision=decision, review_reason=reason)


orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105, 106, 107, 108],
    "status": ["paid", " PAID ", "refunded", "paid", "pending", "paid", "paid", "paid"],
    "amount_raw": ["99.99", "100", "100", "bad", "50", None, "-1", "0"],
}, index=[10, 20, 30, 40, 50, 60, 70, 80])

result = classify_orders(orders)
print(result[["order_id", "decision", "review_reason"]].to_string(index=False))

to_numeric(errors="coerce") turns conversion failures into missing numeric values. That conversion is a detection step: the function retains the original string and routes bad values to review. It does not treat a successful call as proof that every input was acceptable.

Each .loc assignment handles a whole matching set. Standard paid orders are assigned first and priority paid orders overwrite that subset. The refunded mask is separate, preventing a large refund from becoming a priority sale. The initial review default catches all rows that fail validation.

Check the exact expected answers

The reconciliation is eight source orders, eight output orders, four review orders, two standard orders, one priority order, and one refund. The review reasons reconcile to two invalid or missing amounts, one unknown status, and one negative amount. No rows disappear because a rule did not match.

expected = ["standard", "priority", "refunded", "review",
            "review", "review", "review", "standard"]
assert result["decision"].tolist() == expected
assert len(result) == len(orders)
assert result.index.equals(orders.index)
assert result["order_id"].equals(orders["order_id"])
assert result["decision"].value_counts().to_dict() == {
    "review": 4, "standard": 2, "priority": 1, "refunded": 1,
}

These are tutorial checks. Use explicit exceptions or a test framework for required production gates; Python can remove assert statements under optimization.

Protect indexes, nulls, and condition precedence

Constructing pd.Series("review", index=out.index) matters. A new Series with a default zero-based index can align to different labels when assigned back after filtering. This fixture deliberately starts at labels 10, 20, and so on. Resetting an index can be valid, but should be an explicit identity decision.

Nullable comparisons can produce an unknown boolean value. The function applies fillna(False) where a condition must mean “this row definitely qualifies.” It also checks numeric validity separately, so a missing amount cannot fall through to a standard paid result.

For SQL-style first-match rules, either make masks mutually exclusive or mark rows already assigned. Successive assignments otherwise give the last matching rule priority. Test a row satisfying multiple conditions: the refunded order at 100 protects exactly that boundary.

Do not mutate the row returned by iterrows(). Its official API documentation explains that row iteration can change dtype representation and writing to the iterated row is unreliable. Build the output column and assign it deliberately.

Validate the rewrite before measuring it

Use the existing implementation as one reference, then add independently stated expected answers. Agreement between two implementations is insufficient if both encode the same misunderstood rule. Include the exact threshold, values immediately around it, zero, negatives, missing fields, invalid text, infinities, a refund over the threshold, unknown statuses, and an empty input.

This tutorial's verification compared the fixture and 18 boundary rows against a separate scalar rule, checked that the source frame remained unchanged, preserved nondefault and duplicate index labels, and exercised invalid keys and missing columns. Those checks establish the example's behavior, not the performance of your own pipeline.

For your timing experiment, load a representative input once, run both functions repeatedly against independent copies if they mutate, and compare the complete output before retaining timings. Include parsing and validation in both measurements. Report pandas version, row count, dtypes, tested hardware, and whether loading was included. Measure memory as well when several masks and copied columns are large.

Troubleshoot the result you actually observe

• Everything requires review: inspect normalized statuses and the raw failed amounts. Expect known statuses to be exactly paid or refunded. Fix an explicit feed mapping rather than broadening the rule blindly.
• Priority counts changed: inspect orders at 100 and refunds at or above 100. Check comparison direction and assignment order before blaming floating-point speed.
• Values become missing after assignment: compare the output Series index to the target frame's index. Build both from the same frame, or use an intentional positional assignment only after proving matching length and order.
• The rewrite is still slow: profile parsing, object/string operations, joins, and file I/O separately. Vectorized-looking syntax alone is not a timing result.

FAQ

Is every Python loop over a DataFrame wrong?

No. External API calls, stateful recurrences, and small irregular jobs can justify a loop. If iteration is required, select only needed columns, consider itertuples(), collect results, and assign once. Preserve error handling and rate limits for external calls.

Should I replace every apply call with NumPy?

No. Start with the operation that expresses the rule clearly. A dictionary lookup can use Series.map; arithmetic can stay in Series operations. Moving to arrays can discard index alignment and requires deliberate handling of nullable dtypes.

Why can a faster version still be the wrong answer?

It may change first-match precedence, fill unknown values with zero, align values to the wrong index, or omit rejected records. Compare row identity, every output decision, reject counts, and boundary results before accepting a timing improvement.