DataFrame Null & Missing Value Handling

Manipulating, filling, replacing, and dropping null values in PySpark using DataFrameNaFunctions (dropna, fillna, and replace).

What is Null Handling in PySpark?

Real-world datasets are often incomplete, containing null, None, or missing fields. In PySpark, we manage missing values through the DataFrameNaFunctions sub-API, which is accessed via df.na or directly as DataFrame methods like dropna() and fillna().

Core Null Handling Methods

1. Dropping Nulls with dropna() or na.drop()

Removes rows containing null values in all or specific columns:

# Drop row if ANY column is null
df.dropna()

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

# Drop row only if specific subset columns are null
df.dropna(subset=["email", "phone"])

2. Filling Nulls with fillna() or na.fill()

Imputes missing values with a default constant. PySpark matches types automatically:

# Fill all null numeric columns with 0, and all null string columns with "UNKNOWN"
df.fillna(0).fillna("UNKNOWN")

# Fill specific columns with custom defaults using a dictionary map
df.fillna({"salary": 50000, "department": "Unassigned"})

3. Replacing Values with replace() or na.replace()

Replaces specific matching values (null or non-null) with alternative values:

# Replace specific strings across all columns
df.replace("N/A", "Unknown")

# Replace subset of specific values using a dictionary mapping
df.replace({"Male": "M", "Female": "F"}, subset=["gender"])

Example Usage Pipeline

Below is a complete, copy-paste-ready PySpark script demonstrating missing value management:

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

# 1. Setup local Spark session
spark = SparkSession.builder \
    .appName("DataFrame Null Handling Demo") \
    .master("local[*]") \
    .getOrCreate()

# 2. Dummy dataset (Patient Registrations)
data = [
    ("PAT_01", "John Smith", 45, "M", None),
    ("PAT_02", "Jane Doe", None, "F", "999-888-7777"),
    ("PAT_03", "N/A", 29, None, None),
    ("PAT_04", None, None, None, None), # Completely empty row
]
columns = ["patient_id", "name", "age", "gender", "phone"]
df = spark.createDataFrame(data, columns)

# 3. Apply Null Handling ETL Rules:
# Rule A: Drop rows where patient id is null (toxic records)
# Rule B: Drop rows where ALL columns (except patient id) are null
# Rule C: Replace string "N/A" with "Unknown"
# Rule D: Fill null age with the median age (e.g. 35) and null gender with "U"
cleaned_df = df \
    .dropna(subset=["patient_id"]) \
    .filter(~(df.name.isNull() & df.age.isNull() & df.gender.isNull() & df.phone.isNull())) \
    .replace("N/A", "Unknown", subset=["name"]) \
    .fillna({"age": 35, "gender": "U", "phone": "NO_PHONE"})

# 4. Show results
print("=== Original Patient Data (with Nulls) ===")
df.show(truncate=False)

print("=== Cleaned and Imputed Patient Data ===")
cleaned_df.show(truncate=False)

Rendered Output:

=== Original Patient Data (with Nulls) ===
+----------+----------+----+------+------------+
|patient_id|name      |age |gender|phone       |
+----------+----------+----+------+------------+
|PAT_01    |John Smith|45   |M     |null        |
|PAT_02    |Jane Doe  |null |F     |999-888-7777|
|PAT_03    |N/A       |29   |null  |null        |
|PAT_04    |null      |null |null  |null        |
+----------+----------+----+------+------------+

=== Cleaned and Imputed Patient Data ===
+----------+----------+---+------+------------+
|patient_id|name      |age|gender|phone       |
+----------+----------+---+------+------------+
|PAT_01    |John Smith|45 |M     |NO_PHONE    |
|PAT_02    |Jane Doe  |35 |F     |999-888-7777|
|PAT_03    |Unknown   |29 |U     |NO_PHONE    |
+----------+----------+---+------+------------+