8  The words that repeat

You have met six verbs and a handful of small words that go with them: by, where, as, all_but, descending. This chapter is about those small words, and it is the shortest useful thing in the book.

Each of them means one thing. Not one thing per verb, one thing. When you meet a verb you have never seen, the small words in it already make sense, and that is most of what makes this grammar quick to get back after a year away.

8.1 by says which rows go together

You saw by on summarize, where it made the groups. It is the same word on add, where it says which rows a value is worked out within, and on take, where it says which rows the first few are taken from.

sales |> summarize(total = total(revenue), by = product)
product total
Gadget 450
Widget 800 
sales >> summarize(total = total(col.revenue), by = col.product)
product total
Gadget 450.0
Widget 800.0 
sales |> add(share = revenue / total(revenue), by = product) |> pick(product, revenue, share)
product revenue share
Widget 100 0.1250000
Widget 200 0.2500000
Gadget 300 0.6666667
Gadget 150 0.3333333
Widget 500 0.6250000 
sales >> add(share = col.revenue / total(col.revenue), by = col.product) >> pick(col.product, col.revenue, col.share)
product revenue share
Widget 100 0.125000
Widget 200 0.250000
Widget 500 0.625000
Gadget 300 0.666667
Gadget 150 0.333333 
sales |> sort(descending(revenue)) |> take(1, by = product)
region product revenue cost
East Widget 500 100
West Gadget 300 100 
sales >> sort(descending(col.revenue)) >> take(1, by = col.product)
region product revenue cost
East Widget 500 100
West Gadget 300 100

Three verbs, one word, one meaning. Later you will meet by on join and on widen, and it will mean the same thing there: the columns that say which rows correspond.

8.2 as gives something a name

add [name] as [value]. The name goes first, the way assignment reads, and the same shape names the result of rename and the filler in fill_missing.

sales |> add(margin = revenue - cost) |> rename(earned = revenue) |> pick(product, earned, margin)
product earned margin
Widget 100 60
Widget 200 150
Gadget 300 200
Gadget 150 100
Widget 500 400 
sales >> add(margin = col.revenue - col.cost) >> rename(earned = col.revenue) >> pick(col.product, col.earned, col.margin)
product earned margin
Widget 100 60
Widget 200 150
Gadget 300 200
Gadget 150 100
Widget 500 400

8.3 all_but turns a list inside out

Wrap the names you do not want. It is the same word wherever a set of columns is chosen, which so far is pick and, in the next part, lengthen.

sales |> pick(all_but(cost, region))
product revenue
Widget 100
Widget 200
Gadget 300
Gadget 150
Widget 500 
sales >> pick(all_but(col.cost, col.region))
product revenue
Widget 100
Widget 200
Gadget 300
Gadget 150
Widget 500

8.4 descending reverses an ordering

It is a modifier on a column, in a position where an ordering is being given. So it reads the same on sort and inside rank, and it needs no argument of its own on either.

sales |> add(place = rank(descending(revenue))) |> sort(place) |> pick(product, revenue, place)
product revenue place
Widget 500 1
Gadget 300 2
Widget 200 3
Gadget 150 4
Widget 100 5 
sales >> add(place = rank(descending(col.revenue))) >> sort(col.place) >> pick(col.product, col.revenue, col.place)
product revenue place
Widget 500 1
Gadget 300 2
Widget 200 3
Gadget 150 4
Widget 100 5

8.5 where asks a question

On keep the question is about a row. Later you will see it on pick, where the question is about a column, and the difference is written down rather than guessed: inside pick you say name or kind to name what you are asking about.

sales |> keep(revenue > 150)
region product revenue cost
West Widget 200 50
West Gadget 300 100
East Widget 500 100 
sales >> keep(col.revenue > 150)
region product revenue cost
West Widget 200 50
West Gadget 300 100
East Widget 500 100

8.6 Why this matters more than it looks

A vocabulary is easy to learn and easy to lose. What survives a year away is not the word list, it is the rules for putting words together, and those only survive if there are few of them and they have no exceptions.

So the test this grammar sets itself is not whether it has a word for everything. It is whether a word you learned in one place still means the same thing in the next place you meet it. Every part after this one is that test being run.