17. Handling nulls

Missing data is everywhere, and PySpark has sharp edges around it. Our orders has real gaps: two rows miss quantity, three miss country.

Finding nulls

isNull() and isNotNull() are column tests you filter on:

orders.filter(F.col("quantity").isNull()).select("order_id", "product", "quantity").show()

Counting the nulls in a column is a one-liner worth memorizing:

orders.filter(F.col("country").isNull()).count()

Filling and dropping

na.fill replaces nulls; pass a dict to fill different columns with type-appropriate values:

orders.na.fill({"quantity": 0, "country": "unknown"}).filter(
    F.col("order_id").isin(1010, 1012)
).show()

na.drop removes rows with nulls — use subset to only care about certain columns:

print("all rows:", orders.count())
print("rows with a country:", orders.na.drop(subset=["country"]).count())

coalesce — first non-null wins

coalesce(a, b, c, …) returns the first argument that isn't null. Perfect for "use this, or fall back to that":

orders.select(
    "order_id",
    F.coalesce("country", F.lit("unknown")).alias("country"),
).show(5)

The filter null-trap (revisited)

Recall from Module 1: a comparison against null is itself null, not false — so filtering drops those rows, even with a negated condition.

# quantity is null for two orders. NEITHER of these includes them:
print("qty >= 2 :", orders.filter(F.col("quantity") >= 2).count())
print("qty <  2 :", orders.filter(F.col("quantity") < 2).count())
# to keep the nulls, ask explicitly:
print("qty < 2 or null:", orders.filter((F.col("quantity") < 2) | F.col("quantity").isNull()).count())

If your row counts don't add up after a filter, suspect nulls first.

Your turn

Return order_id and a country column where missing countries are replaced with the string "unknown" (use coalesce), from orders. Assign to result.

result = orders  # <- select order_id and coalesce(country, lit("unknown")) AS country
result.show()

result = orders.select(
    "order_id",
    F.coalesce("country", F.lit("unknown")).alias("country"),
)