4 Adding a column

add makes a column, or replaces one that is already there, and one add can make several at once.

sales |> add(margin = revenue - cost, paid = revenue * 2) |> pick(margin, paid)

margin	paid
60	200
150	400
200	600
100	300
400	1000

sales >> add(margin = col.revenue - col.cost, paid = col.revenue * 2) >> pick(col.margin, col.paid)

margin	paid
60	200
150	400
200	600
100	300
400	1000

A column made in one 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 the second one cannot see the first.

sales |> add(margin = revenue - cost, doubled = margin * 2) |> collect()

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)
  |                                                             ^^^^^^

try:
    collect(sales >> add(margin = col.revenue - col.cost, doubled = col.margin * 2))
except GodError as refusal:
    print(refusal)


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)
  |                                                             ^^^^^^

Give it a step of its own and it works, and from there on the new column is an ordinary column that any later step can use.

sales |>
  add(margin = revenue - cost) |>
  add(doubled = margin * 2) |>
  keep(margin > 50) |>
  sort(descending(margin))

region	product	revenue	cost	margin	doubled
East	Widget	500	100	400	800
West	Gadget	300	100	200	400
West	Widget	200	50	150	300
West	Gadget	150	50	100	200
West	Widget	100	40	60	120

(sales
  >> add(margin = col.revenue - col.cost)
  >> add(doubled = col.margin * 2)
  >> keep(col.margin > 50)
  >> sort(descending(col.margin)))

region	product	revenue	cost	margin	doubled
East	Widget	500	100	400	800
West	Gadget	300	100	200	400
West	Widget	200	50	150	300
West	Gadget	150	50	100	200
West	Widget	100	40	60	120

This is worth knowing early 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, rather than a sequence hiding inside one. Replacing a column is a different thing and works as you would expect, because the old value is on the table when the step begins.

sales |> add(revenue = revenue * 2) |> pick(product, revenue)

product	revenue
Widget	200
Widget	400
Gadget	600
Gadget	300
Widget	1000

sales >> add(revenue = col.revenue * 2) >> pick(col.product, col.revenue)

product	revenue
Widget	200
Widget	400
Gadget	600
Gadget	300
Widget	1000