17 Asking whether a row has a partner

Sometimes you do not want the other table’s columns. You want to know which of your rows appear in it, and keep those. That is a question about a row, so it is written as one, and the verb that takes a question is keep.

stocked <- data.frame(product = "Widget", stringsAsFactors = FALSE)

sales |> keep(matching(stocked, by = product))

region	product	revenue	cost
West	Widget	100	40
West	Widget	200	50
East	Widget	500	100

stocked = pd.DataFrame({"product": ["Widget"]})

sales >> keep(matching(stocked, by = col.product))

region	product	revenue	cost
West	Widget	100	40
West	Widget	200	50
East	Widget	500	100

Nothing was added. The columns are the ones sales started with, and only the rows changed, which is why this is not spelled join. Negate it and you get exactly the rows the first one dropped.

sales |> keep(!matching(stocked, by = product))

region	product	revenue	cost
West	Gadget	300	100
West	Gadget	150	50

sales >> keep(~matching(stocked, by = col.product))

region	product	revenue	cost
West	Gadget	300	100
West	Gadget	150	50

by works here exactly as it does on join, and leaving it out means the same thing: god matches on the column names both tables share and says which it chose.

sales |> keep(matching(stocked))

region	product	revenue	cost
West	Widget	100	40
West	Widget	200	50
East	Widget	500	100

sales >> keep(matching(stocked))

region	product	revenue	cost
West	Widget	100	40
West	Widget	200	50
East	Widget	500	100

There is one thing matching guarantees that join cannot. If the other table has three rows for a product, a join gives you three rows back for each of yours. A question does not: a row either has a partner or it does not, and how many it has never reaches the answer.

restocks <- data.frame(
  product  = c("Widget", "Widget", "Widget"),
  quantity = c(10, 20, 30),
  stringsAsFactors = FALSE
)

nrow(collect(sales |> keep(matching(restocks, by = product))))

[1] 3

nrow(collect(sales |> join(restocks, by = product, unmatched = "none")))

[1] 9

restocks = pd.DataFrame({
    "product":  ["Widget", "Widget", "Widget"],
    "quantity": [10, 20, 30],
})

print(len(collect(sales >> keep(matching(restocks, by = col.product)))))

print(len(collect(sales >> join(restocks, by = col.product, unmatched = "none"))))

matching is the whole question keep asks rather than one part of one, so it does not combine with and. Ask it in its own step.

collect(sales |> keep(matching(products, by = product) & revenue > 100))

Error:
! 
illegal: `matching` chooses whole rows, so it is the whole question `keep` asks rather than one part of it. Ask it in its own step: `then keep where matching(products, by [id]) then keep where ...`
  |
2 |   then keep where (matching(products, by [product]) and ([revenue] > 100))
  |                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

try:
    collect(sales >> keep(matching(products, by = col.product) & (col.revenue > 100)))
except GodError as refusal:
    print(refusal)


illegal: `matching` chooses whole rows, so it is the whole question `keep` asks rather than one part of it. Ask it in its own step: `then keep where matching(products, by [id]) then keep where ...`
  |
2 |   then keep where (matching(products, by [product]) and ([revenue] > 100))
  |                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

sales |> keep(matching(products, by = product)) |> keep(revenue > 100)

region	product	revenue	cost
West	Widget	200	50
West	Gadget	300	100
West	Gadget	150	50
East	Widget	500	100

sales >> keep(matching(products, by = col.product)) >> keep(col.revenue > 100)

region	product	revenue	cost
West	Widget	200	50
West	Gadget	300	100
West	Gadget	150	50
East	Widget	500	100