Which region sold no Sprockets? Ask the sales table what each region sold of each product, and the answer comes back looking complete.
run('
sales
then summarize [sold] as total([revenue]) by [region, product]
')| region | product | sold |
|---|---|---|
| East | Doohickey | 200 |
| East | Gadget | 180 |
| East | Sprocket | 150 |
| East | Widget | 200 |
| North | Doohickey | 80 |
| North | Gadget | 240 |
| North | Widget | 225 |
| West | Doohickey | 120 |
| West | Gadget | 300 |
| West | Sprocket | 90 |
| West | Widget | 275 |
sales |> summarize(sold = total(revenue), by = c(region, product))| region | product | sold |
|---|---|---|
| East | Doohickey | 200 |
| East | Gadget | 180 |
| East | Sprocket | 150 |
| East | Widget | 200 |
| North | Doohickey | 80 |
| North | Gadget | 240 |
| North | Widget | 225 |
| West | Doohickey | 120 |
| West | Gadget | 300 |
| West | Sprocket | 90 |
| West | Widget | 275 |
sales >> summarize(sold = total(col.revenue), by = [col.region, col.product])| region | product | sold |
|---|---|---|
| East | Doohickey | 200 |
| East | Gadget | 180 |
| East | Sprocket | 150 |
| East | Widget | 200 |
| North | Doohickey | 80 |
| North | Gadget | 240 |
| North | Widget | 225 |
| West | Doohickey | 120 |
| West | Gadget | 300 |
| West | Sprocket | 90 |
| West | Widget | 275 |
sales then summarize [sold] as total([revenue]) by [region, product]
Eleven rows, for three regions and four products. Three fours are twelve. The missing row is North and Sprocket, and the reason it is missing is that the North never sold one. That is the very thing the question was asking, and it is the only answer that did not survive to be read.
This is the quiet failure of grouping. A group is made from the rows that are there, so a group with no rows is not empty: it is absent. A total that should read zero is not shown at all, and nothing tells you that a line is missing. Sort the answer and the gap moves. Chart it and the bar is not short, it is not drawn.
add_combinations takes the values two columns already hold and makes every combination of them a row.
run('
sales
then add_combinations [region, product]
then summarize [sold] as total([revenue]) by [region, product]
')| region | product | sold |
|---|---|---|
| East | Doohickey | 200 |
| East | Gadget | 180 |
| East | Sprocket | 150 |
| East | Widget | 200 |
| North | Doohickey | 80 |
| North | Gadget | 240 |
| North | Sprocket | NA |
| North | Widget | 225 |
| West | Doohickey | 120 |
| West | Gadget | 300 |
| West | Sprocket | 90 |
| West | Widget | 275 |
sales |>
add_combinations(region, product) |>
summarize(sold = total(revenue), by = c(region, product))| region | product | sold |
|---|---|---|
| East | Doohickey | 200 |
| East | Gadget | 180 |
| East | Sprocket | 150 |
| East | Widget | 200 |
| North | Doohickey | 80 |
| North | Gadget | 240 |
| North | Sprocket | NA |
| North | Widget | 225 |
| West | Doohickey | 120 |
| West | Gadget | 300 |
| West | Sprocket | 90 |
| West | Widget | 275 |
sales >> add_combinations(col.region, col.product) \
>> summarize(sold = total(col.revenue), by = [col.region, col.product])| region | product | sold |
|---|---|---|
| East | Doohickey | 200 |
| East | Gadget | 180 |
| East | Sprocket | 150 |
| East | Widget | 200 |
| North | Doohickey | 80 |
| North | Gadget | 240 |
| North | Sprocket | NaN |
| North | Widget | 225 |
| West | Doohickey | 120 |
| West | Gadget | 300 |
| West | Sprocket | 90 |
| West | Widget | 275 |
Twelve rows now, and North/Sprocket is one of them. It reads as missing rather than as zero, and that is right so far: the verb added a row and no value, because nothing in the table says what the North sold. fill_missing is where that gets said.
run('
sales
then add_combinations [region, product]
then fill_missing [revenue] as 0
then summarize [sold] as total([revenue]) by [region, product]
then keep where [sold] < 100
')| region | product | sold |
|---|---|---|
| North | Doohickey | 80 |
| North | Sprocket | 0 |
| West | Sprocket | 90 |
sales |>
add_combinations(region, product) |>
fill_missing(revenue = 0) |>
summarize(sold = total(revenue), by = c(region, product)) |>
keep(sold < 100)| region | product | sold |
|---|---|---|
| North | Doohickey | 80 |
| North | Sprocket | 0 |
| West | Sprocket | 90 |
sales >> add_combinations(col.region, col.product) \
>> fill_missing(revenue = 0) \
>> summarize(sold = total(col.revenue), by = [col.region, col.product]) \
>> keep(col.sold < 100)| region | product | sold |
|---|---|---|
| North | Doohickey | 80 |
| North | Sprocket | 0 |
| West | Sprocket | 90 |
Two steps rather than one argument, and deliberately. Whether an absent combination means zero is a question about the data and not about the verb. A region that sold no Sprockets sold zero of them. A sensor that was switched off did not record a zero. The grammar cannot tell those apart, so it declines to guess, and the second step is where you say which one you have.
Only the values already in a column are crossed. That is the boundary of what this verb can do. A product nobody has ever sold anywhere is not in the product column, so no row is made for it. A month with no orders at all appears nowhere in the table, so no crossing can produce it.
The same rule settles what happens to a hole. A missing value is not a category, so it makes no combinations, and nothing is lost by that: the rows that were already there are handed on untouched. This verb never looks at the holes.
Crossing every value against every other is right for regions and products, and wrong as soon as a third column says which combinations are real. Every student took their own school’s paper:
| school | student | question |
|---|---|---|
| east | ann | q1 |
| east | ann | q2 |
| east | bob | q1 |
| west | cal | q3 |
run('
sittings
then add_combinations [student, question] by [school]
')| school | student | question |
|---|---|---|
| east | ann | q1 |
| east | ann | q2 |
| east | bob | q1 |
| west | cal | q3 |
| east | bob | q2 |
sittings |> add_combinations(student, question, by = school)| school | student | question |
|---|---|---|
| east | ann | q1 |
| east | ann | q2 |
| east | bob | q1 |
| west | cal | q3 |
| east | bob | q2 |
sittings >> add_combinations(col.student, col.question, by = col.school)| school | student | question |
|---|---|---|
| east | ann | q1 |
| east | ann | q2 |
| east | bob | q1 |
| west | cal | q3 |
| east | bob | q2 |
sittings then add_combinations [student, question] by [school]
by names the columns to hold fixed, and the crossing happens inside each group. Bob is given q2, because a student at bob’s school took it; nobody is given q3, because that is the west’s paper. Cal gains nothing at all.
Without by, the same sentence would cross all three students against all three questions and hand back nine rows with the school column empty in every one that was made. That is a table saying something false in a shape that looks tidy.
Two columns or more, always. One column crossed with nothing is the values it already holds, so the sentence would hand the table straight back, and a step that cannot do anything is better refused than obeyed in silence.
collect(sales |> add_combinations(region))Error:
!
illegal: `add_combinations` crosses two columns or more, and this names one. One column on its own has no combinations to make — its distinct values are already the values it holds — so the table would come back unchanged. `add_combinations [region, ...]` needs a second column to cross `region` against
|
2 | then add_combinations [region]
| ^^^^^^^^^^^^^^^^^^^^^^^^try:
collect(sales >> add_combinations(col.region))
except GodError as refusal:
print(refusal)
illegal: `add_combinations` crosses two columns or more, and this names one. One column on its own has no combinations to make — its distinct values are already the values it holds — so the table would come back unchanged. `add_combinations [region, ...]` needs a second column to cross `region` against
|
2 | then add_combinations [region]
| ^^^^^^^^^^^^^^^^^^^^^^^^A column cannot be crossed and held fixed at once either, and the message names both repairs. Crossing a column and holding it fixed mean different tables, so the grammar leaves the choice to you.
collect(sales |> add_combinations(region, product, by = region))Error:
!
illegal: `[region]` is being crossed and held fixed at once, and it can only be one. Take it out of `by` to cross it, or out of the brackets to keep it as the group
|
2 | then add_combinations [region, product] by [region]
| ^^^^^^try:
collect(sales >> add_combinations(col.region, col.product, by = col.region))
except GodError as refusal:
print(refusal)
illegal: `[region]` is being crossed and held fixed at once, and it can only be one. Take it out of `by` to cross it, or out of the brackets to keep it as the group
|
2 | then add_combinations [region, product] by [region]
| ^^^^^^Another verb in the vocabulary takes a table rather than columns, and the two words start alike. Writing a table’s name here is answered by the verb that wanted one:
run('sales then add_combinations more')Error:
!
illegal: `add_combinations` works on this table's own values, so it takes columns rather than a table: `add_combinations [region, product]`. For another table's rows underneath: `add_rows more`
|
1 | sales then add_combinations more
| ^^^^That message belongs to the sentence rather than to either language. Written in R or in Python, a bare name in this position is a column, so the two bindings hand the grammar add_combinations [more]. The reply is the one for a column that is not there. That is also the truth, and it leads to the same repair.
add_rows stacks another table underneath this one; add_combinations works on this table’s own values and uses nothing outside it. Both make the table taller, which is why they share a first word, and only one of them needs a second table to do it.