What did each order actually earn, and what was the price per unit? Neither number is in the table, but everything needed to work them out is. add makes a column from the ones already there, and one add can make several at once.
sales then add [margin] as [revenue] - [cost], [price] as [revenue] / [quantity] then pick [product, quantity, margin, price]
Read the price column down and it repeats by product. Each product sells at one price all year in this table, which makes its totals easy to check by eye. The new columns are ordinary columns from here on: pickable, keepable, sortable, and summable like anything the table arrived with.
5.1 The name goes first
as is the word that names what was made, and the name comes first: new name, then the value. In R and Python you type =, and it is playing as’s part; the text form writes the word out. That is the order assignment reads in almost every language, and rename and summarize will use it too. There is one direction to remember across the whole grammar rather than one per verb.
5.2 A step is one step
A column made in a step is not there yet for the rest of that same step. Every value in a step is worked out from the table as it arrives, so a second value cannot read the first. When one column needs another, give it a step of its own, and the pipeline says what it does in the order it does it:
run(' sales then add [margin] as ([revenue] - [cost]) then add [doubled] as ([margin] * 2) then keep where ([margin] > 50) then sort [margin] descending')
This matters most if you are arriving from dplyr or pandas, because mutate and assign both let the second column read the first. This grammar does not, for the same reason SQL does not: a step is one step, not a sequence hiding inside one. The moment two values in a step can depend on each other, the order they are written in starts to matter, and a reader has to trace a hidden pipeline inside every line. Two short steps cost you nothing now: no step runs until the whole pipeline is asked for, which is the chapter on laziness, so the two become one query. And they read as what they are.
5.3 Replacing a column
Replacing is a different thing from self-reference, and it is allowed, because the old value is on the table when the step begins:
The doubled revenue exists only in the pipeline’s answer. The sales your session holds is unchanged, the way every verb leaves its table.
5.4 What is left over
Everything so far was written with the four arithmetic signs. One operation needs a word of its own. remainder divides and hands back what did not divide evenly. It is how you say “every third row”, or sort rows into a fixed number of buckets, or ask whether a number is even.
sales then sort [date] then add [n] as row_number() then keep where remainder([n], 3) is 0
The answer takes the sign of what you divided by, so a negative number still gives a remainder between 0 and one less than the divisor. The table below shows it, because this is the half of remainder people get wrong.
That is R’s answer and Python’s. SQL engines give -1 where these give 2, and the grammar names which one it means, so the same sentence cannot come back two ways depending on where it ran. It is the same decision weekday needed, for the same reason: a difference that never announces itself is worse than one that stops you.
Of the arithmetic words the grammar has beyond +, -, * and /, this is the one you cannot write any other way. Whole division is round_below([n] / 3), using a word the next section introduces, and a square is [x] * [x], so neither gets a word of its own.
5.5round_below and round_above
Dividing rarely comes out even. round_below gives the whole number below the answer and round_above gives the one above, and a value that is already whole does not move under either.
sales then add [each] as round_below([revenue] / 7)
The words say below and above rather than down and up, and the reason is negative numbers. Below always means the smaller number, so round_below(-5.5) is -6, not -5. Spreadsheets round “down” the other way, toward zero. That gives one word two opposite meanings on two screens, and nobody notices a difference like that until an invoice is wrong.
There is no word for the nearest whole number, because you can already say it: add a half and round below. A word whose whole job is something the grammar can already write would be a second way to say one thing.
run('edges then add [nearest] as round_below([n] + 0.5)', edges = edges)
n
nearest
5.5
6
5.1
5
-5.5
-5
-5.7
-6
5.0
5
edges |>add(nearest =round_below(n +0.5))
n
nearest
5.5
6
5.1
5
-5.5
-5
-5.7
-6
5.0
5
edges >> add(nearest = round_below(col.n +0.5))
n
nearest
5.5
6
5.1
5
-5.5
-5
-5.7
-6
5
5
5.6 What travels with it
as names what a step makes, name first, and it means exactly this again in summarize and in rename.
The whole expression language rides inside add: arithmetic here, and in the part on values the text repairs, conversions, dates, and conditionals, all written in this same seat.
by can follow an add whose value summarizes a group, which turns a collapse into a broadcast: the group’s one answer written onto every row of the group. The chapter on the small words shows it, and share-of-group is the idiom it exists for.
5.7 What it refuses
That a step is one step is enforced, not advised. A value that reads a column made in its own step is refused whole, before anything runs:
Error:
!
illegal: `[margin]` is made by this same `add`, so it is not on the table yet. Every value in one step is worked out from the table as it arrives. Make it in a step of its own: `then add [margin] as ... then add ...`
|
2 | then add [margin] as ([revenue] - [cost]), [doubled] as ([margin] * 2)
| ^^^^^^
illegal: `[margin]` is made by this same `add`, so it is not on the table yet. Every value in one step is worked out from the table as it arrives. Make it in a step of its own: `then add [margin] as ... then add ...`
|
2 | then add [margin] as ([revenue] - [cost]), [doubled] as ([margin] * 2)
| ^^^^^^
The message does more than refuse: it names the two-step spelling that works. The same law refuses a second mistake, one only the text form can spell: making one name twice in a single step. It is refused just as directly, because a column silently thrown away is work silently thrown away. If you remember that a step is one step, you will never meet either message again.