Null Is Not a Value

The card reader at The Daily Grind was down for part of Tuesday, so some orders have no tip recorded. Not a tip of zero: no tip on record. The shift manager asks two questions that sound like opposites: which orders were tipped nothing, and which were tipped something? Between them, the two answers should cover every order. They don't.

Watch it happen

Six rows of till_log:

from pyspark.sql import functions as F

till_log.filter(F.col("tip") == 0).count()   # 2
till_log.filter(F.col("tip") != 0).count()   # 2

Two and two make four, and there are six orders. Orders 2003 and 2005 are in neither answer. Put the test in a column and you can see why:

till_log.select("order_id", "tip", (F.col("tip") != 0).alias("tipped"))

Is an unknown tip different from zero? Unknown. So the test doesn't come back true or false, it comes back null, and filter keeps a row only when the test is true. A null is dropped as firmly as a false, and nothing warns you.

Ask about null by name

== and != can't find a null, because comparing anything with null gives null. That includes comparing null with itself: F.col("tip") == None matches no rows at all. There are two methods made for the job:

till_log.filter(F.col("tip").isNull())      # 2003, 2005
till_log.filter(F.col("tip").isNotNull())   # the other four

When you want "everything except zero" to include the unknowns, say so:

till_log.filter((F.col("tip") != 0) | F.col("tip").isNull())

Replace it, if you mean to

coalesce returns the first of its arguments that isn't null. fillna does the same job for whole columns at once:

till_log.withColumn("tip", F.coalesce(F.col("tip"), F.lit(0.0)))
till_log.fillna({"tip": 0.0})

Both give the same table. F.lit(0.0) wraps a plain value so it can stand where a column is expected.

Filling is a decision about meaning, not a tidy-up. After it, the caramel macchiato says its customer tipped nothing, which nobody knows. Fill when zero is the truth. Otherwise keep the null and carry it honestly.

Label rows with when

when and otherwise are an if and an else, evaluated per row. The first branch that is true wins:

result = till_log.select(
    "order_id", "drink", "tip",
    F.when(F.col("tip").isNull(), "not recorded")
     .when(F.col("tip") >= 1, "generous")
     .otherwise("none")
     .alias("tipper"),
)

The null branch comes first on purpose. Take it out, and 2003 and 2005 fail tip >= 1 (the test is null, not true), fall through to otherwise, and are labelled none: the same silent mistake as the filter, wearing a different hat. Leave otherwise off entirely and every unmatched row gets null.

Four problems, in the order they build:

1. Not Paid in Cash — everything except cash, unknowns included.
2. Has an Email — find the rows where something is there.
3. Fill the Blanks — replace a null, and only the null.
4. The Phantom Watchers — clean, label, then filter, without losing a row.