Why is this row in the table twice? And why is the column called revenue when the report you owe someone says earned? Two small verbs do the housekeeping the core six leave behind: rename changes what a column is called, and drop_duplicates removes exact repeats.
run('
sales
then rename [earned] as [revenue]
then pick [product, earned]
')| product | earned |
|---|---|
| Widget | 100 |
| Gadget | 120 |
| Doohickey | 120 |
| Widget | 150 |
| Widget | 200 |
| Gadget | 300 |
| Doohickey | 80 |
| Doohickey | 200 |
| Widget | 50 |
| Gadget | 240 |
| Sprocket | 150 |
| Sprocket | 90 |
| Widget | 75 |
| Gadget | 60 |
| Widget | 125 |
sales |> rename(earned = revenue) |> pick(product, earned)| product | earned |
|---|---|
| Widget | 100 |
| Gadget | 120 |
| Doohickey | 120 |
| Widget | 150 |
| Widget | 200 |
| Gadget | 300 |
| Doohickey | 80 |
| Doohickey | 200 |
| Widget | 50 |
| Gadget | 240 |
| Sprocket | 150 |
| Sprocket | 90 |
| Widget | 75 |
| Gadget | 60 |
| Widget | 125 |
sales >> rename(earned = col.revenue) >> pick(col.product, col.earned)| product | earned |
|---|---|
| Widget | 100 |
| Gadget | 120 |
| Doohickey | 120 |
| Widget | 150 |
| Widget | 200 |
| Gadget | 300 |
| Doohickey | 80 |
| Doohickey | 200 |
| Widget | 50 |
| Gadget | 240 |
| Sprocket | 150 |
| Sprocket | 90 |
| Widget | 75 |
| Gadget | 60 |
| Widget | 125 |
sales then rename [earned] as [revenue] then pick [product, earned]
rename changes the name and nothing else: not the values, not the order, not the kind. The new name goes first, the way assignment reads and the way every naming verb in this grammar already works. If you are arriving from pandas, read that direction twice. rename(columns={"old": "new"}) is the other way around, and nothing about the shape of the line tells you which convention you are in. Here the rule is single, the new name first, so a pipeline stays readable without a glossary.
drop_duplicates drops the rows that are identical across every column. The answer comes back in a settled order. Dropping repeats says nothing about which order the rest should be in, and an answer that reorders itself between runs is an answer you cannot test.
| a | b |
|---|---|
| 1 | x |
| 1 | x |
| 2 | y |
run('
repeats
then drop_duplicates
')| a | b |
|---|---|
| 1 | x |
| 2 | y |
repeats |> drop_duplicates()| a | b |
|---|---|
| 1 | x |
| 2 | y |
repeats >> drop_duplicates()| a | b |
|---|---|
| 1 | x |
| 2 | y |
repeats then drop_duplicates
Other tools let you write distinct(g) or drop_duplicates(subset="g"), and the argument means two different things depending on the tool and the day. Sometimes it means “the distinct values of g”, sometimes “whole rows, one per value of g”. The second reading has a hole in it that no tool can fill: which whole row, out of the several that share a g? Any answer is an arbitrary choice dressed as a function.
So here the verb takes no columns, and each of the two things you might have meant has its own honest spelling. The distinct values of a column are a pick followed by the drop:
run('
sales
then pick [region, product]
then drop_duplicates
')| region | product |
|---|---|
| East | Doohickey |
| East | Gadget |
| East | Sprocket |
| East | Widget |
| North | Doohickey |
| North | Gadget |
| North | Widget |
| West | Doohickey |
| West | Gadget |
| West | Sprocket |
| West | Widget |
sales |> pick(region, product) |> drop_duplicates()| region | product |
|---|---|
| East | Doohickey |
| East | Gadget |
| East | Sprocket |
| East | Widget |
| North | Doohickey |
| North | Gadget |
| North | Widget |
| West | Doohickey |
| West | Gadget |
| West | Sprocket |
| West | Widget |
sales >> pick(col.region, col.product) >> drop_duplicates()| region | product |
|---|---|
| East | Doohickey |
| East | Gadget |
| East | Sprocket |
| East | Widget |
| North | Doohickey |
| North | Gadget |
| North | Widget |
| West | Doohickey |
| West | Gadget |
| West | Sprocket |
| West | Widget |
sales then pick [region, product] then drop_duplicates
And one whole row per group is sort then take 1 by, which sorting and taking answers under the latest one of each. It forces you to say which row, because “the latest” or “the largest” was always the real question hiding inside “one per group”. Two meanings, two spellings, and neither has an arbitrary choice inside it.
as in the gloss is the same naming word it was in add and summarize, with the same direction: what it becomes, then what it was.pick then drop_duplicates is this grammar’s spelling of every distinct() you have written elsewhere.sort then take 1 by is its spelling of every “first row per group”, and the refusal that guards it was staged two chapters ago.Renaming a column onto a name the table already has would leave two columns with one name, and every later step ambiguous:
sales |> rename(region = revenue) |> collect()Error:
!
illegal: the table already has a column called `region`, so renaming `revenue` to it would leave two of that name. The new name goes first: `rename [new] as [revenue]`
|
2 | then rename [region] as [revenue]
| ^^^^^^try:
collect(sales >> rename(region = col.revenue))
except GodError as refusal:
print(refusal)
illegal: the table already has a column called `region`, so renaming `revenue` to it would leave two of that name. The new name goes first: `rename [new] as [revenue]`
|
2 | then rename [region] as [revenue]
| ^^^^^^The message carries the fix in both directions, because this mistake has two common causes: you meant a different new name, or you had the direction backward and were trying to rename region itself. Naming which side goes first, in the message, at the moment of failure, is how a one-direction rule earns the small strictness it asks of you.