15 Missing values

drop_missing drops rows where a named column is missing. With no column named, it looks at every column.

patchy <- data.frame(
  product = c("Widget", "Gadget", "Doohickey"),
  revenue = c(100, NA, 300)
)

patchy |> drop_missing(revenue)

product	revenue
Widget	100
Doohickey	300

patchy = pd.DataFrame({
    "product": ["Widget", "Gadget", "Doohickey"],
    "revenue": [100.0, None, 300.0],
})

patchy >> drop_missing(col.revenue)

product	revenue
Widget	100.0
Doohickey	300.0

fill_missing replaces missing values with something.

patchy |> fill_missing(revenue = 0)

product	revenue
Widget	100
Gadget	0
Doohickey	300

patchy >> fill_missing(revenue = 0)

product	revenue
Widget	100.0
Gadget	0.0
Doohickey	300.0

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"
  |                                  ^^^^^^

15.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 <- data.frame(
  name     = c("ana", "bo", "cy"),
  mobile   = c("555-01", NA, NA),
  landline = c("555-99", "555-02", NA),
  email    = c("a@x", "b@x", "c@x"),
  stringsAsFactors = FALSE
)

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 = pd.DataFrame({
    "name":     ["ana", "bo", "cy"],
    "mobile":   ["555-01", None, None],
    "landline": ["555-99", "555-02", None],
    "email":    ["a@x", "b@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

Two things about it are worth knowing before you rely on it, and both are places people are usually surprised.

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.

readings <- data.frame(sensor = c(0, NA), backup = c(99, 99))
readings |> add(used = first_present(sensor, backup))

sensor	backup	used
0	99	0
NA	99	99

readings = pd.DataFrame({"sensor": [0.0, None], "backup": [99.0, 99.0]})
readings >> add(used = first_present(col.sensor, col.backup))

sensor	backup	used
0.0	99.0	0.0
NaN	99.0	99.0

The first sensor read zero, which is a reading, so it is kept. Only the row where the sensor recorded nothing at all falls through to 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 says the part that trips people up.