data.table joins

Natural equi-joins in data.table, no frills.

library(data.table)

data("band_members", package = "dplyr")
data("band_instruments", package = "dplyr")

setDT(band_members)
setDT(band_instruments)

Datasets #

band_members

##    name    band
## 1: Mick  Stones
## 2: John Beatles
## 3: Paul Beatles

band_instruments

##     name  plays
## 1:  John guitar
## 2:  Paul   bass
## 3: Keith guitar

Full join #

merge(band_members, band_instruments, all = TRUE)

##     name    band  plays
## 1:  John Beatles guitar
## 2: Keith    <NA> guitar
## 3:  Mick  Stones   <NA>
## 4:  Paul Beatles   bass

Left join #

merge(band_members, band_instruments, all.x = TRUE)

##    name    band  plays
## 1: John Beatles guitar
## 2: Mick  Stones   <NA>
## 3: Paul Beatles   bass

Right join #

band_members[band_instruments, on = .NATURAL]

##     name    band  plays
## 1:  John Beatles guitar
## 2:  Paul Beatles   bass
## 3: Keith    <NA> guitar

Inner join #

band_members[band_instruments, on = .NATURAL, nomatch = NULL]

##    name    band  plays
## 1: John Beatles guitar
## 2: Paul Beatles   bass