setkey.Rd
setkey
sorts a data.table
and marks it as sorted with an
attribute "sorted"
. The sorted columns are the key. The key can be any
number of columns. The data is always sorted in ascending order with NA
s
(if any) always first. The table is changed by reference and there is
no memory used for the key (other than marking which columns the data is sorted by).
There are three reasons setkey
is desirable:
binary search and joins are faster when they detect they can use an existing key
grouping by a leading subset of the key columns is faster because the groups are already gathered contiguously in RAM
simpler shorter syntax; e.g. DT["id",]
finds the group "id" in the first column of DT
's key using binary search. It may be helpful to think of a key as supercharged rownames: multicolumn and multitype.
NA
s are always first because:
NA
is internally INT_MIN
(a large negative number) in R. Keys and indexes are always in increasing order so if NA
s are first, no special treatment or branch is needed in many data.table
internals involving binary search. It is not optional to place NA
s last for speed, simplicity and rubustness of internals at C level.
if any NA
s are present then we believe it is better to display them up front (rather than hiding them at the end) to reduce the risk of not realizing NA
s are present.
In data.table
parlance, all set*
functions change their input
by reference. That is, no copy is made at all other than for temporary
working memory, which is as large as one column. The only other data.table
operator that modifies input by reference is :=
. Check out the
See Also
section below for other set*
functions data.table
provides.
setindex
creates an index for the provided columns. This index is simply an
ordering vector of the dataset's rows according to the provided columns. This order vector
is stored as an attribute of the data.table
and the dataset retains the original order
of rows in memory. See the vignette("datatablesecondaryindicesandautoindexing")
for more details.
key
returns the data.table
's key if it exists; NULL
if none exists.
haskey
returns TRUE
/FALSE
if the data.table
has a key.
setkey(x, ..., verbose=getOption("datatable.verbose"), physical = TRUE) setkeyv(x, cols, verbose=getOption("datatable.verbose"), physical = TRUE) setindex(...) setindexv(x, cols, verbose=getOption("datatable.verbose")) key(x) indices(x, vectors = FALSE) haskey(x)
x  A 

...  The columns to sort by. Do not quote the column names. If 
cols  A character vector of column names. For 
verbose  Output status and information. 
physical 

vectors 

setkey
reorders (i.e. sorts) the rows of a data.table
by the columns
provided. The sort method used has developed over the years and we have contributed
to base R too; see sort
. Generally speaking we avoid any type
of comparison sort (other than insert sort for very small input) preferring instead
counting sort and forwards radix. We also avoid hash tables.
Note that setkey
always uses "Clocale"; see the Details in the help for setorder
for more on why.
The sort is stable; i.e., the order of ties (if any) is preserved.
For character vectors, data.table
takes advantage of R's internal global string cache, also exported as chorder
.
In general, it's good practice to use column names rather than numbers. This is
why setkey
and setkeyv
only accept column names.
If you use column numbers then bugs (possibly silent) can more easily creep into
your code as time progresses if changes are made elsewhere in your code; e.g., if
you add, remove or reorder columns in a few months time, a setkey
by column
number will then refer to a different column, possibly returning incorrect results
with no warning. (A similar concept exists in SQL, where "select * from ..."
is considered poor programming style when a robust, maintainable system is
required.)
If you really wish to use column numbers, it is possible but
deliberately a little harder; e.g., setkeyv(DT,names(DT)[1:2])
.
If you wanted to use grep
to select key columns according to
a pattern, note that you can just set value = TRUE
to return a character vector instead of the default integer indices.
The input is modified by reference and returned (invisibly) so it can be used
in compound statements; e.g., setkey(DT,a)[.("foo")]
. If you require a
copy, take a copy first (using DT2=copy(DT)
). copy
may also
sometimes be useful before :=
is used to subassign to a column by
reference.
https://en.wikipedia.org/wiki/Radix_sort
https://en.wikipedia.org/wiki/Counting_sort
http://stereopsis.com/radix.html
https://codercorner.com/RadixSortRevisited.htm
https://cran.rproject.org/package=bit64
https://github.com/Rdatatable/data.table/wiki/Presentations
data.table
, tables
, J
,
sort.list
, copy
, setDT
,
setDF
, set
:=
, setorder
,
setcolorder
, setattr
, setnames
,
chorder
, setNumericRounding
# Type 'example(setkey)' to run these at the prompt and browse output DT = data.table(A=5:1,B=letters[5:1]) DT # before#> A B #> <int> <char> #> 1: 5 e #> 2: 4 d #> 3: 3 c #> 4: 2 b #> 5: 1 asetkey(DT,B) # reorders table and marks it sorted. DT # after#> Key: <B> #> A B #> <int> <char> #> 1: 1 a #> 2: 2 b #> 3: 3 c #> 4: 4 d #> 5: 5 e#> NAME NROW NCOL MB COLS KEY #> 1: DT 5 2 0 A,B B #> Total: 0MBkey(DT)#> [1] "B"keycols = c("A","B") setkeyv(DT,keycols) DT = data.table(A=5:1,B=letters[5:1]) DT2 = DT # does not copy setkey(DT2,B) # does not copyonwrite to DT2 identical(DT,DT2) # TRUE. DT and DT2 are two names for the same keyed table#> [1] TRUEDT = data.table(A=5:1,B=letters[5:1]) DT2 = copy(DT) # explicit copy() needed to copy a data.table setkey(DT2,B) # now just changes DT2 identical(DT,DT2) # FALSE. DT and DT2 are now different tables#> [1] FALSEDT = data.table(A=5:1,B=letters[5:1]) setindex(DT) # set indices setindex(DT, A) setindex(DT, B) indices(DT) # get indices single vector#> [1] "A__B" "A" "B"indices(DT, vectors = TRUE) # get indices list#> [[1]] #> [1] "A" "B" #> #> [[2]] #> [1] "A" #> #> [[3]] #> [1] "B" #>