How would you rewrite a row-by-row pandas transformation, and what is SettingWithCopyWarning telling you?

Where the time is going

The set-up an interviewer usually hands you is a few million rows and a transformation written as df.apply(f, axis=1) or a for _, row in df.iterrows() loop. The cost is not that Python is a slow language in the abstract; it is that both constructs enter the interpreter once per row, and everything they touch is a boxed Python object rather than a machine word.

iterrows() is the worse of the two for a reason worth being able to state. It materialises each row as a Series, and a row spanning an int column and a string column has no common dtype, so the row comes back as object. Your int64 values arrive as Python ints, your float64 values as Python floats, and any arithmetic downstream runs on objects. You lose dtype fidelity as well as speed, which is how an iterrows refactor occasionally changes results rather than just timings.

A whole-column expression pays the interpreter once for the whole column and then runs a tight C loop over a contiguous typed buffer. That is the whole of the speed argument, and it is why the useful question is not "is there a loop in my code" but "how many times does the interpreter run per row". Series.apply has no visible loop and still runs one Python call per element. df.apply(..., axis=1) is the same thing with more overhead. Neither is vectorised in any sense that matters.

The rewrites that cover most of what gets asked

A conditional column becomes np.where for one branch, np.select for several, or Series.mask if you prefer the pandas idiom. A value derived per group becomes df.groupby(key)[col].transform("mean") rather than a loop over groupby output, because transform broadcasts the group result back to the original index in one pass. A lookup against a second frame becomes a merge, or Series.map against an indexed Series, rather than a loop with .loc inside it - the loop version turns an O(n) hash join into n separate index lookups. Anything cumulative becomes cumsum, cumcount, shift, or rolling.

import numpy as np

# Slow: one interpreter round-trip per row, and each row is object dtype
# because "tier" and "amount" have no common type.
df["fee"] = df.apply(
    lambda r: r["amount"] * 0.02 if r["tier"] == "basic" else 0.0, axis=1
)

# Fast: two full-column C loops and one branchless select. No per-row Python.
df["fee"] = np.where(df["tier"] == "basic", df["amount"] * 0.02, 0.0)

String work is the honest exception to mention unprompted. The .str accessor on object dtype loops element-wise underneath, so .str.contains is not the same kind of win as np.where. Since pandas 2.0 you can ask for Arrow-backed strings with dtype="string[pyarrow]", or read a whole frame that way with dtype_backend="pyarrow", which pushes many string operations into C++ instead. And a genuinely recursive calculation - where row n depends on the computed value of row n-1 - does not vectorise at all. Saying so, and then doing the loop over NumPy arrays rather than over a DataFrame, is a better answer than pretending otherwise.

What the warning is reporting

SettingWithCopyWarning is not a style complaint and suppressing it is not a fix. It fires when you assign into an object pandas cannot prove is the frame you think it is. The canonical shape is chained indexing:

# Two separate operations. The first returns a new object; the second
# assigns into that object. Whether df changes is not something you
# should have to reason about.
df[df["score"] > 90]["grade"] = "A"

# One indexing operation, so the target of the write is unambiguous.
df.loc[df["score"] > 90, "grade"] = "A"

Read the warning as a sentence: this write may have gone into a temporary and left the original untouched. Whether it did depends on whether the intermediate was a view or a copy, which depends on dtypes and internal block layout. That is the real problem - not the performance of the extra copy, but that the correctness of your assignment is decided by an implementation detail. The fix is either one indexing call, as above, or an explicit .copy() if you genuinely wanted a detached frame, at which point the warning stops because you have declared the intent.

Copy-on-Write settles the ambiguity. It is opt-in in pandas 2.x via pd.options.mode.copy_on_write = True and is documented as becoming the default in pandas 3.0. Under it, every indexing result behaves as its own object, so chained assignment never reaches the parent - deterministically wrong rather than unpredictably wrong - and pandas raises ChainedAssignmentError instead of a warning you can filter away.

Sizing the frame before recommending a cluster

Do the arithmetic out loud, because the answer changes with it. Twenty million rows across fifty int64 columns is 20,000,000 x 50 x 8 bytes = 8,000,000,000 bytes, so 8 GB before a single string column. Most pandas operations return a new frame rather than mutating in place, so peak residency during the transformation is roughly twice the frame. On a 16 GB machine that workload is already at the edge, and the failure will look like swapping rather than an exception.

Three moves come before reaching for Spark. Read less: Parquet supports column projection and predicate pushdown, so columns= and filters= on the read mean the 8 GB never materialises. Represent it better: downcast where the range allows and make low-cardinality string columns categorical, which stores small integer codes plus one dictionary. Then push the aggregation into an engine built for it - DuckDB querying the Parquet files directly, or Polars' lazy frames - both of which are still one machine but multi-threaded and able to stream data larger than memory. Spark earns its operational cost when the data does not fit one machine at all, or when it already lives in a cluster and moving it out is the expensive part.

Vectorisation is not about avoiding the for keyword, it is about how many times the interpreter runs per row. Once you can state that, the right rewrite for each case follows without memorising a list.