Skip to content
Laviyx Learn

Try

Python for Data course menu

Filter Rows in pandas: Conditions, isin, between and query

Filter pandas DataFrames with boolean conditions, combine them with & | ~, use isin, between and str.contains, write readable filters with query, and count or select the matching rows.

Beginner9 min readUpdated
On this page

Filtering keeps only the rows that match a condition: orders over ₹500, customers in Mumbai, products out of stock. It is the pandas equivalent of SQL's WHERE.

The examples use this DataFrame:

Python
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Asha", "Meera", "John", "Priya", "Ravi"],
    "city":     ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount":   [250, 120, 560, 90, 310, 700, 45],
    "status":   ["paid", "paid", "refunded", "paid", "pending", "paid", "paid"],
})
print(orders)
Output
  customer     city  amount    status
0     Asha   Mumbai     250      paid
1     Ravi    Delhi     120      paid
2     Asha   Mumbai     560  refunded
3    Meera     Pune      90      paid
4     John  Chennai     310   pending
5    Priya    Delhi     700      paid
6     Ravi    Delhi      45      paid

One condition#

A comparison on a column gives a boolean Series (one True/False per row). Put it inside [] to keep the True rows:

Python
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Asha", "Meera", "John", "Priya", "Ravi"],
    "city":     ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount":   [250, 120, 560, 90, 310, 700, 45],
    "status":   ["paid", "paid", "refunded", "paid", "pending", "paid", "paid"],
})
big = orders["amount"] > 300
print(big.tolist())
print(orders[big])
Output
[False, False, True, False, True, True, False]
  customer     city  amount    status
2     Asha   Mumbai     560  refunded
4     John  Chennai     310   pending
5    Priya    Delhi     700      paid

The original row labels (2, 4, 5) are kept, so you can always tell which rows matched.

Several conditions#

Combine conditions with & (and), | (or) and ~ (not). Each condition needs its own parentheses:

Python
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Asha", "Meera", "John", "Priya", "Ravi"],
    "city":     ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount":   [250, 120, 560, 90, 310, 700, 45],
    "status":   ["paid", "paid", "refunded", "paid", "pending", "paid", "paid"],
})
print(orders[(orders["city"] == "Delhi") & (orders["amount"] > 100)])
print(orders[(orders["amount"] < 100) | (orders["status"] == "pending")])
print(orders[~(orders["status"] == "paid")])
Output
  customer   city  amount status
1     Ravi  Delhi     120   paid
5    Priya  Delhi     700   paid
  customer     city  amount   status
3    Meera     Pune      90     paid
4     John  Chennai     310  pending
6     Ravi    Delhi      45     paid
  customer     city  amount    status
2     Asha   Mumbai     560  refunded
4     John  Chennai     310   pending

isin, between and text matching#

Python
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Asha", "Meera", "John", "Priya", "Ravi"],
    "city":     ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount":   [250, 120, 560, 90, 310, 700, 45],
})
print(orders[orders["city"].isin(["Pune", "Chennai"])])          # like SQL IN
print(orders[orders["amount"].between(100, 300)])                 # inclusive at both ends
print(orders[orders["customer"].str.startswith("A")])
print(orders[orders["city"].str.contains("i", case=False)]["city"].unique())
Output
  customer     city  amount
3    Meera     Pune      90
4     John  Chennai     310
  customer    city  amount
0     Asha  Mumbai     250
1     Ravi   Delhi     120
  customer    city  amount
0     Asha  Mumbai     250
2     Asha  Mumbai     560
['Mumbai' 'Delhi' 'Chennai']

query: filters as readable text#

query takes the condition as a string, using column names directly. Use @ to refer to a Python variable:

Python
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Asha", "Meera", "John", "Priya", "Ravi"],
    "city":     ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount":   [250, 120, 560, 90, 310, 700, 45],
    "status":   ["paid", "paid", "refunded", "paid", "pending", "paid", "paid"],
})
minimum = 200
print(orders.query("city == 'Delhi' and amount > @minimum"))
print(orders.query("status in ['pending', 'refunded']"))
Output
  customer   city  amount status
5    Priya  Delhi     700   paid
  customer     city  amount    status
2     Asha   Mumbai     560  refunded
4     John  Chennai     310   pending

query reads well for long conditions. Column names with spaces must be wrapped in backticks: query("`order total` > 100").

Counting and using the matches#

Python
import pandas as pd

orders = pd.DataFrame({
    "city":   ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount": [250, 120, 560, 90, 310, 700, 45],
})
delhi = orders["city"] == "Delhi"
print(delhi.sum(), "orders from Delhi")
print(round(delhi.mean() * 100, 1), "% of all orders")
print(orders.loc[delhi, "amount"].sum(), "total from Delhi")
Output
3 orders from Delhi
42.9 % of all orders
865 total from Delhi

Summing a boolean Series counts the True values; its mean is the fraction that are True.

Common mistakes#

Practice#

Practice exercise

Using the orders DataFrame from this lesson, print: (1) the paid orders of at least 200, (2) the orders that are not from Delhi or Mumbai, and (3) the number of refunded or pending orders.

Show hint

(1) two conditions with &; (2) ~orders["city"].isin([...]); (3) isin on status, then .sum().

Show solution
Python
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Asha", "Meera", "John", "Priya", "Ravi"],
    "city":     ["Mumbai", "Delhi", "Mumbai", "Pune", "Chennai", "Delhi", "Delhi"],
    "amount":   [250, 120, 560, 90, 310, 700, 45],
    "status":   ["paid", "paid", "refunded", "paid", "pending", "paid", "paid"],
})
print(orders[(orders["status"] == "paid") & (orders["amount"] >= 200)])
print(orders[~orders["city"].isin(["Delhi", "Mumbai"])])
print(orders["status"].isin(["refunded", "pending"]).sum())
Output
  customer    city  amount status
0     Asha  Mumbai     250   paid
5    Priya   Delhi     700   paid
  customer     city  amount   status
3    Meera     Pune      90     paid
4     John  Chennai     310  pending
2

Key takeaway#

Next: Sorting and Ranking.

Finished this lesson?

Related lessons

Python for Datapandas loc vs iloc: Selecting Rows and ColumnsSelect data in pandas with loc (by label) and iloc (by position): single values, rows, columns, slices, lists, combining row and column selection, and how to avoid the SettingWithCopyWarning.Beginner10 min readPython for DataSorting and Ranking in pandas: sort_values, nlargest and rankSort pandas DataFrames by one or more columns, in ascending or descending order, sort by the index, get top rows quickly with nlargest and nsmallest, and rank values with different tie methods.Beginner8 min readSQLSQL WHERE Clause: Filter Rows With ConditionsLearn how to filter rows in SQL with WHERE. Covers comparison operators, text and number filters, AND, OR, IN, BETWEEN, LIKE and NULL, with examples and exercises.Beginner8 min read