Browse cheat sheets · PySpark
Select and Filter DataFrames
Select, project, filter, and validate PySpark DataFrames efficiently while avoiding ambiguous columns, null-logic mistakes, unnecessary scans, and driver-side filtering.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Select specific columns
- 2. Select with aliases
- 3. Add or replace a column with withColumn
- 4. Select all existing columns plus one expression
- 5. Drop columns explicitly
- 6. Filter with one condition
- 7. Filter with multiple conditions
- 8. Do not use Python and / or
- 9. Filter for nulls
- 10. Understand three-valued null logic
- 11. Filter by a list with isin
- 12. Exclude values
- 13. Filter numeric ranges
- 14. Filter strings with contains
- 15. Filter with startswith and endswith
- 16. Filter with like
- 17. Filter with regular expressions
- 18. Filter dates with explicit boundaries
- 19. Cast before date comparison when the source is text
- 20. Filter timestamps safely
- 21. Filter using SQL expression syntax
- 22. Build dynamic filter expressions safely
- 23. Filter before a join
- 24. Select only necessary columns before a join
- 25. Avoid ambiguous columns after joins
- 26. Use a list of join keys when names match
- 27. Use left-semi joins for existence filtering
- 28. Use left-anti joins for missing-reference detection
- 29. Avoid filtering by collecting keys to Python
- 30. Avoid row-by-row Python filtering
- 31. Prefer built-in functions over Python UDF filters
- 32. Inspect whether filters reached the physical plan
- 33. Inspect Parquet/Delta partition filters in the plan
- 34. Avoid wrapping partition columns unnecessarily
- 35. Use limit() only for bounded inspection
- 36. Use sample() for approximate inspection
- 37. Check row counts before and after an important filter
- 38. Aggregate filter diagnostics in one pass
- 39. Validate unexpected filter results
- 40. Normalize repeated string logic once
- What the result means
- What to look for
- Join is expensive even though output is small
- Filter returns unexpectedly few rows
- Filter returns zero rows after a schema change
- Physical plan still scans a large amount of data
- Driver memory grows during filtering
- Same string cleanup appears in many filters
- Production notes
- Spark transformations are lazy
- Pushdown is source-dependent
- Null semantics are SQL-like
- Large isin() lists are a smell
- Column width matters
- Filter order is usually optimizer-managed
- Expensive user-defined functions can block optimization
- Common mistakes
- 1. Using Python and / or
- 2. Forgetting parentheses around conditions
- 3. Using == None
- 4. Assuming != value keeps nulls
- 5. Collecting reference keys to Python
- 6. Selecting * through the whole pipeline
- 7. Filtering after the expensive join
- 8. Using regex for simple prefix/suffix logic
- 9. Assuming every filter is pushed down
- 10. Using limit() as proof that data is correct
- Next step
- Related deep dives
Problem
You need to reduce a PySpark DataFrame to the rows and columns required by the next transformation.
The syntax is simple, but production bugs often come from:
- carrying unnecessary columns through expensive joins
- using Python boolean logic instead of Spark column expressions
- filtering nulls incorrectly
- comparing strings, dates, or timestamps with the wrong semantics
- creating ambiguous column names after joins
- using
collect()or Python loops to filter distributed data - applying expensive functions before reducing the dataset
- assuming a filter always pushes down to the source
The goal is to make row and column reduction explicit, efficient, and easy to reason about.
Quick answer
Select only required columns:
from pyspark.sql import functions as F
sales = (
spark.table("sales")
.select(
"sale_id",
"merchant_id",
"sale_date",
"amount",
)
)
Filter with Spark expressions:
filtered = sales.filter(
(F.col("sale_date") >= F.lit("2026-10-01"))
& (F.col("amount") > 0)
)
For null-safe filtering:
filtered = sales.filter(
F.col("merchant_id").isNotNull()
)
For production workloads, reduce rows and columns before large joins, aggregations, or Python-side operations.
Code / commands
1. Select specific columns
df = df.select(
"sale_id",
"merchant_id",
"amount",
)
Use this instead of carrying every source column through the whole pipeline.
Column pruning can reduce:
- I/O
- serialization
- shuffle width
- memory usage
2. Select with aliases
df = df.select(
F.col("sale_id"),
F.col("merchant_id"),
F.col("amount").alias("sale_amount"),
)
Use aliases to make downstream meaning explicit and to avoid collisions after joins.
3. Add or replace a column with withColumn
df = df.withColumn(
"net_amount",
F.col("amount") - F.col("discount"),
)
Use withColumn() for targeted derivations.
If you are creating many columns, consider one select() with all expressions for readability.
4. Select all existing columns plus one expression
df = df.select(
"*",
(
F.col("amount")
- F.col("discount")
).alias("net_amount"),
)
Use this sparingly.
select("*") is convenient but can keep more data than the downstream workload actually needs.
5. Drop columns explicitly
df = df.drop(
"_raw_payload",
"_debug_column",
)
Use drop() when a temporary column is no longer needed.
For wide production pipelines, prefer explicit positive selection where possible because it makes the schema contract clearer.
6. Filter with one condition
filtered = df.filter(
F.col("amount") > 100
)
Equivalent:
filtered = df.where(
F.col("amount") > 100
)
filter() and where() are aliases.
Use whichever is clearer for the team.
7. Filter with multiple conditions
filtered = df.filter(
(F.col("status") == "active")
& (F.col("amount") > 100)
)
Use:
&for AND|for OR~for NOT
Wrap each comparison in parentheses.
8. Do not use Python and / or
Incorrect:
df.filter(
(F.col("amount") > 100)
and (F.col("status") == "active")
)
Correct:
df.filter(
(F.col("amount") > 100)
& (F.col("status") == "active")
)
Spark expressions build a distributed execution plan.
Python and / or do not operate element-wise on Spark columns.
9. Filter for nulls
missing = df.filter(
F.col("merchant_id").isNull()
)
Not null:
valid = df.filter(
F.col("merchant_id").isNotNull()
)
Do not write:
F.col("merchant_id") == None
Use the explicit null functions.
10. Understand three-valued null logic
This filter:
df.filter(
F.col("status") != "closed"
)
does not keep null status rows because comparisons with null evaluate to unknown.
If null should also be kept:
df.filter(
(F.col("status") != "closed")
| F.col("status").isNull()
)
Always make null semantics deliberate.
11. Filter by a list with isin
filtered = df.filter(
F.col("country").isin(
"US",
"CA",
"ES",
)
)
For a Python list:
allowed = [
"US",
"CA",
"ES",
]
filtered = df.filter(
F.col("country").isin(allowed)
)
Use this for small in-memory lookup lists.
For large reference sets, join to a DataFrame instead of creating a huge isin() expression.
12. Exclude values
filtered = df.filter(
~F.col("status").isin(
"cancelled",
"deleted",
)
)
If nulls must be kept, add that rule explicitly.
13. Filter numeric ranges
filtered = df.filter(
F.col("amount").between(
100,
500,
)
)
between() is inclusive on both ends.
Equivalent:
filtered = df.filter(
(F.col("amount") >= 100)
& (F.col("amount") <= 500)
)
14. Filter strings with contains
filtered = df.filter(
F.col("description").contains(
"premium"
)
)
This is case-sensitive.
For case-insensitive matching:
filtered = df.filter(
F.lower(
F.col("description")
).contains("premium")
)
Be careful with repeated lower/trim calls on large datasets; normalize once if the same logic is reused.
15. Filter with startswith and endswith
filtered = df.filter(
F.col("sku").startswith("PROD-")
)
filtered = df.filter(
F.col("filename").endswith(".parquet")
)
These are often clearer than regex for simple prefix/suffix rules.
16. Filter with like
filtered = df.filter(
F.col("email").like("%@example.com")
)
SQL LIKE wildcards:
% → any sequence
_ → one character
Use rlike() only when regular expressions are actually needed.
17. Filter with regular expressions
filtered = df.filter(
F.col("product_code").rlike(
r"^[A-Z]{3}-\d{4}$"
)
)
Regex can be expensive on large string datasets.
Prefer simpler string operations when possible.
18. Filter dates with explicit boundaries
filtered = df.filter(
(F.col("sale_date") >= F.lit("2026-10-01"))
& (F.col("sale_date") < F.lit("2026-11-01"))
)
Use half-open intervals:
start <= value < next_boundary
This avoids end-of-day precision problems.
19. Cast before date comparison when the source is text
df = df.withColumn(
"sale_date",
F.to_date(
F.col("sale_date_raw"),
"yyyy-MM-dd",
),
)
Then filter:
filtered = df.filter(
(F.col("sale_date") >= F.to_date(F.lit("2026-10-01")))
& (F.col("sale_date") < F.to_date(F.lit("2026-11-01")))
)
Do not assume arbitrary date strings sort or compare correctly.
20. Filter timestamps safely
filtered = df.filter(
(F.col("event_time") >= F.to_timestamp(F.lit("2026-10-01 00:00:00")))
& (F.col("event_time") < F.to_timestamp(F.lit("2026-11-01 00:00:00")))
)
Keep timezone semantics consistent with how the timestamp column was produced.
Do not mix local-time business boundaries with UTC timestamps without explicit conversion.
21. Filter using SQL expression syntax
filtered = df.filter(
"""
status = 'active'
AND amount > 100
"""
)
This can be useful when the condition is naturally expressed in SQL.
Prefer the Column API when you need programmatic composition and stronger refactoring support.
22. Build dynamic filter expressions safely
conditions = []
if min_amount is not None:
conditions.append(
F.col("amount") >= min_amount
)
if country is not None:
conditions.append(
F.col("country") == country
)
Combine:
from functools import reduce
from operator import and_
if conditions:
predicate = reduce(
and_,
conditions,
)
df = df.filter(predicate)
Do not concatenate untrusted input directly into SQL expression strings.
23. Filter before a join
Prefer:
sales_small = (
sales
.filter(
F.col("sale_date") >= "2026-10-01"
)
.select(
"merchant_id",
"amount",
)
)
joined = sales_small.join(
merchants.select(
"merchant_id",
"region",
),
on="merchant_id",
how="left",
)
over joining wide/unfiltered inputs first.
Reducing both sides before a join can reduce shuffle and memory pressure.
24. Select only necessary columns before a join
sales_small = sales.select(
"merchant_id",
"sale_id",
"amount",
)
merchants_small = merchants.select(
"merchant_id",
"merchant_name",
)
Then join.
This avoids carrying large unused payload columns through shuffle.
25. Avoid ambiguous columns after joins
Alias both DataFrames:
s = sales.alias("s")
m = merchants.alias("m")
joined = (
s.join(
m,
F.col("s.merchant_id")
== F.col("m.merchant_id"),
"left",
)
.select(
F.col("s.sale_id"),
F.col("s.merchant_id"),
F.col("m.merchant_name"),
F.col("s.amount"),
)
)
This avoids ambiguity when both inputs contain the same column names.
26. Use a list of join keys when names match
joined = sales.join(
merchants,
on=["merchant_id"],
how="left",
)
This usually avoids duplicate join-key columns.
Use an explicit condition when:
- key names differ
- multiple non-equality conditions are needed
- you need more control
27. Use left-semi joins for existence filtering
Keep rows from sales whose merchant_id exists in merchants:
valid_sales = sales.join(
merchants.select("merchant_id"),
on="merchant_id",
how="left_semi",
)
This is clearer and often more efficient than a full join followed by column dropping.
28. Use left-anti joins for missing-reference detection
Find sales with no matching merchant:
orphan_sales = sales.join(
merchants.select("merchant_id"),
on="merchant_id",
how="left_anti",
)
Use anti joins for:
- unmatched foreign keys
- incremental new-key detection
- exception reporting
29. Avoid filtering by collecting keys to Python
Bad pattern:
keys = [
row["merchant_id"]
for row in merchants.collect()
]
filtered = sales.filter(
F.col("merchant_id").isin(keys)
)
This moves data to the driver and can fail at scale.
Use a semi join instead.
30. Avoid row-by-row Python filtering
Bad:
rows = df.collect()
filtered = [
row
for row in rows
if row["amount"] > 100
]
Correct:
filtered = df.filter(
F.col("amount") > 100
)
Keep filtering distributed in Spark.
31. Prefer built-in functions over Python UDF filters
Prefer:
filtered = df.filter(
F.length(
F.trim(F.col("email"))
) > 5
)
over a Python UDF that checks string length.
Built-in Spark functions are easier for the optimizer to understand and avoid Python serialization overhead.
32. Inspect whether filters reached the physical plan
filtered.explain("formatted")
Look for:
Filter- pushed filters where shown
- partition filters
- reduced scans
Do not assume every filter is pushed down to the source.
Expression shape and data-source support matter.
33. Inspect Parquet/Delta partition filters in the plan
For a physically partitioned table, a filter such as:
df.filter(
F.col("sale_year") == 2026
)
may allow partition pruning.
Use:
df.filter(
F.col("sale_year") == 2026
).explain("formatted")
Look for partition-filter information in the scan.
If pruning is not happening, check:
- partition column
- expression type
- casts/functions wrapping the partition column
- table layout
34. Avoid wrapping partition columns unnecessarily
Less optimizer-friendly:
df.filter(
F.year(
F.col("sale_date")
) == 2026
)
If a dedicated partition column already exists:
df.filter(
F.col("sale_year") == 2026
)
is usually clearer for partition pruning.
Design partition columns around actual access patterns.
35. Use limit() only for bounded inspection
sample = df.limit(100)
This is useful for:
- previews
- local inspection
- test output
Do not treat limit() as a substitute for business filtering.
A limited result is not representative unless the ordering/sample semantics are defined.
36. Use sample() for approximate inspection
sample = df.sample(
withReplacement=False,
fraction=0.01,
seed=42,
)
Use sampling for exploration.
Do not use it to prove:
- uniqueness
- completeness
- absence of bad data
A sample cannot validate the full dataset.
37. Check row counts before and after an important filter
before = df.count()
filtered = df.filter(
F.col("status") == "active"
)
after = filtered.count()
print("Before:", before)
print("After:", after)
print("Removed:", before - after)
For large production jobs, avoid repeated full scans just for logging.
Where possible, combine quality metrics in one aggregation.
38. Aggregate filter diagnostics in one pass
metrics = (
df.agg(
F.count("*").alias("rows_total"),
F.sum(
F.when(
F.col("status") == "active",
1,
).otherwise(0)
).alias("rows_active"),
F.sum(
F.when(
F.col("merchant_id").isNull(),
1,
).otherwise(0)
).alias("rows_missing_merchant"),
)
)
metrics.show()
This is usually more efficient than several separate count() jobs.
39. Validate unexpected filter results
If a filter returns zero rows:
df.select(
"status"
).distinct().show(
100,
truncate=False,
)
Or:
(
df.groupBy("status")
.count()
.orderBy(
F.desc("count")
)
.show(
100,
truncate=False,
)
)
Look for:
- casing
- whitespace
- nulls
- unexpected codes
- source changes
40. Normalize repeated string logic once
Instead of:
df.filter(
F.lower(
F.trim(F.col("status"))
) == "active"
)
in many places, normalize once:
df = df.withColumn(
"status_normalized",
F.lower(
F.trim(F.col("status"))
),
)
Then:
filtered = df.filter(
F.col("status_normalized") == "active"
)
Do this only when the normalized field is reused enough to justify carrying it.
What the result means
| Operation | Meaning |
|---|---|
select() |
Projects the columns/expressions needed downstream |
withColumn() |
Adds or replaces one column |
drop() |
Removes columns |
filter() / where() |
Keeps rows matching a Spark predicate |
isin() |
Membership test for small value sets |
isNull() / isNotNull() |
Explicit null filtering |
left_semi |
Keep left rows with a matching key on the right |
left_anti |
Keep left rows without a matching key on the right |
explain() |
Shows logical/physical execution plan |
| partition pruning | Avoids scanning irrelevant physical partitions |
| predicate pushdown | Pushes supported filtering closer to the data source |
The important performance principle is:
reduce early
→ shuffle less
→ carry less data
→ do less work
What to look for
Join is expensive even though output is small
Check whether both sides were filtered and projected before the join.
A narrow output does not help if Spark still shuffled wide inputs.
Filter returns unexpectedly few rows
Check:
- null behavior
- string formatting
- dtype
- date boundaries
- timezone semantics
- unexpected source values
Filter returns zero rows after a schema change
The source may now contain different codes, types, or formats.
Inspect actual values before changing the predicate.
Physical plan still scans a large amount of data
Check whether:
- the filter can be pushed down
- partition pruning is possible
- you wrapped partition columns in transformations
- source layout supports pruning
Driver memory grows during filtering
Look for:
collect()- Python lists of keys
toPandas()- row-wise processing
Filtering should usually stay distributed.
Same string cleanup appears in many filters
Normalize once in a controlled transformation rather than repeating expensive expressions everywhere.
Production notes
Spark transformations are lazy
select() and filter() build a plan.
Actual work begins when an action runs, such as:
count()
show()
write...
collect()
Do not benchmark only DataFrame construction.
Pushdown is source-dependent
Delta/Parquet can often benefit from predicate and column pushdown.
Not every expression is pushdown-compatible.
Use the physical plan instead of assuming optimization occurred.
Null semantics are SQL-like
Spark uses three-valued logic:
true
false
unknown
A comparison with null usually returns unknown, which does not pass a filter.
Make null handling explicit.
Large isin() lists are a smell
A few literal values are fine.
Thousands or millions of values should usually become a joinable DataFrame.
Column width matters
A DataFrame with:
10 million rows × 5 columns
can be dramatically cheaper to shuffle than:
10 million rows × 80 columns
Select early.
Filter order is usually optimizer-managed
Do not micro-optimize by manually reordering simple deterministic predicates unless the plan or runtime shows a real issue.
Focus more on:
- source pruning
- expensive UDFs
- joins
- partition layout
Expensive user-defined functions can block optimization
A Python UDF inside a filter can introduce serialization overhead and reduce optimization opportunities.
Prefer built-in Spark SQL functions.
Common mistakes
1. Using Python and / or
Spark column expressions require & and |.
2. Forgetting parentheses around conditions
Operator precedence can produce errors or unintended logic.
3. Using == None
Use isNull() / isNotNull().
4. Assuming != value keeps nulls
It does not.
Null comparisons evaluate to unknown.
5. Collecting reference keys to Python
Use a semi join instead.
6. Selecting * through the whole pipeline
Wide DataFrames increase shuffle and memory cost.
7. Filtering after the expensive join
Reduce rows before shuffle when the business logic allows it.
8. Using regex for simple prefix/suffix logic
Use startswith(), endswith(), or contains() when they express the rule clearly.
9. Assuming every filter is pushed down
Check the plan.
10. Using limit() as proof that data is correct
A small preview cannot validate the full dataset.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.