A survey arrives with a row per person and a column per question, so how do you total by question? Most things you want to ask of a wide table need the opposite shape.
The table is answers, two students and three questions:
The table grew taller, which is what the verb’s name says. Direction is in the name because the alternative is a pair of words you have to look up every time.
Two new columns arrived, called name and value. Those are the grammar’s own words for what a column is called and what it holds, and they are the defaults here, so the shortest sentence is also the one that teaches the vocabulary.
Give them names of your own when the table is about something:
run(' answers then lengthen [q1, q2, q3] as name [question], value [mark]')
student
question
mark
ann
q1
1
ann
q2
2
ann
q3
3
bob
q1
4
bob
q2
5
bob
q3
6
answers |>lengthen(q1, q2, q3, name = question, value = mark)
student
question
mark
ann
q1
1
ann
q2
2
ann
q3
3
bob
q1
4
bob
q2
5
bob
q3
6
(answers>> lengthen(col.q1, col.q2, col.q3, name = col.question, value = col.mark))
student
question
mark
ann
q1
1
ann
q2
2
ann
q3
3
bob
q1
4
bob
q2
5
bob
q3
6
12.1 Choosing the columns
The columns are chosen the same three ways pick chooses them, so there is nothing new to learn. The most common one by far is everything except the column that identifies a row:
run(' answers then lengthen where (name starts "q") as name [question], value [mark] then take 2')
student
question
mark
ann
q1
1
ann
q2
2
answers |>lengthen(where(startsWith(name, "q")), name = question, value = mark) |>take(2)
student
question
mark
ann
q1
1
ann
q2
2
(answers>> lengthen(where(name.starts("q")), name = col.question, value = col.mark)>> take(2))
student
question
mark
ann
q1
1
ann
q2
2
The columns being stacked have to hold the same kind of thing, because the one column they become can only hold one kind. Stacking a text column onto a number column is refused, and the refusal names both:
Error:
!
illegal: `[student]` is text and `[q1]` is a number, so stacking them would put two kinds of thing in one column. Convert one of them first, or lengthen them separately
|
2 | then lengthen [student, q1]
| ^^^^^^^^^^^^^^^^^^^^^
try: collect(answers >> lengthen(col.student, col.q1))except GodError as refusal:print(refusal)
illegal: `[student]` is text and `[q1]` is a number, so stacking them would put two kinds of thing in one column. Convert one of them first, or lengthen them separately
|
2 | then lengthen [student, q1]
| ^^^^^^^^^^^^^^^^^^^^^
12.2 What the columns became
Reshaping changes more of the table at once than any other step, so it is the one to look at before it runs.
answers |>lengthen(all_but(student), name = question, value = mark) |>show_steps()
show_steps(answers>> lengthen(all_but(col.student), name = col.question, value = col.mark))
Three columns leave and two arrive. The note underneath says the rows went the other way, which is the trade this verb makes: a table gets taller by getting narrower.
12.3 When a name holds two things
Column names often encode more than one thing: q1_2020 is a question and a year. Say what the names look like, and name each piece:
run(' terms then lengthen all_but [id] as name "{question}_{year}", value [mark] then take 4')
id
question
year
mark
1
q1
2020
10
1
q1
2021
11
1
q2
2020
12
1
q2
2021
13
terms |>lengthen(all_but(id), name ="{question}_{year}", value = mark) |>take(4)
id
question
year
mark
1
q1
2020
10
1
q1
2021
11
1
q2
2020
12
1
q2
2021
13
(terms>> lengthen(all_but(col.id), name ="{question}_{year}", value = col.mark)>> take(4))
id
question
year
mark
1
q1
2020
10
1
q1
2021
11
1
q2
2020
12
1
q2
2021
13
terms then lengthen all_but [id] as name "{question}_{year}", value [mark]
The braces mark a piece, and everything outside them is the text that separates the pieces. That is the whole rule. It is not a regular expression, and it is not going to become one: a pattern you cannot read two months later is a pattern that costs more than it saves.
A piece written {value} says something different. It says that piece picks which value column the row belongs to, rather than becoming a value itself. That is what to write when the names encode both what was measured and which measure it is: