In pandas, how do you set a column's value only on the rows that match a condition, such as flagging suspicious payments?
answer
- one accessor call, two axes
- rows first, then the column label
- mask as the row selector
- new column: initialise before the masked write
basics
~20 sUse one loc call with the boolean mask as the row selector and the column label as the column selector: df.loc[df['amount'] > 5000, 'is_fraud'] = True. Initialise a new column first so its dtype stays clean.
solid answer
~40 sI write it as a single `.loc` call: `df.loc[mask, 'is_fraud'] = True`, where `mask` is a boolean Series such as `df['amount'] > 5000`. Rows come first, the column label second, and pandas writes only where the mask is True. I avoid the two-step form `df[mask]['is_fraud'] = True`: the first step builds a filtered object and the second step writes into that, so `df` never changes. If the column does not exist yet, the masked write creates it and fills the unmatched rows with NaN, which turns a True/False flag into an `object` column. So I write `df['is_fraud'] = False` first and then the masked `loc` write, and the column stays `bool`. The same call takes a list of columns, `df.loc[mask, ['fee', 'amount']] = 0`.
code
python · 18 linesimport pandas as pd
payments = pd.DataFrame(
{"amount": [120.0, 9800.0, 45.5, 15000.0], "fee": [3, 25, 1, 40]},
index=pd.Index([101, 102, 103, 104], name="payment_id"),
)
large = payments["amount"] > 5000
# Created by the masked write: unmatched rows become NaN
wide = payments.copy()
wide.loc[large, "is_fraud"] = True
print(wide["is_fraud"].dtype) # object
# Initialised first: the column stays bool
payments["is_fraud"] = False
payments.loc[large, "is_fraud"] = True
print(payments["is_fraud"].dtype) # bool
print(payments["is_fraud"].tolist()) # [False, True, False, True]go deeper
Recall the shape df.loc[mask, 'col'] = value: rows first, column label second, one call. Know that the two-step df[mask]['col'] form leaves the frame unchanged.
Explain what the masked write does to a column it creates: unmatched rows get NaN, so a bool flag becomes object and an integer becomes float64. Initialise the column first.
In review, catch the two-step write and the masked creation of a flag column, and point to the downstream symptoms: object flags that filter and sum badly, and float IDs or counts.
Set a team convention that every conditional write is one loc call on an initialised column, so flag dtypes stay predictable across notebooks and pipeline steps.
## The one-call pattern A **conditional write** in pandas sets values on the rows where a condition holds and leaves the other rows alone. The idiom is a single `.loc` call that names both axes at once: ```python df.loc[df["amount"] > 5000, "is_fraud"] = True ``` - The **first** position of `.loc[...]` selects rows. Here it receives a **boolean mask**: a Series of True/False values with the same index as `df`. - The **second** position selects columns, by label: `"is_fraud"`. - The right-hand side is the value to write. A scalar is broadcast to every selected cell. Because the selection and the write happen in **one** indexing operation, pandas writes into `df` itself. That is the whole point of the pattern. ## What goes wrong with two steps A common first attempt splits the work into two indexing steps: ```python df[df["amount"] > 5000]["is_fraud"] = True # does not change df ``` `df[mask]` builds a new, filtered DataFrame. The second step, `["is_fraud"] = True`, then writes into that filtered object, which is thrown away at the end of the line. `df` is untouched. Why pandas treats every intermediate result this way, and the warning it prints, belongs to the lesson on pandas Copy-on-Write; the practical rule here is simply **one `.loc` call per write**. Other near misses: 1. `df.iloc[mask, "is_fraud"] = True` fails, because `iloc` takes positions and `"is_fraud"` is a label, so pandas raises `TypeError`. 2. `df.loc["is_fraud", mask] = True` swaps the axes: `.loc` always reads rows first, so this raises instead of writing. 3. Looping over rows and writing one cell at a time works but is slow and hard to read; the mask expresses the same rule in one vectorised step. ## The dtype of a column the write creates If `is_fraud` does not exist yet, the masked write **creates** it. Every row the mask did not select gets a missing value, and the column's dtype must be able to hold that missing value next to the written one: | Written value | Unmatched rows | Resulting dtype | |---|---|---| | `True` | NaN | `object` | | `1` | NaN | `float64` | | `2.5` | NaN | `float64` | A NumPy `bool` or `int64` column cannot hold NaN, so pandas widens the new column. An `object` column of `True` and NaN then misbehaves later: summing it, filtering on it or saving it gives results nobody intended. The fix is to **create the column with a default for every row first**, then apply the masked write: ```python df["is_fraud"] = False df.loc[df["amount"] > 5000, "is_fraud"] = True # stays bool ``` Now every row already holds a `bool`, the masked write stores `True` into a `bool` column, and nothing needs widening. ## Variations on the same call - **Several columns:** `df.loc[mask, ["fee", "amount"]] = 0` writes the scalar into every selected cell of both columns. - **A combined condition:** build the mask first, `mask = (df["amount"] > 5000) & (df["country"] != "DE")`, and pass it in. How to combine masks is covered with pandas boolean masks. - **A computed value:** the right-hand side can be a Series, such as `df.loc[mask, "amount"] = df["amount"] * 0.98`. `.loc` matches that Series to the rows by index label, not by position. - **One known cell:** `df.at[102, "is_fraud"] = True` sets a single cell by row label and column label. ## A whole-column rule is an ordinary assignment When every row gets a value derived from a condition, write the whole column once instead of using a mask: `df["is_large"] = df["amount"] > 5000` produces a `bool` column directly. The masked `loc` write is for changing **some** rows of a column and leaving the rest as they are. ## Checking that the write landed A conditional write fails quietly more often than it fails loudly, so check it right after it runs: 1. **Count the flags.** `df["is_fraud"].sum()` should equal `mask.sum()` when the column was initialised to `False`; a smaller number means rows were missed. 2. **Check the dtype.** `df["is_fraud"].dtype` should be `bool`. An `object` dtype is the sign of a column created by the masked write itself. 3. **Check the other rows.** `df.loc[~mask, "is_fraud"].any()` should be `False`, which confirms the write did not spill past the mask. 4. **Check the original frame.** When the code was refactored into helper functions, confirm the write targets the frame you later read, not a filtered object created along the way. These checks are cheap, and they turn a silent no-op into a failed assertion during development.
- Why does a masked `loc` write that creates a new integer column give `float64` instead of `int64`?The rows the mask did not select need a missing value, and a NumPy `int64` column cannot hold NaN. pandas therefore builds the new column as `float64`, with NaN in the unmatched rows and the written integers stored as floats. Initialising the column with a default integer first, then applying the masked write, keeps it `int64`.
- When would you write `df['flag'] = condition` instead of a masked `loc` write?When every row's value follows from the condition. `df['is_large'] = df['amount'] > 5000` assigns a whole `bool` column in one step. The masked `loc` write is for changing only the selected rows and keeping the existing values in the rest, for example flagging new suspects without clearing earlier flags.
- Writes df[mask]['col'] = value and expects df to change
- Uses iloc with a column name for a conditional write
- Thinks loc takes the column first and the rows second
- Loops over rows with a per-cell write instead of using a mask
- Expects a new flag column created by a masked write to be bool