13  Rows into columns

How do you get the wide table back, one row per student again? widen is the inverse, and it reads the same two words the other way. name points at the column holding column names in both verbs. Here it is being read rather than made, and the verb is what says which.

The table is marks, two students and two questions in the tall shape the last chapter made:

student	question	mark
ann	q1	1
ann	q2	2
bob	q1	4
bob	q2	5

run

run('
  marks
    then widen name [question], value [mark] by [student]
')

student	q1	q2
ann	1	2
bob	4	5

R

marks |> widen(name = question, value = mark, by = student)

student	q1	q2
ann	1	2
bob	4	5

Python

marks >> widen(name = col.question, value = col.mark, by = col.student)

student	q1	q2
ann	1	2
bob	4	5

marks then widen name [question], value [mark] by [student]

by says which columns identify a row. Leaving it out means every column not already named, which is usually right and is occasionally a surprise. One column you had forgotten about makes every row distinct, and the table comes back as tall as it went in. The grammar tells you what it assumed rather than deciding quietly: a note arrives beside the answer, and the laws show one arriving.

Because the two verbs are inverses and share their defaults, widen undoes the lengthen with no arguments at all:

run

run('
  answers
    then lengthen [q1, q2, q3]
    then widen
')

student	q1	q2	q3
ann	1	2	3
bob	4	5	6

R

answers |> lengthen(q1, q2, q3) |> widen()

student	q1	q2	q3
ann	1	2	3
bob	4	5	6

Python

answers >> lengthen(col.q1, col.q2, col.q3) >> widen()

student	q1	q2	q3
ann	1	2	3
bob	4	5	6

13.1 When two rows want the same cell

A cell holds one value. If two rows both claim it, there is no answer, and choosing one of them silently is how a wrong number reaches a report.

This refusal arrives when the query runs rather than when the pipeline is checked. Whether two rows collide is a fact about the data, and the grammar reads column names rather than rows, so it cannot be known any earlier.

R

twice <- data.frame(
  student  = c("ann", "ann"),
  question = c("q1", "q1"),
  mark     = c(1, 9),
  stringsAsFactors = FALSE
)

collect(twice |> widen(name = question, value = mark, by = student))

Error in `duckdb_result()`:
! Invalid Error: Invalid Input Error: two rows want the same cell, and nothing here says which of them wins. Say what to do about that with `value average(...)` or `value first(...)`, or summarize before widening
ℹ Context: rapi_execute
ℹ Error type: INVALID

Python

try:
    twice = pd.DataFrame({
        "student":  ["ann", "ann"],
        "question": ["q1", "q1"],
        "mark":     [1, 9],
    })
    collect(twice
      >> widen(name = col.question, value = col.mark, by = col.student))
except GodError as refusal:
    # This refusal is raised by the engine as the query runs, and the
    # binding hands it over as a `GodError` like every other one. The words
    # are the grammar's, wrapped in the engine's report.
    print(refusal)

Invalid Input Error: two rows want the same cell, and nothing here says which of them wins. Say what to do about that with `value average(...)` or `value first(...)`, or summarize before widening

Saying what to do about that does not need a new word. value takes an expression, so an aggregation there is the answer:

run

run('
  twice
    then widen name [question], value average([mark]) by [student]
')

student	q1
ann	5

R

twice |> widen(name = question, value = average(mark), by = student)

student	q1
ann	5

Python

(twice
  >> widen(name = col.question, value = average(col.mark),
           by = col.student))

student	q1
ann	5

twice then widen name [question], value average([mark]) by [student]

13.2 What the columns became

widen is the one step whose new columns come from the data rather than from the sentence, so what the drawing can say about it has a limit.

R

marks |> widen(name = question, value = mark, by = student) |> show_steps()

Python

show_steps(marks >> widen(name = col.question, value = col.mark, by = col.student))

question and mark leave, student stays, and the drawing says nothing at all about what arrives. That silence is honest rather than a gap: the new column names are values sitting in the question column, and nothing has read a row yet. It is also why this is the last step here, and what the next section is about.

13.3 Saying what it makes

The columns widen produces come from the data, which nothing can know before the query runs. Every other step in the grammar is checked against columns that are known in advance, and this one cannot be.

So a widen that says nothing about what it makes is allowed to be the answer, and is not allowed to be the middle of one:

R

marks |>
  widen(name = question, value = mark, by = student) |>
  take(1) |>
  collect()

Error:
! 
illegal: the columns `widen` makes come from the data, so the grammar cannot know their names until the query runs, and a step after it would be naming columns nothing has checked. Say what it makes: `giving [q1, q2, q3]`, or let the `widen` be the last step
  |
2 |   then widen name [question], value [mark] by [student]
  |        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Python

try:
    collect(marks
      >> widen(name = col.question, value = col.mark, by = col.student)
      >> take(1))
except GodError as refusal:
    print(refusal)

illegal: the columns `widen` makes come from the data, so the grammar cannot know their names until the query runs, and a step after it would be naming columns nothing has checked. Say what it makes: `giving [q1, q2, q3]`, or let the `widen` be the last step
  |
2 |   then widen name [question], value [mark] by [student]
  |        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Write the columns down and everything after the widen is checked as usual:

run

run('
  marks
    then widen name [question], value [mark] by [student] giving [q1, q2]
    then add [gain] as ([q2] - [q1])
')

student	q1	q2	gain
ann	1	2	1
bob	4	5	1

R

marks |>
  widen(name = question, value = mark, by = student, giving = c(q1, q2)) |>
  add(gain = q2 - q1)

student	q1	q2	gain
ann	1	2	1
bob	4	5	1

Python

(marks
 >> widen(name = col.question, value = col.mark, by = col.student,
          giving = [col.q1, col.q2])
 >> add(gain = col.q2 - col.q1))

student	q1	q2	gain
ann	1	2	1
bob	4	5	1

Writing them down buys two more things. A value in the data that you did not list stops the query instead of disappearing from the answer. And an empty cell can be filled, which needs giving for the same reason: saying what an empty cell holds means knowing which cells there are.

sparse is missing a mark: bob answered q1 and not q2.

student	question	mark
ann	q1	1
ann	q2	2
bob	q1	4

run

run('
  sparse
    then widen name [question], value [mark] by [student] missing 0 giving [q1, q2]
')

student	q1	q2
ann	1	2
bob	4	0

R

sparse |> widen(name = question, value = mark, by = student,
                missing = 0, giving = c(q1, q2))

student	q1	q2
ann	1	2
bob	4	0

Python

sparse >> widen(name = col.question, value = col.mark, by = col.student,
                missing = 0, giving = [col.q1, col.q2])

student	q1	q2
ann	1	2
bob	4	0

sparse then widen name [question], value [mark] by [student] missing 0 giving [q1, q2]

Say what it makes with giving, say what fills the holes with missing, and a sparse table widens with no surprise in it.