Pandas Filter Rows: AND, OR, NOT and Practical Conditions
Build Boolean masks to filter rows by numeric, text and membership rules. Combine comparisons with &, | and ~, and decide how unknown values should be handled.
Show Table of Contents ↓Create the student table 🔝
Build conditions from the table being filtered. Keep source values unchanged and store selections separately.
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": [30, 40, 50, 60, 70, 80],
"ENGLISH": [20, 30, 40, 50, 60, 70]
})
df = student_data()
print(df)Expected output
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30
2 Alex 3 50 40
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70Filter with one comparison 🔝
A comparison returns a Boolean Series. Apply it with loc to keep matching rows.
df = student_data()
mask = df["MATH"] >= 50
print(mask.tolist())
print(df.loc[mask])
assert df.loc[mask, "ID"].tolist() == [3, 4, 5, 6]Expected output
[False, False, True, True, True, True]
NAME ID MATH ENGLISH
2 Alex 3 50 40
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70Require both conditions with & 🔝
Wrap each comparison in parentheses. Python and does not combine whole Series. Both subject columns are required for the two-subject rule.
df = student_data()
passed_both = (df["MATH"] >= 50) & (df["ENGLISH"] >= 50)
failed_both = (df["MATH"] < 50) & (df["ENGLISH"] < 50)
print("Passed both:")
print(df.loc[passed_both])
print("Failed both:")
print(df.loc[failed_both])
assert df.loc[passed_both, "ID"].tolist() == [4, 5, 6]
assert df.loc[failed_both, "ID"].tolist() == [1, 2]Expected output
Passed both:
NAME ID MATH ENGLISH
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70
Failed both:
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30Accept either condition with | 🔝
OR keeps a row satisfying at least one condition. It does not require exactly one condition to be true.
df = student_data()
mask = (df["MATH"] >= 50) | (df["ENGLISH"] >= 50)
print(df.loc[mask])
assert df.loc[mask, "ID"].tolist() == [3, 4, 5, 6]Expected output
NAME ID MATH ENGLISH
2 Alex 3 50 40
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70Match an exact mark or exclude marks 🔝
Use == for equality and != for inequality. Membership with isin() expresses exclusion of several specific values clearly.
df = student_data()
print("Exactly 50:")
print(df.loc[df["MATH"] == 50])
mask = (df["MATH"] != 50) & (df["MATH"] != 60)
print("Neither 50 nor 60:")
print(df.loc[mask])
assert mask.equals(~df["MATH"].isin([50, 60]))Expected output
Exactly 50:
NAME ID MATH ENGLISH
2 Alex 3 50 40
Neither 50 nor 60:
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30
4 King 5 70 60
5 Jack 6 80 70Filter on the sum of subjects 🔝
For this complete table, row-wise sum produces the combined mark. For missing subjects, require both known values with min_count=2 rather than silently adding only the known subject.
df = student_data()
total = df[["MATH", "ENGLISH"]].sum(axis=1, min_count=2)
mask = total > 70
print("Total above 70:")
print(df.loc[mask])
print("Not above 70:")
print(df.loc[~mask])
assert df.loc[mask, "ID"].tolist() == [3, 4, 5, 6]Expected output
Total above 70:
NAME ID MATH ENGLISH
2 Alex 3 50 40
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70
Not above 70:
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30Compare two columns 🔝
Both Series come from the same table and align by row label.
df = student_data()
print(df.loc[df["MATH"] > df["ENGLISH"]])
assert (df["MATH"] > df["ENGLISH"]).all()Expected output
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30
2 Alex 3 50 40
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70Filter by text endings 🔝
Specify na=False for missing text to be a nonmatch. Inverting that mask includes missing names; add notna() if you require a known name. See str.contains() for substring filtering.
df = student_data()
ends_x = df["NAME"].str.endswith("x", na=False)
ends_ck = df["NAME"].str.endswith("ck", na=False)
print(df.loc[ends_x])
print(df.loc[ends_x | ends_ck])
assert df.loc[ends_x | ends_ck, "ID"].tolist() == [3, 6]Expected output
NAME ID MATH ENGLISH
2 Alex 3 50 40
NAME ID MATH ENGLISH
2 Alex 3 50 40
5 Jack 6 80 70Match a list with isin() 🔝
isin() tests complete values rather than partial text. Invert its mask with ~ to exclude listed names.
df = student_data()
mask = df["NAME"].isin(["Raju", "King"])
print(df.loc[mask])
print("Other IDs:", df.loc[~mask, "ID"].tolist())
assert df.loc[mask, "ID"].tolist() == [2, 5]Expected output
NAME ID MATH ENGLISH
1 Raju 2 40 30
4 King 5 70 60
Other IDs: [1, 3, 4, 6]Filter with apply() or a text operation 🔝
A callback can express a custom rule. For text length, the dedicated str.len() operation communicates the rule directly. See apply() and str.len().
df = student_data()
custom = df["NAME"].apply(lambda value: len(value) < 4)
direct = df["NAME"].str.len().lt(4)
print(df.loc[custom])
assert custom.equals(direct)
assert df.loc[direct, "ID"].tolist() == [4]Expected output
NAME ID MATH ENGLISH
3 Ron 4 60 50Update all rows or only selected rows 🔝
Assigning a scalar to a column updates every row. For a conditional update use one loc assignment. Changes remain in memory until you explicitly export.
df = student_data()
df["ENGLISH"] = 50
print(df)
conditional = student_data()
conditional.loc[conditional["MATH"].lt(50), "ENGLISH"] = 50
print("Conditional update:")
print(conditional)
assert df["ENGLISH"].eq(50).all()Expected output
NAME ID MATH ENGLISH
0 Ravi 1 30 50
1 Raju 2 40 50
2 Alex 3 50 50
3 Ron 4 60 50
4 King 5 70 50
5 Jack 6 80 50
Conditional update:
NAME ID MATH ENGLISH
0 Ravi 1 30 50
1 Raju 2 40 50
2 Alex 3 50 40
3 Ron 4 60 50
4 King 5 70 60
5 Jack 6 80 70Select columns separately from filtering rows 🔝
DataFrame.filter(items=...) selects column labels, not rows by their values. Direct column-list selection raises an error for absent columns, while filter() keeps only matching labels.
df = student_data()
cols = ["MATH", "ENGLISH"]
selected = df[cols]
print(selected)
print("Columns:", selected.columns.tolist())
print("Rows:", len(selected))
print("Same using filter:", selected.equals(df.filter(items=cols, axis=1)))
assert selected.shape == (6, 2)Expected output
MATH ENGLISH
0 30 20
1 40 30
2 50 40
3 60 50
4 70 60
5 80 70
Columns: ['MATH', 'ENGLISH']
Rows: 6
Same using filter: TrueChoose a policy for missing conditions 🔝
Nullable comparisons can return pd.NA. Here unknown marks are excluded from both accepted and rejected groups and retained for review. Fill missing masks deliberately before inversion.
marks = pd.Series([40, 60, pd.NA], dtype="Int64")
condition = marks.ge(50)
accepted = marks.loc[condition.fillna(False)]
rejected = marks.loc[(~condition).fillna(False)]
review = marks.loc[condition.isna()]
print("Condition:", condition.tolist())
print("Accepted:", accepted.tolist(), "Rejected:", rejected.tolist(), "Review:", review.tolist())
assert len(accepted) + len(rejected) + len(review) == len(marks)Expected output
Condition: [False, True, <NA>]
Accepted: [60] Rejected: [40] Review: [<NA>]Practical example: prepare orders for filtering 🔝
This synthetic table includes invalid amounts and test orders. Convert amounts, then retain invalid values for review. See the Data Cleaning hub.
raw = pd.DataFrame({
"order_id": [101, 102, 103, 104, 105, 106],
"status": ["paid", "paid", "test", "paid", "paid", "paid"],
"region": ["North", "South", "North", "North", "South", "North"],
"amount": ["20", "35", "99", "bad", None, "10"]
})
amount = pd.to_numeric(raw["amount"], errors="coerce")
review_mask = amount.isna()
review = raw.loc[review_mask].copy()
print(review)
assert review["order_id"].tolist() == [104, 105]Expected output
order_id status region amount
3 104 paid North bad
4 105 paid South NaNCombine paid status, regions and amount 🔝
Use named masks so each selection rule is easy to inspect. Selected orders meet every requirement. Other known-amount orders are nonmatches, not necessarily data errors.
paid = raw["status"].eq("paid")
in_region = raw["region"].isin(["North", "South"])
enough = amount.ge(20)
selected_mask = paid & in_region & enough & ~review_mask
selected = raw.loc[selected_mask].copy()
selected["amount"] = amount.loc[selected.index]
other = raw.loc[~selected_mask & ~review_mask].copy()
print(selected)
print("Selected:", len(selected), "Other:", len(other), "Review:", len(review))
print("Selected revenue:", selected["amount"].sum())
assert selected["order_id"].tolist() == [101, 102]
assert len(selected) + len(other) + len(review) == len(raw)
assert selected["amount"].sum() == 55Expected output
order_id status region amount
0 101 paid North 20.0
1 102 paid South 35.0
Selected: 2 Other: 2 Review: 2
Selected revenue: 55.0Export the selection with its scope clear 🔝
Save the selected rows separately from review records. Keep IDs as columns and avoid using a head() preview for a full-selection export. See Input and Output and to_excel().
print(selected.to_csv(index=False).rstrip())
print("Export rows:", len(selected))
assert len(selected) == 2
# Optional Colab exports:
# selected.to_csv("filtered_orders.csv", index=False)
# review.to_csv("review_orders.csv", index=False)Expected output
order_id,status,region,amount
101,paid,North,20.0
102,paid,South,35.0
Export rows: 2Common questions 🔝
Why does and/or fail? Combine Series with & and |. Why are parentheses needed? They separate each comparison from the bitwise operator. Does filter() apply a value condition? It selects labels. Why is a missing row absent? A missing comparison needs an explicit policy. Does filtering reset row labels? No; use reset_index() deliberately. Can I highlight rather than remove rows? Use DataFrame styling for presentation. Continue with query(), where() and mask().
Exercise: paid North orders below 30 🔝
Using the orders table, select paid North orders with a known amount below 30. Predict their IDs and combined revenue; retain invalid amounts in review.
Exercise solution 🔝
IDs 101 and 106 meet the rule, with total revenue 30.
mask = raw["status"].eq("paid") & raw["region"].eq("North") & amount.lt(30) & amount.notna()
answer = raw.loc[mask].copy()
answer["amount"] = amount.loc[answer.index]
print(answer)
print("Revenue:", answer["amount"].sum())
assert answer["order_id"].tolist() == [101, 106]
assert answer["amount"].sum() == 30Expected output
order_id status region amount
0 101 paid North 20.0
5 106 paid North 10.0
Revenue: 30.0Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change regions and amount limits, then inspect selected, other and review orders. Save your own copy to keep edits. All sample tables are included in the notebook.
Reference: Pandas DataFrame.loc 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