PySpark DataFrame.filter()

pyspark.sql.DataFrameFilteringSince Spark 1.3.0Alias: where()

filter() keeps the rows of a DataFrame where a condition is true and drops every other row. It is a method on pyspark.sql.DataFrame, so you call it as df.filter(condition), and where() is an alias that takes the same arguments and gives the same result.

The condition can be a boolean Column such as F.col("salary") > 60000 or a SQL expression string such as "salary > 60000". Every example below was run on PySpark 3.5.5, and all except the array example use the same six-row employee DataFrame. You can paste any of them into the PySpark online compiler and run it as is.

Quick answer

  • df.filter(condition) returns a new DataFrame with only the rows where condition is true; df.where(condition) is the same method under another name.
  • Combine conditions with &, | and ~, and wrap each comparison in its own parentheses. Python's and, or and not raise an error on Columns.
  • A comparison with a null value gives null, not true, so !=, == None and NOT IN (~isin()) all skip null rows. Use isNull() or isNotNull() to handle nulls on purpose.
  • filter() is lazy: no data is read until an action such as show() or count() runs, although classic PySpark checks column names as soon as you call it.
  • pyspark.sql.functions.filter() is a different function: it removes elements inside an array column and keeps every row.
from pyspark.sql import functions as F

DataFrame.filter(condition)
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("filter-example").getOrCreate()

data = [
    ("Alice", "Sales", 62000, "Delhi"),
    ("Bob", "Engineering", 85000, "Mumbai"),
    ("Chitra", "Sales", 48000, None),
    ("Dev", "Marketing", 55000, "Pune"),
    ("Esha", "Engineering", 72000, "Delhi"),
    ("Farhan", "HR", 39000, "Mumbai"),
]
df = spark.createDataFrame(data, ["name", "dept", "salary", "city"])

# Keep employees who earn more than 60,000
df.filter(F.col("salary") > 60000).show()
Output
+-----+-----------+------+------+
| name|       dept|salary|  city|
+-----+-----------+------+------+
|Alice|      Sales| 62000| Delhi|
|  Bob|Engineering| 85000|Mumbai|
| Esha|Engineering| 72000| Delhi|
+-----+-----------+------+------+

Only Alice (62000), Bob (85000) and Esha (72000) earn more than 60,000, so they are the three rows left. Chitra, Dev and Farhan are dropped, and the original df is unchanged because filter() returns a new DataFrame.

Syntax and parameters

DataFrame.filter(condition)
ParameterTypeDefaultDescription
conditionColumn or strrequired

A Column of BooleanType, or a string containing a SQL expression. Rows where it evaluates to true are kept.

Returns: DataFrame, a new DataFrame containing only the rows where condition is true.

filter() was added in Spark 1.3.0, and where() is an alias with the same arguments, added in the same version. Use whichever reads better in your code. The documentation links on this page point to the Spark 3.5.7 docs, because the 3.5.5 API reference pages returned 404 when this page was tested; the filter() and where() signatures are the same there.

filter() is a lazy transformation. Calling it builds and checks a plan (in classic PySpark a misspelled column fails right there); Spark reads and filters the data only when you call an action such as show(), count() or collect().

PySpark filter with multiple conditions

Use & for AND, | for OR and ~ for NOT when a row has to pass more than one test. Put each comparison in its own parentheses.

# AND: both conditions must be true
df.filter((F.col("dept") == "Sales") & (F.col("salary") > 50000)).show()

# OR: at least one condition must be true
df.filter((F.col("dept") == "HR") | (F.col("salary") > 80000)).show()

# NOT: invert a condition with ~
df.filter(~(F.col("dept") == "Engineering")).show()
Output
+-----+-----+------+-----+
| name| dept|salary| city|
+-----+-----+------+-----+
|Alice|Sales| 62000|Delhi|
+-----+-----+------+-----+

+------+-----------+------+------+
|  name|       dept|salary|  city|
+------+-----------+------+------+
|   Bob|Engineering| 85000|Mumbai|
|Farhan|         HR| 39000|Mumbai|
+------+-----------+------+------+

+------+---------+------+------+
|  name|     dept|salary|  city|
+------+---------+------+------+
| Alice|    Sales| 62000| Delhi|
|Chitra|    Sales| 48000|  NULL|
|   Dev|Marketing| 55000|  Pune|
|Farhan|       HR| 39000|Mumbai|
+------+---------+------+------+

The AND filter keeps only Alice: Chitra is also in Sales, but her 48000 salary fails the second test. The OR filter keeps Bob because his salary is over 80,000 and Farhan because he is in HR. The NOT filter removes the two Engineering rows (Bob and Esha) and keeps the other four, including Chitra, because her dept is not null.

Filter with a SQL expression string

If you already think in SQL, pass the condition as a string. Spark parses it the same way it parses a WHERE clause.

# The same AND filter written as a SQL expression string
df.filter("dept = 'Sales' AND salary > 50000").show()

# where() is an alias of filter() and accepts the same arguments
df.where("city IN ('Delhi', 'Pune')").show()
Output
+-----+-----+------+-----+
| name| dept|salary| city|
+-----+-----+------+-----+
|Alice|Sales| 62000|Delhi|
+-----+-----+------+-----+

+-----+-----------+------+-----+
| name|       dept|salary| city|
+-----+-----------+------+-----+
|Alice|      Sales| 62000|Delhi|
|  Dev|  Marketing| 55000| Pune|
| Esha|Engineering| 72000|Delhi|
+-----+-----------+------+-----+

The string version of the AND filter returns Alice alone, the same row as the Column version in the previous section. The where() call with IN keeps Alice and Esha (Delhi) and Dev (Pune). Inside the string, use SQL keywords (AND, OR, NOT, =) and single quotes around text values.

Filter rows where a column is in a list (IN and NOT IN)

Use isin() to keep rows whose value appears in a Python list, and put ~ in front of it for NOT IN.

cities = ["Delhi", "Pune"]

# IN: keep rows whose city is in the list
df.filter(F.col("city").isin(cities)).show()

# NOT IN: negate isin() with ~
df.filter(~F.col("city").isin(cities)).show()
Output
+-----+-----------+------+-----+
| name|       dept|salary| city|
+-----+-----------+------+-----+
|Alice|      Sales| 62000|Delhi|
|  Dev|  Marketing| 55000| Pune|
| Esha|Engineering| 72000|Delhi|
+-----+-----------+------+-----+

+------+-----------+------+------+
|  name|       dept|salary|  city|
+------+-----------+------+------+
|   Bob|Engineering| 85000|Mumbai|
|Farhan|         HR| 39000|Mumbai|
+------+-----------+------+------+

The IN filter keeps Alice, Dev and Esha, whose cities are Delhi or Pune. The NOT IN filter keeps Bob and Farhan (Mumbai), but Chitra appears in neither result. Her city is null, so isin() returns null for her row, ~ of null is still null, and filter() only keeps rows where the condition is true. To include her in the NOT IN result, add | F.col("city").isNull() to the condition. The handling nulls tutorial covers this behaviour in more depth.

Filter null and not null values

Use isNull() and isNotNull() whenever a filter depends on missing values. Standard comparison operators (==, !=, >, <) cannot match nulls.

# Rows where city is null
df.filter(F.col("city").isNull()).show()

# Rows where city is not null
df.filter(F.col("city").isNotNull()).show()
Output
+------+-----+------+----+
|  name| dept|salary|city|
+------+-----+------+----+
|Chitra|Sales| 48000|NULL|
+------+-----+------+----+

+------+-----------+------+------+
|  name|       dept|salary|  city|
+------+-----------+------+------+
| Alice|      Sales| 62000| Delhi|
|   Bob|Engineering| 85000|Mumbai|
|   Dev|  Marketing| 55000|  Pune|
|  Esha|Engineering| 72000| Delhi|
|Farhan|         HR| 39000|Mumbai|
+------+-----------+------+------+

isNull() returns Chitra, the only row with no city. isNotNull() returns the other five rows. Together the two results cover all six rows, which is not true of a pair like == "Delhi" and != "Delhi" (see the errors section below).

Filter by string match: contains, like and rlike

Use contains() for a plain substring, like() for SQL wildcards (% for any characters, _ for one character) and rlike() for a Java regular expression.

# contains(): substring match (case-sensitive)
df.filter(F.col("city").contains("um")).show()

# like(): SQL wildcard pattern, % matches any characters
df.filter(F.col("name").like("%a")).show()

# rlike(): regular expression, case-insensitive with (?i)
df.filter(F.col("dept").rlike("(?i)^eng")).show()
Output
+------+-----------+------+------+
|  name|       dept|salary|  city|
+------+-----------+------+------+
|   Bob|Engineering| 85000|Mumbai|
|Farhan|         HR| 39000|Mumbai|
+------+-----------+------+------+

+------+-----------+------+-----+
|  name|       dept|salary| city|
+------+-----------+------+-----+
|Chitra|      Sales| 48000| NULL|
|  Esha|Engineering| 72000|Delhi|
+------+-----------+------+-----+

+----+-----------+------+------+
|name|       dept|salary|  city|
+----+-----------+------+------+
| Bob|Engineering| 85000|Mumbai|
|Esha|Engineering| 72000| Delhi|
+----+-----------+------+------+
MethodPattern typeCase-sensitivePattern used hereRows returned
contains()Plain substringYes"um" on cityBob, Farhan
like()SQL wildcards % and _Yes"%a" on nameChitra, Esha
rlike()Java regular expressionYes, unless the pattern starts with (?i)"(?i)^eng" on deptBob, Esha

contains("um") matches "Mumbai", so Bob and Farhan come back. like("%a") matches names that end in "a", which gives Chitra and Esha. rlike("(?i)^eng") matches departments that start with "eng" in any case, so Bob and Esha in Engineering are returned even though the pattern is lowercase. For more text functions, see the string functions tutorial.

Filter elements inside an array column

To drop values inside an array rather than whole rows, use pyspark.sql.functions.filter() (written F.filter() here). It was added in Spark 3.1.0 and takes the array column plus a function that returns a boolean Column for each element.

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("filter-example").getOrCreate()

orders = spark.createDataFrame(
    [(1, [120, 45, 300]), (2, [15, 80]), (3, [500])],
    ["order_id", "item_prices"],
)

# functions.filter() keeps array elements that match, without dropping rows
orders.withColumn(
    "over_100", F.filter("item_prices", lambda price: price > 100)
).show()
Output
+--------+--------------+----------+
|order_id|   item_prices|  over_100|
+--------+--------------+----------+
|       1|[120, 45, 300]|[120, 300]|
|       2|      [15, 80]|        []|
|       3|         [500]|     [500]|
+--------+--------------+----------+

All three orders are still there. Order 1 loses the 45 and keeps [120, 300], order 3 keeps its single 500, and order 2 ends up with an empty array [] because neither 15 nor 80 is over 100. If you want to drop rows like order 2, follow this with a row-level df.filter().

Common errors and fixes

CANNOT_CONVERT_COLUMN_INTO_BOOL: using and / or in filter()

Python
Run it
# Python's "and" / "or" do not work on Columns
df.filter((F.col("dept") == "Sales") and (F.col("salary") > 50000)).show()
Error
PySparkValueError: [CANNOT_CONVERT_COLUMN_INTO_BOOL] Cannot convert column into bool: please use '&' for 'and', '|' for 'or', '~' for 'not' when building DataFrame boolean expressions.

Why it happens: Python's and, or and not call bool() on a Column to decide the result, and a Column has no true or false value until Spark evaluates it row by row, so PySpark raises this error instead.

Fix: Replace and with &, or with | and not with ~, keeping each condition in parentheses.

Python
# Use & (and), | (or), ~ (not), with each condition in parentheses
df.filter((F.col("dept") == "Sales") & (F.col("salary") > 50000)).show()
Output
+-----+-----+------+-----+
| name| dept|salary| city|
+-----+-----+------+-----+
|Alice|Sales| 62000|Delhi|
+-----+-----+------+-----+

Py4JError An error occurred while calling o<id>.and: missing parentheses

Python
Run it
# Without parentheses, & binds before == and >
df.filter(F.col("dept") == "Sales" & F.col("salary") > 50000).show()
Error
Py4JError: An error occurred while calling o<id>.and.

Why it happens: In Python, & and | bind more tightly than == and >, so Python evaluates "Sales" & F.col("salary") first, and PySpark 3.5 fails with this Py4JError. The o<id> number in the message changes from run to run.

Fix: Wrap each comparison in its own parentheses so & combines two finished boolean Columns.

Python
# Parentheses around each comparison make & combine the two results
df.filter((F.col("dept") == "Sales") & (F.col("salary") > 50000)).show()
Output
+-----+-----+------+-----+
| name| dept|salary| city|
+-----+-----+------+-----+
|Alice|Sales| 62000|Delhi|
+-----+-----+------+-----+

filter() with != silently drops rows with null values

Python
Run it
# Chitra's city is null, so she is missing from the result
df.filter(F.col("city") != "Delhi").show()
Output (no error, but wrong)
+------+-----------+------+------+
|  name|       dept|salary|  city|
+------+-----------+------+------+
|   Bob|Engineering| 85000|Mumbai|
|   Dev|  Marketing| 55000|  Pune|
|Farhan|         HR| 39000|Mumbai|
+------+-----------+------+------+

Why it happens: Chitra's city is null, and in SQL comparing null with != gives null rather than true. filter() keeps only rows where the condition is true, so her row disappears without any error.

Fix: Add an explicit isNull() check with | when null rows should count as "not Delhi".

Python
# Keep nulls explicitly
df.filter((F.col("city") != "Delhi") | F.col("city").isNull()).show()
Output
+------+-----------+------+------+
|  name|       dept|salary|  city|
+------+-----------+------+------+
|   Bob|Engineering| 85000|Mumbai|
|Chitra|      Sales| 48000|  NULL|
|   Dev|  Marketing| 55000|  Pune|
|Farhan|         HR| 39000|Mumbai|
+------+-----------+------+------+

filter(col == None) returns no rows

Python
Run it
# Comparing with None never matches, even for null rows
print(df.filter(F.col("city") == None).count())
Output (no error, but wrong)
0

Why it happens: == None becomes an SQL comparison with null, and that comparison is null for every row, including Chitra's. No row evaluates to true, so the count is 0 instead of 1.

Fix: Use isNull() (or isNotNull() for the opposite) to match null values.

Python
# Use isNull() to match null values
print(df.filter(F.col("city").isNull()).count())
Output
1

UNRESOLVED_COLUMN: a typo in the column name

Python
Run it
# Typo in the column name
df.filter(F.col("salry") > 50000).show()
Error
AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name `salry` cannot be resolved. Did you mean one of the following? [`salary`, `city`, `name`, `dept`].

Why it happens: In classic PySpark (not Spark Connect), column names are resolved as soon as filter() builds the new DataFrame, so a misspelled name like salry fails at that line, before any action runs, even though filter() is otherwise lazy. The message lists the closest real columns, with salary first.

Fix: Print df.columns to check the exact names, then use the correct one.

Python
# Check the exact column names, then use the right one
print(df.columns)
df.filter(F.col("salary") > 50000).show()
Output
['name', 'dept', 'salary', 'city']
+-----+-----------+------+------+
| name|       dept|salary|  city|
+-----+-----------+------+------+
|Alice|      Sales| 62000| Delhi|
|  Bob|Engineering| 85000|Mumbai|
|  Dev|  Marketing| 55000|  Pune|
| Esha|Engineering| 72000| Delhi|
+-----+-----------+------+------+
  • where()

    an alias of filter() with the same arguments, useful if you prefer SQL-style naming.

  • isin()

    builds an IN condition from a list of values; negate it with ~ for NOT IN.

  • isNull()

    returns true for null values, the reliable way to filter missing data.

  • eqNullSafe()

    an equality test that handles nulls explicitly instead of returning null.

  • functions.filter()

    filters elements inside an array column and keeps every row.

  • rlike()

    matches a column against a Java regular expression, with (?i) for case-insensitive matching.

Practice problems

Read the full walkthrough in Filtering Data in PySpark, then practise with these interview questions in the browser.

Frequently asked questions

What is the difference between filter() and where() in PySpark?

There is no difference in behaviour. where() is an alias of filter() on DataFrame, takes the same Column or SQL string argument, and returns the same rows. Both have been available since Spark 1.3.0, so pick the name that reads better to you.

How do I filter with NOT IN in PySpark?

Negate isin() with ~, as in df.filter(~F.col("city").isin(cities)), or use NOT IN inside a SQL string. Rows where the column is null are dropped by NOT IN because the comparison is null, not true. Add | F.col("city").isNull() if you want to keep them.

Is PySpark filter() lazy?

Yes. filter() is a transformation, so it only adds a step to the query plan and returns a new DataFrame. Spark reads and filters the data when an action such as show(), count() or collect() runs. In classic PySpark (not Spark Connect), column names are still checked when you call filter(), which is why a typo fails straight away.

What is the difference between DataFrame.filter() and functions.filter()?

DataFrame.filter() removes whole rows that fail a condition. pyspark.sql.functions.filter(), added in Spark 3.1.0, works on an array column: it removes elements inside each array that fail a lambda condition and keeps every row, as shown with the over_100 column above.