Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 48 ยท DataFrames and Spark SQL

Filtering, Sorting and Deduplicating

Learn to filter rows, sort with orderBy, remove duplicates with distinct and dropDuplicates, use limit and handle nulls correctly in Spark DataFrames.

After you know how to build columns, the next everyday job is deciding which rows to keep, in what order, and without repeats. Spark has direct tools for each: filter (also called where), orderBy, distinct, dropDuplicates and limit. They look simple, but each hides a detail that trips up newcomers: filters silently drop rows with nulls, sorting is an expensive shuffle, and limit without a sort returns arbitrary rows.

This lesson goes through them one at a time, with a special focus on null handling, because nulls cause more wrong results in real data pipelines than anything else. It continues from Columns and Expressions.

Filtering rows

filter and where are exact synonyms. They keep the rows for which a condition is true.

Python
from pyspark.sql.functions import col

df.filter(col("amount") > 20)
df.where("amount > 20 AND status = 'paid'")             # SQL text also works
df.filter((col("status") == "paid") & (col("amount") > 20))
df.filter(col("status").isin("paid", "shipped"))
df.filter(col("amount").between(10, 50))                # inclusive on both ends
df.filter(col("name").startswith("Al"))
df.filter(col("name").like("A%"))                       # SQL LIKE: % and _
df.filter(col("name").rlike("^[A-C]"))                  # regular expression
df.filter(~col("status").isin("cancelled"))             # negation

Remember to use &, | and ~ with parentheses, not Python's and, or, not. Filtering is a narrow transformation: each partition is filtered independently, with no shuffle. When the data comes from Parquet, Spark can even push the condition into the file reader so entire row groups are skipped (see Catalyst Optimizer and Query Plans).

Nulls and three-valued logic

SQL, and therefore Spark, has three truth values: true, false and unknown (null). Any comparison with null, even null = null, gives null. A filter keeps a row only when the condition is true, so rows where it is false and rows where it is null are both dropped.

flowchart LR
    R["Row"] --> C{"Condition"}
    C -->|"true"| K["Kept"]
    C -->|"false"| D["Dropped"]
    C -->|"null (unknown)"| D

A filter keeps only rows where the condition is true; unknown counts as not kept.

The consequence surprises people: df.filter(col("status") != "cancelled") does not return "everything that is not cancelled". Rows where status is null evaluate to null and vanish too. To keep them, test for null explicitly:

Python
df.filter(col("status").isNull() | (col("status") != "cancelled"))
df.filter(~col("status").eqNullSafe("cancelled"))      # null-safe comparison, SQL: NOT (status <=> 'cancelled')
df.filter(col("status").isNull())                      # isNull() and isNotNull() are the reliable null tests

Never write col("x") == None; it produces null and filters out everything. Use isNull(). The null-safe equality eqNullSafe (SQL <=>) treats two nulls as equal and null versus a value as false, never as unknown.

Also distinguish null from NaN. NaN ("not a number") is a real floating-point value; Spark treats NaN = NaN as true and sorts NaN as larger than any other number. Test for it with isnan(col). Missing data is usually null, but numbers read from some sources can contain NaN.

The stand-in below models three-valued logic with pandas, whose nullable boolean type can hold <NA> (unknown). The helper sql_ne makes the comparison behave like SQL instead of Python.

Python Why != silently drops null rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Ids 3 and 6 have a null status, so the first filter loses them even though they are clearly "not cancelled". That is the behavior to keep in mind whenever you write a negative condition.

Sorting with orderBy

orderBy (alias sort) sorts the whole DataFrame. You can pass column names, column expressions and sort directions.

Python
df.orderBy("amount")                                   # ascending
df.orderBy(col("amount").desc())
df.orderBy(col("status").asc(), col("amount").desc())  # several keys
df.orderBy(col("amount").desc_nulls_last())

Null placement has a default you should know: ascending puts nulls first, descending puts nulls last. The variants asc_nulls_first, asc_nulls_last, desc_nulls_first and desc_nulls_last let you choose.

Sorting is a wide operation. Spark samples the data to pick range boundaries, shuffles rows so each partition holds a range of keys, and sorts each partition (see Shuffles and Stages). That is why a global orderBy on a huge DataFrame is costly, and why you should sort as late as possible and only when you need the order. sortWithinPartitions sorts inside each partition without a shuffle, which is often enough before writing files. Note that the order of rows is not preserved by most other operations, so never rely on an earlier sort surviving a join or aggregation.

limit and deterministic top-N

limit(n) returns a DataFrame of at most n rows. It is a transformation, but df.limit(5).show() is cheap because Spark stops early. The catch: without a sort, you get whichever rows Spark reaches first, which can change between runs. For a top-N result, sort first:

Python
df.orderBy(col("amount").desc_nulls_last()).limit(3)    # three biggest orders, deterministically

Spark recognizes this pattern and executes it efficiently without a full sort. Compare with show(n) and take(n), which are actions returning rows to you, while limit stays inside Spark.

Python Sorting with null placement, then top-N

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

distinct and dropDuplicates

distinct() removes rows that are identical in every column. dropDuplicates(subset) removes rows that are identical in the listed columns only, keeping one row from each group of duplicates.

Python
df.distinct()
df.dropDuplicates()                       # same as distinct()
df.dropDuplicates(["customer"])           # one row per customer
df.select("customer").distinct().count()  # number of different customers

Both are wide transformations: Spark has to bring equal rows together, so there is a shuffle. Two details matter. First, nulls are considered equal to each other here (unlike in a filter or a join), so rows with a null key collapse into one. Second, when you use a subset, which row survives is not defined: Spark keeps an arbitrary one. If you need the latest record per key, do not use dropDuplicates; use a window function with row_number() over a sort order (see Window Functions).

Handling null values

Three tools cover most cleanup. They live on df.na, with shortcuts on the DataFrame itself.

Python
df.na.drop()                              # drop rows with ANY null    (how="any" is the default)
df.na.drop(how="all")                     # drop rows where ALL columns are null
df.na.drop(subset=["amount"])             # only look at selected columns
df.na.fill(0)                             # fill nulls in numeric columns with 0
df.na.fill({"status": "unknown", "amount": 0.0})   # per-column defaults
df.na.replace(["n/a", ""], None)          # turn placeholder strings into real nulls

from pyspark.sql.functions import coalesce, lit
df.withColumn("amount", coalesce(col("amount"), lit(0.0)))   # first non-null value

fill only touches columns whose type matches the fill value: fill(0) leaves string columns alone. Filling hides missing data, so decide whether zero or "unknown" is honest for your analysis; sometimes a null should stay a null. coalesce is the expression form, handy inside select.

Python distinct, dropDuplicates and null cleanup

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real versus stand-in. Spark runs these operations in parallel per partition, shuffles for orderBy, distinct and dropDuplicates, and pushes filters into the file readers. The pandas stand-ins run on one machine in one pass. They reproduce the visible rules: three-valued logic in filters, nulls-first/nulls-last defaults, deduplicating on a subset, and the null-handling helpers.

Common mistakes

  • Writing col("x") != "a" and expecting null rows to stay.
  • Testing nulls with == None instead of isNull().
  • Using limit without orderBy and expecting stable results.
  • Using dropDuplicates(subset) to "keep the latest" row.
  • Sorting early in a pipeline: later steps do not keep the order, and the sort is a shuffle.
  • Filling every null with zero without thinking about what zero means in the data.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A DataFrame has status values 'paid', 'cancelled' and null. What does df.filter(col('status') != 'cancelled') return?

    null != 'cancelled' evaluates to null (unknown), and a filter keeps only rows where the condition is true, so the null rows are dropped as well.
  2. 2. What is Spark's default null placement when you sort ascending?

    Ascending sorts put nulls first and descending sorts put them last. Use asc_nulls_last and the other variants to change that.
  3. 3. How do you reliably get the three largest orders?

    limit alone returns whichever rows Spark reaches first. Sorting first makes the top-N deterministic.
  4. 4. df.dropDuplicates(['customer']) is applied to a DataFrame with several rows per customer. Which row is kept for each customer?

    Spark does not define which row survives. To keep a particular row, such as the latest, use a window function with row_number.
  5. 5. Which is the correct null test in a filter?

    Comparisons with null yield null, so == None filters out every row. isNull() returns true or false.

Summary

  • filter and where keep rows where the condition is true; they are narrow and can be pushed into file readers.
  • Conditions on null give unknown, and unknown rows are dropped, so negative conditions like != lose null rows unless you add isNull().
  • orderBy is a wide operation; ascending puts nulls first and descending puts them last by default.
  • limit needs an orderBy for deterministic results; sort first, then limit for top-N.
  • distinct compares all columns, dropDuplicates(subset) compares chosen ones but keeps an arbitrary row; both shuffle and treat nulls as equal.
  • Handle missing data with na.drop, na.fill, na.replace and coalesce, and fill only when the replacement is honest.

Next: summarize data per group in Grouping and Aggregations.