Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 48 ยท DataFrames and Spark SQL

Cleaning Data: Nulls, Fill, Replace and Outliers

Clean Spark DataFrames: find nulls, drop rows or columns, fill per column, replace values and remove outliers with the IQR rule, with runnable stand-ins.

Real data is messy. Fields arrive empty, a "missing" marker such as -1 or N/A sneaks in, and a typo turns a price of 19.99 into 1999. If you aggregate before cleaning, the averages, counts and joins quietly lie to you. Spark gives you a small toolbox for this: isNull, dropna, fillna, replace and ordinary filters.

This lesson shows each tool on one small table, explains the choices (drop, fill or keep), and finishes with a classic outlier rule. It builds on Filtering, Sorting and Deduplicating and Columns and Expressions. Spark cannot run on this site, so the PySpark code is read-only. The runnable examples are plain-Python stand-ins that apply the same rules to a list of dictionaries and print the result you would see.

The sample table

Python
rows = [
    (1, "Ann", 34, 52000.0, "DE"),
    (2, "Bo",  None, 48000.0, "FR"),
    (3, None,  29, None, None),
    (4, "Dee", 41, 61000.0, "DE"),
    (5, "Eli", None, None, None),
]
people = spark.createDataFrame(rows, ["id", "name", "age", "salary", "country"])

Nulls are real values of the type system: None in Python becomes SQL NULL. A null is not equal to anything, not even another null, so col("age") == None never matches. Use the dedicated tests:

Python
from pyspark.sql import functions as F

people.filter(F.col("age").isNull()).show()          # rows where age is missing
people.filter(F.col("age").isNotNull()).show()
people.filter(F.col("age").eqNullSafe(None))         # null-safe equality, true for null = null

To see how bad things are, count nulls per column in one pass. count ignores nulls, so count a flag instead:

Python
people.select([
    F.sum(F.col(c).isNull().cast("int")).alias(c) for c in people.columns
]).show()
# +---+----+---+------+-------+
# | id|name|age|salary|country|
# +---+----+---+------+-------+
# |  0|   1|  2|     2|      2|
# +---+----+---+------+-------+

Dropping rows with dropna

dropna (also df.na.drop) removes rows that contain nulls. Three parameters control how aggressive it is:

Python
people.dropna()                                  # how="any": drop if ANY column is null
people.dropna(how="all")                         # drop only rows where EVERY column is null
people.dropna(thresh=4)                          # keep rows with at least 4 non-null values
people.dropna(subset=["age", "salary"])          # only look at these columns

thresh overrides how. subset is the one you will use most: you rarely care about nulls everywhere, only in the columns your analysis needs. Dropping is the right answer when the missing field makes the row useless (a sale without an amount). It is the wrong answer when nulls are common, because you throw away good data in the other columns.

Filling with fillna

fillna (also df.na.fill) replaces nulls with a value. Pass one value to fill every column of a matching type, or a dictionary for per-column choices:

Python
people.fillna(0)                                 # numeric columns only
people.fillna("unknown")                         # string columns only
people.fillna({"age": 0, "salary": 0.0, "country": "n/a"})

A single value is only applied to columns of the same type, so fillna(0) leaves string columns alone. Use the dictionary form so every decision is explicit. Often a constant is poor: filling a missing age with 0 drags the average down. A better fill is a statistic, for example the median, which you compute first and then pass in:

Python
median_age = people.approxQuantile("age", [0.5], 0.01)[0]
people.fillna({"age": median_age})

For many columns at once, the Imputer in MLlib fits means or medians as part of a pipeline (see MLlib Overview and Pipelines).

Replacing values with replace

Some "missing" values are not null at all. replace swaps one value for another:

Python
df.replace(-1, None, subset=["age"])             # sentinel -1 becomes a real null
df.replace(["N/A", "n/a", ""], None, subset=["country"])
df.replace({"Germany": "DE", "France": "FR"}, subset=["country"])

Converting sentinels to real nulls first is a good habit, because every null-aware function (dropna, avg, coalesce) then behaves correctly. For one-off logic use F.when(...).otherwise(...) or F.coalesce(col, F.lit(0)), which returns the first non-null argument.

Dropping empty columns

Sometimes a whole column is null. Find them with one aggregation and drop them:

Python
counts = people.select([F.count(c).alias(c) for c in people.columns]).first().asDict()
empty = [c for c, n in counts.items() if n == 0]
people = people.drop(*empty)

F.count(col) counts non-null values, so a column with count 0 is entirely null.

Python dropna, fillna and replace on a list of 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.

Outliers with the IQR rule

An outlier is a value far from the rest. It might be an error (a negative age) or a real extreme (a very large order). Decide which before removing anything. A widely used rule of thumb is the interquartile range (IQR): compute the first quartile Q1 and third quartile Q3, let IQR = Q3 - Q1, and treat anything outside [Q1 - 1.5 * IQR, Q3 + 1.5 * IQR] as an outlier.

Python
q1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.001)
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr

clean = df.filter(F.col("amount").between(low, high))
flagged = df.withColumn("is_outlier", ~F.col("amount").between(low, high))

approxQuantile is approximate on purpose: an exact quantile would need a full sort of the data. The last argument is the relative error; smaller means slower and more exact. Note that between returns null for a null input, which filter treats as false, so null rows also disappear. Flagging (the second form) is often better than deleting, because you can inspect and report what you excluded.

Python Finding outliers with the IQR rule

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. Real approxQuantile uses an approximate streaming algorithm across partitions, so its quartiles can differ slightly from the exact interpolation used here. The stand-ins also work on a small in-memory list; Spark applies the same logic in parallel to every partition.

Choosing a strategy

Situation Good choice
Key field is missing, row is useless dropna(subset=[...])
Numeric field, few nulls, model input fill with median or mean (Imputer)
Category missing fill with an explicit label such as "unknown"
Placeholder like -1 or "N/A" replace(..., None) first, then decide
Whole column empty drop the column
Extreme values flag with IQR, then review before deleting

Whatever you choose, record how many rows each step touched. Cleaning that silently drops 30% of the data is a bug you will meet later.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does df.filter(col('age') == None) return no rows?

    In SQL logic NULL = NULL is unknown, not true. Use isNull(), isNotNull() or eqNullSafe().
  2. 2. What does df.dropna(thresh=4) do?

    thresh is the minimum number of non-null values a row needs to be kept, and it overrides how.
  3. 3. df.fillna(0) was run on a table with numeric and string columns. What happens to null strings?

    A single fill value only applies to columns of a matching type. Use a dictionary to fill strings explicitly.
  4. 4. Which values count as outliers under the IQR rule?

    The fences are Q1 - 1.5 x IQR and Q3 + 1.5 x IQR; anything outside them is flagged.

Summary

  • Test for nulls with isNull, isNotNull or eqNullSafe, never == None.
  • dropna takes how, thresh and subset; prefer subset so you only judge the columns that matter.
  • fillna with a dictionary makes per-column decisions explicit; compute a median first when a constant would distort results.
  • replace converts sentinel values such as -1 or "N/A" into real nulls.
  • Count non-null values with F.count(col) to find and drop empty columns.
  • Use approxQuantile and the 1.5 x IQR fences to flag outliers, and review them before deleting.

Next: reshape data with Pivot, Unpivot and Union.