7.2 Handling Missing and Null Data

Real-world datasets are rarely pristine. Missing fields, unrecorded measurements, corrupted values, and malformed inputs are pervasive across enterprise data pipelines. Apache Spark provides robust, dedicated APIs for detecting, filtering, dropping, imputing, and replacing missing data.

To use these tools effectively and succeed on certification exams, developers must understand how Spark models nulls, the interaction between SQL NULL and IEEE NaN, and the precise behavior of the DataFrameNaFunctions suite (df.na).

---

The Taxonomy of Missing Data in Spark

Spark distinguishes between three related but fundamentally distinct concepts of missing or undefined data:

1. SQL NULL: Represents missing, unknown, or unassigned data in any SQL data type. SQL three-valued logic applies: comparisons involving NULL evaluate to UNKNOWN (NULL) rather than TRUE or FALSE.
2. Python None: The client-side representation of SQL NULL in the PySpark environment. When inspecting individual Row objects in Python or passing literal values, None translates to SQL NULL in the JVM.
3. IEEE 754 NaN (Not a Number): Specific to floating-point numeric columns (FloatType and DoubleType). Represents undefined mathematical results (such as an explicitly supplied float("nan"); Spark division-by-zero behavior instead depends on ANSI mode and is not a reliable NaN constructor).

Critical Differences Between NULL and NaN

---

Detecting and Filtering Missing Values

To filter rows based on nullity, PySpark provides dedicated Column methods:

from pyspark.sql.functions import col, isnan

# Filter rows where email is missing (NULL)
df_missing_email = df.filter(col("email").isNull())

# Filter rows where email is populated (NOT NULL)
df_valid_email = df.filter(col("email").isNotNull())

# Filter rows where a float measurement is NaN
df_nan_temp = df.filter(isnan(col("temperature")))

# Filter rows where a float column is EITHER null OR NaN
df_invalid_temp = df.filter(col("temperature").isNull() | isnan(col("temperature")))

The Equality Comparison Trap

A critical mistake made by new PySpark developers is attempting to test for nulls using equality comparisons:

# INCORRECT: Returns an empty DataFrame or null condition
# df_bad = df.filter(col("email") == None)
# df_bad2 = df.filter(col("email") == "null")

In SQL three-valued logic, NULL == NULL does not evaluate to TRUE; it evaluates to UNKNOWN (NULL). Because filter operations only retain rows where the condition evaluates strictly to TRUE, writing col("email") == None drops all rows including those where email is null! Always use col.isNull() and col.isNotNull().

---

Dropping Missing Data: df.na.drop() / df.dropna()

The DataFrameNaFunctions suite provides df.na.drop(), which is also available via the top-level DataFrame alias df.dropna(). Both methods are completely identical.

df.na.drop(how="any", thresh=None, subset=None)

Parameter Breakdown

1. how (str):
   • "any" (default): Drops a row if any of the considered columns contain null or NaN.
   • "all": Drops a row only if all of the considered columns contain null or NaN.
2. thresh (int, optional):
   • If specified, overrides the how parameter.
   • Defines the minimum number of non-null values a row must possess across the examined columns to be kept. Rows with fewer non-null values than thresh are dropped.
3. subset (List[str], optional):
   • Restricts null evaluation to a specific list of column names. If omitted, all columns in the DataFrame are evaluated.

Practical Dropping Scenarios

# 1. Drop row if ANY column contains null
df_clean1 = df.na.drop()

# 2. Drop row ONLY if ALL columns are null
df_clean2 = df.dropna(how="all")

# 3. Drop row if customer_id OR email is null (ignoring nulls in other columns)
df_clean3 = df.dropna(how="any", subset=["customer_id", "email"])

# 4. Keep row only if at least 3 columns have non-null values
df_clean4 = df.dropna(thresh=3)

---

Imputing Missing Data: df.na.fill() / df.fillna()

Rather than discarding rows with missing values, pipelines frequently impute fallback defaults using df.na.fill() (aliased as df.fillna()):

df.na.fill(value, subset=None)

Type Matching Semantics

A pivotal feature of fillna() is data type matching. The type of the value argument determines which columns are eligible for imputation:

• If value is an int or float, Spark replaces nulls only in numeric columns (IntegerType, DoubleType, etc.).
• If value is a str, Spark replaces nulls only in string columns (StringType).
• If value is a bool, Spark replaces nulls only in boolean columns (BooleanType).

# Replaces nulls with 0 in numeric columns only (string columns remain null)
df_filled_num = df.fillna(0)

# Replaces nulls with 'UNKNOWN' in string columns only (numeric columns remain null)
df_filled_str = df.fillna("UNKNOWN")

Multi-Column Dictionary Imputation

To impute different default values across distinct columns and data types in a single operation, pass a Python dictionary mapping column names to replacement values:

# Dictionary-based multi-type imputation
imputation_dict = {
    "age": 0,
    "salary": 50000.0,
    "department": "Unassigned",
    "is_active": False
}

df_imputed = df.fillna(imputation_dict)

When using a dictionary, the subset parameter is not required because the dictionary keys define the target columns.

---

Replacing Arbitrary Values: df.na.replace()

While fillna() specifically targets SQL NULL and NaN, datasets often represent missing or invalid data using sentinel values such as "-999", "N/A", "UNKNOWN", or empty strings "".

The df.na.replace() method searches and replaces specific values across target columns:

# Replace sentinel strings across all string columns
df_replaced = df.na.replace("-999", None)

# Replace multiple distinct values using a dictionary mapping on specific columns
replace_rules = {
    "N/A": "Unknown",
    "missing": "Unknown",
    "null_str": "Unknown"
}
df_clean = df.na.replace(replace_rules, subset=["city", "state"])

---

Advanced Null Handling: coalesce() and Conditional Fallbacks

Multi-Column Fallback with F.coalesce(*cols)

The coalesce() function from pyspark.sql.functions evaluates a sequence of column expressions from left to right and returns the first non-null value encountered for each row:

from pyspark.sql.functions import coalesce, col, lit

# Establish contact hierarchy: mobile -> home -> work -> fallback literal
df_contact = df.withColumn(
    "primary_contact",
    coalesce(
        col("mobile_phone"),
        col("home_phone"),
        col("work_phone"),
        lit("No Contact Available")
    )
)

Conditional Imputation with when().otherwise()

For complex imputation logic depending on multiple criteria, combine when() and otherwise():

from pyspark.sql.functions import when

# Illustrative policy defaults by department; these are not computed averages
df_conditional = df.withColumn(
    "imputed_salary",
    when(col("salary").isNotNull(), col("salary"))
    .when(col("department") == "Engineering", lit(115000.0))
    .when(col("department") == "Sales", lit(85000.0))
    .otherwise(lit(60000.0))
)

Summary of Missing Data Handling APIs

Mastering these missing data handling techniques ensures your Spark data pipelines remain resilient against malformed inputs and produce clean, analytics-ready datasets.