DataFrame Filter & Where Operations

Filtering row records in PySpark using column expressions and SQL conditional strings.

What is the Filter / Where Operation?

The filter() and where() operations restrict row results in a DataFrame based on a boolean conditional clause, equivalent to the WHERE clause in relational databases.

In PySpark, filter() and where() are completely identical aliases—there is absolutely no functional or performance difference between them.

Syntax and Comparison Operators

You can build query conditions using Column object expressions or standard SQL comparison strings:

from pyspark.sql.functions import col

# Method A: SQL Query String (Quick, but not type-safe)
df.filter("salary > 80000 AND department = 'Engineering'")

# Method B: Column Object Expressions (Highly recommended, type-safe, supports multiple brackets)
df.filter((col("salary") > 80000) & (col("department") == "Engineering"))

Supported Operators (Column Objects)

• AND: & (requires parentheses around each condition)
• OR: | (requires parentheses around each condition)
• NOT: ~ or .isNotNull() / .isNull()
• Equality: ==
• In-List: .isin("A", "B", "C")
• String Match: .like("%pattern%") or .contains("sub")

Example Usage Pipeline

Below is a complete, copy-paste-ready PySpark script demonstrating advanced filtering:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

# 1. Setup local Spark session
spark = SparkSession.builder \
    .appName("DataFrame Filter Demo") \
    .master("local[*]") \
    .getOrCreate()

# 2. Dummy dataset (Hospital inventory)
data = [
    ("item_01", "Syringe", 120, "ICU"),
    ("item_02", "Stethoscope", 15, "Outpatient"),
    ("item_03", "Ventilator", 4, "ICU"),
    ("item_04", "Oxygen Mask", 0, "Emergency"),
    ("item_05", "Surgical Glove", 800, "Emergency"),
]
columns = ["item_id", "item_name", "stock_count", "department"]
df = spark.createDataFrame(data, columns)

# 3. Apply Multi-Condition Column Filters:
# - Stock must be low (less than 50)
# - Department must be either 'ICU' or 'Emergency'
filtered_low_stock = df.filter(
    (col("stock_count") < 50) & 
    (col("department").isin("ICU", "Emergency"))
)

# 4. Apply string condition (identical where clause)
filtered_outpatient = df.where("department = 'Outpatient'")

# 5. Show results
print("=== ICU/Emergency Low Stock Alerts ===")
filtered_low_stock.show(truncate=False)

print("=== Outpatient Ward Inventory ===")
filtered_outpatient.show(truncate=False)

Rendered Output:

=== ICU/Emergency Low Stock Alerts ===
+-------+-----------+-----------+----------+
|item_id|item_name  |stock_count|department|
+-------+-----------+-----------+----------+
|item_03|Ventilator |4          |ICU       |
|item_04|Oxygen Mask|0          |Emergency |
+-------+-----------+-----------+----------+

=== Outpatient Ward Inventory ===
+-------+-----------+-----------+----------+
|item_id|item_name  |stock_count|department|
+-------+-----------+-----------+----------+
|item_02|Stethoscope|15          |Outpatient|
+-------+-----------+-----------+----------+