Lesson 6 of 15 · Lab

Null vs blank vs zero: cleaning missing values

Cleaning fixes how values are written without changing what they mean, by trimming stray spaces and making case consistent. Missing values are a separate decision. A null means unknown, a blank is empty text and a zero is a real amount.

This lab's 18 rows come from fictional Vandelay Coffee Supply, which writes booked with capitals and with a trailing space, so a filter on exactly booked would miss three invoices worth €3,040.

Cleanse could lowercase every text column at once. What would that do to customer_id C2?

It becomes c2, and no longer matches the customer register. Case is meaningful in a key and only noise in a status, so the rule has to name the column.

Lowercase a single column

  1. Select Summarize, the last block of this live flow in Strata, Oplema's browser app for data prep. Trim Whitespace is already on, and status still has five values, including Booked and BOOKED.
  2. Select Cleanse. Open Per-Column Overrides, select the arrow at the right of status, scroll down and set its Modify Case to Lower.
  3. Select Summarize. The capitalized spellings should have merged into booked, with pending and cancelled untouched.
  4. Select Cleanse and scroll the preview to INV-1014. amount_eur is still NULL, not 0, and customer_id is still uppercase.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

How the statuses should add up

booked 15, pending 2, cancelled 1, which adds back to 18. Trim also fixed INV-1012's key, C2, which would otherwise have failed to find its customer.

Cleaning rules belong to columns

One block holds two rules here. Trim applies to every text column, because a stray space carries no meaning. Lowercase applies only to status, through an override.

Cleansing never decides which row is true, though. INV-1008 still has two clean rows, and they still disagree, which finding duplicate rows settles. See the Cleanse reference.

Keep, fill or exclude a missing value

Take the values 10, a null and 20. The average skips the null and comes to 15. Fill the null with zero and the average drops to 10.

Three choices for a missing value
ChoiceWhen
Keep it nullThe value is unknown, and the row still counts
Fill itA stated rule gives the value, as when no discount recorded means none
Exclude the rowIt can't serve the question, and you record that you removed it

INV-1014 stays null. Its price really is unknown, and the sum simply skips it. Someone in Finance still has to price that invoice, so ask. For rule-based filling, see Impute.

Common mistakes follow from mixing these up. Filling with zero quietly lowers every average. Treating TBC, N/A or an empty string as data leaves a column that looks complete. And merging similar names, such as two companies with one word in common, changes meaning, so it goes beyond cleaning.

TRIM, LOWER and COALESCE

In SQL, LOWER(TRIM(status)) cleans the text, NULLIF(status, '') turns a blank into a null, and COALESCE(discount, 0) fills a null with zero. In Excel, =LOWER(TRIM(A2)) does the same job in a helper column, and =IF(A2="", 0, A2) fills a blank. Excel's AVERAGE skips empty cells but counts zeros, like SQL's AVG.