36 A whole question

The chapters that teach the verbs take one verb at a time, which is the right way to meet them and not how anyone uses them. Here is a question that needs most of the book.

The table is a set of survey returns. Each respondent answered three questions, some answers are missing, a backup file fills one of the gaps, and the regions have long names that a lookup table holds.

returns <- data.frame(
  id     = c(1, 2, 3, 4, 5),
  code   = c("W", "W", "E", "E", "N"),
  q1     = c(5, 3, NA, 5, 2),
  q2     = c(4, NA, 5, 4, 3),
  q3     = c(NA, 2, 5, 5, 1),
  q1_alt = c(NA, NA, 4, NA, NA),
  stringsAsFactors = FALSE
)

places <- data.frame(
  code = c("W", "E"),
  area = c("West", "East"),
  stringsAsFactors = FALSE
)

id	code	q1	q2	q3	q1_alt
1	W	5	4	NA	NA
2	W	3	NA	2	NA
3	E	NA	5	5	4
4	E	5	4	5	NA
5	N	2	3	1	NA

returns = pd.DataFrame({
    "id":     [1, 2, 3, 4, 5],
    "code":   ["W", "W", "E", "E", "N"],
    "q1":     [5.0, 3.0, None, 5.0, 2.0],
    "q2":     [4.0, None, 5.0, 4.0, 3.0],
    "q3":     [None, 2.0, 5.0, 5.0, 1.0],
    "q1_alt": [None, None, 4.0, None, None],
})

places = pd.DataFrame({
    "code": ["W", "E"],
    "area": ["West", "East"],
})

id	code	q1	q2	q3	q1_alt
1	W	5	4	NaN	NaN
2	W	3	NaN	2	NaN
3	E	NaN	5	5	4
4	E	5	4	5	NaN
5	N	2	3	1	NaN

The question is this. For each area the lookup names, what did the average answer to each question look like, and which area scored highest on question one?

Getting there takes seven steps, and each one is a verb from this book.

run('
  returns
    then keep where matching(places, by [code])
    then add [q1] as first_present([q1], [q1_alt])
    then pick all_but [q1_alt]
    then join places by [code]
    then summarize where (name starts "q") as average(value) by [area]
    then add [place] as rank([q1] descending)
    then sort [place]
')

area	q1	q2	q3	place
East	4.5	4.5	5	1
West	4.0	4.0	2	2

answers <- returns |>
  keep(matching(places, by = code)) |>
  add(q1 = first_present(q1, q1_alt)) |>
  pick(all_but(q1_alt)) |>
  join(places, by = code) |>
  summarize(where(startsWith(name, "q"), average(value)), by = area) |>
  add(place = rank(descending(q1))) |>
  sort(place)

answers

area	q1	q2	q3	place
East	4.5	4.5	5	1
West	4.0	4.0	2	2

answers = (returns
  >> keep(matching(places, by = col.code))
  >> add(q1 = first_present(col.q1, col.q1_alt))
  >> pick(all_but(col.q1_alt))
  >> join(places, by = col.code)
  >> summarize(where(name.starts("q"), average(value)), by = col.area)
  >> add(place = rank(descending(col.q1)))
  >> sort(col.place))

answers

area	q1	q2	q3	place
East	4.5	4.5	5	1
West	4	4	2	2

Read it aloud and it is the question. Keep the rows matching a place the lookup knows. Take the backup answer for question one where the first is missing. Drop the backup column. Bring in the area names. For each area, average every column whose name starts with q. Rank the areas by question one, highest first. Sort by that rank.

Two things that would have been easy to get wrong are done by single clauses. matching drops respondent 5, whose code N is in no lookup row, and it cannot multiply the rows the way a join could. And the summarize names no question column at all, so a fourth question arriving next month is included without this line changing.

Here is the whole thing as SQL, which is what actually ran.

show_as(returns |>
  keep(matching(places, by = code)) |>
  add(q1 = first_present(q1, q1_alt)) |>
  pick(all_but(q1_alt)) |>
  join(places, by = code) |>
  summarize(where(startsWith(name, "q"), average(value)), by = area) |>
  add(place = rank(descending(q1))) |>
  sort(place), "sql")

WITH step0 AS (SELECT * FROM "returns"),
     step1 AS (SELECT * FROM step0 WHERE EXISTS (SELECT 1 FROM "places" WHERE "places"."code" = step0."code")),
     step2 AS (SELECT * EXCLUDE ("q1"), coalesce("q1", "q1_alt") AS "q1" FROM step1),
     step3 AS (SELECT * EXCLUDE ("q1_alt") FROM step2),
     step4 AS (SELECT step3.*, "places".* EXCLUDE ("code") FROM step3 LEFT JOIN "places" ON step3."code" = "places"."code"),
     step5 AS (SELECT "area", avg("q1") AS "q1", avg("q2") AS "q2", avg("q3") AS "q3" FROM step4 GROUP BY "area" ORDER BY "area" NULLS LAST),
     step6 AS (SELECT *, RANK() OVER (ORDER BY "q1" DESC NULLS LAST) AS "place" FROM step5),
     step7 AS (SELECT * FROM step6 ORDER BY "place" NULLS LAST)
SELECT * FROM step7

A misspelling anywhere in those seven steps would have stopped the whole sentence before anything ran, with the caret on the step that carries it. Here is the grouping mistyped:

returns |>
  keep(matching(places, by = code)) |>
  add(q1 = first_present(q1, q1_alt)) |>
  pick(all_but(q1_alt)) |>
  join(places, by = code) |>
  summarize(where(startsWith(name, "q"), average(value)), by = aera) |>
  add(place = rank(descending(q1))) |>
  sort(place) |>
  collect()

Error:
! 
illegal: there is no column called `aera`. The table has: id, code, q1, q2, q3, area
  |
6 |   then summarize where (name starts "q") as average(value) by [aera]
  |                                                                ^^^^

try:
    collect(returns
      >> keep(matching(places, by = col.code))
      >> add(q1 = first_present(col.q1, col.q1_alt))
      >> pick(all_but(col.q1_alt))
      >> join(places, by = col.code)
      >> summarize(where(name.starts("q"), average(value)), by = col.aera)
      >> add(place = rank(descending(col.q1)))
      >> sort(col.place))
except GodError as refusal:
    print(refusal)


illegal: there is no column called `aera`. The table has: id, code, q1, q2, q3, area
  |
6 |   then summarize where (name starts "q") as average(value) by [aera]
  |                                                                ^^^^

The columns it lists are the table as it stands at that step, which is why q1_alt is not among them. The third step dropped it.

Seven steps became one query. The query is the proof rather than the point: the sentence above it is the version a person can read aloud, and still write, a year from now.