16  Bringing in another table

join adds another table’s columns to this one, matching rows by the columns that say which rows correspond. products holds a maker for each product.

sales |> join(products, by = product) |> pick(product, maker, revenue)
product maker revenue
Widget Acme 100
Widget Acme 200
Gadget Globex 300
Gadget Globex 150
Widget Acme 500 
sales >> join(products, by = col.product) >> pick(col.product, col.maker, col.revenue)
product maker revenue
Widget Acme 100
Widget Acme 200
Gadget Globex 300
Gadget Globex 150
Widget Acme 500

Leave by out and god matches on the column names both tables share, then tells you which it chose. It is never silent about a choice you did not make.

sales |> join(products) |> take(2)
region product revenue cost maker
West Widget 100 40 Acme
West Widget 200 50 Acme 
sales >> join(products) >> take(2)
region product revenue cost maker
West Widget 100 40 Acme
West Widget 200 50 Acme

What varies between the four joins you may know by name is only this: what happens to a row that found no match. So that is what the argument is called.

unmatched = Which unmatched rows survive Called elsewhere
"this" (the default) this table’s left join
"none" neither table’s inner join
"both" both tables’ full join

There is no "other", and that is not an omission. A right join is this join with the tables the other way round, so it adds no meaning and gets no word.

sales |> join(products, unmatched = "none") |> summarize(revenue = total(revenue), by = maker)
maker revenue
Acme 800
Globex 450 
sales >> join(products, unmatched = "none") >> summarize(revenue = total(col.revenue), by = col.maker)
maker revenue
Acme 800.0
Globex 450.0

A column on both tables that is not being matched on would arrive twice, so it is refused rather than quietly renamed to something you did not choose.

overlap <- data.frame(product = "Widget", revenue = 1, stringsAsFactors = FALSE)
collect(sales |> join(overlap, by = product))
Error:
! 
illegal: both tables have `revenue`, and a join would bring back two columns of that name. Rename one first, or drop it: `then pick all_but [revenue]`
  |
2 |   then join overlap by [product]
  |        ^^^^^^^^^^^^
overlap = pd.DataFrame({"product": ["Widget"], "revenue": [1]})

try:
    collect(sales >> join(overlap, by = col.product))
except GodError as refusal:
    print(refusal)

illegal: both tables have `revenue`, and a join would bring back two columns of that name. Rename one first, or drop it: `then pick all_but [revenue]`
  |
2 |   then join overlap by [product]
  |        ^^^^^^^^^^^^