data.table joins I always re-look-up

Left, anti, rolling and update joins in data.table syntax.

Why

dplyr join names are verbs; data.table joins are punctuation. This is the translation table I keep reopening.

Left join

library(data.table)

# All rows of x, matching columns from y
y[x, on = .(id)]

# Equivalent, more explicit
merge(x, y, by = "id", all.x = TRUE)

Anti join

# Rows of x with no match in y
x[!y, on = .(id)]

Update by reference

The one that makes data.table worth it — add columns from y into x without copying.

x[y, on = .(id), `:=`(score = i.score, label = i.label)]

i. prefixes the column from the second table in the join.

Rolling join

Match each row of x to the most recent earlier row of y.

setkey(y, id, date)
y[x, on = .(id, date), roll = TRUE]

# Only roll forward up to 7 days
y[x, on = .(id, date), roll = 7]

Non-equi join

# Every event in y falling inside x's window
y[x, on = .(id, date >= start, date <= end)]