Pandas where(): Keep Valid Values and Replace Others
Use where() to keep values where a condition is True and replace the others. Choose the target columns carefully, handle unknown conditions explicitly and retain raw values for review.
Keep True values and replace False values 🔝
where(cond, other) keeps values where cond is True and replaces them where it is False. It preserves the shape. To select rows, use loc. mask() replaces True values instead. Current arguments are cond, other, inplace, axis and level; omit obsolete errors and try_cast arguments.
Apply a row condition to all columns 🔝
This student example keeps only the row with MATH greater than 80 and replaces cells in other rows. Provide dtype-compatible replacements: a text marker for NAME and -9 for numeric columns. IDs also change, so use a column-specific operation when identifiers must be preserved.
import pandas as pd
def student_data():
return pd.DataFrame({"NAME": ["Ravi", "Raju", "Alex", "Ron", "King", "Jack"], "ID": [1, 2, 3, 4, 5, 6], "MATH": [80, 40, 70, 70, 82, 30], "ENGLISH": [81, 70, 40, 50, 60, 30]})
df = student_data()
other = pd.DataFrame({"NAME": ["review"] * 6, "ID": [-9] * 6, "MATH": [-9] * 6, "ENGLISH": [-9] * 6}, index=df.index)
result = df.where(df["MATH"].gt(80), other)
print(result)
assert result.loc[4].tolist() == ["King", 5, 82, 60]
assert result.loc[0].tolist() == ["review", -9, -9, -9]
assert result.shape == df.shapeExpected output
NAME ID MATH ENGLISH
0 review -9 -9 -9
1 review -9 -9 -9
2 review -9 -9 -9
3 review -9 -9 -9
4 King 5 82 60
5 review -9 -9 -9Omit other to introduce missing values 🔝
The condition is still required. Omitting other replaces False positions with the dtype-appropriate missing value. Nullable integer columns can retain their integer dtype.
df = student_data().astype({"ID": "Int64", "MATH": "Int64", "ENGLISH": "Int64"})
result = df.where(df["MATH"].gt(80))
print(result)
assert result.loc[4, "MATH"] == 82
assert result["MATH"].count() == 1
assert str(result["MATH"].dtype) == "Int64"Expected output
NAME ID MATH ENGLISH
0 NaN <NA> <NA> <NA>
1 NaN <NA> <NA> <NA>
2 NaN <NA> <NA> <NA>
3 NaN <NA> <NA> <NA>
4 King 5 82 60
5 NaN <NA> <NA> <NA>Use a string marker for display 🔝
If every column must display an asterisk, create a separate string table explicitly. Do not mix display markers into numeric data used for calculations. The original table retains numeric scores.
df = student_data()
display = df.astype("string").where(df["MATH"].gt(80), "*")
print(display)
assert display.loc[0].tolist() == ["*"] * 4
assert display.loc[4, "MATH"] == "82"
assert df["MATH"].sum() == 372Expected output
NAME ID MATH ENGLISH
0 * * * *
1 * * * *
2 * * * *
3 * * * *
4 King 5 82 60
5 * * * *Change only one column 🔝
Assign the returned Series to MATH. NAME, ID and ENGLISH remain intact. For this demonstration, -9 is a marker; for real scores, missing values often express an excluded reading more clearly.
df = student_data()
df["MATH"] = df["MATH"].where(df["MATH"].gt(80), -9)
print(df)
assert df["MATH"].tolist() == [-9, -9, -9, -9, 82, -9]
assert df["ID"].tolist() == [1, 2, 3, 4, 5, 6]Expected output
NAME ID MATH ENGLISH
0 Ravi 1 -9 81
1 Raju 2 -9 70
2 Alex 3 -9 40
3 Ron 4 -9 50
4 King 5 82 60
5 Jack 6 -9 30Combine conditions to describe values to keep 🔝
Use parentheses and & for AND. This fresh table has Alex at 72. The condition keeps values strictly above 70 and below 75; it does not replace values inside that interval.
df = student_data()
df.loc[2, "MATH"] = 72
keep = (df["MATH"] > 70) & (df["MATH"] < 75)
display = df.astype("string").where(keep, "*")
print(display)
assert display.loc[2].tolist() == ["Alex", "3", "72", "40"]
assert display.loc[3].tolist() == ["*"] * 4Expected output
NAME ID MATH ENGLISH
0 * * * *
1 * * * *
2 Alex 3 72 40
3 * * * *
4 * * * *
5 * * * *Understand copies and inplace updates 🔝
Without inplace=True, capture the result to use it; the source remains unchanged. Call inplace on the object you intend to change. Avoid chained column edits; assign a returned Series back to a DataFrame column.
df = student_data()
result = df.where(df["MATH"].gt(80))
print("Source score:", df.loc[0, "MATH"])
print("Result is missing:", pd.isna(result.loc[0, "MATH"]))
scores = pd.Series([80, 40, 72, 70, 82, 30], dtype="Int64")
keep = scores.gt(70) & scores.lt(75)
scores.where(keep, inplace=True)
print(scores)
assert df.loc[0, "MATH"] == 80
assert scores.dropna().tolist() == [72]Expected output
Source score: 80
Result is missing: True
0 <NA>
1 <NA>
2 72
3 <NA>
4 <NA>
5 <NA>
dtype: Int64Align replacement values by index label 🔝
A Series replacement aligns by labels. axis="index" specifies row alignment for a DataFrame. Cover all affected labels; otherwise missing replacement labels can produce missing values.
df = pd.DataFrame({"amount": [10, -2, -3]}, index=["A", "B", "C"])
other = pd.Series([300, 200, 100], index=["C", "B", "A"])
result = df.where(df.ge(0), other, axis="index")
print(result)
assert result["amount"].tolist() == [10, 200, 300]Expected output
amount
A 10
B 200
C 300Choose a policy for missing conditions 🔝
A nullable Boolean condition can contain pd.NA. Decide what an unknown condition means explicitly: fillna(False) sends it to the replacement; fillna(True) retains the source. These are data rules, not interchangeable fixes.
values = pd.Series([10, 20, 30], dtype="Int64")
condition = pd.Series([True, pd.NA, False], dtype="boolean")
replace_unknown = values.where(condition.fillna(False), -1)
keep_unknown = values.where(condition.fillna(True), -1)
print("Replace unknown:", replace_unknown.tolist())
print("Keep unknown:", keep_unknown.tolist())
assert replace_unknown.tolist() == [10, -1, -1]
assert keep_unknown.tolist() == [10, 20, -1]Expected output
Replace unknown: [10, -1, -1]
Keep unknown: [10, 20, -1]Use a callable or compare with mask() 🔝
A callable receives the object being processed and must not mutate it. For a complete Boolean condition, where(valid) equals mask(~valid).
values = pd.Series([5, -2, 8], dtype="Int64")
cleaned = values.where(lambda s: s.ge(0))
equivalent = values.mask(values.lt(0))
print(cleaned)
assert cleaned.equals(equivalent)
assert values.tolist() == [5, -2, 8]Expected output
0 5
1 <NA>
2 8
dtype: Int64Validate a synthetic discount extract 🔝
Here the rule is a numeric discount between 0 and 50 percent inclusive. Keep raw values, parse numbers and retain valid values with where(). An invalid or missing discount needs review; do not silently turn it into zero.
raw = pd.DataFrame({"order_id": [101, 102, 103, 104, 105, 106], "discount": ["10", "-5", "60", "bad", None, "0"]})
parsed = pd.to_numeric(raw["discount"], errors="coerce").astype("Float64")
valid = parsed.between(0, 50).fillna(False)
cleaned = raw.copy()
cleaned["discount"] = parsed.where(valid)
accepted = cleaned.loc[valid].copy()
review = raw.loc[~valid].copy()
print("Accepted:")
print(accepted)
print("Review original values:")
print(review)
assert accepted["order_id"].tolist() == [101, 106]
assert review["order_id"].tolist() == [102, 103, 104, 105]
assert raw.loc[1, "discount"] == "-5"Expected output
Accepted:
order_id discount
0 101 10.0
5 106 0.0
Review original values:
order_id discount
1 102 -5
2 103 60
3 104 bad
4 105 NaNAudit the result before export 🔝
Count parsing failures separately from out-of-range numbers. Together they explain this example’s review rows. Use count() to count available cleaned values and Input and Output for file handling.
unparseable = parsed.isna()
outside_range = parsed.notna() & ~parsed.between(0, 50).fillna(False)
print("Unparseable or missing:", int(unparseable.sum()))
print("Outside allowed range:", int(outside_range.sum()))
print("Review rows:", int((~valid).sum()))
print(accepted.to_csv(index=False).rstrip())
assert int(unparseable.sum()) == 2
assert int(outside_range.sum()) == 2
# Optional Colab export:
# accepted.to_csv("accepted_discounts.csv", index=False)Expected output
Unparseable or missing: 2
Outside allowed range: 2
Review rows: 4
order_id,discount
101,10.0
106,0.0Common questions 🔝
Why did the wrong values change? where() keeps True values; mask() replaces True values. Why are rows still present? where() preserves shape; use loc to filter. Why did the ID change? The condition was applied to the entire DataFrame. Why does a string marker affect calculations? Numeric data and display text require different types; keep a separate display table. Why are replacements misplaced? Check index labels and alignment. Should missing discounts become zero? Only if your data rules establish that they mean no discount. See fillna() for explicit filling and astype() for dtype choices.
Exercise: keep valid stock counts 🔝
For stock counts 12, -3, 0 and 25, keep values from 0 through 20 inclusive using nullable integers. Replace other values with missing values, preserve the source and predict the available count.
Exercise solution 🔝
The zero is valid; negative stock and stock above the stated limit need review.
stock = pd.Series([12, -3, 0, 25], dtype="Int64")
answer = stock.where(stock.between(0, 20))
print(answer)
print("Available counts:", answer.count())
assert answer.dropna().tolist() == [12, 0]
assert answer.count() == 2
assert stock.tolist() == [12, -3, 0, 25]Expected output
0 12
1 <NA>
2 0
3 <NA>
dtype: Int64
Available counts: 2Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change the replacement rules and inspect the cleaned values and review rows. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue with the Data Cleaning hub, mask(), loc, at, NumPy where(), fillna() and iloc.
Reference: Pandas DataFrame.where documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials