Why write the same calculation eight times? Repeating a thing for eight columns is eight chances to write it slightly differently. add where takes the same pattern the last two chapters used and one value to apply to each column it matches. Inside that value, value stands for the column being worked on.
run('
survey
then add where (name starts "q") as (value * 10)
')| respondent | name | region | joined | q1_score | q2_score | q3_score | q4_score | q5_score | q6_score | q7_score | q8_score |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ana | West | 2026-01-12 | 40 | 20 | 50 | 30 | 40 | 50 | 20 | 30 |
| 2 | ben | East | 2026-02-03 | 50 | 50 | 40 | 30 | 20 | 50 | 40 | 50 |
| 3 | cal | North | 2026-02-27 | 30 | 50 | 20 | 40 | 50 | 30 | 40 | 20 |
| 4 | dee | West | 2026-03-15 | 40 | 30 | 50 | 20 | 40 | 30 | 50 | 40 |
| 5 | eli | East | 2026-04-01 | 20 | 40 | 30 | 50 | 40 | 20 | 30 | 50 |
| 6 | fay | North | 2026-04-22 | 50 | 40 | 40 | 50 | 30 | 40 | 50 | 40 |
survey |> add(where(startsWith(name, "q"), value * 10))| respondent | name | region | joined | q1_score | q2_score | q3_score | q4_score | q5_score | q6_score | q7_score | q8_score |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ana | West | 2026-01-12 | 40 | 20 | 50 | 30 | 40 | 50 | 20 | 30 |
| 2 | ben | East | 2026-02-03 | 50 | 50 | 40 | 30 | 20 | 50 | 40 | 50 |
| 3 | cal | North | 2026-02-27 | 30 | 50 | 20 | 40 | 50 | 30 | 40 | 20 |
| 4 | dee | West | 2026-03-15 | 40 | 30 | 50 | 20 | 40 | 30 | 50 | 40 |
| 5 | eli | East | 2026-04-01 | 20 | 40 | 30 | 50 | 40 | 20 | 30 | 50 |
| 6 | fay | North | 2026-04-22 | 50 | 40 | 40 | 50 | 30 | 40 | 50 | 40 |
survey >> add(where(name.starts("q"), value * 10))| respondent | name | region | joined | q1_score | q2_score | q3_score | q4_score | q5_score | q6_score | q7_score | q8_score |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ana | West | 2026-01-12 | 40 | 20 | 50 | 30 | 40 | 50 | 20 | 30 |
| 2 | ben | East | 2026-02-03 | 50 | 50 | 40 | 30 | 20 | 50 | 40 | 50 |
| 3 | cal | North | 2026-02-27 | 30 | 50 | 20 | 40 | 50 | 30 | 40 | 20 |
| 4 | dee | West | 2026-03-15 | 40 | 30 | 50 | 20 | 40 | 30 | 50 | 40 |
| 5 | eli | East | 2026-04-01 | 20 | 40 | 30 | 50 | 40 | 20 | 30 | 50 |
| 6 | fay | North | 2026-04-22 | 50 | 40 | 40 | 50 | 30 | 40 | 50 | 40 |
survey then add where (name starts "q") as (value * 10)
The matched columns keep their names. That is not a rule add where had to invent: add already covers making a column and replacing one, so replacing q1_score with a new q1_score is the ordinary case.
name and value are a pair. One is what the column is called, the other is what it holds, and they are the same two words the reshaping verbs use.
summarize where works the same way and collapses instead.
run('
survey
then summarize where (name ends "_score") as average(value) by [region]
')| region | q1_score | q2_score | q3_score | q4_score | q5_score | q6_score | q7_score | q8_score |
|---|---|---|---|---|---|---|---|---|
| East | 3.5 | 4.5 | 3.5 | 4.0 | 3 | 3.5 | 3.5 | 5.0 |
| North | 4.0 | 4.5 | 3.0 | 4.5 | 4 | 3.5 | 4.5 | 3.0 |
| West | 4.0 | 2.5 | 5.0 | 2.5 | 4 | 4.0 | 3.5 | 3.5 |
survey |>
summarize(where(endsWith(name, "_score"), average(value)), by = region)| region | q1_score | q2_score | q3_score | q4_score | q5_score | q6_score | q7_score | q8_score |
|---|---|---|---|---|---|---|---|---|
| East | 3.5 | 4.5 | 3.5 | 4.0 | 3 | 3.5 | 3.5 | 5.0 |
| North | 4.0 | 4.5 | 3.0 | 4.5 | 4 | 3.5 | 4.5 | 3.0 |
| West | 4.0 | 2.5 | 5.0 | 2.5 | 4 | 4.0 | 3.5 | 3.5 |
(survey
>> summarize(where(name.ends("_score"), average(value)), by = col.region))| region | q1_score | q2_score | q3_score | q4_score | q5_score | q6_score | q7_score | q8_score |
|---|---|---|---|---|---|---|---|---|
| East | 3.5 | 4.5 | 3.5 | 4 | 3 | 3.5 | 3.5 | 5 |
| North | 4 | 4.5 | 3 | 4.5 | 4 | 3.5 | 4.5 | 3 |
| West | 4 | 2.5 | 5 | 2.5 | 4 | 4 | 3.5 | 3.5 |
dplyr calls this across. Here it needed no verb and only one word, where, because the selector was already there for pick and where already introduces a condition.
Every rule that applies to a value written out by hand applies to this one, because the grammar expands it before checking anything. Asking summarize for something that does not collapse a group is refused, and the message names the column it refused rather than the pattern.
collect(survey |> summarize(where(startsWith(name, "q"), value * 2)))Error:
!
illegal: `summarize` returns one row for each group, so `[q1_score]` has to be a value that spans the group. Wrap it: `total(...)`, `average(...)`, `first(...)`, or count the rows with `row_count()`
|
2 | then summarize where (name starts "q") as (value * 2)
| ^^^^^^^^^try:
collect(survey >> summarize(where(name.starts("q"), value * 2)))
except GodError as refusal:
print(refusal)
illegal: `summarize` returns one row for each group, so `[q1_score]` has to be a value that spans the group. Wrap it: `total(...)`, `average(...)`, `first(...)`, or count the rows with `row_count()`
|
2 | then summarize where (name starts "q") as (value * 2)
| ^^^^^^^^^The first few times, look at what the pattern expanded to. format in R and written() in Python show the sentence you wrote; show_as shows the one the grammar settled on.
show_as(survey |> add(where(startsWith(name, "q"), value * 10)), "god")survey
then add [q1_score] as ([q1_score] * 10), [q2_score] as ([q2_score] * 10), [q3_score] as ([q3_score] * 10), [q4_score] as ([q4_score] * 10), [q5_score] as ([q5_score] * 10), [q6_score] as ([q6_score] * 10), [q7_score] as ([q7_score] * 10), [q8_score] as ([q8_score] * 10)show_as(survey >> add(where(name.starts("q"), value * 10)), "god")survey
then add [q1_score] as ([q1_score] * 10), [q2_score] as ([q2_score] * 10), [q3_score] as ([q3_score] * 10), [q4_score] as ([q4_score] * 10), [q5_score] as ([q5_score] * 10), [q6_score] as ([q6_score] * 10), [q7_score] as ([q7_score] * 10), [q8_score] as ([q8_score] * 10)The grammar’s own printing expands the rule into the columns it matched, so what actually ran is on the page rather than implied, which is honest to whoever reads the pipeline after you.
The same argument works for a question. If eight columns hold scores, “who answered above 4 anywhere?” is one question about eight columns, and writing it eight times joined by or is eight chances to miss one. keep takes the same selector, with any in front of it.
run('
survey
then keep where any name starts "q" as value > 4
then pick [name, q1_score, q2_score]
')| name | q1_score | q2_score |
|---|---|---|
| ana | 4 | 2 |
| ben | 5 | 5 |
| cal | 3 | 5 |
| dee | 4 | 3 |
| eli | 2 | 4 |
| fay | 5 | 4 |
survey |>
keep(where_any(startsWith(name, "q"), value > 4)) |>
pick(name, q1_score, q2_score)| name | q1_score | q2_score |
|---|---|---|
| ana | 4 | 2 |
| ben | 5 | 5 |
| cal | 3 | 5 |
| dee | 4 | 3 |
| eli | 2 | 4 |
| fay | 5 | 4 |
(survey
>> keep(where_any(name.starts("q"), value > 4))
>> pick(col.name, col.q1_score, col.q2_score))| name | q1_score | q2_score |
|---|---|---|
| ana | 4 | 2 |
| ben | 5 | 5 |
| cal | 3 | 5 |
| dee | 4 | 3 |
| eli | 2 | 4 |
| fay | 5 | 4 |
survey then keep where any name starts "q" as value > 4
every is the other half, and it asks the harder question: which respondents scored above 2 on all eight?
run('
survey
then keep where every name starts "q" as value > 2
then pick [name]
')| name |
|---|
| fay |
survey |> keep(where_every(startsWith(name, "q"), value > 2)) |> pick(name)| name |
|---|
| fay |
survey >> keep(where_every(name.starts("q"), value > 2)) >> pick(col.name)| name |
|---|
| fay |
The two are or and and written once instead of seven times, and the expansion is on the page if you ask for it, exactly as it is for add.
A selector that matches no column is refused rather than answered. Both answers would be defensible: an any over no columns is false, and an every over no columns is true. Neither is guessable, so a mistyped selector would otherwise empty your table or leave it whole without a word.
collect(survey |> keep(where_any(startsWith(name, "z"), value > 2)))Error:
!
illegal: no column's name matches that, so there is nothing to ask. The table has: respondent, name, region, joined, q1_score, q2_score, q3_score, q4_score, q5_score, q6_score, q7_score, q8_score
|
2 | then keep where any (name starts "z") as (value > 2)
| ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^try:
collect(survey >> keep(where_any(name.starts("z"), value > 2)))
except GodError as refusal:
print(refusal)
illegal: no column's name matches that, so there is nothing to ask. The table has: respondent, name, region, joined, q1_score, q2_score, q3_score, q4_score, q5_score, q6_score, q7_score, q8_score
|
2 | then keep where any (name starts "z") as (value > 2)
| ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^The words are any and every in the sentence, and where_any and where_every in both languages. any is a function R and Python both already have, and god leaves the name to them.
This chapter closes the part on choosing columns. The cookbook comes next, and it is organized by the question rather than by the verb.