Introduction

In PySpark, the filter() function is used to filter rows from a DataFrame based on a given condition or SQL expression.

PySpark DataFrame filter() Syntaxes

# PySpark DataFrame filter() Syntaxes
1) filter(condition: Column)
2) filter(conditionExpr: str) # using SQL expression

The first syntax is used with conditions applied to Column objects with functions like col("colname").

The second syntax is used to provide SQL expressions to filter rows.

Before we dive into examples, let's first create a DataFrame.

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, ArrayType
from pyspark.sql import Row

spark = SparkSession.builder \
    .appName('FilterDataFrames') \
    .getOrCreate()

arrayStructureData = [
    Row(name=Row(firstname="James", middlename="", lastname="Smith"), languages=["Java", "Scala", "C++"], state="OH", gender="M"),
    Row(name=Row(firstname="Anna", middlename="Rose", lastname=""), languages=["Spark", "Java", "C++"], state="NY", gender="F"),
    Row(name=Row(firstname="Julia", middlename="", lastname="Williams"), languages=["CSharp", "VB"], state="OH", gender="F"),
    Row(name=Row(firstname="Maria", middlename="Anne", lastname="Jones"), languages=["CSharp", "VB"], state="NY", gender="M"),
    Row(name=Row(firstname="Jen", middlename="Mary", lastname="Brown"), languages=["CSharp", "VB"], state="NY", gender="M"),
    Row(name=Row(firstname="Mike", middlename="Mary", lastname="Williams"), languages=["Python", "VB"], state="OH", gender="M")
]

arrayStructureSchema = StructType([
    StructField("name", StructType([
        StructField("firstname", StringType(), True),
        StructField("middlename", StringType(), True),
        StructField("lastname", StringType(), True)
    ])),
    StructField("languages", ArrayType(StringType()), True),
    StructField("state", StringType(), True),
    StructField("gender", StringType(), True)
])

df = spark.createDataFrame(spark.sparkContext.parallelize(arrayStructureData), arrayStructureSchema)

df.printSchema()
df.show()

This gives the following schema and DataFrame output:

root
 |-- name: struct (nullable = true)
 |    |-- firstname: string (nullable = true)
 |    |-- middlename: string (nullable = true)
 |    |-- lastname: string (nullable = true)
 |-- languages: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- state: string (nullable = true)
 |-- gender: string (nullable = true)

+--------------------+------------------+-----+------+
|                name|         languages|state|gender|
+--------------------+------------------+-----+------+
|    [James, , Smith]|[Java, Scala, C++]|   OH|     M|
|      [Anna, Rose, ]|[Spark, Java, C++]|   NY|     F|
| [Julia, , Williams]|      [CSharp, VB]|   OH|     F|
|[Maria, Anne, Jones]|      [CSharp, VB]|   NY|     M|
|  [Jen, Mary, Brown]|      [CSharp, VB]|   NY|     M|
|[Mike, Mary, Will...|      [Python, VB]|   OH|     M|
+--------------------+------------------+-----+------+

Use Column with the condition to filter the rows from a DataFrame. This allows you to express complex conditions by referring to column names with functions like col(name). Use == for comparison.

from pyspark.sql.functions import col

# DataFrame filter() with Column condition
df.filter(col("state") == "OH").show(truncate=False)

This outputs:

+----------------------+------------------+-----+------+
|name                  |languages         |state|gender|
+----------------------+------------------+-----+------+
|[James, , Smith]      |[Java, Scala, C++]|OH   |M     |
|[Julia, , Williams]   |[CSharp, VB]      |OH   |F     |
|[Mike, Mary, Williams]|[Python, VB]      |OH   |M     |
+----------------------+------------------+-----+------+

filter() with SQL Expression

If you have an SQL background, you can utilize SQL expressions in PySpark to filter DataFrame rows.

# DataFrame filter() with SQL Expression
df.filter("gender == 'M'").show(truncate=False)

+----------------------+------------------+-----+------+
|name                  |languages         |state|gender|
+----------------------+------------------+-----+------+
|[James, , Smith]      |[Java, Scala, C++]|OH   |M     |
|[Maria, Anne, Jones]  |[CSharp, VB]      |NY   |M     |
|[Jen, Mary, Brown]    |[CSharp, VB]      |NY   |M     |
|[Mike, Mary, Williams]|[Python, VB]      |OH   |M     |
+----------------------+------------------+-----+------+

Filtering with Multiple Conditions

To filter rows on a DataFrame using multiple conditions, you can combine conditions using logical operators. Here is a simple example using and (&), or (|), and not (~).

# Multiple conditions
df.filter((col("state") == "OH") & (col("gender") == "M")).show(truncate=False)

This outputs:

+----------------------+------------------+-----+------+
|name                  |languages         |state|gender|
+----------------------+------------------+-----+------+
|[James, , Smith]      |[Java, Scala, C++]|OH   |M     |
|[Mike, Mary, Williams]|[Python, VB]      |OH   |M     |
+----------------------+------------------+-----+------+

Filtering on an Array Column

You can filter rows from a DataFrame based on values present in an array collection column. Here, the array_contains() SQL function is used to check if a value exists in an array.

from pyspark.sql.functions import array_contains

# Filtering on an Array column
df.filter(array_contains(col("languages"), "Java")).show(truncate=False)

This outputs:

+----------------+------------------+-----+------+
|name            |languages         |state|gender|
+----------------+------------------+-----+------+
|[James, , Smith]|[Java, Scala, C++]|OH   |M     |
|[Anna, Rose, ]  |[Spark, Java, C++]|NY   |F     |
+----------------+------------------+-----+------+

Filtering on Nested Struct Columns

If your DataFrame includes nested struct columns, any of the above syntaxes can be used to filter rows based on nested columns.

# Struct condition
df.filter(col("name.lastname") == "Williams").show(truncate=False)

This outputs:

+----------------------+------------+-----+------+
|name                  |languages   |state|gender|
+----------------------+------------+-----+------+
|[Julia, , Williams]   |[CSharp, VB]|OH   |F     |
|[Mike, Mary, Williams]|[Python, VB]|OH   |M     |
+----------------------+------------+-----+------+