Browse cheat sheets · PySpark

Technology: PySparkCategory: Data TransformationDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Select specific columns
  5. 2. Select with aliases
  6. 3. Add or replace a column with withColumn
  7. 4. Select all existing columns plus one expression
  8. 5. Drop columns explicitly
  9. 6. Filter with one condition
  10. 7. Filter with multiple conditions
  11. 8. Do not use Python and / or
  12. 9. Filter for nulls
  13. 10. Understand three-valued null logic
  14. 11. Filter by a list with isin
  15. 12. Exclude values
  16. 13. Filter numeric ranges
  17. 14. Filter strings with contains
  18. 15. Filter with startswith and endswith
  19. 16. Filter with like
  20. 17. Filter with regular expressions
  21. 18. Filter dates with explicit boundaries
  22. 19. Cast before date comparison when the source is text
  23. 20. Filter timestamps safely
  24. 21. Filter using SQL expression syntax
  25. 22. Build dynamic filter expressions safely
  26. 23. Filter before a join
  27. 24. Select only necessary columns before a join
  28. 25. Avoid ambiguous columns after joins
  29. 26. Use a list of join keys when names match
  30. 27. Use left-semi joins for existence filtering
  31. 28. Use left-anti joins for missing-reference detection
  32. 29. Avoid filtering by collecting keys to Python
  33. 30. Avoid row-by-row Python filtering
  34. 31. Prefer built-in functions over Python UDF filters
  35. 32. Inspect whether filters reached the physical plan
  36. 33. Inspect Parquet/Delta partition filters in the plan
  37. 34. Avoid wrapping partition columns unnecessarily
  38. 35. Use limit() only for bounded inspection
  39. 36. Use sample() for approximate inspection
  40. 37. Check row counts before and after an important filter
  41. 38. Aggregate filter diagnostics in one pass
  42. 39. Validate unexpected filter results
  43. 40. Normalize repeated string logic once
  44. What the result means
  45. What to look for
  46. Join is expensive even though output is small
  47. Filter returns unexpectedly few rows
  48. Filter returns zero rows after a schema change
  49. Physical plan still scans a large amount of data
  50. Driver memory grows during filtering
  51. Same string cleanup appears in many filters
  52. Production notes
  53. Spark transformations are lazy
  54. Pushdown is source-dependent
  55. Null semantics are SQL-like
  56. Large isin() lists are a smell
  57. Column width matters
  58. Filter order is usually optimizer-managed
  59. Expensive user-defined functions can block optimization
  60. Common mistakes
  61. 1. Using Python and / or
  62. 2. Forgetting parentheses around conditions
  63. 3. Using == None
  64. 4. Assuming != value keeps nulls
  65. 5. Collecting reference keys to Python
  66. 6. Selecting * through the whole pipeline
  67. 7. Filtering after the expensive join
  68. 8. Using regex for simple prefix/suffix logic
  69. 9. Assuming every filter is pushed down
  70. 10. Using limit() as proof that data is correct
  71. Next step
  72. 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.

Next step

Use the symptom to choose the next action.

  • Too many rows reach a join → move safe filters earlier.
  • Too many columns reach a join → project only required columns first.
  • Filter returns no rows → inspect actual values, nulls, and dtypes.
  • Reference membership uses Python lists → replace with a semi/anti join.
  • Null behavior is surprising → make null semantics explicit.
  • Date filtering is wrong → use typed dates/timestamps and half-open intervals.
  • Scan remains large → inspect explain("formatted") for pushdown/partition pruning.
  • Filtering uses Python UDFs → replace with built-in Spark functions when possible.
  • Data still feels too large after correct pruning → move to PySpark Performance Quick Reference and inspect partitioning/shuffle.

← All cheat sheets