18  One way or another

Is 95 an A or a B, and who decides in which order the rules apply? when asks a question and gives the answer beside it, then asks the next one.

The table is pupils, three scores that fall in three different bands:

name	score
ann	95
bob	75
cat	50

run

run('
  pupils
    then add [band] as when(([score] >= 90), "A", ([score] >= 70), "B", otherwise "C")
')

name	score	band
ann	95	A
bob	75	B
cat	50	C

R

pupils |>
  add(band = when(score >= 90, "A", score >= 70, "B", otherwise = "C"))

name	score	band
ann	95	A
bob	75	B
cat	50	C

Python

pupils >> add(band = when(col.score >= 90, "A", col.score >= 70, "B",
                          otherwise = "C"))

name	score	band
ann	95	A
bob	75	B
cat	50	C

pupils then add [band] as when(([score] >= 90), "A", ([score] >= 70), "B", otherwise "C")

The arguments come in pairs: a question, then what it gives. The first question that is true wins, so the order is part of what the sentence means. Ask the same two the other way around and the answers change, which is not a danger but the thing you are choosing when you write them in an order:

run

run('
  pupils
    then add [band] as when(([score] >= 70), "B", ([score] >= 90), "A",
         otherwise "C")
')

name	score	band
ann	95	B
bob	75	B
cat	50	C

R

pupils |>
  add(band = when(score >= 70, "B", score >= 90, "A", otherwise = "C"))

name	score	band
ann	95	B
bob	75	B
cat	50	C

Python

pupils >> add(band = when(col.score >= 70, "B", col.score >= 90, "A",
                          otherwise = "C"))

name	score	band
ann	95	B
bob	75	B
cat	50	C

ann scored 95, met the first question that was asked, and got a B.

Leave out otherwise and a row that matched nothing is missing:

run

run('
  pupils
    then add [top] as when(([score] >= 90), "yes")
')

name	score	top
ann	95	yes
bob	75	NA
cat	50	NA

R

pupils |> add(top = when(score >= 90, "yes"))

name	score	top
ann	95	yes
bob	75	NA
cat	50	NA

Python

pupils >> add(top = when(col.score >= 90, "yes"))

name	score	top
ann	95	yes
bob	75	NaN
cat	50	NaN

Every answer has to be the same kind of thing, because they all go into one column:

R

collect(pupils |> add(band = when(score >= 90, "A", otherwise = 0)))

Error:
! 
illegal: `when` gives one column, so all of its answers have to be the same kind of thing. One of them is text and this is a number
  |
2 |   then add [band] as when(([score] >= 90), "A", otherwise 0)
  |                                                           ^

Python

try:
    collect(pupils >> add(band = when(col.score >= 90, "A", otherwise = 0)))
except GodError as refusal:
    print(refusal)

illegal: `when` gives one column, so all of its answers have to be the same kind of thing. One of them is text and this is a number
  |
2 |   then add [band] as when(([score] >= 90), "A", otherwise 0)
  |                                                           ^

Python has a conditional of its own. Here is why god does not use it. Writing "A" if col.score >= 90 else "B" would decide the answer once, while the pipeline was being built, and throw the question away. A column expression therefore refuses to become a plain yes or no: that line is an error before any row is read, and Appendix A shows the message. R’s if wants a single yes or no, so a column of answers is an error there. So the word is when in both, and it is the same word in the text form.

18.1 The lookup table

Often the question is the same question repeated: is the value this, is it that. Recoding survey answers, expanding country codes, and tidying a categorical all have that shape. look_up is that special case with the pairs side by side, each written value next to what it becomes:

run

run('
  sales
    then add [short] as look_up([region], "West", "W", "East", "E", otherwise [region])
    then pick [region, short]
    then drop_duplicates
')

region	short
East	E
North	North
West	W

R

sales |>
  add(short = look_up(region, "West", "W", "East", "E", otherwise = region)) |>
  pick(region, short) |>
  drop_duplicates()

region	short
East	E
North	North
West	W

Python

(sales
 >> add(short = look_up(col.region, "West", "W", "East", "E",
                        otherwise = col.region))
 >> pick(col.region, col.short)
 >> drop_duplicates())

region	short
East	E
North	North
West	W

North has no pair, and the otherwise said what happens to it: naming the column keeps it as it was. Naming missing drops it instead, and a written value is a default. Those three endings are the whole difference between the two functions dplyr gives this idea and the two polars gives it, so god writes the ending rather than naming the word twice.

The ending is required, and leaving it off is refused rather than guessed:

R

collect(sales |> add(short = look_up(region, "West", "W")))

Error:
! 
illegal: `look_up` says where a value with no pair goes, so it ends with `otherwise`: keep those values with `otherwise [code]`, drop them with `otherwise missing`, or write a default
  |
2 |   then add [short] as look_up([region], "West", "W")
  |                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Python

try:
    collect(sales >> add(short = look_up(col.region, "West", "W")))
except GodError as refusal:
    print(refusal)

illegal: `look_up` says where a value with no pair goes, so it ends with `otherwise`: keep those values with `otherwise [code]`, drop them with `otherwise missing`, or write a default
  |
2 |   then add [short] as look_up([region], "West", "W")
  |                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Half of the neighboring tools keep an unpaired value and half send it missing, so whichever way god chose, the sentence would surprise someone arriving from the other half. Writing the ending is how you say you thought about it, exactly as unmatched says it for a join’s rows.

A pair may send a value missing, which is how “this value means absent” is written: look_up([code], "", missing, otherwise [code]) turns empty text into a proper hole and leaves everything else alone. The pairs are written values on both sides, and that is the boundary between the two words: the moment a question is a comparison, or an answer is computed, the sentence belongs to when.

Either word can leave a hole in the column on purpose. What a hole means for your answer is the next chapter.