19 Missing values

What should a hole in the table mean for your answer? drop_missing drops rows where a named column is missing. With no column named, it looks at every column.

The table is patchy, three products with one revenue that never arrived:

product	revenue
Widget	100
Gadget	NA
Doohickey	300

run('
  patchy
    then drop_missing [revenue]
')

product	revenue
Widget	100
Doohickey	300

patchy |> drop_missing(revenue)

product	revenue
Widget	100
Doohickey	300

patchy >> drop_missing(col.revenue)

product	revenue
Widget	100
Doohickey	300

patchy then drop_missing [revenue]

fill_missing replaces missing values with something.

run('
  patchy
    then fill_missing [revenue] as 0
')

product	revenue
Widget	100
Gadget	0
Doohickey	300

patchy |> fill_missing(revenue = 0)

product	revenue
Widget	100
Gadget	0
Doohickey	300

patchy >> fill_missing(revenue = 0)

product	revenue
Widget	100
Gadget	0
Doohickey	300

The filler has to be the same kind of thing the column holds, or the column would quietly stop being what it was.

collect(patchy |> fill_missing(revenue = "none"))

Error:
! 
illegal: `[revenue]` is a number and this fills it with text, which would change what the column holds. Fill it with a number instead, or convert the column first
  |
2 |   then fill_missing [revenue] as "none"
  |                                  ^^^^^^

try:
    collect(patchy >> fill_missing(revenue = "none"))
except GodError as refusal:
    print(refusal)


illegal: `[revenue]` is a number and this fills it with text, which would change what the column holds. Fill it with a number instead, or convert the column first
  |
2 |   then fill_missing [revenue] as "none"
  |                                  ^^^^^^

19.1 Taking the first column that has a value

fill_missing puts one value in every gap. When the value you want is in another column, first_present reads across the columns you name and takes the first one that is there.

contacts holds three ways of reaching someone and not everybody has all three:

name	mobile	landline	email
ana	555-01	555-99	a@x
bo	NA	555-02	b@x
cy	NA	NA	c@x

run('
  contacts
    then add [reach] as first_present([mobile], [landline], [email])
')

name	mobile	landline	email	reach
ana	555-01	555-99	a@x	555-01
bo	NA	555-02	b@x	555-02
cy	NA	NA	c@x	c@x

contacts |> add(reach = first_present(mobile, landline, email))

name	mobile	landline	email	reach
ana	555-01	555-99	a@x	555-01
bo	NA	555-02	b@x	555-02
cy	NA	NA	c@x	c@x

contacts >> add(reach = first_present(col.mobile, col.landline, col.email))

name	mobile	landline	email	reach
ana	555-01	555-99	a@x	555-01
bo	NaN	555-02	b@x	555-02
cy	NaN	NaN	c@x	c@x

contacts then add [reach] as first_present([mobile], [landline], [email])

Two things about it surprise people, and both matter before you rely on it.

The order is a priority rather than a set. It reads left to right and stops at the first column that has a value, so ana is reached on the mobile number even though a landline and an email are there too. Writing the same three columns in another order gives a different answer, which is the point of writing them in an order at all.

The only thing it skips is a missing value. A zero, an empty text and a no are all values, and they come back. sensors holds a reading and its backup for two sensors:

sensor	backup
0	99
NA	99

run('
  sensors
    then add [used] as first_present([sensor], [backup])
')

sensor	backup	used
0	99	0
NA	99	99

sensors |> add(used = first_present(sensor, backup))

sensor	backup	used
0	99	0
NA	99	99

sensors >> add(used = first_present(col.sensor, col.backup))

sensor	backup	used
0	99	0
NaN	99	99

sensors then add [used] as first_present([sensor], [backup])

The first sensor read zero, which is a reading, so it is kept. Only the row where the sensor recorded nothing at all takes its value from the backup. If you want zero treated as missing too, say so in a step of its own, because that is a decision about your data rather than about the word.

Every column it looks in has to hold the same kind of thing, since one of them is going to be the answer and a column holds one kind. If all of them are missing for a row, the answer for that row is missing.

SQL and dplyr both call this coalesce. It is first_present here because missing is already the grammar’s word for the absent value, so present is its opposite. And first names the part people most often misread: the columns are a priority order.

19.2 Carrying the last value forward

A reading taken hourly, where the sensor only reports when the value changes, leaves holes that are not really missing: the value is the last one you saw. latest fills a hole with the most recent value above it.

readings <- data.frame(
  taken_at = 1:6,
  reading  = c(10, NA, NA, 40, NA, 60)
)

run('
  readings
    then sort [taken_at]
    then add [reading] as latest([reading])
', readings = readings)

taken_at	reading
1	10
2	10
3	10
4	40
5	40
6	60

readings |> sort(taken_at) |> add(reading = latest(reading))

taken_at	reading
1	10
2	10
3	10
4	40
5	40
6	60

readings = pd.DataFrame({
    "taken_at": [1, 2, 3, 4, 5, 6],
    "reading":  [10, None, None, 40, None, 60],
})

readings >> sort(col.taken_at) >> add(reading = latest(col.reading))

taken_at	reading
1	10
2	10
3	10
4	40
5	40
6	60

readings then sort [taken_at] then add [reading] as latest([reading])

It fills a run of holes, not just one. That is the difference between this and reaching for previous, which looks back exactly one row and leaves the second hole of a pair still open.

Like every value that walks the rows, it needs a sort first, because “the last value” means nothing until something has said last in what order. And by restarts it for each group, so one group never borrows another’s value.

readings <- data.frame(
  sensor   = c("a", "a", "a", "b", "b", "b"),
  taken_at = c(1, 2, 3, 1, 2, 3),
  reading  = c(10, NA, NA, NA, 50, NA)
)

run('
  readings
    then sort [sensor], [taken_at]
    then add [reading] as latest([reading]) by [sensor]
', readings = readings)

sensor	taken_at	reading
a	1	10
a	2	10
a	3	10
b	1	NA
b	2	50
b	3	50

readings |>
  sort(sensor, taken_at) |>
  add(reading = latest(reading), by = sensor)

sensor	taken_at	reading
a	1	10
a	2	10
a	3	10
b	1	NA
b	2	50
b	3	50

readings = pd.DataFrame({
    "sensor":   ["a", "a", "a", "b", "b", "b"],
    "taken_at": [1, 2, 3, 1, 2, 3],
    "reading":  [10, None, None, None, 50, None],
})

(readings
  >> sort(col.sensor, col.taken_at)
  >> add(reading = latest(col.reading), by = col.sensor))

sensor	taken_at	reading
a	1	10
a	2	10
a	3	10
b	1	NaN
b	2	50
b	3	50

A row with nothing above it and nothing of its own stays missing, because there is no earlier value to carry. fill_missing after it puts a value there if you want one.

19.3 Where the holes go when you sort

Holes arrive on their own once you join two tables. join is taught in the next chapter; here it is only where the holes come from. sales sells a Sprocket and products has never heard of one, so joining them leaves rows whose maker nobody recorded:

run('
  sales
    then join products by [product]
    then summarize [total] as total([revenue]) by [maker]
    then sort [maker]
')

maker	total
Acme	700
Globex	720
Initech	400
NA	240

sales |>
  join(products, by = product) |>
  summarize(total = total(revenue), by = maker) |>
  sort(maker)

maker	total
Acme	700
Globex	720
Initech	400
NA	240

(sales
  >> join(products, by = col.product)
  >> summarize(total = total(col.revenue), by = col.maker)
  >> sort(col.maker))

maker	total
Acme	700
Globex	720
Initech	400
NaN	240

sales then join products by [product] then summarize [total] as total([revenue]) by [maker] then sort [maker]

Sorting raises a question the sort itself does not answer: the row with no maker has nothing to be ordered by, so where does it go? The grammar’s answer is last, and you get it without asking. Reading it aloud is the reason: the values you have, then the ones you do not. It stays last when you reverse the sort, too. descending reverses the values, and a row with no value has nothing to reverse.

When you want the holes first, say so. missing first goes after the keys, because it applies to the whole sort rather than to one of them:

run('
  sales
    then join products by [product]
    then summarize [total] as total([revenue]) by [maker]
    then sort [maker] missing first
')

maker	total
NA	240
Acme	700
Globex	720
Initech	400

sales |>
  join(products, by = product) |>
  summarize(total = total(revenue), by = maker) |>
  sort(maker, missing = "first")

maker	total
NA	240
Acme	700
Globex	720
Initech	400

(sales
  >> join(products, by = col.product)
  >> summarize(total = total(col.revenue), by = col.maker)
  >> sort(col.maker, missing = "first"))

maker	total
NaN	240
Acme	700
Globex	720
Initech	400

sales then join products by [product] then summarize [total] as total([revenue]) by [maker] then sort [maker] missing first

This is a place where the tools underneath genuinely disagree, and the grammar picks one answer for all of them. By default, DuckDB, dplyr and pandas put the absent rows last; polars puts them first; Spark puts them first going up and last coming down. The same question, five tools, three answers. So every tool that would put them anywhere else is told where they go. The sentence means the same thing wherever it runs, which is the whole of what this grammar is for.

19.4 The same answer, wherever an order is read

A sort settles more than the row order. Anything that reads the rows in that order reads them the way the sort left them, so row_number counts the row with no maker where the sort put it, which is last:

run('
  sales
    then join products by [product]
    then summarize [total] as total([revenue]) by [maker]
    then sort [maker]
    then add [place] as row_number()
')

maker	total	place
Acme	700	1
Globex	720	2
Initech	400	3
NA	240	4

sales |>
  join(products, by = product) |>
  summarize(total = total(revenue), by = maker) |>
  sort(maker) |>
  add(place = row_number())

maker	total	place
Acme	700	1
Globex	720	2
Initech	400	3
NA	240	4

(sales
  >> join(products, by = col.product)
  >> summarize(total = total(col.revenue), by = col.maker)
  >> sort(col.maker)
  >> add(place = row_number()))

maker	total	place
Acme	700	1
Globex	720	2
Initech	400	3
NaN	240	4

sales then join products by [product] then summarize [total] as total([revenue]) by [maker] then sort [maker] then add [place] as row_number()

Say missing first and the counting moves with it, because it is the same order being read:

run('
  sales
    then join products by [product]
    then summarize [total] as total([revenue]) by [maker]
    then sort [maker] missing first
    then add [place] as row_number()
')

maker	total	place
NA	240	1
Acme	700	2
Globex	720	3
Initech	400	4

sales |>
  join(products, by = product) |>
  summarize(total = total(revenue), by = maker) |>
  sort(maker, missing = "first") |>
  add(place = row_number())

maker	total	place
NA	240	1
Acme	700	2
Globex	720	3
Initech	400	4

(sales
  >> join(products, by = col.product)
  >> summarize(total = total(col.revenue), by = col.maker)
  >> sort(col.maker, missing = "first")
  >> add(place = row_number()))

maker	total	place
NaN	240	1
Acme	700	2
Globex	720	3
Initech	400	4

sales then join products by [product] then summarize [total] as total([revenue]) by [maker] then sort [maker] missing first then add [place] as row_number()

previous, running_total, latest and a grouped take all read that same line, and all of them move with it. The rule is worth stating once: a window frames the way the sort above it framed, holes and all.