data.table
|
This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production. The reference material consulted while preparing these pages is listed in the section bibliography. |
Checked against R 4.6.1 (2026-06-24) on 2026-10-20; R 4.6.2 is scheduled for 2026-10-29. This page is about the add-on package
data.table, checked at version 1.18.6.1 (dated 22 August 2026 in its NEWS file). data.table extends the data frame of
Data Frames with one query form, DT[i, j, by], that reads “take DT, choose
the rows i, compute j, grouped by by”; with columns that are added, changed and removed in place; with keyed, rolling and
non-equi joins; and with a multi-threaded file reader and writer, fread() and fwrite(). Apart from base R, the scripts on the page
run only data.table, and one script that compares the three styles also runs dplyr, checked at version 1.2.1, always written
dplyr:: so that the two packages are never attached together. The image used for the examples also had bit64 4.8.6, which
supplies the integer64 class that fread() uses for 13-digit numbers such as ISBNs. dtplyr (1.3.3 in the image) is named in one
sentence and is not run. data.table depends on nothing but R itself and the methods package that ships with it, and the vignette on importing it presents that as
a reason to depend on it
(data.table package documentation: Importing data.table).
Every example is a script that was run with Rscript on R 4.6.1 with warnings printed as they happen (options(warn = 1)), and the
lines starting with #> are what it printed, messages included, in order. The scripts also pass the default lintr 3.4.0 linters,
which includes the line-length limit of 80 characters. lintr’s object_usage_linter cannot see that a name inside DT[...]
is a column, so it complains when such a query sits inside a function body; the small functions on this page that do that carry a
# nolint: object_usage_linter. comment (or a nolint start and nolint end pair), which is the only exception, and queries at
the top level of a script need nothing. The examples use the library-shelf scenario of the section: books, loans, shelf_today and
loan_days come from the scenario.R file described on the section index, so a script that
needs them starts with source("scenario.R") and then converts the data frames in place with setDT(). books has 16 rows and the
columns title, author, isbn, year, genre and pages; loans has 60 rows and the columns loan_id, isbn, member,
loan_date and return_date. The scripts that read the files directly use fread("books.csv"). Files that a script writes go to
tempfile() and are deleted before the script ends. Each script is complete, so any block can be pasted and run on its own; the
setup lines are repeated where a later block needs them.
The package website at https://rdatatable.gitlab.io/data.table/ documents the development version of data.table (its page header
said 1.18.99 when it was read for this page), so a few details there are ahead of the installed 1.18.6.1. Where the two disagree, the
page reports what the installed package did: the website’s options page, for example, lists datatable.print.class and
datatable.print.keys with the default FALSE, and the installed package returns TRUE for both (see
Printing and threads). Terms from other pages are used without repeating them: copy-on-modify is in
Memory and Copy Semantics and
Assignment, Names and Values, the evaluation of column names inside a
call is the data-mask idea of Evaluation and Non-Standard
Evaluation, and the same shelf analyses done with dplyr are in The tidyverse and
dplyr.
What a data.table is
A data.table is a data frame: its class is c("data.table", "data.frame"), it is a list of columns of equal length, and every
function that accepts a data frame accepts it. What differs is the [ method. DT[i, j, by] is a query language in the
brackets (the next section), DT[2] is the second ROW (a data frame treats a single number as a column), and a data.table
never has or uses row names. Printing differs too: a data.table shows row numbers followed by a colon, a line of column classes under
the names (<char>, <int>), and for a long table only the first and last rows. data.table() builds a table from vectors as
data.frame() does, with stringsAsFactors = FALSE and check.names = FALSE as defaults and an optional key argument
(data.table package documentation: data.table).
The first script attaches the package with a plain library(data.table) call, which is the only place on the page where its start-up
message is shown. data.table exports its own %notin% (added in data.table 1.15.0), and R 4.6.0 added %notin% to base R
(it is listed in the R NEWS for 4.6.0), so attaching the package prints a conflict message and the
data.table version is found first on the search path; both are the negation of %in%. In an interactive session library(data.table) also
prints a line with the version and the number of threads in use; Rscript does not, because the .onAttach() hook of the package
returns at once when interactive() is FALSE.
library(data.table)
#>
#> Attaching package: ‘data.table’
#>
#> The following object is masked from ‘package:base’:
#>
#> %notin%
#>
# The first print of a data.table shows its class, row numbers and a colon.
shelf <- data.table(
isbn = c("9780141439518", "9780306406157", "9780002000291"),
title = c("Pride and Prejudice", "The Dispossessed", "Loud Roofs"),
copies = c(2L, 1L, 3L)
)
shelf
#> isbn title copies
#> <char> <char> <int>
#> 1: 9780141439518 Pride and Prejudice 2
#> 2: 9780306406157 The Dispossessed 1
#> 3: 9780002000291 Loud Roofs 3
class(shelf)
#> [1] "data.table" "data.frame"
# Strings stay character: no stringsAsFactors argument is needed.
vapply(shelf, \(x) class(x)[[1L]], character(1))
#> isbn title copies
#> "character" "character" "integer"
# The same table as a data.frame: [ with one number selects a COLUMN there.
shelf_df <- as.data.frame(shelf)
shelf_df[2]
#> title
#> 1 Pride and Prejudice
#> 2 The Dispossessed
#> 3 Loud Roofs
shelf[2]
#> isbn title copies
#> <char> <char> <int>
#> 1: 9780306406157 The Dispossessed 1
# The version, and which %notin% is found first on the search path.
packageVersion("data.table")
#> [1] ‘1.18.6.1’
environmentName(environment(`%notin%`))
#> [1] "data.table"Real tables are usually already data frames, and data.table converts them in two ways. as.data.table() makes a copy and leaves the
original alone. setDT() changes the object in place: it adds the class and the over-allocation described below, copies nothing, and
returns the table invisibly, so it is called without an assignment. setDF() goes back in place. The next script converts the shelf
tables, and shows keep.rownames for a data frame whose row names carry information.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
# books and loans from scenario.R are data frames.
class(books)
#> [1] "data.frame"
# as.data.table() copies; the original is untouched.
copy_dt <- as.data.table(books)
class(books)
#> [1] "data.frame"
class(copy_dt)
#> [1] "data.table" "data.frame"
# setDT() converts IN PLACE: no copy, no assignment, no result printed.
setDT(books)
setDT(loans)
class(books)
#> [1] "data.table" "data.frame"
class(loans)
#> [1] "data.table" "data.frame"
# Row names are dropped; keep.rownames = "isbn" keeps them in a column.
copies <- data.frame(copies = 2:3,
row.names = c("9780306406157", "9780141439518"))
as.data.table(copies)
#> copies
#> <int>
#> 1: 2
#> 2: 3
as.data.table(copies, keep.rownames = "isbn")
#> isbn copies
#> <char> <int>
#> 1: 9780306406157 2
#> 2: 9780141439518 3
# setDF() goes back, also in place.
tmp <- copy(books[1:2, .(title, year)])
setDF(tmp)
class(tmp)
#> [1] "data.frame"
is.data.table(books)
#> [1] TRUEThe general form: DT[i, j, by]
The package documentation reads the form out loud: “take DT, subset rows by i, then compute j grouped by by”. The table
lines the three arguments up with the clauses of an SQL query, and lists the arguments that refine them. Every argument of a query is
optional, and the commas keep the positions: DT[, j] has no i, DT[i] has only i.
| Argument | SQL clause | What it does |
|---|---|---|
|
|
chooses and orders the rows: row numbers, a logical condition, |
|
|
what to compute: a column, an expression, a list written |
|
|
runs |
|
|
how a table in |
|
which columns |
Queries chain by writing another pair of brackets straight after the first: DT[...][...] runs the second query on the result
of the first. That is how a result is sorted after grouping (DT[, .N, by = g][order(-N)]), and it replaces the temporary
variables of base R.
i: choosing and ordering rows
i is evaluated inside the table, so the columns are variables and books[year > 2020] needs no books$. It can be a vector of row
numbers (negative numbers drop rows), a logical expression, or order(...) for sorting, where a minus sign means descending. A
logical NA in i is treated as FALSE, as the help page says, which differs from a data frame, where a logical NA produces a row of NA values. A logical i must be as long as the table: recycling a shorter one is an error. The operators %between%, %chin% and
%like% (a regular expression, as grepl()) are shortcuts for common conditions. order() inside i is handled by data.table’s
own fast sort, which sorts text in C-locale, not in the locale of the session (see the setters), and puts NA last, as base order() does.
When a variable of the calling environment has the name of a column, the column wins: after year <- 1900, books[year > 2022] still
compares the column. The last lines of the script show it; the way to reach the variable is env = or .. (see
Programming with data.table).
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
# i is evaluated inside the table: columns are variables, no books$ needed.
books[year > 2020, .(title, year)]
#> title year
#> <char> <int>
#> 1: Loud Roofs 2021
#> 2: THE LONG TABLE 2023
#> 3: Ten Winters 2022
# Row numbers, negative numbers and logical vectors work as in a data frame.
books[-(3:16), .(title)]
#> title
#> <char>
#> 1: The Dispossessed
#> 2: Pride and Prejudice
# NA in a logical i drops the row (year is NA in row 7).
books[year > 2020 | is.na(year), .(title, year)]
#> title year
#> <char> <int>
#> 1: Loud Roofs 2021
#> 2: Garden of Small Fires NA
#> 3: THE LONG TABLE 2023
#> 4: Ten Winters 2022
# order() inside i sorts; - means descending. NA goes last for ascending.
books[order(-pages), .(title, pages)][1:3]
#> title pages
#> <char> <int>
#> 1: Pride and Prejudice 432
#> 2: Notes on Gravity 410
#> 3: The Dispossessed 387
# %between%, %chin% and %like% are shortcuts for common conditions.
books[year %between% c(2015, 2020), .(title, year)]
#> title year
#> <char> <int>
#> 1: the quiet streets 2019
#> 2: Midnight Ledger 2016
#> 3: Notes on Gravity 2017
#> 4: Orchard Logic 2020
#> 5: Salt and Static 2018
books[genre %chin% c("Poetry", "Mystery"), .(title, genre)]
#> title genre
#> <char> <char>
#> 1: Midnight Ledger Mystery
#> 2: Garden of Small Fires Poetry
#> 3: Paper Moons Poetry
#> 4: Salt and Static Mystery
books[title %like% "^The", .(title)]
#> title
#> <char>
#> 1: The Dispossessed
#> 2: The Lamp at Noon
#> 3: The Slow Comet
# A column named like an outside variable: the column wins.
year <- 1900
books[year > 2022, .(title)]
#> title
#> <char>
#> 1: THE LONG TABLEj: columns, expressions and lists
j is evaluated in the scope of the table too. A bare column name returns the column as a vector, .(a, b) (an alias of list(a, b))
returns a data.table, and any other expression is evaluated on whole columns. When j returns a list, every element becomes a column,
named after the argument, or V1, V2 and so on when it is unnamed. A number or a character vector of names in j selects columns and
always gives a data.table back, which is the difference to df[, 2] on a data frame, where one column becomes a vector. A
character vector held in a variable is looked up in the calling scope with the prefix .. (one level up, like .. in a file
path), or passed to .SDcols. j can be any code, so it can also run for its side effect (a plot, a cat()), and then its value is
what the code returns.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
# A bare column in j comes back as a vector; .() or list() as a data.table.
books[, pages]
#> [1] 387 432 214 198 301 NA 96 280 352 256 88 410 175 322 NA 244
books[1:3, .(pages)]
#> pages
#> <int>
#> 1: 387
#> 2: 432
#> 3: 214
# An expression is evaluated on the whole column. na.rm is needed for NA.
books[, mean(pages, na.rm = TRUE)]
#> [1] 268.2143
books[, .(mean_pages = mean(pages, na.rm = TRUE), n = .N)]
#> mean_pages n
#> <num> <int>
#> 1: 268.2143 16
# Several results become several columns; unnamed ones are called V1, V2...
books[genre == "Poetry", .(title, nchar(title), pages * 2L)]
#> title V2 V3
#> <char> <int> <int>
#> 1: Garden of Small Fires 21 192
#> 2: Paper Moons 11 176
# A number in j selects a column and ALWAYS gives a data.table back.
books[1:2, 1]
#> title
#> <char>
#> 1: The Dispossessed
#> 2: Pride and Prejudice
books[1:2, c("title", "year")]
#> title year
#> <char> <int>
#> 1: The Dispossessed 1974
#> 2: Pride and Prejudice 1813
# A character vector held in a variable is looked up one level up with
# `..`, or with .SD and .SDcols.
cols <- c("title", "year")
books[1:2, ..cols]
#> title year
#> <char> <int>
#> 1: The Dispossessed 1974
#> 2: Pride and Prejudice 1813
books[1:2, .SD, .SDcols = cols]
#> title year
#> <char> <int>
#> 1: The Dispossessed 1974
#> 2: Pride and Prejudice 1813by and keyby: groups
by runs j once for each group of rows that share the values of the columns (or expressions) named, and stacks the results. The
groups come out in the order of their first appearance in the table; keyby sorts them and sets them as the key of the result,
which is the usual choice when the result is printed. by accepts a column name, a character vector of names, a range a:c of
columns, or a list of expressions in .(...), and a computed grouping column needs a name there, as decade below. i is applied
before the grouping, which is how a filtered summary is written. A single unnamed value in j is called V1 in the result; .N (the
size of the group) becomes N.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
books[, genre := tolower(trimws(genre))]
# by groups; the groups come out in the order of first appearance.
books[, .(n = .N, pages = sum(pages, na.rm = TRUE)), by = genre]
#> genre n pages
#> <char> <int> <int>
#> 1: science fiction 3 1119
#> 2: classic 2 712
#> 3: fiction 4 668
#> 4: mystery 2 545
#> 5: non-fiction 3 527
#> 6: poetry 2 184
# keyby sorts the groups and sets a key on the result.
books[, .N, keyby = genre]
#> Key: <genre>
#> genre N
#> <char> <int>
#> 1: classic 2
#> 2: fiction 4
#> 3: mystery 2
#> 4: non-fiction 3
#> 5: poetry 2
#> 6: science fiction 3
# An unnamed single expression is called V1; .() and names fix that.
books[, mean(pages, na.rm = TRUE), by = genre]
#> genre V1
#> <char> <num>
#> 1: science fiction 373.0000
#> 2: classic 356.0000
#> 3: fiction 222.6667
#> 4: mystery 272.5000
#> 5: non-fiction 263.5000
#> 6: poetry 92.0000
# by takes expressions: a computed grouping column needs a name in .().
books[, .(n = .N), by = .(decade = year %/% 10L * 10L)][order(decade)]
#> decade n
#> <int> <int>
#> 1: 1810 1
#> 2: 1970 1
#> 3: 1990 1
#> 4: 2000 2
#> 5: 2010 6
#> 6: 2020 4
#> 7: NA 1
# Several columns: a character vector or .() work the same.
books[, .N, by = c("genre", "author")][1:3]
#> genre author N
#> <char> <char> <int>
#> 1: science fiction Ursula K. Le Guin 1
#> 2: classic Jane Austen 1
#> 3: fiction Mara Quill 2
# Rows are subset first (i), then grouped.
books[year > 2000, .(first_title = title[[1L]], n = .N), by = genre]
#> genre first_title n
#> <char> <char> <int>
#> 1: fiction the quiet streets 4
#> 2: mystery Midnight Ledger 2
#> 3: non-fiction A Map of Salt 3
#> 4: poetry Paper Moons 1
#> 5: science fiction Notes on Gravity 2A group of NA is a group like any other: the book without a year forms its own decade in the output above.
Adding, changing and removing columns by reference
The := operator
:= is data.table’s assignment inside j. It adds a column, replaces a column or a part of one, or (with NULL) removes a column,
in the table itself: no copy of the table is made and nothing is assigned back, so books[, age := 2026L - year] is a complete
statement. There are two spellings. The LHS := RHS form takes column names on the left (a single name, or a character vector in c()
or in parentheses) and a value or a list of values on the right. The functional form, := called like a function, as in `:=`(a = ..., b = ...), takes named
arguments, which lets each column carry its own comment; let() is an alias for it in the help page of the package, and it is present in
1.18.6.1. With i, only the rows chosen are changed, and a new column is NA in the other rows. With by, the value is computed for
each group and recycled within it. The call returns the table invisibly, so nothing is printed at the prompt
(Printing after the := operator has the details), and .Last.updated holds the number of rows the last := or set()
changed.
A name in parentheses, (cols) :=, means “the columns whose names are held in cols”, where a bare cols := would create a column
called cols. This is also how one function is applied to many columns, (cols) := lapply(.SD, f). Since data.table 1.16.0
names(.SD) := lapply(.SD, f) updates every column of .SD (NEWS for 1.16.0). The help page states the rule for types: the
right-hand side is coerced to the type of the existing column, not the other way round (unlike <- on a data frame), and a whole
column given as the right-hand side replaces the column, with its new type ("plonk" syntax, as in (num_cols) := lapply(.SD, as.numeric)
below). Coercing a part of a column warns, and a right-hand side whose length is neither 1 nor the number of rows is an error
(both messages are in Common errors and warnings).
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
# := adds a column by reference: no assignment, and nothing is printed.
books[, age := 2026L - year]
books[1:3, .(title, year, age)]
#> title year age
#> <char> <int> <int>
#> 1: The Dispossessed 1974 52
#> 2: Pride and Prejudice 1813 213
#> 3: the quiet streets 2019 7
# With i, only those rows change; a new column is NA in the others.
books[pages > 400L, size := "long"]
books[, .N, by = size]
#> size N
#> <char> <int>
#> 1: <NA> 14
#> 2: long 2
# Several columns: names on the left, a list on the right.
books[, c("title", "genre") := .(trimws(title), tolower(trimws(genre)))]
books[3:4, .(title, genre)]
#> title genre
#> <char> <char>
#> 1: the quiet streets fiction
#> 2: Loud Roofs fiction
# The functional form allows a comment next to each column.
books[, `:=`(
author = gsub("\\s+", " ", trimws(author)), # collapse repeated blanks
isbn = gsub("[^0-9X]", "", isbn) # keep digits and X only
)]
books[9:10, .(title, author, isbn)]
#> title author isbn
#> <char> <char> <char>
#> 1: Rivers of Glass Tomas Arden
#> 2: THE LONG TABLE Iris Fenwick 9780008000851
# One function over several columns: (cols) := lapply(.SD, f).
num_cols <- c("year", "pages")
books[, (num_cols) := lapply(.SD, as.numeric), .SDcols = num_cols]
vapply(books[, ..num_cols], class, character(1))
#> year pages
#> "numeric" "numeric"
# NULL deletes columns.
books[, c("age", "size") := NULL]
names(books)
#> [1] "title" "author" "isbn" "year" "genre" "pages"
# The result is invisible; a trailing [] prints it.
small <- books[1:3, .(title, year)]
small[, decade := year %/% 10 * 10][]
#> title year decade
#> <char> <num> <num>
#> 1: The Dispossessed 1974 1970
#> 2: Pride and Prejudice 1813 1810
#> 3: the quiet streets 2019 2010Reference semantics and copy()
Because := and the set* functions change the table where it is, two names can refer to one table, and a change made through one
name is seen through the other. b <- a gives a data.table a second name, as it does for every R object, and a := on b then changes
the table both names refer to; a data frame, by contrast, is copied the moment it is modified. The same holds when a table is passed to
a function: the function receives the table itself, so a := inside it changes the caller’s data. copy() makes a real, independent
copy and is the way to protect the original. The vignette
data.table package documentation: Reference semantics
gives the reason: since R 3.1.0 adding a column to a data frame copies only the vector of column pointers (a shallow copy), but changing
some rows of a column with df$c[i] <- value still copies the whole column, and := does neither.
suppressPackageStartupMessages(library(data.table))
# b <- a does not copy a data.table, and := changes the one object.
a <- data.table(x = 1:3)
b <- a
b[, y := x * 2L]
names(a)
#> [1] "x" "y"
address(a) == address(b)
#> [1] TRUE
# copy() makes an independent table.
d <- copy(a)
d[, z := 0L]
names(a)
#> [1] "x" "y"
names(d)
#> [1] "x" "y" "z"
address(a) == address(d)
#> [1] FALSE
# A function that uses := on its argument changes the caller's table.
add_flag <- function(tbl) {
tbl[, flag := TRUE] # nolint: object_usage_linter.
invisible(NULL)
}
add_flag(a)
names(a)
#> [1] "x" "y" "flag"
# copy() at the top of the function keeps the caller's table untouched.
with_stamp <- function(tbl) {
tbl <- copy(tbl)
tbl[, stamp := "2026-06-30"] # nolint: object_usage_linter.
tbl[]
}
res <- with_stamp(a)
names(res)
#> [1] "x" "y" "flag" "stamp"
names(a)
#> [1] "x" "y" "flag"
# The base R way, a$col <- value, makes a (shallow) copy as usual.
e <- a
e$w <- 1
names(a)
#> [1] "x" "y" "flag"
names(e)
#> [1] "x" "y" "flag" "w"address(a) == address(b) is TRUE when two names refer to the same table; the addresses themselves are memory locations that change
from run to run and are not worth printing. The last lines of the script show the base R way, e$w <- 1: the replacement function
$<- works on a (shallow) copy, so a is unchanged. Base R replacement functions such as attr<- are the usual way a data.table is
copied by accident, which is the subject of the self-reference warning below. The rule of thumb is to use := and the
set* functions to change a data.table, copy() to keep the original, and not to rely on the copy-on-modify behaviour of a data
frame. The sharing of one table by several names is the
same idea as the shared environments of Scoping, Environments and
Closures; Memory and Copy Semantics covers when R copies and when it does not.
set(), setnames(), setorder() and setcolorder()
Every function whose name starts with set changes its argument by reference and returns it invisibly. set(DT, i, j, value) is
described in the help page as a low-overhead, loop-able version of := that cannot group: the cost of a DT[...] call is small
but is paid on each iteration, and set() avoids it, which makes it the tool for updating columns in a for loop (i must be row
numbers, j a column name or number). setnames() renames columns by name or position, setorder() sorts the table itself (a minus
sign for descending; NA first), setcolorder() moves columns to the front (it gained before and after arguments in 1.15.0), and
setattr() sets an attribute without copying. setkey(), setindex() and setDT() are covered elsewhere on the page.
Sorting in data.table, whether by setorder(), setkey(), or order() in i, always uses the C-locale: capitals sort before lower
case whatever language the session is set to, and the order is the same on every machine. The help page of setorder() explains the
choice and says that DT[base::order(...)] sorts by the locale of the session instead.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
# set() is := for loops: one call per column, with no [.data.table overhead.
for (col in c("title", "author", "genre")) {
set(books, j = col, value = trimws(books[[col]]))
}
# With i it changes only some rows (the rows must be numbers).
set(books, i = which(books$genre == "Science fiction"), j = "genre",
value = "science fiction")
books[genre == "science fiction", .N]
#> [1] 3
# setnames() renames in place, by name or by position.
setnames(books, "pages", "n_pages")
setnames(books, c("title", "author"), c("book_title", "writer"))
names(books)
#> [1] "book_title" "writer" "isbn" "year" "genre"
#> [6] "n_pages"
# setorder() sorts the table itself; - means descending, NA comes first.
setorder(books, year)
books[, .(book_title, year)][1:3]
#> book_title year
#> <char> <int>
#> 1: Garden of Small Fires NA
#> 2: Pride and Prejudice 1813
#> 3: The Dispossessed 1974
setorder(books, genre, -year)
books[, .(genre, year)][1:4]
#> genre year
#> <char> <int>
#> 1: Classic 1999
#> 2: Classic 1813
#> 3: Fiction 2023
#> 4: Fiction 2022
# Strings sort in C-locale (capitals first), whatever the session locale.
words <- data.table(w = c("brazil", "America", "america", "Brazil"))
setorder(words, w)
words$w
#> [1] "America" "Brazil" "america" "brazil"
# setcolorder() moves columns to the front; the others keep their order.
setcolorder(books, c("isbn", "book_title"))
names(books)
#> [1] "isbn" "book_title" "writer" "year" "genre"
#> [6] "n_pages"Over-allocation
The help page of truelength() explains why a data.table has room for more columns than it uses. Adding a column by creating a new list
of column pointers (a shallow copy) "resulted in inconsistent behaviour in some circumstances", so data.table over-allocates the list
of column pointers when data.table() or setDT() creates a table, so that := can add a column fully by reference. truelength()
is the number of slots allocated and length() the number in use. The default is 1024 spare slots, set by
options(datatable.alloccol = ). When the slots are used up, the next := has to reallocate, which takes a shallow copy; the page
suggests copy() first, setalloccol() (also called alloc.col()) to reallocate in advance, or a larger option value to avoid it, and
options(datatable.verbose = TRUE) reports when a shallow copy is taken. The functions are described as experimental and somewhat
advanced, so the names might change.
A table that has been through saveRDS() and readRDS(), or save() and load(), comes back without its slots and without a valid
self-reference. The next := or set() allocates them again, quietly, as the script shows with truelength() and the helper
.selfref.ok(). The help page of .selfref.ok() says that it is for technical use; it is shown only to make the mechanism visible.
Loading a table back is therefore not what triggers the warning in the next section.
suppressPackageStartupMessages(library(data.table))
# A data.table has room for more columns than it uses (over-allocation),
# which is why := can add a column without copying anything.
dt <- data.table(a = 1:3)
length(dt)
#> [1] 1
truelength(dt)
#> [1] 1025
getOption("datatable.alloccol")
#> [1] 1024
# Adding columns fills the spare slots, truelength() stays put.
dt[, c("b", "c") := 1L]
length(dt)
#> [1] 3
truelength(dt)
#> [1] 1025
# A table read back from disk has no spare room and no valid self-reference;
# := and set() allocate the room again, quietly.
path <- tempfile(fileext = ".rds")
saveRDS(dt, path)
back <- readRDS(path)
truelength(back)
#> [1] 0
.selfref.ok(back)
#> [1] FALSE
back[, d := 4L]
truelength(back)
#> [1] 1028
.selfref.ok(back)
#> [1] TRUE
other <- readRDS(path)
set(other, j = "e", value = 5L)
truelength(other)
#> [1] 1027
file.remove(path)
#> [1] TRUEThe self-reference warning
To know that a table is the one it allocated, data.table stores a pointer to the table itself in a hidden attribute,
.internal.selfref. A table that R copied, or that was assembled by hand, does not pass the check. This happens when a table is built
with structure() or by class<- on a data frame, when attr<- copies it, and when a package that knows nothing of data.table
returns a modified copy. := still works on such a table, but it takes a shallow copy so that it can add the column, and warns that it
did. In data.table 1.18.6.1 the warning begins "A shallow copy of this data.table was taken so that := can add or remove 1 columns by
reference" (older releases, and many answers written for them, begin "Invalid .internal.selfref detected and fixed by taking a
(shallow) copy of the data.table"). The cure is the one the message gives: build tables with setDT() or data.table(), copy with
copy(), and use the set* functions in place of the base replacement functions.
suppressPackageStartupMessages(library(data.table))
# A data.table copied by R itself (attr<-, class<- on a data frame,
# structure()) loses its self-reference. := still works, with a warning.
df <- data.frame(x = 1:2)
class(df) <- c("data.table", "data.frame")
.selfref.ok(df)
#> [1] FALSE
res <- tryCatch(df[, y := 2L], warning = conditionMessage)
cat(strwrap(res, width = 70), sep = "\n")
#> A shallow copy of this data.table was taken so that := can add or
#> remove 1 columns by reference. At an earlier point, this data.table
#> was copied by R (or was created manually using structure() or
#> similar). Avoid names<- and attr<- which in R currently (and oddly)
#> may copy the whole data.table. Use set* syntax instead to avoid
#> copying: ?set, ?setnames and ?setattr. It's also not unusual for
#> data.table-agnostic packages to produce tables affected by this
#> issue. If this message doesn't help, please report your use case to
#> the data.table issue tracker so the root cause can be fixed or this
#> message improved.
# Taking the copy with copy() or setDT() avoids the problem.
df <- data.frame(x = 1:2)
setDT(df)
.selfref.ok(df)
#> [1] TRUE
df[, y := 2L]
df
#> x y
#> <int> <int>
#> 1: 1 2
#> 2: 2 2Printing after :=
At the prompt, books[, x := 1] prints nothing: inside := data.table sets a global flag, which the print method uses to know
whether to print. The flag has a visible side effect: a function whose last expression is a := returns the table, and the print of
that result can be swallowed. FAQ 2.23 of the package explains it and recommends a trailing [] after the last := in a
function (data.table package documentation: Frequently asked questions).
Since data.table 1.17.0 an explicit print(DT[, y := 2]) prints, because the detection of auto-printing was improved; the NEWS of
1.17.0 says this makes the older advice to append [] unnecessary for that case. The script shows both in 1.18.6.1.
suppressPackageStartupMessages(library(data.table))
a <- data.table(x = 1:2)
# At the prompt := returns the table invisibly: nothing is printed.
a[, y := 1L]
# An explicit print() prints it (since data.table 1.17.0, also around :=).
print(a[, y := 2L])
#> x y
#> <int> <int>
#> 1: 1 2
#> 2: 2 2
# A function that ends with := returns the table, but the print of that
# result is swallowed by the flag := leaves behind. A trailing [] prints.
add_z <- function(tbl) {
tbl <- copy(tbl)
tbl[, z := 3L] # nolint: object_usage_linter.
}
add_z(a)
add_w <- function(tbl) {
tbl <- copy(tbl)
tbl[, w := 4L][] # nolint: object_usage_linter.
}
add_w(a)
#> x y w
#> <int> <int> <int>
#> 1: 1 2 4
#> 2: 2 2 4Special symbols: .SD, .SDcols, .N, .I, .GRP, .BY and .EACHI
Inside j, data.table defines a few read-only symbols, listed on the help page special-symbols.
| Symbol | Meaning |
|---|---|
|
the number of rows: in the whole table, or in the current group; alone in |
|
the Subset of Data: a data.table with the columns of the current group, except the columns used in |
|
not a symbol but an argument: which columns |
|
the row numbers: |
|
a counter of groups, 1 for the first group and so on; |
|
a list holding the value of the grouping columns for the current group |
|
a value for |
|
only with |
.SD is the symbol behind most "do this to every column" jobs: DT[, lapply(.SD, mean), by = g] computes a mean for every
column that is not in g, and .SDcols restricts the columns so that a text column is not averaged. To change .SD
before returning it, take copy(.SD) first; the symbols are locked and assigning to them is an error. .I is the tool
for "the row where something is maximal in each group": DT[, .I[which.max(x)], by = g] returns the row numbers, which are
then used to subset the table, as the script does. The vignette
data.table package documentation: Using .SD for data analysis
collects the more advanced uses of .SD.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
books[, genre := tolower(trimws(genre))]
# .N is the number of rows (in the group); alone in i it is the last row.
books[, .N]
#> [1] 16
books[.N, .(title)]
#> title
#> <char>
#> 1: Salt and Static
books[, .N, by = genre][order(-N)][1:2]
#> genre N
#> <char> <int>
#> 1: fiction 4
#> 2: science fiction 3
# .SD is the Subset of Data for each group; .SDcols picks its columns.
books[, lapply(.SD, mean, na.rm = TRUE), by = genre,
.SDcols = c("year", "pages")]
#> genre year pages
#> <char> <num> <num>
#> 1: science fiction 2001.667 373.0000
#> 2: classic 1906.000 356.0000
#> 3: fiction 2021.250 222.6667
#> 4: mystery 2017.000 272.5000
#> 5: non-fiction 2013.333 263.5000
#> 6: poetry 2005.000 92.0000
# .SDcols also takes a function or a pattern, and ! inverts it.
books[, lapply(.SD, max, na.rm = TRUE), .SDcols = is.numeric]
#> year pages
#> <int> <int>
#> 1: 2023 432
names(books[, .SD, .SDcols = patterns("^(t|a)")])
#> [1] "title" "author"
names(books[, .SD, .SDcols = !c("title", "author", "isbn")])
#> [1] "year" "genre" "pages"
# .I is the row number in the whole table: here the longest book per genre.
longest <- books[, .I[which.max(pages)], by = genre]
books[longest$V1, .(genre, title, pages)]
#> genre title pages
#> <char> <char> <int>
#> 1: science fiction Notes on Gravity 410
#> 2: classic Pride and Prejudice 432
#> 3: fiction THE LONG TABLE 256
#> 4: mystery Midnight Ledger 301
#> 5: non-fiction Rivers of Glass 352
#> 6: poetry Garden of Small Fires 96
# .GRP numbers the groups, .BY holds the current value of the by columns.
books[, .(group = .GRP, name = .BY$genre, n = .N), by = genre]
#> genre group name n
#> <char> <int> <char> <int>
#> 1: science fiction 1 science fiction 3
#> 2: classic 2 classic 2
#> 3: fiction 3 fiction 4
#> 4: mystery 4 mystery 2
#> 5: non-fiction 5 non-fiction 3
#> 6: poetry 6 poetry 2
# head(.SD, n) by group is optimised: the oldest title of each genre.
books[order(year), head(.SD, 1L), by = genre, .SDcols = c("title", "year")]
#> genre title year
#> <char> <char> <int>
#> 1: classic Pride and Prejudice 1813
#> 2: science fiction The Dispossessed 1974
#> 3: poetry Paper Moons 2005
#> 4: non-fiction A Map of Salt 2008
#> 5: mystery Midnight Ledger 2016
#> 6: fiction the quiet streets 2019Keys, indices and joins
Keys and binary search
A key is a sort order that the table remembers. setkey(DT, col1, col2) sorts the table by those columns, in place and ascending
(NA first), and marks them as the key (the "sorted" attribute). key() returns the key, haskey() tests for one, and
setkeyv() is the version that takes a character vector. A table has at most one key, because the key is the physical order. Its
purpose is speed and brevity: a subset by key value is a binary search, not a scan of the whole column, DT["x"] finds the group
"x" of the first key column, and grouping by a leading part of the key is faster because the groups are already adjacent in memory
(the reasons are in the help page of setkey()). setkey() always sorts in C-locale. nomatch = NULL drops the values of i that
are not in the table, where the default nomatch = NA returns a row of NA for each, and mult chooses "first", "last" or (the
default) "all" of several matches.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
books[, isbn := gsub("[^0-9X]", "", isbn)]
# setkey() sorts the table by the column(s) and marks them as the key.
key(books)
#> NULL
setkey(books, isbn)
key(books)
#> [1] "isbn"
haskey(books)
#> [1] TRUE
books[, .(isbn, title)][1:3]
#> Key: <isbn>
#> isbn title
#> <char> <char>
#> 1: Rivers of Glass
#> 2: 9780001000100 the quiet streets
#> 3: 9780002000291 Loud Roofs
# A key subset is a binary search, not a scan of the column.
books["9780306406157", .(title, year)]
#> title year
#> <char> <int>
#> 1: The Dispossessed 1974
books[.("9780306406157"), .(title, year)]
#> title year
#> <char> <int>
#> 1: The Dispossessed 1974
# An unknown key gives a row of NA (nomatch = NA); nomatch = NULL drops it.
books[c("9780306406157", "0000000000000"), .(isbn, title)]
#> isbn title
#> <char> <char>
#> 1: 9780306406157 The Dispossessed
#> 2: 0000000000000 <NA>
books[c("9780306406157", "0000000000000"), .(isbn, title), nomatch = NULL]
#> Key: <isbn>
#> isbn title
#> <char> <char>
#> 1: 9780306406157 The Dispossessed
# mult picks one of several matches; here several books share an author.
setkey(books, author)
books["Mara Quill", .(title, year)]
#> title year
#> <char> <int>
#> 1: the quiet streets 2019
#> 2: Loud Roofs 2021
#> 3: Salt and Static 2018
books["Mara Quill", .(title, year), mult = "first"]
#> title year
#> <char> <int>
#> 1: the quiet streets 2019
books["Mara Quill", .(title, year), mult = "last"]
#> title year
#> <char> <int>
#> 1: Salt and Static 2018Joins with on=
x[i, on = ...] looks up the rows of i in x, and the result has one row for each row of i (for each match): in SQL terms
x[i] is a right join, and i can be a data.table, a data frame, a list, or a vector. on names the columns to match: an unnamed
character vector when both tables have the same names (on = "isbn", or on = .(isbn)), a named one when they differ
(on = c(isbn = "code"), or on = "isbn==code"), and an inequality for a non-equi join (next section). It overrides the keys of the
tables, and the help page recommends it even when the tables are keyed, because it makes the code readable. In j, a column of i
that has the same name as one of x is reached with the prefix i., and one of x with x..
The same tool makes the other joins of the table below. nomatch = NULL turns a join into an inner join, the ! prefix gives the
rows of x that have no match in i (an anti join), x[i, col := i.col, on = ...] is an update join that adds columns to x
by reference, and by = .EACHI evaluates j once for each row of i, with .N the number of matches, which is how counts and sums
over matches are written without first building the large joined table. merge() also works on data.tables, with all, all.x,
all.y and by as for data frames, sort = TRUE by default and a default by taken from the shared key (or the common column
names). A join that would produce more rows than nrow(x) + nrow(i) stops with an error unless allow.cartesian = TRUE, a guard
against joining on a key that is not unique in both tables.
| SQL join | In merge() |
With on = |
|---|---|---|
inner join |
|
|
right join, keeping every row of |
|
|
left join, keeping every row of |
|
|
full outer join |
|
no single form; use |
anti join |
not available |
|
update join |
not available |
|
cross join |
not available ( |
|
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
setDT(loans)
books[, `:=`(isbn = gsub("[^0-9X]", "", isbn), genre = tolower(trimws(genre)))]
# x[i, on = ] looks every row of i up in x, so the result has the rows of i
# (a right join in SQL terms). Here i = books: one row per book.
per_book <- loans[books, on = "isbn",
.(title = i.title, n_loans = .N), by = .EACHI]
per_book[order(-n_loans)][1:3]
#> isbn title n_loans
#> <char> <char> <int>
#> 1: 9780001000100 the quiet streets 8
#> 2: 9780306406157 The Dispossessed 7
#> 3: 9780013001331 Ten Winters 7
# Put loans in i to keep every loan, with NA where no book matches.
joined <- books[loans, on = "isbn"]
nrow(joined)
#> [1] 60
joined[is.na(title), .(loan_id, isbn)]
#> loan_id isbn
#> <char> <char>
#> 1: L005 978030640615
# nomatch = NULL turns it into an inner join.
nrow(books[loans, on = "isbn", nomatch = NULL])
#> [1] 59
# on = c(left = "right") joins columns that have different names.
copies <- data.table(code = c("9780306406157", "9780141439518"), copies = 2:1)
books[copies, on = c(isbn = "code"), .(title, copies)]
#> title copies
#> <char> <int>
#> 1: The Dispossessed 2
#> 2: Pride and Prejudice 1
# An update join adds a column to the left table by reference.
loans[books, on = "isbn", genre := i.genre]
loans[, .N, by = genre][order(-N)][1:3]
#> genre N
#> <char> <int>
#> 1: fiction 23
#> 2: science fiction 15
#> 3: classic 6
# An anti join keeps the rows without a match (the ! in i).
loans[!books, on = "isbn", .(loan_id, isbn)]
#> loan_id isbn
#> <char> <char>
#> 1: L005 978030640615
# merge() works as for data frames; the result is sorted by the key.
nrow(merge(loans, books[, .(isbn, title)], by = "isbn"))
#> [1] 59
nrow(merge(loans, books[, .(isbn, title)], by = "isbn", all.x = TRUE))
#> [1] 60The first query in the script counts the loans of every book: loans[books, ..., by = .EACHI] runs j for each row of books, so
the result has one row per book, and .N counts its loans. The default of merge() and the nomatch = NULL of on = give the same
59 matched loans, the count behind the overdue analysis below, because the loan L005 has an ISBN with one digit missing.
Rolling and non-equi joins
Two joins have no counterpart in merge(). A rolling join, roll = TRUE, matches each row of i to the last row of x whose value is
less than or equal to it, in the last column of on; it is the tool for "the price, the fee or the status in force on this date". The
other values of roll are -Inf (the next row, the opposite direction), "nearest", and a finite number that limits how far a row may be
carried. rollends decides what happens before the first row and after the last: by default a forward roll continues past the last
row, and a date before the first row gets NA. A non-equi join matches on >=, >, <= and < in on, which is how a value is placed in
a range or a band; the operators are written in on = .(lo <= pages, hi > pages) (a column of x on the left of each operator and a column of i on the
right) or as strings, on = c("lo<=pages", "hi>pages"). Joins on overlapping intervals have their own
function, foverlaps()
(data.table package documentation: foverlaps). Non-equi joins match a row of i to every row of x that satisfies the condition, so the result can be larger than
either table.
The script gives each loan the late fee in force on its loan date from a small schedule (fees changed on 2025-07-01 and 2026-01-01), then places books in page-size bands.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(loans)
loans <- loans[!is.na(loan_date)]
# A fee schedule: each row is valid from its date until the next row.
fees <- data.table(
from = as.Date(c("2025-01-01", "2025-07-01", "2026-01-01")),
late_fee = c(0.10, 0.15, 0.20)
)
# Rolling join: for each loan, the last fee that started on or before the
# loan date (roll = TRUE carries the prevailing row forward).
fees[loans, on = .(from = loan_date), roll = TRUE,
.(loan_id, loan_date = i.loan_date, late_fee)][c(1, 30, 58)]
#> loan_id loan_date late_fee
#> <char> <Date> <num>
#> 1: L001 2025-01-14 0.10
#> 2: L031 2025-10-02 0.15
#> 3: L059 2026-05-17 0.20
# roll = "nearest" takes the closest row, before or after.
fees[.(as.Date("2025-06-20")), on = "from", roll = "nearest"]
#> from late_fee
#> <Date> <num>
#> 1: 2025-06-20 0.15
# A date before the first row gets NA, unless rollends[1] is TRUE.
early <- as.Date("2024-12-01")
fees[.(early), on = "from", roll = TRUE]
#> from late_fee
#> <Date> <num>
#> 1: 2024-12-01 NA
fees[.(early), on = "from", roll = TRUE, rollends = c(TRUE, TRUE)]
#> from late_fee
#> <Date> <num>
#> 1: 2024-12-01 0.1
# A finite roll limits how far a row is carried: 30 days here.
late <- as.Date(c("2025-07-20", "2025-09-20"))
fees[.(late), on = "from", roll = 30]
#> from late_fee
#> <Date> <num>
#> 1: 2025-07-20 0.15
#> 2: 2025-09-20 NA
# Non-equi join: put each book in a page-size band.
setDT(books)
bands <- data.table(
label = c("short", "medium", "long"),
lo = c(0L, 200L, 300L),
hi = c(200L, 300L, 1000L)
)
banded <- bands[books, on = .(lo <= pages, hi > pages), nomatch = NULL,
.(title = i.title, pages = i.pages, label)]
banded[, .N, by = label]
#> label N
#> <char> <int>
#> 1: long 6
#> 2: medium 4
#> 3: short 4
banded[label == "long", .(title, pages)]
#> title pages
#> <char> <int>
#> 1: The Dispossessed 387
#> 2: Pride and Prejudice 432
#> 3: Midnight Ledger 301
#> 4: Rivers of Glass 352
#> 5: Notes on Gravity 410
#> 6: The Slow Comet 322Secondary indices and auto-indexing
A key reorders the table, and a table has one. A secondary index stores the sorted order of a column (a vector of row numbers) in an
attribute without moving any row, and a table can have many. setindex(DT, col) creates one, indices(DT) lists them, and
setindex(DT, NULL) removes them all. A subset on an indexed column, DT[col == value], DT[col %in% values] or
DT[.(value), on = "col"], uses binary search through the index. Auto-indexing creates the index the first time a column is
used in == or %in% in i, so that the following subsets on that column are fast. The vignette says that auto-indexing is implemented for == and %in% (and for several of them combined with &), not yet for
< and >=, and that on = computes the index on the fly each time without saving it. It is on by default;
options(datatable.auto.index = FALSE) turns it off, and options(datatable.use.index = FALSE) stops the use of existing indices. Changing the
indexed column by reference drops the index, as the script shows, because it would no longer be correct. One consequence is that a == subset can change
the table it reads by adding an attribute, which is harmless for the data but visible in attributes().
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
books[, genre := tolower(trimws(genre))]
# A secondary index records the sorted order of a column WITHOUT reordering
# the table, and a table can have several.
indices(books)
#> NULL
setindex(books, genre)
indices(books)
#> [1] "genre"
key(books)
#> NULL
# == and %in% on an indexed column use binary search through the index.
books[.("poetry"), on = "genre", .(title)]
#> title
#> <char>
#> 1: Garden of Small Fires
#> 2: Paper Moons
books[genre == "poetry", .(title)]
#> title
#> <char>
#> 1: Garden of Small Fires
#> 2: Paper Moons
# Auto-indexing: the first == or %in% on a column creates the index.
setindex(books, NULL)
indices(books)
#> NULL
invisible(books[year == 2019])
indices(books)
#> [1] "year"
# Changing the indexed column by reference drops the index.
books[1, year := 1975L]
indices(books)
#> NULL
# The behaviour is a global option; turn it off to keep tables unchanged.
options(datatable.auto.index = FALSE)
invisible(books[pages == 96L])
indices(books)
#> NULLReading and writing files
fread() reads delimited text and returns a data.table. It detects the separator, the header, the quoting and the type of every
column, from a sample of 10,000 rows taken as 100 blocks spread over the file, and it re-reads a column from the beginning when a later row has a
higher type than the sample suggested. It is multi-threaded, and its help page describes it as similar to read.csv() but faster and
more convenient (compare Reading and Writing Text Data). The arguments
that matter most:
| Argument | Use |
|---|---|
|
a path or URL, a path only, a string of text, or a shell command whose output is read |
|
columns to keep or drop, by name or number; reading fewer columns is faster |
|
as in |
|
strings read as |
|
the number of data rows to read and the lines to skip |
|
|
|
|
|
how integers larger than 2^31 are read: |
|
handling of ragged files |
|
|
|
threads for this call (default |
|
set a key or an index on the result |
suppressPackageStartupMessages(library(data.table))
# fread() guesses the separator, the header and the column types.
books <- fread("books.csv")
class(books)
#> [1] "data.table" "data.frame"
vapply(books, \(x) class(x)[[1L]], character(1))
#> title author isbn year genre pages
#> "character" "character" "character" "integer" "character" "integer"
books[c(6, 7), .(title, year, pages)]
#> title year pages
#> <char> <int> <int>
#> 1: A Map of Salt 2008 NA
#> 2: Garden of Small Fires NA 96
# A quoted empty string stays "". na.strings = '""' reads it as NA.
books[9, .(title, isbn)]
#> title isbn
#> <char> <char>
#> 1: Rivers of Glass
fread("books.csv", na.strings = '""')[9, .(title, isbn)]
#> title isbn
#> <char> <char>
#> 1: Rivers of Glass <NA>
# Thirteen digits are a number to fread(): the isbn column is integer64
# (package bit64) unless colClasses says "character".
vapply(fread("loans.csv"), \(x) class(x)[[1L]], character(1))
#> loan_id isbn member loan_date return_date
#> "character" "integer64" "character" "character" "IDate"
# colClasses fixes the types; Date columns come back as Date or IDate.
loans <- fread("loans.csv", colClasses = list(
character = c("loan_id", "isbn", "member"),
Date = c("loan_date", "return_date")
))
loans[c(1, 20), .(loan_id, isbn, loan_date, return_date)]
#> loan_id isbn loan_date return_date
#> <char> <char> <Date> <IDat>
#> 1: L001 9780141439518 2025-01-14 2025-02-04
#> 2: L020 9780006000624 <NA> 2025-07-15
# select = keeps columns, drop = drops them, nrows reads the first rows.
names(fread("books.csv", select = c("isbn", "year")))
#> [1] "isbn" "year"
names(fread("books.csv", drop = c("author", "genre")))
#> [1] "title" "isbn" "year" "pages"
fread("loans.csv", nrows = 2L, select = c("loan_id", "member"))
#> loan_id member
#> <char> <char>
#> 1: L001 Hugo Silva
#> 2: L002 Ben Okafor
# text = reads a string instead of a file; nThread limits the threads.
fread(text = "a,b\n1,x\n2,y")
#> a b
#> <int> <char>
#> 1: 1 x
#> 2: 2 y
fread("loans.csv", nThread = 1L, nrows = 1L)[, .(loan_id)]
#> loan_id
#> <char>
#> 1: L001Two results in the script need a remark. The ISBNs of the loans are 13-digit numbers, so fread() reads them as numbers (integer64,
from package bit64), and an ISBN with a leading zero would lose it. The colClasses list is the fix, and it is the same discipline as read.csv("loans.csv", colClasses = "character") in the base
scenario. The date columns also differ: return_date is read as an IDate (data.table’s integer-backed subclass of Date), and
loan_date as a plain Date, because that column holds the impossible date 2025-02-30 and cannot be parsed in one pass; with the
bad row removed, both columns are IDate. The compressed files .gz and .bz2 (and .zip archives holding one file) are read by
fread() directly, according to its help page.
fwrite() writes a data.table or data frame, in parallel. It quotes a field only when it needs to (with quote = "auto": when it contains the separator, a quote
or a line ending), writes dates as ISO 8601 and NA as an empty field, and has arguments for sep, quote, na, col.names,
append, compress, nThread and encoding (the last since 1.15.0). The default separator can be changed for a session with
options(datatable.fwrite.sep = ). A round trip is faithful with one trap that follows from the two defaults above: a value with
leading or trailing blanks is written unquoted, and fread() strips unquoted blanks by default, so strip.white = FALSE is needed to
read back what was written.
suppressPackageStartupMessages(library(data.table))
loans <- fread("loans.csv", colClasses = list(
character = c("loan_id", "isbn", "member"),
Date = c("loan_date", "return_date")
))
path <- tempfile(fileext = ".csv")
# fwrite() quotes only where it must, writes dates as ISO 8601 and NA as an
# empty field.
fwrite(loans[18:21], path)
readLines(path)
#> [1] "loan_id,isbn,member,loan_date,return_date"
#> [2] "L018,9780001000100,Dev Patel,2025-07-01,2025-07-26"
#> [3] "L019,9780006000624,Ana Ruiz,2025-07-02,2025-07-25"
#> [4] "L020,9780006000624,Ana Ruiz,,2025-07-15"
#> [5] "L021,9780011001129,Ben Okafor,2025-07-05,2025-07-21"
# na = sets the text for NA, sep = the separator, quote = TRUE quotes strings.
fwrite(loans[18:19], path, na = "NA", sep = ";", quote = TRUE)
readLines(path)
#> [1] "\"loan_id\";\"isbn\";\"member\";\"loan_date\";\"return_date\""
#> [2] "\"L018\";\"9780001000100\";\"Dev Patel\";2025-07-01;2025-07-26"
#> [3] "\"L019\";\"9780006000624\";\"Ana Ruiz\";2025-07-02;2025-07-25"
# A round trip: write the books, read them back, compare. The title
# " the quiet streets " is written unquoted, and fread() strips unquoted
# blanks by default (strip.white = TRUE).
books <- fread("books.csv")
fwrite(books, path)
all.equal(books, fread(path))
#> [1] "Column 'title': 1 string mismatch"
all.equal(books, fread(path, strip.white = FALSE))
#> [1] TRUE
identical(books, fread(path, strip.white = FALSE))
#> [1] TRUE
# append = TRUE adds rows to a file; a .gz name or compress = "gzip" zips it.
fwrite(loans[1:2, .(loan_id, member)], path)
fwrite(loans[3:4, .(loan_id, member)], path, append = TRUE)
fread(path)
#> loan_id member
#> <char> <char>
#> 1: L001 Hugo Silva
#> 2: L002 Ben Okafor
#> 3: L003 Dev Patel
#> 4: L004 Eva Lindqvist
zipped <- tempfile(fileext = ".csv.gz")
fwrite(loans, zipped)
fread(zipped, select = "loan_id")[, .N]
#> [1] 60
unlink(c(path, zipped))
file.exists(c(path, zipped))
#> [1] FALSE FALSEThe base-R side of the same job (read.csv(), write.csv(), readRDS() for R’s own binary format) is in
Reading and Writing Text Data and
Serialization: RDS, RData and JSON.
Reshaping and stacking
dcast() and melt() change the shape of a table, and the formula that dcast() takes is the one described in
Formulas: rows ~ columns. dcast(DT, member ~ quarter, value.var = "N") makes one
row per member and one column per quarter; when several rows fall into one cell, fun.aggregate (a function, or a list of functions)
summarises them, and without it dcast() warns and counts the rows (the warning text is in Common errors and warnings).
fill sets the value of empty cells, and an aggregate over no rows gives NaN for mean(), as the script shows for a member without
loans in a year. Since data.table 1.18.0, dcast() is an error when fun.aggregate returns more than one value. melt() turns columns
into rows: id.vars are kept, measure.vars are stacked, and variable.name and value.name name the two new columns. The helper
measure() (exported since 1.16.0) melts several groups of columns at once, using the pieces of the column names, as in
measure(value.name, year = as.integer, sep = "_"), which splits loans_2025 into the output column loans and the value 2025 of the
column year.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(loans)
loans[, member := tools::toTitleCase(trimws(member))]
loans <- loans[nzchar(member) & !is.na(loan_date)]
loans[, quarter := paste0(year(loan_date), "-Q", quarter(loan_date))]
# Long: one row per member and quarter that has loans.
counts <- loans[, .N, by = .(member, quarter)]
counts[order(member, quarter)][1:4]
#> member quarter N
#> <char> <char> <int>
#> 1: Ana Ruiz 2025-Q1 1
#> 2: Ana Ruiz 2025-Q2 2
#> 3: Ana Ruiz 2025-Q3 2
#> 4: Ana Ruiz 2025-Q4 4
# dcast() spreads a column over new columns; fill gives the empty cells a value.
wide <- dcast(counts, member ~ quarter, value.var = "N", fill = 0L)
wide
#> Key: <member>
#> member 2025-Q1 2025-Q2 2025-Q3 2025-Q4 2026-Q1 2026-Q2
#> <char> <int> <int> <int> <int> <int> <int>
#> 1: Ana Ruiz 1 2 2 4 2 2
#> 2: Ben Okafor 2 1 1 1 0 0
#> 3: Chloe Martin 0 2 2 2 2 1
#> 4: Dev Patel 1 0 1 1 1 2
#> 5: Eva Lindqvist 1 0 3 1 0 1
#> 6: Farid Karimi 0 0 0 1 1 0
#> 7: Grace Liu 1 0 2 1 3 0
#> 8: Hugo Silva 3 3 1 1 1 1
# melt() is the way back; variable.name and value.name name the new columns.
long <- melt(wide, id.vars = "member", variable.name = "quarter",
value.name = "n")
long[n > 0][order(member, quarter)][1:4]
#> member quarter n
#> <char> <fctr> <int>
#> 1: Ana Ruiz 2025-Q1 1
#> 2: Ana Ruiz 2025-Q2 2
#> 3: Ana Ruiz 2025-Q3 2
#> 4: Ana Ruiz 2025-Q4 4
identical(nrow(long), nrow(wide) * (ncol(wide) - 1L))
#> [1] TRUE
# fun.aggregate summarises cells that hold several rows (here: days held).
loans[, held := as.integer(fifelse(is.na(return_date), shelf_today,
return_date) - loan_date)]
loans[, loan_year := year(loan_date)]
dcast(loans, member ~ loan_year, value.var = "held",
fun.aggregate = \(x) round(mean(x), 1))[1:3]
#> Key: <member>
#> member 2025 2026
#> <char> <num> <num>
#> 1: Ana Ruiz 19.9 37.5
#> 2: Ben Okafor 23.2 NaN
#> 3: Chloe Martin 19.2 43.7
# Several value columns give one block of columns for each; measure() melts
# such blocks back into one column per block (value.name) and one for the
# part of the name that varies.
per_year <- loans[, .(loans = .N, days = sum(held)), by = .(member, loan_year)]
two <- dcast(per_year, member ~ loan_year, value.var = c("loans", "days"))
names(two)
#> [1] "member" "loans_2025" "loans_2026" "days_2025" "days_2026"
melt(two, id.vars = "member",
measure.vars = measure(value.name, year = as.integer, sep = "_"))[1:4]
#> member year loans days
#> <char> <int> <int> <int>
#> 1: Ana Ruiz 2025 9 179
#> 2: Ben Okafor 2025 5 116
#> 3: Chloe Martin 2025 6 115
#> 4: Dev Patel 2025 3 50rbindlist() stacks a list of tables in one pass, which its help page recommends for an unknown number of tables, such as the result of
lapply(files, fread). idcol adds a column with the name of each list element, and fill = TRUE pads missing columns
with NA. Its default use.names = "check" is a trap: if the column names are the same set but in another order, rbindlist() sends a
message and then binds by position, as the script shows, with wrong results. use.names = TRUE matches by name, and rbind() on
data.tables has use.names = TRUE by default. Since data.table 1.16.0, rbindlist() also binds columns of different but compatible
classes (Date with IDate, for instance) and has ignore.attr to switch that safety net off. split() has a data.table method with
by = that returns a list of tables, and rbindlist() puts them back.
suppressPackageStartupMessages(library(data.table))
loans <- fread("loans.csv", colClasses = "character")
# Split the loans into one CSV file per year, then read them all back.
dir <- tempfile("loans-")
dir.create(dir)
for (y in c("2025", "2026")) {
fwrite(loans[startsWith(loan_date, y)], file.path(dir, paste0(y, ".csv")))
}
files <- list.files(dir, full.names = TRUE)
names(files) <- basename(files)
# rbindlist() stacks a list of tables in one pass; idcol records the name.
all_loans <- rbindlist(lapply(files, fread, colClasses = "character"),
idcol = "file")
all_loans[, .N, by = file]
#> file N
#> <char> <int>
#> 1: 2025.csv 43
#> 2: 2026.csv 17
# The default use.names = "check" does NOT match by name when the order
# differs: it sends a message and binds by position (here: wrongly).
a <- data.table(id = 1:2, x = c("p", "q"))
b <- data.table(x = "r", id = 3L)
rbindlist(list(a, b))
#> Column 2 ['id'] of item 2 appears in position 1 in item 1. Set use.names=TRUE to match by column name, or use.names=FALSE to ignore column names. use.names='check' (default from v1.12.2) emits this message and proceeds as if use.names=FALSE for backwards compatibility. See news item 5 in v1.12.2 for options to control this message.
#> id x
#> <char> <char>
#> 1: 1 p
#> 2: 2 q
#> 3: r 3
# use.names = TRUE matches by name; rbind() on data.tables does that already.
rbindlist(list(a, b), use.names = TRUE)
#> id x
#> <int> <char>
#> 1: 1 p
#> 2: 2 q
#> 3: 3 r
rbind(a, b)
#> id x
#> <int> <char>
#> 1: 1 p
#> 2: 2 q
#> 3: 3 r
# fill = TRUE pads a missing column with NA instead of failing.
c2 <- data.table(id = 4L, y = "extra")
rbindlist(list(a, c2), fill = TRUE)
#> id x y
#> <int> <char> <char>
#> 1: 1 p <NA>
#> 2: 2 q <NA>
#> 3: 4 <NA> extra
# rbind() on data.tables is rbindlist() underneath; split() has a method
# with by = that returns a list of tables, and rbindlist() puts them back.
parts <- split(all_loans, by = "file")
names(parts)
#> [1] "2025.csv" "2026.csv"
nrow(rbindlist(parts))
#> [1] 60
unlink(dir, recursive = TRUE)
dir.exists(dir)
#> [1] FALSEHelper functions
data.table ships a set of vectorised helpers that are used inside j, but work on any vector. The first script covers the conditional
and membership ones: fifelse() is the help page’s “faster and more robust replacement of ifelse()” (it keeps the attributes of the first non-missing
branch, so a Date stays a Date, which ifelse() does not do, and it requires yes and no to be of length 1 or of the length of
test; see Common errors and warnings), fcase() is the case_when() of data.table (the first condition that is TRUE wins, and
default = catches the rest), %between% and %inrange% test ranges (inrange() against several ranges at once), %chin% is a
faster %in% for character vectors, %like% matches a regular expression (see
Strings and Regular Expressions), and %notin% is the negation of
%in%. The fcase() below splits the 32 overdue loans of the base-R analysis in
Data Frames into 26 late loans and 6 very late ones (held for more than twice
loan_days), and the two loans that cannot be classified, the one returned before it was made and the one with no date, fall to the
default.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(loans)
setDT(books)
loans[, loan_date := as.Date(loan_date)]
loans[, held := as.integer(fifelse(is.na(return_date), shelf_today,
return_date) - loan_date)]
# fifelse() is a strict, fast ifelse(): both branches must have one type.
loans[, status := fifelse(is.na(return_date), "out", "returned")]
loans[, .N, by = status]
#> status N
#> <char> <int>
#> 1: returned 53
#> 2: out 7
# fcase() is case_when() for data.table: the first TRUE condition wins.
loans[, band := fcase(
held > 2L * loan_days, "very late",
held > loan_days, "late",
held >= 0L, "on time",
default = "check"
)]
loans[, .N, by = band][order(-N)]
#> band N
#> <char> <int>
#> 1: on time 26
#> 2: late 26
#> 3: very late 6
#> 4: check 2
# %between%, %inrange% and %chin% are shortcuts for common conditions.
summer <- as.Date(c("2025-07-01", "2025-09-30"))
loans[loan_date %between% summer, .N]
#> [1] 12
loans[member %chin% c("Hugo Silva", "Ben Okafor"), .N]
#> [1] 15
books[, .(title, pages)][pages %inrange% list(200L, 300L)][1:3]
#> title pages
#> <char> <int>
#> 1: the quiet streets 214
#> 2: The Lamp at Noon 280
#> 3: THE LONG TABLE 256
# inrange() tests each value against several ranges and returns one logical.
inrange(c(150L, 250L, 500L), c(100L, 200L), c(160L, 300L))
#> [1] TRUE TRUE FALSE
# %like% matches a regular expression; %notin% is the negation of %in%.
books[title %like% "^The|Map", .N]
#> [1] 4
books[genre %notin% c("Poetry", "Mystery"), .N]
#> [1] 12The second script collects the helpers that look across rows. shift() lags or leads a vector, and with by it restarts for every
group (type = "cyclic" wraps around, since 1.15.0); frank() is described as similar to rank() but much faster, with the extra ties.method = "dense" (it sorts in C-locale); uniqueN() counts the distinct values of a vector or of the rows of a table, also per group; rowid() numbers the rows within
each value and rleid() numbers the runs of equal values; nafill() fills NA forward ("locf"), backward ("nocb") or with a
constant, and fcoalesce() takes the first non-missing value of several vectors; tstrsplit() splits a character column into several
columns at once. year(), month(), quarter() and the other date extractors of data.table (help page IDateTime) work on Date and IDate and are
used in the reshaping script above. nafill() and shift() raise an error for a matrix since 1.15.0.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(loans)
loans <- loans[!is.na(loan_date) & nzchar(trimws(member))]
loans[, member := tools::toTitleCase(trimws(member))]
# shift() lags (or leads) a column; by = restarts it for each member.
setorder(loans, member, loan_date)
loans[, gap := as.integer(loan_date - shift(loan_date)), by = member]
loans[member == "Ana Ruiz", .(loan_id, loan_date, gap)][1:4]
#> loan_id loan_date gap
#> <char> <Date> <int>
#> 1: L008 2025-03-27 NA
#> 2: L014 2025-05-07 41
#> 3: L015 2025-06-04 28
#> 4: L019 2025-07-02 28
# frank() ranks like rank() but faster; ties.method = "min" shares a rank.
loans[, held := as.integer(fifelse(is.na(return_date), shelf_today,
return_date) - loan_date)]
loans[, rank := frank(-held, ties.method = "min")]
loans[rank <= 3, .(loan_id, member, held, rank)][order(rank)]
#> loan_id member held rank
#> <char> <char> <int> <int>
#> 1: L054 Chloe Martin 84 1
#> 2: L055 Eva Lindqvist 81 2
#> 3: L056 Dev Patel 71 3
# uniqueN() counts distinct values, also per group.
uniqueN(loans$member)
#> [1] 8
loans[, .(members = uniqueN(member), loans = .N), by = year(loan_date)]
#> year members loans
#> <int> <int> <int>
#> 1: 2025 8 41
#> 2: 2026 7 17
# rowid() numbers the rows within each value, rleid() the runs of values.
loans[, nth := rowid(member)]
loans[nth == 1L, .N]
#> [1] 8
rleid(c("a", "a", "b", "a"))
#> [1] 1 1 2 3
# nafill() fills NA in a numeric column; fcoalesce() takes the first non-NA.
pages <- c(214L, NA, 301L, NA, NA, 96L)
nafill(pages, type = "locf")
#> [1] 214 214 301 301 301 96
nafill(pages, fill = 0L)
#> [1] 214 0 301 0 0 96
fcoalesce(pages, 0L)
#> [1] 214 0 301 0 0 96
# tstrsplit() splits a character column into several columns at once.
loans[, c("first_name", "last_name") := tstrsplit(member, " ", fixed = TRUE)]
unique(loans[, .(member, first_name, last_name)])[1:3]
#> member first_name last_name
#> <char> <char> <char>
#> 1: Ana Ruiz Ana Ruiz
#> 2: Ben Okafor Ben Okafor
#> 3: Chloe Martin Chloe MartinProgramming with data.table
A query that names its columns in the code cannot be reused for another column, and building the text of a query for
eval(parse(text = ...)) is fragile. data.table offers four routes, from the vignette
data.table package documentation: Programming on data.table.
-
..varinj(or.SDcols = var) looks up a character vector of column names in the calling scope. It selects columns; it does not substitute names into an expression. -
get(name)andmget(names)insidejevaluate a name or names as columns. They work, but the column name is then hidden inside a string, where tools and readers cannot see it. -
env = list(a = value, ...)is the safe route: the names in the list are substituted intoi,jandbyBEFORE the query is evaluated, assubstitute2()does, so a function can take column names as arguments and build the query from them. By default a character element of length 1 becomes a name (a longer one is an error), andI()keeps an element as it is, which is how a character vector is inserted as a vector. -
substitute2(expr, env)shows the expressionenv =builds, which is how a generated query is checked. Its help page calls it an experimental, more robust and friendliersubstitute().
The next script uses them in order. The function mean_by() carries the nolint comments mentioned in the introduction, because
lintr cannot see that value and group are substituted.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
books[, genre := tolower(trimws(genre))]
# 1. A column name held in a variable: ..col looks it up one level up.
col <- "pages"
books[1:3, ..col]
#> pages
#> <int>
#> 1: 387
#> 2: 432
#> 3: 214
# get() evaluates a name inside j; it works but hides the column from checks.
books[, mean(get(col), na.rm = TRUE)]
#> [1] 268.2143
# 2. env = substitutes names, calls and values into the query BEFORE it is
# evaluated, so the expression stays readable and safe.
mean_by <- function(tbl, value, group) {
# nolint start: object_usage_linter.
tbl[, .(avg = mean(value, na.rm = TRUE), n = .N), by = group,
env = list(value = value, group = group)]
# nolint end
}
mean_by(books, "pages", "genre")[1:2]
#> genre avg n
#> <char> <num> <int>
#> 1: science fiction 373 3
#> 2: classic 356 2
# substitute2() shows what env = builds.
substitute2(mean(value, na.rm = TRUE), list(value = as.name("pages")))
#> mean(pages, na.rm = TRUE)
substitute2(f(x), list(f = "sum", x = "pages"))
#> sum(pages)
# 3. Many columns at once: .SDcols takes a character vector, and env with
# I() inserts a character vector as a vector (not as a name).
num_cols <- c("year", "pages")
books[, lapply(.SD, max, na.rm = TRUE), .SDcols = num_cols]
#> year pages
#> <int> <int>
#> 1: 2023 432
books[, cols, env = list(cols = I(num_cols))][1:2]
#> year pages
#> <int> <int>
#> 1: 1974 387
#> 2: 1813 432
# 4. Assigning to a column whose name is in a variable: (var) := value.
new_col <- "pages_k"
books[, (new_col) := pages / 1000]
books[1:2, .(title, pages_k)]
#> title pages_k
#> <char> <num>
#> 1: The Dispossessed 0.387
#> 2: Pride and Prejudice 0.432
books[, c(new_col) := NULL]
names(books)
#> [1] "title" "author" "isbn" "year" "genre" "pages"The last lines show (var) :=, the form for assigning to a column whose name is in a variable. The env argument was added in
data.table 1.15.0; older answers do the same job with bquote(), get() or eval(parse(text = )). See Evaluation and Non-Standard Evaluation for
the general mechanism that substitute2() follows.
Printing and threads
print.data.table() has arguments for the number of rows shown (nrows, default 100, and topn, default 5: a longer table shows
the first and last topn rows with a --- line between), class (the line of column classes), row.names, col.names ("auto",
"top" or "none"), print.keys (the key and indices above the data), trunc.cols (only the columns that fit the width) and
quote. Every default is read from an option, datatable.print.nrows, datatable.print.topn, datatable.print.class,
datatable.print.keys, datatable.print.rownames, datatable.print.colnames and datatable.print.trunc.cols, so a session or a
profile can change it for every table (options are set in a script or in a startup file; see
Sessions, Workspaces and Startup). Character cells longer than datatable.prettyprint.char characters are truncated with
.... When a table of more than 20 rows is printed in full, since 1.18.0 the column classes are repeated at the bottom as the names already were (a truncated table, as in the first script below, shows its --- line instead).
Threads are controlled separately. fread(), fwrite(), forder() (the sort behind setkey() and
order() in i), grouping with GForce, fifelse(), nafill() and a few other functions use OpenMP threads. The number of threads is
decided when the package is loaded: 50% of the logical CPUs by default (R_DATATABLE_NUM_PROCS_PERCENT changes the percentage and
R_DATATABLE_NUM_THREADS fixes the number, both read at load time), and never more than OMP_THREAD_LIMIT allows. getDTthreads()
reports it, and setDTthreads(n) changes it for the session (setDTthreads(0) is all logical CPUs) and returns the previous setting. The number depends on the machine, so the script prints only a comparison with 1 and a restored value. A throttle
(default 1024 rows) keeps small tasks on one thread, and data.table switches to a single thread inside a forked process (the mechanism
of parallel::mclapply()), so it does not oversubscribe the CPUs of code that is already parallel. CRAN’s own checks set
OMP_THREAD_LIMIT to 2, and a package must not raise it above 2. See
Parallel and Asynchronous Computing for the other parallel tools.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(loans)
# The defaults of the printing options in this version.
str(options("datatable.print.nrows", "datatable.print.topn",
"datatable.print.class", "datatable.print.keys"))
#> List of 4
#> $ datatable.print.nrows: int 100
#> $ datatable.print.topn : int 5
#> $ datatable.print.class: logi TRUE
#> $ datatable.print.keys : logi TRUE
# A table longer than nrows (100) prints its first and last topn (5) rows.
data.table(n = 1:150, square = (1:150)^2)
#> n square
#> <int> <num>
#> 1: 1 1
#> 2: 2 4
#> 3: 3 9
#> 4: 4 16
#> 5: 5 25
#> ---
#> 146: 146 21316
#> 147: 147 21609
#> 148: 148 21904
#> 149: 149 22201
#> 150: 150 22500
# nrows and topn can be given to print() for a single call.
print(loans[, .(loan_id, member)], nrows = 8, topn = 2)
#> loan_id member
#> <char> <char>
#> 1: L001 Hugo Silva
#> 2: L002 Ben Okafor
#> ---
#> 59: L059 Ana Ruiz
#> 60: L060 Hugo Silva
# Other arguments: no row numbers, no class line, no column names.
print(loans[1:3, .(loan_id, member)], row.names = FALSE)
#> loan_id member
#> <char> <char>
#> L001 Hugo Silva
#> L002 Ben Okafor
#> L003 Dev Patel
print(loans[1:3, .(loan_id, member)], class = FALSE)
#> loan_id member
#> 1: L001 Hugo Silva
#> 2: L002 Ben Okafor
#> 3: L003 Dev Patel
print(loans[1:3, .(loan_id, member)], class = FALSE, col.names = "none")
#> 1: L001 Hugo Silva
#> 2: L002 Ben Okafor
#> 3: L003 Dev Patel
# The same settings as options apply to every table of the session.
options(datatable.print.nrows = 5, datatable.print.topn = 1,
datatable.print.class = FALSE)
loans[, .(loan_id, member)]
#> loan_id member
#> 1: L001 Hugo Silva
#> ---
#> 60: L060 Hugo Silva
options(datatable.print.nrows = 100, datatable.print.topn = 5,
datatable.print.class = TRUE)
# Threads: data.table uses OpenMP threads for fread(), fwrite(), sorting and
# grouping. The number depends on the machine, so only a check is shown.
getDTthreads() >= 1L
#> [1] TRUE
old <- setDTthreads(1L)
getDTthreads()
#> [1] 1
invisible(setDTthreads(old))
getDTthreads() == old
#> [1] TRUEThe shelf analyses with data.table
The analyses of Data Frames are rewritten here, and the numbers are the same: nine spellings of six genres, 32 overdue loans, 27 that are not and one that cannot be told, and an overdue rate by genre that runs from 0.67 for poetry to 0.47 for science fiction. Each script repeats its own setup and can be run alone.
Cleaning the books and the loans
One := call cleans four columns of books: it trims the blanks of title, author and genre, collapses the repeated blanks of
the author (gsub("\\s+", " ", ...)), lower-cases the genre, and keeps only the digits and X of the ISBN. The two spellings of Ursula
K. Le Guin remain, as in the base-R page: no rule fixes them. For the loans, tools::toTitleCase() repairs the member names and the
empty name becomes NA; the dates are converted from the character columns of the scenario, and the rows that need a decision are
listed.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
setDT(loans)
# books: nine spellings of six genres, padded titles, two spellings of Le Guin.
books[, .N, by = genre][order(genre)]
#> genre N
#> <char> <int>
#> 1: Classic 2
#> 2: Fiction 2
#> 3: Fiction 1
#> 4: Mystery 2
#> 5: Non-fiction 3
#> 6: Poetry 2
#> 7: Science fiction 2
#> 8: fiction 1
#> 9: science fiction 1
# Clean in place with one := call; the isbn keeps only digits and X.
books[, `:=`(
title = trimws(title),
author = gsub("\\s+", " ", trimws(author)),
genre = tolower(trimws(genre)),
isbn = gsub("[^0-9X]", "", toupper(isbn))
)]
books[, .N, by = genre][order(genre)]
#> genre N
#> <char> <int>
#> 1: classic 2
#> 2: fiction 4
#> 3: mystery 2
#> 4: non-fiction 3
#> 5: poetry 2
#> 6: science fiction 3
books[c(3, 10), .(title, author)]
#> title author
#> <char> <char>
#> 1: the quiet streets Mara Quill
#> 2: THE LONG TABLE Iris Fenwick
# loans: member names differ in case and padding, and one is empty.
loans[, member := tools::toTitleCase(trimws(member))]
loans[!nzchar(member), member := NA]
sort(unique(loans$member))
#> [1] "Ana Ruiz" "Ben Okafor" "Chloe Martin" "Dev Patel"
#> [5] "Eva Lindqvist" "Farid Karimi" "Grace Liu" "Hugo Silva"
# Rows that need a look: a loan returned before it was made, an impossible
# date (2025-02-30 is NA after the conversion).
loans[, `:=`(
loan_date = as.Date(loan_date),
return_date = as.Date(return_date)
)]
loans[return_date < loan_date | is.na(loan_date),
.(loan_id, loan_date, return_date)]
#> loan_id loan_date return_date
#> <char> <Date> <Date>
#> 1: L012 2025-04-23 2025-04-20
#> 2: L020 <NA> 2025-07-15Overdue loans
A loan is overdue when it was held for more than loan_days (21) days: until the return date when there is one, and until
shelf_today (2026-06-30) when the book is still out. fifelse() picks the end date and keeps its class, so the subtraction is
between dates. The counts agree with the base-R page (27, 32 and 1), and the remaining overdue loans are the books to chase.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(loans)
loans[, `:=`(
loan_date = as.Date(loan_date),
return_date = as.Date(return_date)
)]
# Days held: until the return date, or until shelf_today when still out.
loans[, held := as.integer(fifelse(is.na(return_date), shelf_today,
return_date) - loan_date)]
loans[, overdue := held > loan_days]
loans[1:4, .(loan_id, held, overdue)]
#> loan_id held overdue
#> <char> <int> <lgcl>
#> 1: L001 21 FALSE
#> 2: L002 10 FALSE
#> 3: L003 1 FALSE
#> 4: L004 26 TRUE
loans[, .N, by = overdue][order(overdue)]
#> overdue N
#> <lgcl> <int>
#> 1: FALSE 27
#> 2: TRUE 32
#> 3: NA 1
# Overdue loans that are still out: the books to chase.
still_out <- loans[is.na(return_date) & overdue,
.(loan_id, member, loan_date, held)]
still_out[order(-held)][1:4]
#> loan_id member loan_date held
#> <char> <char> <Date> <int>
#> 1: L054 Chloe Martin 2026-04-07 84
#> 2: L055 Eva Lindqvist 2026-04-10 81
#> 3: L056 Dev Patel 2026-04-20 71
#> 4: L057 Dev Patel 2026-04-26 65
# Overdue returned loans per member, most first (the loan without a member
# is left out).
per_member <- loans[!is.na(member) & overdue, .(overdue = .N), by = member]
per_member[order(-overdue, member)][1:3]
#> member overdue
#> <char> <int>
#> 1: Chloe Martin 6
#> 2: Ana Ruiz 5
#> 3: Hugo Silva 5Loans per month and the overdue rate by genre
keyby sorts the months and sets the key; the busiest months are July and October 2025 with seven loans each, as in the chart of
Base Graphics and Devices. The overdue rate joins the loans to the books
with books[loans, on = "isbn", nomatch = NULL], which keeps the 59 loans that match a book; dropping the one whose overdue flag is NA leaves the 58 loans of the base-R page, and the
table is the same as its merge() and aggregate() result, from poetry (4 of 6) to science fiction (7 of 15).
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
setDT(loans)
books[, `:=`(
title = trimws(title),
genre = tolower(trimws(genre)),
isbn = gsub("[^0-9X]", "", toupper(isbn))
)]
loans[, `:=`(
loan_date = as.Date(loan_date),
return_date = as.Date(return_date)
)]
loans[, held := as.integer(fifelse(is.na(return_date), shelf_today,
return_date) - loan_date)]
loans[, overdue := held > loan_days]
# Loans per month; keyby sorts the months and the NA date is left out.
per_month <- loans[!is.na(loan_date), .N,
keyby = .(month = format(loan_date, "%Y-%m"))]
per_month[N == max(N)]
#> Key: <month>
#> month N
#> <char> <int>
#> 1: 2025-07 7
#> 2: 2025-10 7
per_month[1:3]
#> Key: <month>
#> month N
#> <char> <int>
#> 1: 2025-01 4
#> 2: 2025-02 2
#> 3: 2025-03 3
# Join the loans to the books, then the overdue rate by genre.
joined <- books[loans, on = "isbn", nomatch = NULL]
by_genre <- joined[!is.na(overdue), .(loans = .N, overdue = sum(overdue)),
by = genre]
by_genre[, share := round(overdue / loans, 2)]
by_genre[order(-share, genre)]
#> genre loans overdue share
#> <char> <int> <int> <num>
#> 1: poetry 6 4 0.67
#> 2: classic 5 3 0.60
#> 3: non-fiction 5 3 0.60
#> 4: fiction 23 12 0.52
#> 5: mystery 4 2 0.50
#> 6: science fiction 15 7 0.47
# The three titles loaned most often.
joined[, .N, by = title][order(-N, title)][1:3]
#> title N
#> <char> <int>
#> 1: the quiet streets 8
#> 2: Ten Winters 7
#> 3: The Dispossessed 7data.table or dplyr?
The two packages answer the same question, how to write a table transformation as a short, readable pipeline, and both are valid choices. They differ in the vocabulary, in the treatment of the table, and in the cost of a wrong guess. The table lines up the same operations in base R (the functions of Data Frames), dplyr (the verbs of The tidyverse and dplyr, following the official dplyr vignette on base R equivalents) and data.table.
| Task | base R | dplyr | data.table |
|---|---|---|---|
read a CSV |
|
|
|
filter rows |
|
|
|
choose columns |
|
|
|
sort |
|
|
|
add a column |
|
|
|
change some rows |
|
|
|
summarise |
|
|
|
count by group |
|
|
|
summarise by group |
|
|
|
inner join |
|
|
|
left join |
|
|
|
rolling or non-equi join |
no direct form |
|
|
wide to long |
|
|
|
long to wide |
|
|
|
stack tables |
|
|
|
cases |
|
|
|
previous value in a group |
|
|
|
distinct count |
|
|
|
modifies in place |
no |
no |
yes: |
The script below does not rest on the table: it runs the base-R, dplyr and data.table versions of five of its rows (filter, choose
and sort, summarise by group, count by group, inner join) and compares the results by value.
dplyr is always called as dplyr::, because attaching it after data.table prints a conflict message for between(), first() and
last() (and one for filter() and lag() of the stats package), and the later package hides the earlier one’s functions.
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
# books_df stays a data frame; books_dt is a data.table copy of it.
books$genre <- tolower(trimws(books$genre))
books$isbn <- normalise_isbn(books$isbn)
loans$loan_date <- as.Date(loans$loan_date)
books_df <- books
books_dt <- as.data.table(books)
loans_dt <- as.data.table(loans)
# same() compares two results by value, whatever their class.
same <- \(a, b) {
isTRUE(all.equal(as.data.frame(a), as.data.frame(b),
check.attributes = FALSE))
}
# Filter rows.
r_base <- subset(books_df, year > 2020)
r_dplyr <- dplyr::filter(books_df, year > 2020)
r_dt <- books_dt[year > 2020]
c(same(r_base, r_dplyr), same(r_base, r_dt))
#> [1] TRUE TRUE
# Select columns, then sort (NA last in all three).
r_base <- books_df[order(-books_df$pages), c("title", "pages")]
r_dplyr <- dplyr::arrange(dplyr::select(books_df, title, pages),
dplyr::desc(pages))
r_dt <- books_dt[order(-pages), .(title, pages)]
c(same(r_base, r_dplyr), same(r_base, r_dt))
#> [1] TRUE TRUE
# Count and summarise by group.
r_base <- aggregate(pages ~ genre, books_df, \(x) round(mean(x), 1))
r_dplyr <- books_df |>
dplyr::summarise(pages = round(mean(pages, na.rm = TRUE), 1), .by = genre) |>
dplyr::arrange(genre)
r_dt <- books_dt[, .(pages = round(mean(pages, na.rm = TRUE), 1)),
keyby = genre]
c(same(r_base, r_dplyr), same(r_base, r_dt))
#> [1] TRUE TRUE
n_base <- as.data.frame(table(genre = books_df$genre),
responseName = "n", stringsAsFactors = FALSE)
n_dplyr <- dplyr::count(books_df, genre)
n_dt <- books_dt[, .(n = .N), keyby = genre]
c(same(n_base, n_dplyr), same(n_base, n_dt))
#> [1] TRUE TRUE
# Join: the loans that match a book, with the title.
j_base <- merge(loans, books_df[c("isbn", "title")], by = "isbn")
j_dplyr <- dplyr::inner_join(loans, books_df[c("isbn", "title")], by = "isbn")
j_dt <- books_dt[, .(isbn, title)][loans_dt, on = "isbn", nomatch = NULL]
sort_ids <- \(d) as.data.frame(d)[order(d$loan_id), c("loan_id", "title")]
c(same(sort_ids(j_base), sort_ids(j_dplyr)),
same(sort_ids(j_base), sort_ids(j_dt)))
#> [1] TRUE TRUE
nrow(j_base)
#> [1] 59Speed depends on the operation, the size of the data and the machine, so this page prints no timings. The data.table website links to
the db-benchmark project (db-benchmark, whose page lists the timings with the syntax timed and
the machine type, from 16 cores and 32 GB to 128 cores and 256 GB), and
Performance, Profiling and Benchmarking shows how to measure a
real case with bench. The differences that do not depend on timings are these:
-
Syntax. dplyr is a set of verbs, each doing one thing, joined with the pipe; data.table is one query form with three slots, in which the work of several verbs fits into one call. The SQL reading of
DT[i, j, by]is a way to learn it. -
Copying. dplyr verbs return a new table and leave the input alone, so a pipeline can be reasoned about step by step;
:=and theset*functions change the table in place, which saves a copy of a large table and makes it easy to change something the caller did not expect (see Pitfalls). -
Joins. dplyr has
join_by()for equality, inequality, rolling and overlap joins since version 1.1.0 (dplyr package documentation: join_by), so the two cover the same ground; data.table’son =is terser, andby = .EACHIevaluatesjfor each row ofi, which the error message for an accidental many-to-many join suggests as a way "to avoid the large allocation". -
Files.
fread(data.table = FALSE)returns a plain data frame andfwrite()writes data frames, so the two functions can be used with either package. -
Dependencies. data.table depends on R 3.4.0 and imports only
methods, which ships with R (packageDescription("data.table")in the image used here). dplyr 1.2.1 depends on R 4.1.0 and imports thirteen packages (cli, generics, glue, lifecycle, magrittr, methods, pillar, R6, rlang, tibble, tidyselect, utils and vctrs). -
Mixing. A data.table is a data frame, so the dplyr verbs accept it, but code that mixes the two vocabularies is read by people who must know both, and a
:=among pipes can surprise a reader who expects a copy. Teams usually choose one for the main pipeline. -
dtplyr. The package dtplyr (dtplyr package documentation) is a data.table back end for dplyr: its README states the goal as translating dplyr code into the equivalent data.table code. It is installed in the image used for the examples and is not run on this page.
Common errors and warnings
The messages below are what data.table 1.18.6.1 printed. A helper function runs an expression and prints its first condition as
conditionMessage() with error: or warning: in front, wrapped to 76 columns. Many of the messages say what to do next.
suppressPackageStartupMessages(library(data.table))
# what() runs an expression and prints the first condition it signals,
# wrapped to 76 columns (the text is otherwise exactly conditionMessage()).
what <- function(expr) {
msg <- "no condition"
withCallingHandlers(
tryCatch(expr, error = \(e) msg <<- paste("error:", conditionMessage(e))),
warning = \(w) {
if (msg == "no condition") {
msg <<- paste("warning:", conditionMessage(w))
}
invokeRestart("muffleWarning")
}
)
cat(strwrap(msg, width = 76, exdent = 2), sep = "\n")
}
new_dt <- \() data.table(a = 1:3, b = c("x", "y", "z"))
# A column that does not exist, in j, in i, and as a selection.
what(new_dt()[, zzz])
#> error: j (the 2nd argument inside [...]) is a single symbol but column name
#> 'zzz' is not found. If you intended to select columns using a variable in
#> calling scope, please try DT[, ..zzz]. The .. prefix conveys one-level-up
#> similar to a file system path.
what(new_dt()[, sum(zzz)])
#> error: object 'zzz' not found
what(new_dt()[zzz > 1])
#> error: Object 'zzz' not found amongst [a, b]
what(new_dt()[, c("a", "zzz")])
#> error: column not found: [zzz]
what(new_dt()[, ..zzz])
#> error: Variable 'zzz' is not found in calling scope. Looking in calling
#> scope because you used the .. prefix.
# Assigning the wrong number of values, or a value of the wrong type.
what(new_dt()[, a := c(1, 2)])
#> error: Supplied 2 items to be assigned to 3 items of column 'a'. If you
#> wish to 'recycle' the RHS please use rep() to make this intent clear to
#> readers of your code.
what(new_dt()[1:2, a := "x"])
#> warning: Coercing 'character' RHS to 'integer' to match the type of column
#> 1 named 'a'.
what(new_dt()[, `:=`(p = 1, 2)])
#> error: In `:=`(col1=val1, col2=val2, ...) form, all arguments must be
#> named, but the last argument has no name. Did you forget a trailing
#> comma?
what(new_dt()[, zzz := NULL])
#> warning: Tried to assign NULL to column 'zzz', but this column does not
#> exist to remove
# j that returns different types in different groups.
what(new_dt()[, if (.GRP == 1L) 1 else "a", by = b])
#> error: Column 1 of result for group 2 is type 'character' but expecting
#> type 'double'. Column types must be consistent for each group.
# A logical i of the wrong length (no recycling).
what(new_dt()[c(TRUE, FALSE)])
#> error: i evaluates to a logical vector length 2 but there are 3 rows.
#> Recycling of logical i is no longer allowed as it hides more bugs than is
#> worth the rare convenience. Explicitly use rep(...,length=.N) if you
#> really need to recycle.
# Names and positions.
what(setnames(new_dt(), "zzz", "q"))
#> error: Items of 'old' not found in column names: [zzz]. Consider
#> skip_absent=TRUE.
what(setnames(new_dt(), c("p", "q", "r")))
#> error: Can't assign 3 names to a 2-column data.table
what(new_dt()[, 5])
#> error: Item 1 of j is 5 which is outside the column number range [1,ncol=2]
what(setkey(new_dt(), zzz))
#> error: some columns are not in the data.table: [zzz]suppressPackageStartupMessages(library(data.table))
what <- function(expr) {
msg <- "no condition"
withCallingHandlers(
tryCatch(expr, error = \(e) msg <<- paste("error:", conditionMessage(e))),
warning = \(w) {
if (msg == "no condition") {
msg <<- paste("warning:", conditionMessage(w))
}
invokeRestart("muffleWarning")
}
)
cat(strwrap(msg, width = 76, exdent = 2), sep = "\n")
}
x <- data.table(a = 1:3, b = c("x", "y", "z"))
# Joins: wrong column, wrong type, accidental many-to-many.
what(x[x, on = "zzz"])
#> error: argument specifying columns received non-existing column(s):
#> cols[1]='zzz'
what(x[.("x"), on = "a"])
#> error: Incompatible join types: x.a (integer) and i.V1 (character)
what(merge(x, x, by = "zzz"))
#> error: The following columns listed in `by` are missing from x: [zzz]
dup <- data.table(k = c(1, 1, 1), v = 1:3)
what(dup[dup, on = "k"])
#> error: Join results in 9 rows; more than 6 = nrow(x)+nrow(i). Check for
#> duplicate key values in i each of which join to the same group in x over
#> and over again. If that's ok, try by=.EACHI to run j for each group to
#> avoid the large allocation. If you are sure you wish to proceed, rerun
#> with allow.cartesian=TRUE. Otherwise, please search for this error
#> message in the FAQ, Wiki, Stack Overflow and data.table issue tracker for
#> advice.
dup[dup, on = "k", allow.cartesian = TRUE][, .N]
#> [1] 9
# Reshaping.
what(melt(x, id.vars = "zzz"))
#> error: One or more values in 'id.vars' is invalid.
what(dcast(data.table(g = c(1, 1), k = c("u", "u"), v = 1:2), g ~ k,
value.var = "v"))
#> warning: 'fun.aggregate' is NULL, but found duplicate row/column
#> combinations, so defaulting to length(). That is, the variables [g, k]
#> used in 'formula' do not uniquely identify rows in the input 'data'. In
#> such cases, 'fun.aggregate' is used to derive a single representative
#> value for each combination in the output data.table, for example by
#> summing or averaging (fun.aggregate=sum or fun.aggregate=mean,
#> respectively). Check the resulting table for values larger than 1 to see
#> which combinations were not unique. See ?dcast.data.table for more
#> details.
# fifelse() is strict about lengths.
what(fifelse(c(TRUE, FALSE), 1:3, 0L))
#> error: Length of 'yes' is 3 but must be 1 or length of 'test' (2).
# Reading text: a ragged file and a header that is too short.
what(fread(text = "a,b\n1,2\n3,4,5\n6,7"))
#> warning: Stopped early on line 3. Expected 2 fields but found 3. Consider
#> fill=TRUE. First discarded non-empty line: <<3,4,5>>
what(fread(text = "a,b\n1,2,3\n4,5,6"))
#> warning: Detected 2 column names but the data has 3 columns (i.e. invalid
#> file). Added an extra default column name for the first column which is
#> guessed to be row names or an index. Use setnames() afterwards if this
#> guess is not correct, or fix the file write command that created the file
#> to create a valid file.
# A data.table made by R itself, not by data.table.
df <- data.frame(x = 1:2)
class(df) <- c("data.table", "data.frame")
what(df[, y := 2L])
#> warning: A shallow copy of this data.table was taken so that := can add or
#> remove 1 columns by reference. At an earlier point, this data.table was
#> copied by R (or was created manually using structure() or similar). Avoid
#> names<- and attr<- which in R currently (and oddly) may copy the whole
#> data.table. Use set* syntax instead to avoid copying: ?set, ?setnames and
#> ?setattr. It's also not unusual for data.table-agnostic packages to
#> produce tables affected by this issue. If this message doesn't help,
#> please report your use case to the data.table issue tracker so the root
#> cause can be fixed or this message improved.| Message (start) | Raised by | Cause and fix |
|---|---|---|
|
|
A column that is not in the table. If a variable holding names was meant, write |
|
|
A column that is not in the table, used in |
|
|
A character vector in |
|
|
The |
|
|
The value is not of length 1 or the number of rows. Use |
|
|
A sub-assignment of the wrong type. Replace the whole column to change its type. |
|
the functional form of |
An unnamed argument, often a trailing comma. |
|
|
Deleting a column that is not there. |
|
|
|
|
|
A logical |
|
|
An old name that is not there; |
|
|
The new names do not match the number of columns. |
|
|
A column position past the end. |
|
|
A key column that is not in the table. |
|
|
A join column that is missing from one table. |
|
|
The join columns have different types. Convert one of them. |
|
|
The |
|
|
The key is not unique in both tables. Check for duplicates, use |
|
|
A name in |
|
|
Several rows per cell: give |
|
|
|
|
|
A ragged file: the rows from the problem on are not read. Use |
|
|
The header has fewer names than the data has columns; the first column is taken to be row names. |
|
|
The table was copied by R or built by hand. Use |
|
|
Names in a different order are bound by position. Use |
A missing file is reported by fread() as an error that starts File 'no-such-file.csv' does not exist or is non-readable and ends
with getwd()== and the working directory, which depends on the machine and so is not run here.
Pitfalls
-
DT[, 2]is a data.table, not a vector. A data frame drops a single column to a vector; data.table never does. UseDT[[2]],DT[, col]orDT$colfor the vector. -
b <- ais not a copy. A:=orset*call onbchangesatoo, and so does the same call inside a function that was givena. Usecopy()when the original must stay as it is. This is the opposite of the copy-on-modify rule that the rest of R follows (Memory and Copy Semantics). -
A column wins over a variable. In
i,jandbya name is looked up among the columns first, so afteryear <- 1900the queryDT[year > 2022]still compares the column. Use..var,env =or a differently named variable. -
x[y]is a join, and the order matters.x[i, on = ]starts from the rows ofi.books[loans, on = "isbn"]has 60 rows, one for each loan, andloans[books, on = "isbn"]starts from the books: a row for each loan of each book, and one row for a book that was never loaned. -
Check how a join matched. Keys that are not unique give a many-to-many join (an error unless
allow.cartesian = TRUE), types that differ give an error, and values that look alike but are not equal (trailing blanks, an ISBN with one digit missing) giveNA. -
rbindlist()binds by position unless told otherwise.use.names = "check"(the default) reports a different order with a message and binds by position. Passuse.names = TRUE. -
fread()guesses. A column of 13-digit codes isinteger64; an empty quoted string stays""; unquoted blanks are stripped; a date column with one impossible date is a different class from one without. Fix the types withcolClassesand check them withvapply(DT, \(x) class(x)[[1L]], character(1)). -
Sorting is in C-locale.
setkey(),setorder()andorder()inisort capitals before lower case in every locale. Sort by the locale of the session withDT[base::order(...)]when that is what is wanted. -
A function that ends with
:=may print nothing. Add a trailing[](see Printing after the := operator). -
Base replacement functions copy.
attr<-,class<-on a data frame andstructure()produce tables that:=has to repair with a warning (the self-reference warning). Usesetattr(),setDT()andsetnames(). -
Loops need
set(). Aforloop ofDT[...]calls pays the cost of the bracket on every iteration;set()does not. -
Packages that use data.table. In a package, import the symbols (
import(data.table)orimportFrom(data.table, :=, .N)), and declare the columns used inDT[...]to avoid theR CMD checkNOTE about "no visible binding for global variable" (assign themNULLor use character names), as the importing vignette describes; see Packages, Libraries and Namespaces and Creating Packages.
Version notes
The table lists the changes that matter for the code on this page, from the NEWS file of the installed data.table 1.18.6.1 (also at https://rdatatable.gitlab.io/data.table/news/index.html). The first rows are the R versions that matter to the page.
| Release | Change |
|---|---|
R 4.0.0 |
|
R 4.4.0 |
base R gets |
R 4.6.0 |
base R gets |
data.table 1.15.0 (Jan 2024) |
the |
data.table 1.16.0 (Aug 2024) |
|
data.table 1.17.0 (Feb 2025) |
|
data.table 1.18.0 (Dec 2025) |
|
data.table 1.18.2.1 (Jan 2026) |
|
data.table 1.18.6.1 (Aug 2026) |
rows can be deleted by reference with |
The 1.18 additions that this page ran are in the next script. The row deletion works only with i (not by), clears the keys and
the indices of the table, and is the newest item in the table; the mergelist() call joins a list of tables from left to right with
how set to "inner" or "left" (the other modes are "right", "full", "semi", "anti" and "cross").
suppressPackageStartupMessages(library(data.table))
source("scenario.R")
setDT(books)
# data.table 1.18.6.1: rows can be deleted by reference with .ROW := NULL.
# Only i selects the rows (no by), and keys and indices are dropped.
n_before <- nrow(books)
books[is.na(year) | is.na(pages), .ROW := NULL]
c(before = n_before, after = nrow(books))
#> before after
#> 16 13
books[, .(title, year, pages)][1:3]
#> title year pages
#> <char> <int> <int>
#> 1: The Dispossessed 1974 387
#> 2: Pride and Prejudice 1813 432
#> 3: the quiet streets 2019 214
# data.table 1.18.0: a sort_by() method for data.tables (see base R 4.4.0).
sort_by(books[, .(title, year)], ~year)[1:3]
#> title year
#> <char> <int>
#> 1: Pride and Prejudice 1813
#> 2: The Dispossessed 1974
#> 3: The Lamp at Noon 1999
# data.table 1.18.0: mergelist() joins a list of tables, left to right.
parts <- list(
data.table(id = 1:3, x = c("a", "b", "c")),
data.table(id = c(1L, 2L, 4L), y = c("d", "e", "f")),
data.table(id = c(1L, 3L, 4L), z = c("g", "h", "i"))
)
mergelist(parts, on = "id", how = "inner")
#> id x y z
#> <int> <char> <char> <char>
#> 1: 1 a d g
mergelist(parts, on = "id", how = "left")
#> id x y z
#> <int> <char> <char> <char>
#> 1: 1 a d g
#> 2: 2 b e <NA>
#> 3: 3 c <NA> hWhere older tutorials differ
Much data.table material on the web predates the 1.15 to 1.18 releases. Most of it still runs; the differences below are the ones a reader will meet.
| Older tutorial | Current practice | Why | Version |
|---|---|---|---|
|
leave the argument out |
its default is |
R 4.0.0 |
a join needs |
|
the help page recommends |
inequalities in |
|
|
|
1.15.0 |
append |
|
the detection of auto-printing was improved in 1.17.0 for the explicit |
1.17.0 |
some older posts attribute the "Invalid .internal.selfref detected" warning to |
|
the message of 1.18.6.1 starts "A shallow copy of this data.table was taken" |
1.18.6.1 |
|
|
both pad the missing columns with |
current |
|
|
|
1.15.0 |
|
|
data.table has its own, which take several value columns ( |
current |
|
|
the arguments were removed in 1.18.0 ( |
1.18.0 |
|
|
setting the option has been an error since 1.18.0, after warning from 1.15.0 |
1.18.0 |
For the changes of R itself see What’s New and Migrating Older Code.
Where to go next
-
Data Frames for the base-R side of every operation on this page, and The tidyverse and dplyr for the tidyverse side.
-
tidyr, purrr and stringr for
pivot_longer()andpivot_wider(), the tidyr equivalents ofmelt()anddcast(). -
Reading and Writing Text Data for
read.csv()andwrite.csv(), and Serialization: RDS, RData and JSON forsaveRDS(). -
Evaluation and Non-Standard Evaluation for the idea behind
env =andsubstitute2(). -
Memory and Copy Semantics and Performance, Profiling and Benchmarking for when copies happen and how to measure a data.table against the alternatives.
-
Parallel and Asynchronous Computing for threads and processes.
-
Packages, Libraries and Namespaces and Style, Linting and Static Analysis for importing data.table in a package and for the
object_usage_linterexceptions. -
Dates and Times and Strings and Regular Expressions for the columns that most data tables hold.
References
-
data.table package documentation (the website of the development version): the reference pages data.table, special-symbols, := and set, copy, setDT, setorder, setkey and setindex, truelength, .selfref.ok, merge, foverlaps, mergelist, fread, fwrite, melt, dcast, rbindlist, fifelse, fcase, shift, frank, nafill, substitute2, print.data.table, setDTthreads and getDTthreads
-
The vignettes: Introduction to data.table, Reference semantics, Using .SD for data analysis, Keys and fast binary search based subset, Joins in data.table, Secondary indices and auto indexing, Efficient reshaping using data.table, Programming on data.table, Fast Read and Fast Write, Frequently asked questions, Importing data.table
-
dplyr package documentation: dplyr and base R and join_by; dtplyr (named, not run)
-
R NEWS: Changes in R 4.0.0, 4.4.0 and 4.6.0
-
sort_by, merge, read.csv, saveRDS and readRDS, ifelse, reshape