setops

0th

Percentile

Set operations for data tables

Similar to base R set functions, union, intersect, setdiff and setequal but for data.tables. Additional all argument controls how duplicated rows are handled. Functions fintersect, setdiff (MINUS or EXCEPT in SQL) and funion are meant to provide functionality of corresponding SQL operators. Unlike SQL, data.table functions will retain row order.

Keywords
data
Usage
fintersect(x, y, all = FALSE)
fsetdiff(x, y, all = FALSE)
funion(x, y, all = FALSE)
fsetequal(x, y, all = TRUE)
Arguments
x, y

data.tables.

all

Logical. Default is FALSE and removes duplicate rows on the result. When TRUE, if there are xn copies of a particular row in x and yn copies of the same row in y, then:

  • fintersect will return min(xn, yn) copies of that row.

  • fsetdiff will return max(0, xn-yn) copies of that row.

  • funion will return xn+yn copies of that row.

  • fsetequal will return FALSE unless xn == yn.

Details

bit64::integer64 columns are supported but not complex and list, except for funion.

Value

A data.table in case of fintersect, funion and fsetdiff. Logical TRUE or FALSE for fsetequal.

References

https://db.apache.org/derby/papers/Intersect-design.html

See Also

data.table, rbindlist, all.equal.data.table, unique, duplicated, uniqueN, anyDuplicated

Aliases
  • setops
  • intersect
  • fintersect
  • setdiff
  • fsetdiff
  • except
  • fexcept
  • union
  • funion
  • setequal
  • fsetequal
Examples
# NOT RUN {
x = data.table(c(1,2,2,2,3,4,4))
x2 = data.table(c(1,2,3,4)) # same set of rows as x
y = data.table(c(2,3,4,4,4,5))
fintersect(x, y)            # intersect
fintersect(x, y, all=TRUE)  # intersect all
fsetdiff(x, y)              # except
fsetdiff(x, y, all=TRUE)    # except all
funion(x, y)                # union
funion(x, y, all=TRUE)      # union all
fsetequal(x, x2, all=FALSE) # setequal
fsetequal(x, x2)            # setequal all
# }
Documentation reproduced from package data.table, version 1.12.0, License: MPL-2.0 | file LICENSE

Community examples

Looks like there are no examples yet.