Chapter 3 Tables

3.1.1 The magrittr package

Magrittr introduces syntax with “pipes,” improving readability by unnesting function calls:

# we can do
x %>% f %>% g %>% h
# instead of 
h(g(f(x)))

In addition, it allows more compact function definitions (2.4.4):

fun = . %>% .^2 %>% sum %>% sqrt
fun(1:3)

# [1] 3.741657

Despite these advantages, magrittr can slow down code, so avoid using it anywhere where speed might be an issue.

To install, just use CRAN:

install.packages('magrittr')

The name is a pun on René Magritte’s pipe painting. To get a handle on the package, I would start with the package vignette.

Beyond the %>% operator, magrittr includes many other convenience tools:

• If x = "boo", then x %<>% paste("ya!") overwrites it as “boo ya!”.
• If L = list(a = 1, b = 2), then L %$% { a + b } acts like L$a + L$b.
• "boo" %>% paste("ya!") %T>% print %>% toupper takes a “Timeout” to print before continuing with later pipes.

It also supports function defintions, like

f = . %>% paste("ya!") %T>% print %>% toupper
# usage
f("boo")
"baz" %>% f

Finally, it has some helper functions, to make common chains more readable:

12 %>% divide_by(3) %>% is_greater_than(5)

See help(package = "magrittr") for a full listing. I almost never use anything beyond %>%.

3.1.2 The data.table package

Data.table offers a variant of the data.frame class for data frames (seen in 2.6), optimized for fast sorted and grouped operations and enhanced with cleaner syntax. Section 3.2 introduces this data.table class.

The package also bundles in a variety of other functionality:

• ITime and IDate date and time classes
• fread and fwrite for fast reading and writing of delimited files
• dcast and melt for reshaping tables

Because these disparate features are bundled together in data.table, we don’t have to load more packages and worry about their complicated dependencies or namespace conflicts (2.4.1.3).

install.packages('data.table')

remove.packages("data.table")
install.packages("data.table", type = "source",
  repos = "http://Rdatatable.github.io/data.table")

$(R_HOME)/etc/$(R_ARCH)/Makeconf

BINPREF ?= c:/Rtools/mingw_64/bin/

browseVignettes(package="data.table")

3.2.1 Inspecting

Consider the quakes data set (with more info in ?quakes):

quakeDT = data.table(quakes)

#          lat   long depth mag stations
#    1: -20.42 181.62   562 4.8       41
#    2: -20.62 181.03   650 4.2       15
#    3: -26.00 184.10    42 5.4       43
#    4: -17.97 181.66   626 4.1       19
#    5: -20.42 181.96   649 4.0       11
#   ---                                 
#  996: -25.93 179.54   470 4.4       22
#  997: -12.28 167.06   248 4.7       35
#  998: -20.13 184.20   244 4.5       34
#  999: -17.40 187.80    40 4.5       14
# 1000: -21.59 170.56   165 6.0      119

By default, data.tables with over 100 rows will print in the compressed form seen above, showing just the first and last five rows. To globally change how many rows are needed, use options(datatable.print.nrows = n). To override it a single time, use print with nrows=. I usually set nrows=Inf to print all rows:

quakeDT %>% print(nrow = Inf)

To browse a table in a new window, View for data frames (2.6.2) again works:

quakeDT %>% View
# or if using RStudio
quakeDT %>% utils::View()

To inspect the structure of a data.table, we can again use str (2.1.4):

quakeDT %>% str

# Classes 'data.table' and 'data.frame':    1000 obs. of  5 variables:
#  $ lat     : num  -20.4 -20.6 -26 -18 -20.4 ...
#  $ long    : num  182 181 184 182 182 ...
#  $ depth   : int  562 650 42 626 649 195 82 194 211 622 ...
#  $ mag     : num  4.8 4.2 5.4 4.1 4 4 4.8 4.4 4.7 4.3 ...
#  $ stations: int  41 15 43 19 11 12 43 15 35 19 ...
#  - attr(*, ".internal.selfref")=<externalptr>

Here, we see the class of each column along with the first few values. In addition, there is an obscure ".internal.selfref" attribute, which we can ignore except when saving or loading the table from disk (3.6.9). Other attributes will sometimes also show up here, related to optimizing the performance of ordered or grouped queries on the table (3.5.8.1).

In the code above, we could use syntax like View(quakeDT) instead of quakeDT %>% View, but I often find the latter handier, since it’s easy to insert intermediate steps, like quakeDT %>% head(10) %>% View. Also, while %>% is slow, it’s not going to matter for tasks like browsing data.

3.2.2 Slicing

A slice of a data.table is a smaller data.table formed by subsetting rows, columns, or both, analogous to vector and matrix slices seen in 2.1.7 and 2.2.3. The remaining columns will retain attributes from the full table (class, levels for factors, etc.).

The syntax is familiar from vectors and matrices:

# example data
DT = data.table(
  x = letters[c(1, 2, 3, 4, 5)], 
  y = c(1, 2, 3, 4, 5), 
  z = c(1, 2, 3, 4, 5) > 3
)

DT[1:2, ]

#    x y     z
# 1: a 1 FALSE
# 2: b 2 FALSE

DT[, "z"]

#        z
# 1: FALSE
# 2: FALSE
# 3: FALSE
# 4:  TRUE
# 5:  TRUE

DT[-3, c("y", "z")]

#    y     z
# 1: 1 FALSE
# 2: 2 FALSE
# 3: 4  TRUE
# 4: 5  TRUE

One difference with matrices is that when only slicing rows, we can skip the comma:

DT[1:2]

#    x y     z
# 1: a 1 FALSE
# 2: b 2 FALSE

Other differences come into play when subsetting columns programmatically, where we need either a .. prefix…

keep_cols = c("y", "z")
DT[, keep_cols]                  # error

# Error in `[.data.table`(DT, , keep_cols): j (the 2nd argument inside [...]) is a single symbol but column name 'keep_cols' is not found. Perhaps you intended DT[,..keep_cols] or DT[,keep_cols,with=FALSE]. This difference to data.frame is deliberate and explained in FAQ 1.1.

DT[, ..keep_cols]                # use this instead

#    y     z
# 1: 1 FALSE
# 2: 2 FALSE
# 3: 3 FALSE
# 4: 4  TRUE
# 5: 5  TRUE

… or we need with=FALSE if programming inline:

DT[, letters[25:26]]             # no error, but doesn't print what we want

# [1] "y" "z"

DT[, letters[25:26], with=FALSE] # use this instead

#    y     z
# 1: 1 FALSE
# 2: 2 FALSE
# 3: 3 FALSE
# 4: 4  TRUE
# 5: 5  TRUE

These requirements are a quirk of the more flexible DT[...] syntax that supports far more than taking slices, as discussed in 3.2.4.3.

head, tail and the special empty and missing-data slices (all seen in the last chapter) work by row:

head(DT, -3)    # top, removing last 3

#    x y     z
# 1: a 1 FALSE
# 2: b 2 FALSE

tail(DT, 2)     # bottom 2

#    x y    z
# 1: d 4 TRUE
# 2: e 5 TRUE

DT[0L]          # empty 

# Empty data.table (0 rows) of 3 cols: x,y,z

DT[NA_integer_] # missing

#     x  y  z
# 1: NA NA NA

The package also offers first and last, which are simply special cases of head and tail.

Fancier slicing methods, like DT[x > "b", .(y, z)], will be introduced with the rest of the DT[...] syntax in 3.2.4.3.

There are some syntactical shortcuts for slicing to a set of columns in j; see 3.8.4.3.

3.2.3 Extracting columns

Since data.tables (and data frames) are lists, we can extract columns like DT$z or DT[["z"]].

With the full DT[...] syntax (3.2.4.3), it is easy to extract a column for a limited set of rows, like DT[x > "b", z].

3.2.4 Extensions to the data.frame class

The first thing to note is that data.tables are data frames, so any function that works on a data frame will work fine with data.tables, too (even if the data.table package is not installed or attached).

# example data
DF = data.frame(
  x = letters[c(1, 2, 3, 4, 5)], 
  y = c(1, 2, 3, 4, 5), 
  z = c(1, 2, 3, 4, 5) > 3
)

str(DF)

# 'data.frame': 5 obs. of  3 variables:
#  $ x: Factor w/ 5 levels "a","b","c","d",..: 1 2 3 4 5
#  $ y: num  1 2 3 4 5
#  $ z: logi  FALSE FALSE FALSE TRUE TRUE

setDT(DF)
str(DF)

# Classes 'data.table' and 'data.frame':    5 obs. of  3 variables:
#  $ x: Factor w/ 5 levels "a","b","c","d",..: 1 2 3 4 5
#  $ y: num  1 2 3 4 5
#  $ z: logi  FALSE FALSE FALSE TRUE TRUE
#  - attr(*, ".internal.selfref")=<externalptr>

DT = data.table(
  x = letters[c(1, 2, 3, 4, 5)], 
  y = c(1, 2, 3, 4, 5), 
  z = c(1, 2, 3, 4, 5) > 3
)
str(DT)

# Classes 'data.table' and 'data.frame':    5 obs. of  3 variables:
#  $ x: chr  "a" "b" "c" "d" ...
#  $ y: num  1 2 3 4 5
#  $ z: logi  FALSE FALSE FALSE TRUE TRUE
#  - attr(*, ".internal.selfref")=<externalptr>

# (pseudocode)
DT[where, select|update|do, by] # SQL verbs
DT[i, j, by]                    # R function arguments

DT[x > "b", sum(y), by=z]

#        z V1
# 1: FALSE  3
# 2:  TRUE  9

DT[x > "b", .(n_bigy = sum(y > 2), n_smally = sum(y <= 2)), by=z]

#        z n_bigy n_smally
# 1: FALSE      1        0
# 2:  TRUE      2        0

# note: this code will save to your current working directory
# type getwd() and read ?setwd for details

bwDT = data.table(MASS::birthwt)

pdf(file="birthweight_graphs.pdf")
bwDT[, {
  case = sprintf("high #visits? = % 5s, smoking? = % 5s", .BY$high_vis, .BY$smoke)
  
  cat("Handling case:", case, "...\n")
  plot(
    age ~ lwt, 
    main = case
  )  
}, by=.(high_vis = ftv >= 1, smoke = as.logical(smoke))]

# Handling case: high #visits? = FALSE, smoking? = FALSE ...

# Handling case: high #visits? =  TRUE, smoking? = FALSE ...

# Handling case: high #visits? =  TRUE, smoking? =  TRUE ...

# Handling case: high #visits? = FALSE, smoking? =  TRUE ...

# Empty data.table (0 rows) of 2 cols: high_vis,smoke

dev.off()

# png 
#   2

3.3.1 Iterating over columns

Now suppose we want to compare mean horsepower, weight and displacement:

## carsDT[, .(mean_hp = mean(hp), mean_wt = mean(wt), mean_disp = mean(disp)), by=.(am, vs)]
## # can be simplified to...
carsDT[, lapply(.SD, mean), by=.(am, vs), .SDcols = c("hp", "wt", "disp")]

#    am vs        hp       wt     disp
# 1:  1  0 180.83333 2.857500 206.2167
# 2:  1  1  80.57143 2.028286  89.8000
# 3:  0  1 102.14286 3.194286 175.1143
# 4:  0  0 194.16667 4.104083 357.6167

So, we just write the relevant columns in .SDcols and refer to .SD, the Subset of Data. Within each by= group, the query has access to .SD with rows for that group and columns as specified in .SDcols. .SD, like .BY seen earlier, is a special symbol available in some arguments of DT[...], documented at ?.SD.

We can use lapply (a function designed for iterating over lists) here since .SD is a data.table, which is a list of column vectors (see 2.6.1). The column names carry over to the result because lapply always carries over names.

While dot notation .SDcols=.(hp, wt, disp) is not yet supported, there are a variety of convenience features for specifying .SDcols, covered in 3.8.4.3.

3.3.2 Exercises

1. Within each .(am, vs) group, return the last row (or equivalently, the last element of each column). There are many ways to do this.

3.4.1 Creating columns

# example data
DT = data.table(
  x = letters[c(1, 2, 3, 4, 5)], 
  y = c(1, 2, 3, 4, 5), 
  z = c(1, 2, 3, 4, 5) > 3
)

#    x y     z
# 1: a 1 FALSE
# 2: b 2 FALSE
# 3: c 3 FALSE
# 4: d 4  TRUE
# 5: e 5  TRUE

# creating a column
DT[, u := 5:1][]

#    x y     z u
# 1: a 1 FALSE 5
# 2: b 2 FALSE 4
# 3: c 3 FALSE 3
# 4: d 4  TRUE 2
# 5: e 5  TRUE 1

# creating multiple
DT[, `:=`(v = 2, w = 3L)][]

#    x y     z u v w
# 1: a 1 FALSE 5 2 3
# 2: b 2 FALSE 4 2 3
# 3: c 3 FALSE 3 2 3
# 4: d 4  TRUE 2 2 3
# 5: e 5  TRUE 1 2 3

# creating with dynamic names
nms = c("a", "b", "c")
DT[, (nms) := .(1, 2, 3)][]

#    x y     z u v w a b c
# 1: a 1 FALSE 5 2 3 1 2 3
# 2: b 2 FALSE 4 2 3 1 2 3
# 3: c 3 FALSE 3 2 3 1 2 3
# 4: d 4  TRUE 2 2 3 1 2 3
# 5: e 5  TRUE 1 2 3 1 2 3

All of these tasks are performed in-place – altering DT without making a new object. Usually, the results are not printed in the console; but they appear here because [] is “chained” onto the end of each task.

3.4.2 Removing columns

nms = c("u", "v", "w", "a", "b", "c")
DT[, (nms) := NULL][]

#    x y     z
# 1: a 1 FALSE
# 2: b 2 FALSE
# 3: c 3 FALSE
# 4: d 4  TRUE
# 5: e 5  TRUE

A warning will print if we remove some columns that don’t currently exist.

3.4.3 Replacing entire columns

Data.table is careful about column types:

DT[, a := 10L ][]  # Create a new column of integers

#    x y     z  a
# 1: a 1 FALSE 10
# 2: b 2 FALSE 10
# 3: c 3 FALSE 10
# 4: d 4  TRUE 10
# 5: e 5  TRUE 10

DT[, a := 21L ][]  # Replace it with another column of integers

#    x y     z  a
# 1: a 1 FALSE 21
# 2: b 2 FALSE 21
# 3: c 3 FALSE 21
# 4: d 4  TRUE 21
# 5: e 5  TRUE 21

DT[, a := 32.5 ][] # Replace it with a float

# Warning in `[.data.table`(DT, , `:=`(a, 32.5)): Coerced 'double' RHS to 'integer' to
# match the column's type; may have truncated precision. Either change the target column
# to 'double' first (by creating a new 'double' vector length 5 (nrows of entire table)
# and assign that; i.e. 'replace' column), or coerce RHS to 'integer' (e.g. 1L, NA_[real|
# integer]_, as.*, etc) to make your intent clear and for speed. Or, set the column type
# correctly up front when you create the table and stick to it, please.

#    x y     z  a
# 1: a 1 FALSE 32
# 2: b 2 FALSE 32
# 3: c 3 FALSE 32
# 4: d 4  TRUE 32
# 5: e 5  TRUE 32

The warning in the last call is related to coercion of vector classes (2.1.2).

If we want this assignment to work, we need to change a’s type by passing a full vector, as described in the warning:

DT[, a := rep(32.5, .N) ][]

#    x y     z    a
# 1: a 1 FALSE 32.5
# 2: b 2 FALSE 32.5
# 3: c 3 FALSE 32.5
# 4: d 4  TRUE 32.5
# 5: e 5  TRUE 32.5

The coercion is done silently, but it can be made more visible by turning on verbose, which notes the “plonk” of a full vector replacement. .N is a special symbol for the number of rows (or the number of rows in a subset when i or by= is present).

3.4.4 Replacing columns conditionally

Conditional replacement here is analogous to a SQL UPDATE query or a replace if command in Stata. To illustrate, we will look again at a vectorized if/else assignment, mentioned in 2.5.2:

DT[     , b := "Aardvark"] # initialize to baseline value

DT[y > 1, b := "Zebra"][] # replace based on a condition

#    x y     z    a        b
# 1: a 1 FALSE 32.5 Aardvark
# 2: b 2 FALSE 32.5    Zebra
# 3: c 3 FALSE 32.5    Zebra
# 4: d 4  TRUE 32.5    Zebra
# 5: e 5  TRUE 32.5    Zebra

This can also be done with chaining:

DT[, b := "Aardvark"][y > 1, b := "Zebra"]

This chaining works because DT[...][...] is evaluated like (DT[...])[...] and the return value of the piece in parentheses is DT – provided j has a := statement.

As we saw in the last section, partial replacement of a column will trigger a warning if the classes don’t match:

DT[, b := "Aardvark"][y > 1, b := 111]

# Warning in `[.data.table`(DT[, `:=`(b, "Aardvark")], y > 1, `:=`(b, 111)): Coerced
# 'double' RHS to 'character' to match the column's type; may have truncated precision.
# Either change the target column to 'double' first (by creating a new 'double' vector
# length 5 (nrows of entire table) and assign that; i.e. 'replace' column), or coerce RHS
# to 'character' (e.g. 1L, NA_[real|integer]_, as.*, etc) to make your intent clear and for
# speed. Or, set the column type correctly up front when you create the table and stick to
# it, please.

Another nice feature, similar to Stata, is reporting of the number of rows modified. This can be seen by turning verbose on:

DT[, b := "Aardvark"][y > 1, b := "Zebra", verbose = TRUE]

# Detected that j uses these columns: b 
# Assigning to 4 row subset of 5 rows

The number reported is the number of rows assigned to (irrespective of whether their values were changed by the assignment), in contrast with Stata, which reports the number of changes.

3.4.5 Other in-place modifications

The data.table package has a few other tools for modifying table attributes in-place:

• The set function is another way of making assignments like :=.

• setDT and setDF, seen earlier, alter the class.

• setorder will sort the table by some or all of its columns.

• setcolorder changes the order in which columns are displayed.

• Indices and the key (explained in 3.5.8.1) can be set with setindex and setkey, respectively.

• setnames will alter column names. For example, in 3.3.1 we saw…

  carsDT[, lapply(.SD, mean), by=.(am, vs), .SDcols = c("hp", "wt", "disp")]

  #    am vs        hp       wt     disp
  # 1:  1  0 180.83333 2.857500 206.2167
  # 2:  1  1  80.57143 2.028286  89.8000
  # 3:  0  1 102.14286 3.194286 175.1143
  # 4:  0  0 194.16667 4.104083 357.6167

  … and if we want to add the prefix "mean_" to the results, we can do

  cols = c("hp", "wt", "disp")
  carsDT[, lapply(.SD, mean), by=.(am, vs), .SDcols = cols] %>% 
    setnames(cols, sprintf("mean_%s", cols)) %>% print

  #    am vs   mean_hp  mean_wt mean_disp
  # 1:  1  0 180.83333 2.857500  206.2167
  # 2:  1  1  80.57143 2.028286   89.8000
  # 3:  0  1 102.14286 3.194286  175.1143
  # 4:  0  0 194.16667 4.104083  357.6167

  The trailing %>% print command is used because, setnames, like := and the other set* operators, does not print the table on its own. The string-formatter sprintf will be explained in 4.8.

• setattr is a general function for altering attributes of a vector or other object (see ?attributes). For example, if we have a factor column (encoding categorical data), we can change its “levels”:

  # example data
  fDT = data.table(fac = factor(c("A", "B"), levels = c("A", "B", "C")))

  levels(fDT$fac)

  # [1] "A" "B" "C"

  fDT$fac %>% setattr("levels", c("A", "B", "Q"))
  levels(fDT$fac)

  # [1] "A" "B" "Q"

With setattr, we avoid the copy usually incurred when using levels(x) <- lvls (base R syntax), but it will also skip some checks, so it is important to be careful to assign a valid vector of levels.

3.4.6 Avoiding in-place modification

To create a new data.table starting from an existing table, use

DT2 = copy(DT1)

We have to use this instead of DT2 = DT1 since with the latter we have only created a new “pointer” to the first table. I routinely make copies like this after reading in data (3.6.2) so that I can back out where I tripped over something in the process of data cleaning.

Besides DT2 = DT1, names1 = names(DT1) is also unsafe, since the names function does not extract the column names as they are at a given time, but rather points at the names attribute, which can change as columns are modified or rearranged.

3.4.7 Using in-place modification in functions

A user-written function (2.4.4) like

f <- function(DT) DT[, newcol := 1111 ][]

will act like := and the set* functions, altering its input in-place. This can be useful, but requires extra caution. See the “:= for its side effect” section of vignette("datatable-reference-semantics") for discussion.

The [] inside the function is necessary for printing, due to restrictions of R syntax and how data.table gets around them.

3.4.8 Exercises

1. With carsDT = data.table(mtcars, keep.rownames = TRUE), overwrite the am column with a factor so 0 becomes “automatic” and 1 becomes “manual”, so head(carsDT) looks like…

   #                   rn  mpg cyl disp  hp drat    wt  qsec vs        am gear carb
   # 1:         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0    manual    4    4
   # 2:     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0    manual    4    4
   # 3:        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1    manual    4    1
   # 4:    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1 automatic    3    1
   # 5: Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0 automatic    3    2
   # 6:           Valiant 18.1   6  225 105 2.76 3.460 20.22  1 automatic    3    1

2. In DT = data.table(x = letters[1:5], y = 1:5, z = 1:5 > 3), edit x to equal “yolo” on rows where z is true, so DT looks like…

   #       x y     z
   # 1:    a 1 FALSE
   # 2:    b 2 FALSE
   # 3:    c 3 FALSE
   # 4: yolo 4  TRUE
   # 5: yolo 5  TRUE

3. In DT = data.table(x = letters[1:5], matrix(1:15,5) > 7), add a column id that contains the row number; and use setcolorder, names(DT) and setdiff to move id to be the first column.

4. In DT = data.table(id = 1:5, x = letters[1:5], matrix(1:15,5) > 7), use setnames to rename id to ID.

3.5.1 Equi joins

# example data
a = data.table(id = c(1L, 1L, 2L, 3L, NA_integer_), t = c(1L, 2L, 1L, 2L, NA_integer_), x = 11:15)

#    id  t  x
# 1:  1  1 11
# 2:  1  2 12
# 3:  2  1 13
# 4:  3  2 14
# 5: NA NA 15

b = data.table(id = 1:2, y = c(11L, 15L))

#    id  y
# 1:  1 11
# 2:  2 15

The idiom for a simple equi join is x[i, on=.(...)] or x[i] for short:

a[b, on=.(id)]

#    id t  x  y
# 1:  1 1 11 11
# 2:  1 2 12 11
# 3:  2 1 13 15

It is called an equi join since we are only getting matches where equality holds between the on= columns in the two tables.

Think of x[i] as using index table i to look up rows of x, in the same way an “index matrix” can look up elements of a matrix (2.2.4). By default, we see results for every row of i, even those that are unmatched.

Here are some more complicated examples:

a[b, on=.(x = y)]

#    id  t  x i.id
# 1:  1  1 11    1
# 2: NA NA 15    2

a[b, on=.(id, x = y)]

#    id  t  x
# 1:  1  1 11
# 2:  2 NA 15

When merging on columns with different names, they must be written in on= like x = y where x is from the “left” table, and y from the “right” one. Because we are using i to lookup rows in x, the displayed column will have its name from x and its values from i.

A character vector also works in, e.g., on=c("id", x = "y"), making it easier to merge programmatically.

3.5.2 Subset lookup

For browsing dynamic data (that grows over time), it is convenient and quick to use joins:

a[.(1L), on=.(id)]

#    id t  x
# 1:  1 1 11
# 2:  1 2 12

# or, as a function
ra <- function(my_id) a[.(my_id), on=.(id)] 
ra(2L)

#    id t  x
# 1:  2 1 13

Note that the index i is a list here, not a data.table. The link between the two is close, since data.tables are just lists of columns (3.3.1). Any list passed in i will be treated the same as the analogous data.table with appropriate column names.

Subset browsing becomes even easier when keys are set so that on= can be skipped (3.5.8.1).

3.5.3 Aggregating in a join

Looking again at the first join above, suppose we want to use b to find rows in a and then add up a$x. We’ll do this by using by=.EACHI:

a[b, on=.(id)]

#    id t  x  y
# 1:  1 1 11 11
# 2:  1 2 12 11
# 3:  2 1 13 15

a[b, on=.(id), sum(x), by=.EACHI]

#    id V1
# 1:  1 23
# 2:  2 13

It is called “each i” since the syntax is x[i, ...] and we are computing per row of the index i. I prefer to write the on= before the j (unlike the core syntax of DT[i,j,by]) since the on= merging columns are closely related to the i index.

If we tried summing b$y here, we would not get 11+11 for id 1:

a[b, on=.(id)] # y shows up twice

#    id t  x  y
# 1:  1 1 11 11
# 2:  1 2 12 11
# 3:  2 1 13 15

a[b, on=.(id), .(sum(x), sum(y)), by=.EACHI] # we only get one y

#    id V1 V2
# 1:  1 23 11
# 2:  2 13 15

This is because we are working by each row of b. For id 1 in b, y is a single value, 11. So really there is no point in summing y or otherwise aggregating columns from i when using by=.EACHI.

3.5.4 Updating in a join

Continuing from the last example, we are computing sum(x) per row of b, so maybe we want to save the result as a column in b:

b[, sumx := 
    a[b, on=.(id), sum(x), by=.EACHI]$V1
]
# or
b[, sumx := 
    a[.SD, on=.(id), sum(x), by=.EACHI]$V1
]

#    id  y sumx
# 1:  1 11   23
# 2:  2 15   13

On the other hand, we may want to take values from b and assign them to the larger table a:

a[b, on=.(id), y := i.y ]

#    id  t  x  y
# 1:  1  1 11 11
# 2:  1  2 12 11
# 3:  2  1 13 15
# 4:  3  2 14 NA
# 5: NA NA 15 NA

This sort of operation is very common when doing analysis with well-organized relational data, and will come up again in 3.8. It is essentially the same as a SQL UPDATE JOIN. It might also be called a “merge assign” (though I seem to be the only one using that term).

The i.* prefix in i.y indicates that we are taking the column from the i table in x[i]. We can similarly use an x.* prefix for columns from x. This helps to disambiguate if the same column names appear in both tables, and is particularly helpful with non-equi joins (3.5.7). I recommend always using i.* prefixes when copying columns in an update join.

3.5.5 Self join to fill in missing levels

Consider the a data set from above:

# example data
a = data.table(id = c(1L, 1L, 2L, 3L, NA_integer_), t = c(1L, 2L, 1L, 2L, NA_integer_), x = 11:15)

#    id  t  x
# 1:  1  1 11
# 2:  1  2 12
# 3:  2  1 13
# 4:  3  2 14
# 5: NA NA 15

Now we want to “complete” the data set so that every id has a row for every time t:

a[CJ(id = id, t = t, unique=TRUE), on=.(id, t)]

#     id  t  x
#  1: NA NA 15
#  2: NA  1 NA
#  3: NA  2 NA
#  4:  1 NA NA
#  5:  1  1 11
#  6:  1  2 12
#  7:  2 NA NA
#  8:  2  1 13
#  9:  2  2 NA
# 10:  3 NA NA
# 11:  3  1 NA
# 12:  3  2 14

This is called a self join because we are using the table’s own columns in i of x[i, on=]. The CJ function is a helper that takes all combinations of vectors, alternately called the Cartesian product or a “cross join.” See 4.2 for details. By applying unique=TRUE, we treat each vector as a set in the mathematical sense, only considering distinct values.

Notice that missing values (NA) in i are treated in the same way as other values.

3.5.6 Handling matches

For this section, we’ll reset the tables:

# example data
a = data.table(id = c(1L, 1L, 2L, 3L, NA_integer_), t = c(1L, 2L, 1L, 2L, NA_integer_), x = 11:15)

#    id  t  x
# 1:  1  1 11
# 2:  1  2 12
# 3:  2  1 13
# 4:  3  2 14
# 5: NA NA 15

b = data.table(id = 1:2, y = c(11L, 15L))

#    id  y
# 1:  1 11
# 2:  2 15

a[b, on=.(id), mult="first"]

#    id t  x  y
# 1:  1 1 11 11
# 2:  2 1 13 15

b[a, on=.(id)]

#    id  y  t  x
# 1:  1 11  1 11
# 2:  1 11  2 12
# 3:  2 15  1 13
# 4:  3 NA  2 14
# 5: NA NA NA 15

b[a, on=.(id), nomatch=0]

#    id  y t  x
# 1:  1 11 1 11
# 2:  1 11 2 12
# 3:  2 15 1 13

# target x
myxDT = list(myx = c(5L, 10L, 15L, 20L))

# exact match (equi-join)
a[myxDT, on=.(x = myx), .(i.myx, x.x)]

#    i.myx x.x
# 1:     5  NA
# 2:    10  NA
# 3:    15  15
# 4:    20  NA

# nearest match
a[myxDT, on=.(x = myx), roll="nearest", .(i.myx, x.x)]

#    i.myx x.x
# 1:     5  11
# 2:    10  11
# 3:    15  15
# 4:    20  15

# upward match within 3
a[myxDT, on=.(x = myx), roll=-3, .(i.myx, x.x)]

#    i.myx x.x
# 1:     5  NA
# 2:    10  11
# 3:    15  15
# 4:    20  NA

3.5.7 Non-equi joins

It is sometimes useful to match on a range of values. To do this, we explicitly name all columns in i and define inequalities in on=. Suppose we want to see, for every time t in 1..5, how many individuals were seen in the preceding three days:

mDT = data.table(id = 1:3, x_dn = 10L, x_up = 13L)

#    id x_dn x_up
# 1:  1   10   13
# 2:  2   10   13
# 3:  3   10   13

a[mDT, on=.(id, x >= x_dn, x <= x_up), .(id, i.x_dn, i.x_up, x.x)]

#    id i.x_dn i.x_up x.x
# 1:  1     10     13  11
# 2:  1     10     13  12
# 3:  2     10     13  13
# 4:  3     10     13  NA

So we are defining a range x_dn to x_up for each id and finding all matches of x within the range.

These could alternately be called “interval joins.” For more on interval joins and subsets, see ?inrange, ?foverlaps and the IRanges package that inspired these data.table tools.

3.5.8 Shortcuts and tricks

When joining on a single character or factor column, the .() in i can be skipped:

DT = data.table(id = c("A", "A", "B"), u = c(0, 1, 2), v = c(1, 4, 7))
DT["A", on=.(id)]

#    id u v
# 1:  A 0 1
# 2:  A 1 4

## # instead of
## DT[.("A"), on=.(id)]

setkey(DT, id)
DT["A"]

#    id u v
# 1:  A 0 1
# 2:  A 1 4

setkey(DT, u, v)
DT[.(v = 7, u = 2)]

#    id u v
# 1: NA 7 2

setkey(DT, id, u, v)
DT[.("B", 1, 1)]

#    id u v
# 1:  B 1 1

r0 <- function(..., d = DT) {
  x = d[.(...), nomatch=0]
  print(x = x, nrows=Inf, row.names=FALSE)
  invisible(x)
}

r0("B", 1, 1)

# Empty data.table (0 rows) of 3 cols: id,u,v

r0("B", 2, 7)

#  id u v
#   B 2 7

setindex(DT, id, u)
setindex(DT, id, v)
## key(DT)
## indices(DT, vectors=TRUE)
## str(DT)

a[!b, on=.(id)]

#    id  t  x
# 1:  3  2 14
# 2: NA NA 15

3.5.9 Exercises

1. In DT = data.table(id = rep(1:2, 2:3), x = 1:5, y = FALSE), use an update self join with mult= to edit y to TRUE in the first row of each id group.
2. In DT = data.table(id = 1:4, v = 10:13), use an anti join to set v to missing if id is not in my_ids = 2:3.

3. In DT = data.table(id = rep(1:2, c(10,5)), t = cumsum(1:15)), use a non-equi self join to add a column counting how many observations of the current id are in the interval \((t-20, t)\). The result should be…

   #     id   t N
   #  1:  1   1 0
   #  2:  1   3 1
   #  3:  1   6 2
   #  4:  1  10 3
   #  5:  1  15 4
   #  6:  1  21 4
   #  7:  1  28 3
   #  8:  1  36 2
   #  9:  1  45 2
   # 10:  1  55 2
   # 11:  2  66 0
   # 12:  2  78 1
   # 13:  2  91 1
   # 14:  2 105 1
   # 15:  2 120 1

4. The “Grouping on floats” note (3.2.4) says to use cut to bin continuous variables, but this task can also be accomplished with a rolling join. For irisDT = data.table(iris), create mDT so that

   irisDT[, v := mDT[irisDT, on=.(Species, Petal.Width), roll = TRUE, x.lab ]]

   creates a column the same as..

   irisDT[, vcut := cut(
     Petal.Width,
     breaks = c(-Inf, median(Petal.Width), Inf),
     right = FALSE,
     lab = c("low", "high")
   ), by=Species]

3.6.1 File paths

Don’t provide paths with backslashes, like "C:\data\input.csv", since \ is special character in R. My workaround is to use forward slashes or construct the path using file.path.

• For a list of files and folders, use dir or list.files.
• For relative paths, "." is the current folder; ".." navigates one level up; and normalizePath converts to an absolute path.
• For the current path, use getwd; and to alter it, setwd(new_path).
• To extract parts of a path, use, e.g., file_ext from the tools package (included in the base R installation).

For functions to manipulate files, see ?files and ?dir.create; and for file attributes, ?file.info.

3.6.2 fread to read delimited files

To read tables from CSVs or other delimited formats, fread is quite fast and reliable:

DT = fread("file.csv")

See ?fread and the wiki for a discussion of features. I usually find that fread just works without any need for tweaks. However, it currently doesn’t recognize date or time formats, which need to be handled after reading (3.6.4).

3.6.3 rbindlist to combine tables

To read and combine several tables with the same columns, rbindlist is the right tool. The short approach is:

rbindlist(lapply(list.files(patt="csv$"), fread), id=TRUE)
# or...
list.files(patt="csv$") %>% lapply(fread) %>% rbindlist(id=TRUE)

The rest of this section covers the longer approach I recommend, using a table with one row per file:

# note: this code will save to your current working directory
# type getwd() and read ?setwd for details

# example data
set.seed(1)
for (i in 1:3) 
  fwrite(data.table(id = 1:2, v = sample(letters, 2)), file = sprintf("file201%s.csv", i))

# First, identify the files wanted:
fileDT = data.table(fn = list.files(pattern="csv$"))

# Next, optionally parse the names for metadata using regex:
fileDT[, year := type.convert(sub(".*([0-9]{4}).*", "\\1", fn))]

# Finally construct a string file-ID column:
fileDT[, id := as.character(.I)][]

#              fn year id
# 1: file2011.csv 2011  1
# 2: file2012.csv 2012  2
# 3: file2013.csv 2013  3

From here, read in the files as a new column:

fileDT[, contents := .(lapply(fn, fread))][]

#              fn year id     contents
# 1: file2011.csv 2011  1 <data.table>
# 2: file2012.csv 2012  2 <data.table>
# 3: file2013.csv 2013  3 <data.table>

Then, combine them for the final table:

DT = fileDT[, rbindlist(setNames(contents, id), idcol="file_id")]

# Perhaps add in metadata using an update join
DT[fileDT, on=.(file_id = id), year := i.year ][]

#    file_id id v year
# 1:       1  1 g 2011
# 2:       1  2 j 2011
# 3:       2  1 o 2012
# 4:       2  2 w 2012
# 5:       3  1 f 2013
# 6:       3  2 w 2013

Results can be debugged by investigating individual files like fileDT[year == 2012, contents[[1]]]. The setNames function (2.1.5) is needed here since rbindlist uses names to populate the id column.

3.6.4 Date and time columns

Currently, fread does not recognize or translate time or date columns to native R formats.

I recommend a couple packages, anytime and nanotime, for use when needed (more on which below). They have minimal dependencies and are maintained with an eye towards continued compatibility with the data.table package.

# example data
DT = data.table(id = 1:2, d = c("2017/01/21", "2017/01/22"), t = c("5:50", "13:50"))

#    id          d     t
# 1:  1 2017/01/21  5:50
# 2:  2 2017/01/22 13:50

str(DT)

# Classes 'data.table' and 'data.frame':    2 obs. of  3 variables:
#  $ id: int  1 2
#  $ d : chr  "2017/01/21" "2017/01/22"
#  $ t : chr  "5:50" "13:50"
#  - attr(*, ".internal.selfref")=<externalptr>

DT[, `:=`(
  d = as.IDate(d),
  t = as.ITime(t)
)][]

#    id          d        t
# 1:  1 2017-01-21 05:50:00
# 2:  2 2017-01-22 13:50:00

str(DT)

# Classes 'data.table' and 'data.frame':    2 obs. of  3 variables:
#  $ id: int  1 2
#  $ d : IDate, format: "2017-01-21" "2017-01-22"
#  $ t :Class 'ITime'  int [1:2] 21000 49800
#  - attr(*, ".internal.selfref")=<externalptr>

DT[1L, as.IDate(d, "%Y%M%D")] 

DT = data.table(dt = "2017-01-01 01:11")
DT[, c("d", "t") := IDateTime(as.factor(dt))][]

#                  dt          d        t
# 1: 2017-01-01 01:11 2017-01-01 01:11:00

tDT = data.table(d = as.IDate("2017-01-01") + 0:10)

# find min date greater than today
d_next = tDT[ d > as.IDate(Sys.Date()), min(d) ]

# Warning in min.default(structure(integer(0), class = c("IDate", "Date")), : no non-missing
# arguments to min; returning Inf

d_next

# [1] NA

# check if d_next is past the year 2099
d_next > as.IDate("2099-01-01")

# [1] TRUE

# str is no help
str(d_next)

#  IDate[1:1], format: NA

# as.vector strips class
as.vector(d_next)

# [1] Inf

# or unclass
unclass(d_next)

# [1] Inf

# or we can inspect with dput
dput(d_next)

# structure(Inf, class = c("IDate", "Date"))

d_next2 = tDT[ d > as.IDate(Sys.Date()) ][order(-d), d[1L] ]
dput(d_next2)

# structure(NA_integer_, class = c("IDate", "Date"))

# or a rolling join
d_next3 = tDT[order(-d)][.(as.IDate(Sys.Date())), on =.(d), roll=-Inf, x.d]
dput(d_next3)

# structure(NA_integer_, class = c("IDate", "Date"))

3.6.5 Character columns

With character columns that combine multiple variables, there are a few options.

• If the “split” of the variable is fixed-width, substr or substring works.

  DT = data.table(x = c("A001ABC", "B001DEF"))
  DT[, `:=`(x_id = substring(x, 1, 1), x_num = substring(x, 2, 4), x_val = substring(x, 5, 7))][]

  #          x x_id x_num x_val
  # 1: A001ABC    A   001   ABC
  # 2: B001DEF    B   001   DEF

  str(DT)

  # Classes 'data.table' and 'data.frame':    2 obs. of  4 variables:
  #  $ x    : chr  "A001ABC" "B001DEF"
  #  $ x_id : chr  "A" "B"
  #  $ x_num: chr  "001" "001"
  #  $ x_val: chr  "ABC" "DEF"
  #  - attr(*, ".internal.selfref")=<externalptr>

• If the split follows a pattern that can be characterized using regular expressions (4.8), tstrsplit can make the split and automatically convert to appropriate column types just as fread does:

  DT = data.table(x = c("A_1_22.2", "B_33_44.4"))
  DT[, c("x_id", "x_int", "x_float") := tstrsplit(x, "_", type.convert = TRUE)][]

  #            x x_id x_int x_float
  # 1:  A_1_22.2    A     1    22.2
  # 2: B_33_44.4    B    33    44.4

  str(DT)

  # Classes 'data.table' and 'data.frame':    2 obs. of  4 variables:
  #  $ x      : chr  "A_1_22.2" "B_33_44.4"
  #  $ x_id   : chr  "A" "B"
  #  $ x_int  : int  1 33
  #  $ x_float: num  22.2 44.4
  #  - attr(*, ".internal.selfref")=<externalptr>

With US states, built-ins (2.7) and an update join (3.5.4) can be used to convert between abbreviated and long forms:

DT = data.table(state_code = c("MN", "KS"))
DT[.(state_code = state.abb, state_name = state.name), on=.(state_code), 
   state_name := i.state_name 
]

#    state_code state_name
# 1:         MN  Minnesota
# 2:         KS     Kansas

In the case of messier strings, the convenience functions in the stringi package may be helpful, along with a read of ?regex and the string section in the next chapter (4.8).

3.6.6 Categorical columns

fread will read string columns in as character by default, and this is usually the way to go.

When “recoding” values in a character column, I suggest being explicit by storing the mapping in its own table and doing an update join (3.5.4):

# example data
DT = data.table(v = c("Yes", "argle", "bargle", "No", "Yep", "garble", "Nope", "blargh"))
# desired mapping
v_translateDT = rbind(
  data.table(v_old = c("Yes", "Yep"), v_new = TRUE),
  data.table(v_old = c("No", "Nope"), v_new = FALSE)
)
# update join
DT[v_translateDT, on=.(v = v_old), vt := i.v_new ]

Here, we are mapping to a logical column. Of course, the same thing could be done with a factor, an ordered factor or a string.

3.6.7 List columns

Data.table supports columns of class list. A “list column” is a list with a length equal to the number of rows of the table. Each element of the list (that is, each row) can hold anything, just like a normal list. We saw an example of this in 3.6.3.

List columns are rarely a good fit with tabular data for a few reasons. First, they make the data structure significantly harder to understand. Second, grouping, sorting or merging on them is not supported and probably never will be. Third, they do not play nice with time series operators like shift (4.4.1).

They are good for many things, but should be avoided if possible while cleaning the primary data. Nonetheless, fread does support reading list columns in.

3.6.8 fwrite to write delimited files

To write a CSV or other delimited-table format, fwrite is usually the best option.

It is fast and includes nice features for printing dates and times, like the squash option that will print dates like 20171020. For other tools for printing exotic columns out as strings see 4.8.

3.6.9 Saving and loading R objects

To save a table for later use in R, there are native formats, RData and RDS. In contrast with delimited files like CSVs, these files are small; are quick to save and load; and will allow the later user to skip tedious and error-prone data processing tasks.

Another consideration is whether the data storage is long-term or short term. If the objects are important and need to be accessible in 10-20 years, it would naturally be safer to store them in flat delimited file (assuming a tabular format makes sense for them). On the other hand, R’s storage formats will always be accessible by opening a suitable old version of R; and R’s formats are open and (implicitly) documented and so should presumably be readable with a little work.

One thing to watch out for when loading data.tables from disk is broken “self” pointers, leading to an error about “Invalid .internal.selfref”. This can be fixed by using setDT or alloc.col on all loaded tables, as explained in vignette("datatable-faq") under “Reading data.table from RDS or RData file”. Generally, I don’t bother with this safety precaution and haven’t run into problems yet, so I’m not sure how critical it is.

# note: this code will save to your current working directory
# type getwd() and read ?setwd for details
n   = 10
L   = list(a = 1, b = 2)
dat = data.table(v = 3)
save(n, L, dat, file = "stuff.rdata")

# and later, in another script
rm(list = ls()) # clear workspace
n   = 20          # create a different n
load("stuff.rdata", env = e_stuff <- new.env())

# note: this code will save to your current working directory
# type getwd() and read ?setwd for details
dat = data.table(v = 3)
saveRDS(dat, file = "dat.rds")

# and later, in another script
old_dat <- readRDS("dat.rds")

3.6.10 Reading and writing other formats

For “foreign” table formats (Excel, Stata, etc), use an internet search. These input and output tools will always be in active development because the formats themselves are in flux. of course, it is unwise to use any such formats for long-term data storage.

3.6.11 Formatting the display of columns

While the way R displays dates (like 2017-11-22) might not align with what is seen elsewhere, I suggest getting used to it rather than seeking a workaround, though I’m sure there are some (like making a new class). It will be easier for everyone looking at the data if it is in a standard format.

For the display of numbers, again, I suggest leaving it alone instead of, e.g., rounding or truncating everything. If it’s really an issue that must be addressed, read about scipen in ?options.

3.6.12 Exercises

1. To enumerate the first day of each month in a year, we can do seq(as.IDate("2017-01-01"), by="month", length.out = 12). How about the last day of each month?

3.7.1 Browsing loaded objects

For a list of all loaded tables, run…

tables()

#      NAME   NROW NCOL MB COLS                                            KEY
# [1,] airDT   153    6  1 Ozone,Solar.R,Wind,Temp,Month,Day                  
# [2,] carsDT   32   12  1 rn,mpg,cyl,disp,hp,drat,wt,qsec,gear,carb,am,vs    
# Total: 2MB

We saw in 3.6.9 that a collection of tables (and other objects) saved on disk can be loaded into an environment. We can look at tables in such an environment, too:

load("stuff.rdata", env = e_stuff <- new.env())
tables(env = e_stuff)

#      NAME NROW NCOL MB COLS KEY
# [1,] dat     1    1  1 v       
# Total: 1MB

The tables output shows keys and other attributes, which is handy. If we look at objects of all types (tables, vectors, matrices, functions and more), we usually only view names:

ls()

# [1] "airDT"   "carsDT"  "e_stuff"

Output can be filtered according to a regex (4.8):

ls(patt = "DT$")

# [1] "airDT"  "carsDT"

And it can be done within an environment:

ls(env = e_stuff)

# [1] "dat" "L"   "n"

To remove tables or other objects, use rm. A common step is to remove all objects listed by ls(), like rm(list = ls()).

3.7.2 Subsetting

carsDT[ am == "auto" & disp < 300 ]

#                 rn  mpg cyl  disp  hp drat    wt  qsec gear carb   am       vs
#  1: Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44    3    1 auto straight
#  2:        Valiant 18.1   6 225.0 105 2.76 3.460 20.22    3    1 auto straight
#  3:      Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00    4    2 auto straight
#  4:       Merc 230 22.8   4 140.8  95 3.92 3.150 22.90    4    2 auto straight
#  5:       Merc 280 19.2   6 167.6 123 3.92 3.440 18.30    4    4 auto straight
#  6:      Merc 280C 17.8   6 167.6 123 3.92 3.440 18.90    4    4 auto straight
#  7:     Merc 450SE 16.4   8 275.8 180 3.07 4.070 17.40    3    3 auto        v
#  8:     Merc 450SL 17.3   8 275.8 180 3.07 3.730 17.60    3    3 auto        v
#  9:    Merc 450SLC 15.2   8 275.8 180 3.07 3.780 18.00    3    3 auto        v
# 10:  Toyota Corona 21.5   4 120.1  97 3.70 2.465 20.01    3    1 auto straight

carsDT[ , if (mean(wt) > 3) .SD, by=.(am, vs) ]

#       am       vs                  rn  mpg cyl  disp  hp drat    wt  qsec gear carb
#  1: auto straight      Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44    3    1
#  2: auto straight             Valiant 18.1   6 225.0 105 2.76 3.460 20.22    3    1
#  3: auto straight           Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00    4    2
#  4: auto straight            Merc 230 22.8   4 140.8  95 3.92 3.150 22.90    4    2
#  5: auto straight            Merc 280 19.2   6 167.6 123 3.92 3.440 18.30    4    4
#  6: auto straight           Merc 280C 17.8   6 167.6 123 3.92 3.440 18.90    4    4
#  7: auto straight       Toyota Corona 21.5   4 120.1  97 3.70 2.465 20.01    3    1
#  8: auto        v   Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02    3    2
#  9: auto        v          Duster 360 14.3   8 360.0 245 3.21 3.570 15.84    3    4
# 10: auto        v          Merc 450SE 16.4   8 275.8 180 3.07 4.070 17.40    3    3
# 11: auto        v          Merc 450SL 17.3   8 275.8 180 3.07 3.730 17.60    3    3
# 12: auto        v         Merc 450SLC 15.2   8 275.8 180 3.07 3.780 18.00    3    3
# 13: auto        v  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.250 17.98    3    4
# 14: auto        v Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82    3    4
# 15: auto        v   Chrysler Imperial 14.7   8 440.0 230 3.23 5.345 17.42    3    4
# 16: auto        v    Dodge Challenger 15.5   8 318.0 150 2.76 3.520 16.87    3    2
# 17: auto        v         AMC Javelin 15.2   8 304.0 150 3.15 3.435 17.30    3    2
# 18: auto        v          Camaro Z28 13.3   8 350.0 245 3.73 3.840 15.41    3    4
# 19: auto        v    Pontiac Firebird 19.2   8 400.0 175 3.08 3.845 17.05    3    2

# sorting by carb decreasing, selecting first row per group
# thus a row with maximum carb within the group
carsDT[ order(-carb), .SD[1L], by=.(am, vs) ]

#        am       vs            rn  mpg cyl  disp  hp drat    wt  qsec gear carb
# 1: manual        v Maserati Bora 15.0   8 301.0 335 3.54 3.570 14.60    5    8
# 2:   auto        v    Duster 360 14.3   8 360.0 245 3.21 3.570 15.84    3    4
# 3:   auto straight      Merc 280 19.2   6 167.6 123 3.92 3.440 18.30    4    4
# 4: manual straight   Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52    4    2

# instead select all row(s) with maximum carb
carsDT[ , .SD[carb == max(carb)], by=.(am, vs) ]

#         am       vs                  rn  mpg cyl  disp  hp drat    wt  qsec gear carb
#  1: manual        v       Maserati Bora 15.0   8 301.0 335 3.54 3.570 14.60    5    8
#  2: manual straight         Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52    4    2
#  3: manual straight        Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90    5    2
#  4: manual straight          Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60    4    2
#  5:   auto straight            Merc 280 19.2   6 167.6 123 3.92 3.440 18.30    4    4
#  6:   auto straight           Merc 280C 17.8   6 167.6 123 3.92 3.440 18.90    4    4
#  7:   auto        v          Duster 360 14.3   8 360.0 245 3.21 3.570 15.84    3    4
#  8:   auto        v  Cadillac Fleetwood 10.4   8 472.0 205 2.93 5.250 17.98    3    4
#  9:   auto        v Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82    3    4
# 10:   auto        v   Chrysler Imperial 14.7   8 440.0 230 3.23 5.345 17.42    3    4
# 11:   auto        v          Camaro Z28 13.3   8 350.0 245 3.73 3.840 15.41    3    4

cond = c("aleph", "baz", "boz") > "b"
which(cond)

# [1] 2 3

x       = 1:5
w_not   = which(x^2 > 50)
w_not

# integer(0)

x[-w_not]

# integer(0)

3.7.3 Sorting

To sort the table in-place, use setorder. And to set a key (which sorts as a side effect), use setkey. When passing columns to sort by programmatically, use setorderv or setkeyv.

For a one-time sort (not altering the order of the object itself), write an order(...) expression in the first argument, like carsDT[order(hp, -carb), j, by=] where j and by= characterize some task to be done with the sorted data.

3.7.4 Examining each variable

summary(airDT)

#      Ozone           Solar.R           Wind             Temp           Month      
#  Min.   :  1.00   Min.   :  7.0   Min.   : 1.700   Min.   :56.00   Min.   :5.000  
#  1st Qu.: 18.00   1st Qu.:115.8   1st Qu.: 7.400   1st Qu.:72.00   1st Qu.:6.000  
#  Median : 31.50   Median :205.0   Median : 9.700   Median :79.00   Median :7.000  
#  Mean   : 42.13   Mean   :185.9   Mean   : 9.958   Mean   :77.88   Mean   :6.993  
#  3rd Qu.: 63.25   3rd Qu.:258.8   3rd Qu.:11.500   3rd Qu.:85.00   3rd Qu.:8.000  
#  Max.   :168.00   Max.   :334.0   Max.   :20.700   Max.   :97.00   Max.   :9.000  
#  NA's   :37       NA's   :7                                                       
#       Day      
#  Min.   : 1.0  
#  1st Qu.: 8.0  
#  Median :16.0  
#  Mean   :15.8  
#  3rd Qu.:23.0  
#  Max.   :31.0  
# 

summary(carsDT)

#       rn                 mpg             cyl             disp             hp       
#  Length:32          Min.   :10.40   Min.   :4.000   Min.   : 71.1   Min.   : 52.0  
#  Class :character   1st Qu.:15.43   1st Qu.:4.000   1st Qu.:120.8   1st Qu.: 96.5  
#  Mode  :character   Median :19.20   Median :6.000   Median :196.3   Median :123.0  
#                     Mean   :20.09   Mean   :6.188   Mean   :230.7   Mean   :146.7  
#                     3rd Qu.:22.80   3rd Qu.:8.000   3rd Qu.:326.0   3rd Qu.:180.0  
#                     Max.   :33.90   Max.   :8.000   Max.   :472.0   Max.   :335.0  
#       drat             wt             qsec            gear            carb      
#  Min.   :2.760   Min.   :1.513   Min.   :14.50   Min.   :3.000   Min.   :1.000  
#  1st Qu.:3.080   1st Qu.:2.581   1st Qu.:16.89   1st Qu.:3.000   1st Qu.:2.000  
#  Median :3.695   Median :3.325   Median :17.71   Median :4.000   Median :2.000  
#  Mean   :3.597   Mean   :3.217   Mean   :17.85   Mean   :3.688   Mean   :2.812  
#  3rd Qu.:3.920   3rd Qu.:3.610   3rd Qu.:18.90   3rd Qu.:4.000   3rd Qu.:4.000  
#  Max.   :4.930   Max.   :5.424   Max.   :22.90   Max.   :5.000   Max.   :8.000  
#       am                 vs           
#  Length:32          Length:32         
#  Class :character   Class :character  
#  Mode  :character   Mode  :character  
#                                       
#                                       
# 

airDT[, .(
  col = names(.SD),
  nna = nna <- colSums(is.na(.SD)),
  nok = .N - nna,
  tot = .N
)]

#        col nna nok tot
# 1:   Ozone  37 116 153
# 2: Solar.R   7 146 153
# 3:    Wind   0 153 153
# 4:    Temp   0 153 153
# 5:   Month   0 153 153
# 6:     Day   0 153 153

split(airDT, by=c("Month")) %>% lapply(summary)
airDT[, .(
  col = names(.SD),
  nna = nna <- colSums(is.na(.SD)),
  nok = .N - nna,
  tot = .N
), by=Month]

# single-valued functions
carsDT[, mean(drat), by=am]

#        am       V1
# 1: manual 4.050000
# 2:   auto 3.286316

# multi-valued functions
carsDT[, quantile(drat, c(.1, .25, .75, .9)) %>% as.list, by=am]

#        am   10%  25%   75%   90%
# 1: manual 3.650 3.85 4.220 4.388
# 2:   auto 2.896 3.07 3.695 3.920

# multiple functions
carsDT[, c(
  .(mean = mean(drat)), 
  quantile(drat, c(.1, .25, .75, .9)) %>% as.list
), by=am]

#        am     mean   10%  25%   75%   90%
# 1: manual 4.050000 3.650 3.85 4.220 4.388
# 2:   auto 3.286316 2.896 3.07 3.695 3.920

carsDT[, unlist(recursive=FALSE, lapply(
  .(med = median, iqr = IQR),
  function(f) lapply(.SD, f)
)), by=.(am), .SDcols=wt:qsec]

#        am med.wt med.qsec iqr.wt iqr.qsec
# 1: manual   2.32    17.02  0.845    2.150
# 2:   auto   3.52    17.82  0.405    1.995

carsDT[, unlist(recursive=FALSE, lapply(
  .SD,
  function(x) lapply(.(med = median, iqr = IQR), function(f) f(x))
)), by=.(am), .SDcols=wt:qsec]

#        am wt.med wt.iqr qsec.med qsec.iqr
# 1: manual   2.32  0.845    17.02    2.150
# 2:   auto   3.52  0.405    17.82    1.995

carsDT[, table(am, useNA = "always")]

# am
#   auto manual   <NA> 
#     19     13      0

# or
carsDT[, .N, by=am]

#        am  N
# 1: manual 13
# 2:   auto 19

carsDT[, .N, by=.(drat_bin = cut(drat, 4))]

#       drat_bin  N
# 1: (3.84,4.39] 11
# 2:  (2.76,3.3] 13
# 3:  (3.3,3.84]  6
# 4: (4.39,4.93]  2

# or
carsDT[, drat %>% cut(4) %>% table ]

# .
#  (2.76,3.3]  (3.3,3.84] (3.84,4.39] (4.39,4.93] 
#          13           6          11           2

carsDT[, drat %>% cut(4) %>% table %>% plot ]

# [1] 1 2 3 4

# or
carsDT[, drat %>% hist(4) %>% {NULL} ]

# NULL

# or
carsDT[, drat %>% stem ]

# 
#   The decimal point is 1 digit(s) to the left of the |
# 
#   26 | 66
#   28 | 3
#   30 | 07778855
#   32 | 13
#   34 | 4
#   36 | 29037
#   38 | 500222
#   40 | 881
#   42 | 22
#   44 | 3
#   46 | 
#   48 | 3

# NULL

# or
carsDT[, drat %>% density %>% plot ]

# NULL

# or
carsDT[, drat %>% boxplot  %>% {NULL} ]

# NULL

3.7.5 Examining variables jointly

carsDT[, var(.SD), .SDcols = sapply(carsDT, is.numeric)]

#              mpg         cyl        disp          hp         drat          wt
# mpg    36.324103  -9.1723790  -633.09721 -320.732056   2.19506351  -5.1166847
# cyl    -9.172379   3.1895161   199.66028  101.931452  -0.66836694   1.3673710
# disp -633.097208 199.6602823 15360.79983 6721.158669 -47.06401915 107.6842040
# hp   -320.732056 101.9314516  6721.15867 4700.866935 -16.45110887  44.1926613
# drat    2.195064  -0.6683669   -47.06402  -16.451109   0.28588135  -0.3727207
# wt     -5.116685   1.3673710   107.68420   44.192661  -0.37272073   0.9573790
# qsec    4.509149  -1.8868548   -96.05168  -86.770081   0.08714073  -0.3054816
# gear    2.135685  -0.6491935   -50.80262   -6.358871   0.27598790  -0.4210806
# carb   -5.363105   1.5201613    79.06875   83.036290  -0.07840726   0.6757903
#              qsec        gear        carb
# mpg    4.50914919   2.1356855 -5.36310484
# cyl   -1.88685484  -0.6491935  1.52016129
# disp -96.05168145 -50.8026210 79.06875000
# hp   -86.77008065  -6.3588710 83.03629032
# drat   0.08714073   0.2759879 -0.07840726
# wt    -0.30548161  -0.4210806  0.67579032
# qsec   3.19316613  -0.2804032 -1.89411290
# gear  -0.28040323   0.5443548  0.32661290
# carb  -1.89411290   0.3266129  2.60887097

# or
carsDT[, cor(.SD), .SDcols = sapply(carsDT, is.numeric)]

#             mpg        cyl       disp         hp        drat         wt        qsec
# mpg   1.0000000 -0.8521620 -0.8475514 -0.7761684  0.68117191 -0.8676594  0.41868403
# cyl  -0.8521620  1.0000000  0.9020329  0.8324475 -0.69993811  0.7824958 -0.59124207
# disp -0.8475514  0.9020329  1.0000000  0.7909486 -0.71021393  0.8879799 -0.43369788
# hp   -0.7761684  0.8324475  0.7909486  1.0000000 -0.44875912  0.6587479 -0.70822339
# drat  0.6811719 -0.6999381 -0.7102139 -0.4487591  1.00000000 -0.7124406  0.09120476
# wt   -0.8676594  0.7824958  0.8879799  0.6587479 -0.71244065  1.0000000 -0.17471588
# qsec  0.4186840 -0.5912421 -0.4336979 -0.7082234  0.09120476 -0.1747159  1.00000000
# gear  0.4802848 -0.4926866 -0.5555692 -0.1257043  0.69961013 -0.5832870 -0.21268223
# carb -0.5509251  0.5269883  0.3949769  0.7498125 -0.09078980  0.4276059 -0.65624923
#            gear       carb
# mpg   0.4802848 -0.5509251
# cyl  -0.4926866  0.5269883
# disp -0.5555692  0.3949769
# hp   -0.1257043  0.7498125
# drat  0.6996101 -0.0907898
# wt   -0.5832870  0.4276059
# qsec -0.2126822 -0.6562492
# gear  1.0000000  0.2740728
# carb  0.2740728  1.0000000

# or
carsDT[, cor(.SD) %>% corrplot::corrplot() %>% {NULL}, .SDcols = sapply(carsDT, is.numeric)]

# NULL

carsDT[, .N, by=.(am, drat_bin = cut(drat, 4))]

#        am    drat_bin  N
# 1: manual (3.84,4.39]  8
# 2:   auto  (2.76,3.3] 13
# 3:   auto  (3.3,3.84]  3
# 4:   auto (3.84,4.39]  3
# 5: manual (4.39,4.93]  2
# 6: manual  (3.3,3.84]  3

# or
carsDT[, table(am, drat_bin = cut(drat, 4))]

#         drat_bin
# am       (2.76,3.3] (3.3,3.84] (3.84,4.39] (4.39,4.93]
#   auto           13          3           3           0
#   manual          0          3           8           2

airDT[, { 
  hexbin::hexbin(Wind, Temp) %>% plot
  NULL 
}]

# NULL

airDT[,  plot(Wind, Temp)]

# NULL

3.7.6 Reshaping to wide

The dcast function is a flexible way of making a table of summary stats. Extending some examples above…

carsDT[, .(.N, mean_hp = mean(hp)), by=.(am, drat_bin = cut(drat, 4))][,
  dcast(.SD, drat_bin ~ am, value.var = c("N", "mean_hp"))
]

#       drat_bin N_auto N_manual mean_hp_auto mean_hp_manual
# 1:  (2.76,3.3]     13       NA     176.9231             NA
# 2:  (3.3,3.84]      3        3     134.6667       207.6667
# 3: (3.84,4.39]      3        8     113.6667       110.3750
# 4: (4.39,4.93]     NA        2           NA        71.5000

So we get row counts and mean horsepower computed by auto/manual and bins of drat (rear-axle ration). Unfortunately, we get NA counts instead of zeros here, but if the goal is simply to explore data, that shouldn’t be a big deal.

The dcast function and its counterpart, melt, are covered in vignette("datatable-reshape"). Also, melt comes up again in 3.8 below.

3.7.7 Exercises

1. With DT = data.table(g = c(1,1,2,2,2,3), v = 1:6), select only g groups having only a single row. Then, instead select only g groups having more than one row.

3.8.1 Removing redundant data

Consider this table of experimental results (see ?datasets::CO2):

carbDT = data.table(CO2)
carbDT %>% print(nrow = 10)

#     Plant        Type  Treatment conc uptake
#  1:   Qn1      Quebec nonchilled   95   16.0
#  2:   Qn1      Quebec nonchilled  175   30.4
#  3:   Qn1      Quebec nonchilled  250   34.8
#  4:   Qn1      Quebec nonchilled  350   37.2
#  5:   Qn1      Quebec nonchilled  500   35.3
# ---                                         
# 80:   Mc3 Mississippi    chilled  250   17.9
# 81:   Mc3 Mississippi    chilled  350   17.9
# 82:   Mc3 Mississippi    chilled  500   17.9
# 83:   Mc3 Mississippi    chilled  675   18.9
# 84:   Mc3 Mississippi    chilled 1000   19.9

Using the tools for inspecting and browsing tables (3.2.1), we can see that each Plant always has the same type and treatment. Reading the docs for the data set, we see that these are in fact attributes of the plant codes.

We can spin these plant code attributes off:

plantDT = unique(carbDT[, .(Plant, Type, Treatment)])
setkey(plantDT, Plant)
carbDT[, c("Type", "Treatment") := NULL ]
setkey(carbDT, Plant, conc)

The unique function drops duplicates. Related tools are discussed in 4.1.

There are a few advantages to having data in this format:

1. The data takes up less space.
2. The relationships among variables are transparent. When building a statistical model, we won’t accidentally forget that Treatment is determined at the Plant level, for example; and we can easily count how many plants have each treatment with plantDT[, table(Treatment)] (see 3.7.4.2).

3. The data will be easier to browse, since we won’t have columns spilling off the right side of the screen whenever inspecting the data; and, with the keys (3.5.8.1), we can subset like…

   carbDT[.("Mc1")]

   #    Plant conc uptake
   # 1:   Mc1   95   10.5
   # 2:   Mc1  175   14.9
   # 3:   Mc1  250   18.1
   # 4:   Mc1  350   18.9
   # 5:   Mc1  500   19.5
   # 6:   Mc1  675   22.2
   # 7:   Mc1 1000   21.9

   carbDT[.("Mc1", 95)]

   #    Plant conc uptake
   # 1:   Mc1   95   10.5

4. While we are editing carbDT in the course of data cleaning or analysis, we no longer have to worry about accidentally introducing inconsistencies in Type and Treatment (causing them to vary within a Plant even though they shouldn’t).
5. Any further Plant-level attributes we need to define (like date the sample was taken) can go into the dedicated table, plantDT, without cluttering up the experimental results in carbDT.

6. Each table has keys that we can characterize and test. To verify that there are no duplicates, for example, we can do

   stopifnot(
     plantDT %>% { .[, .N, by=key(.)][N > 1L, .N == 0L] }
   )
   stopifnot(
     carbDT %>% { .[, .N, by=key(.)][N > 1L, .N == 0L] }
   )

   We are counting rows per value of the key, using the tabulation methods mentioned in 3.7.5.2, DT[, .N, by=key(DT)]; then filtering to key values having more than one row with N > 1; and then asserting that there are no such rows .N == 0L. The stopifnot wrapper is R’s basic tool for assertions.

   Similarly, we can verify that every combination of type and treatment appears in plantDT; and that every combination of Plant and conc appears in carbDT:

   stopifnot(
     plantDT[CJ(Type = Type, Treatment = Treatment, unique = TRUE), on=.(Type, Treatment), .N, by=.EACHI][, all(N >= 1L)]
   )

   We are using CJ to enumerate combinations (see 4.2); then counting matches using merge diagnostics (3.5.6.2); and then asserting that every combo was matched by at least 1 row. The repetition of column names required here is annoying and will hopefully be addressed by nicer syntax soon. For the time being, there’s…

   test_cols = c("Type", "Treatment")
   test_lvls = do.call(CJ, c(plantDT[, ..test_cols], unique=TRUE))
   stopifnot(
     plantDT[test_lvls, on=test_cols, .N, by=.EACHI][, all(N >= 1)]
   )

   do.call and other helpful programming tricks are covered in 3.8.4.1.

Continuing the example, if we had many more types of plants, besides Quebec and Mississippi, we might end up assigning attributes to these as well (like country or latitude). In that case, we would want a further table:

typeDT = data.table(
  Type = c("Quebec", "Mississippi", "Queens", "Montreal"), 
  Country = c("CA", "US", "US", "CA")
)

#           Type Country
# 1:      Quebec      CA
# 2: Mississippi      US
# 3:      Queens      US
# 4:    Montreal      CA

3.8.2 Reshaping to long

Continuing the last example, imagine we started out with the CO2 data set in wide format, with concentrations on columns:

DT = dcast(data.table(CO2), Plant ~ conc, value.var="uptake")

#     Plant   95  175  250  350  500  675 1000
#  1:   Qn1 16.0 30.4 34.8 37.2 35.3 39.2 39.7
#  2:   Qn2 13.6 27.3 37.1 41.8 40.6 41.4 44.3
#  3:   Qn3 16.2 32.4 40.3 42.1 42.9 43.9 45.5
#  4:   Qc1 14.2 24.1 30.3 34.6 32.5 35.4 38.7
#  5:   Qc3 15.1 21.0 38.1 34.0 38.9 39.6 41.4
#  6:   Qc2  9.3 27.3 35.0 38.8 38.6 37.5 42.4
#  7:   Mn3 11.3 19.4 25.8 27.9 28.5 28.1 27.8
#  8:   Mn2 12.0 22.0 30.6 31.8 32.4 31.1 31.5
#  9:   Mn1 10.6 19.2 26.2 30.0 30.9 32.4 35.5
# 10:   Mc2  7.7 11.4 12.3 13.0 12.5 13.7 14.4
# 11:   Mc3 10.6 18.0 17.9 17.9 17.9 18.9 19.9
# 12:   Mc1 10.5 14.9 18.1 18.9 19.5 22.2 21.9

This format is arguably better for browsing (3.7.6), but it will become a hassle if we want to find a quantitative relationship between values of concentration (on the columns) and uptake (in the cells). Stubbornly sticking to this data format often (or maybe always) leads to bad code that “aggregates” over columns or runs separately for each and every row (3.3.1).

To convert to a format amenable to analysis (you know, where numbers are numbers), we use melt:

mDT = melt(DT, id = "Plant", variable.name = "conc", value.var = "uptake", variable.factor = FALSE)
setkey(mDT, Plant, conc)

# convert to numeric
mDT[, conc := as.numeric(conc)]

It is not essential to set keys (3.5.8.1), but it’s fairly low-cost and makes thinking through the structure of the data clearer.

Even when it’s not as egregious as storing numbers as column names, it is very often the case that multiple columns store similar information that really would be more naturally stored in a single column.

Stubbornly sticking to wide form can create problems particularly with dynamic data (that is updated over time). For example, if you have a column for “daily sales online” and another column for “daily sales in-store”, what happens when you must also store data on “sales through our affiliate”? Adding more and more columns on-the-fly is asking for trouble: what class should this new column have? what are the rules for where it can have missing values? is its name consistent with those of related columns (“sales_online”, “sales_offline”, “sales_partner”, “sales_newpartner”)? This problem is what is meant by “minimise redesign when extending” (on Wikipedia’s normalization page).

See vignette("datatable-reshape") for more on reshaping tables.

3.8.3 Preparing data with update joins

The set of relational tables is just to structure the data. During analysis, of course we often need to combine variables from the disparate tables; and for this, an update join will always suffice (3.5.4):

dat = copy(carbDT)
dat[plantDT, on=.(Plant), `:=`(Type = i.Type, Treatment = i.Treatment)]
# then do some analysis with dat

3.8.4 Programming with tables

my_args = c(plantDT[, c("Type", "Treatment")], unique=TRUE)

# $Type
#  [1] Quebec      Quebec      Quebec      Quebec      Quebec      Quebec      Mississippi
#  [8] Mississippi Mississippi Mississippi Mississippi Mississippi
# Levels: Quebec Mississippi
# 
# $Treatment
#  [1] nonchilled nonchilled nonchilled chilled    chilled    chilled    nonchilled
#  [8] nonchilled nonchilled chilled    chilled    chilled   
# Levels: nonchilled chilled
# 
# $unique
# [1] TRUE

do.call(CJ, my_args)

#           Type  Treatment
# 1:      Quebec nonchilled
# 2:      Quebec    chilled
# 3: Mississippi nonchilled
# 4: Mississippi    chilled

# ... does the same as entering each argument manually ...
CJ(Type = plantDT$Type, Treatment = plantDT$Treatment, unique=TRUE)

#           Type  Treatment
# 1:      Quebec nonchilled
# 2:      Quebec    chilled
# 3: Mississippi nonchilled
# 4: Mississippi    chilled

r <- function(d, ...) {
    # verify that the table has a key
    stopifnot(length(key(d)) > 0L)

    # grab the tuple
    tuple = list(...)

    # join
    x = d[tuple, nomatch=0]

    # print
    print(x = x, nrows=Inf, row.names=FALSE)

    # return
    invisible(x)
}

r(carbDT, "Mc1", 1000)

#  Plant conc uptake
#    Mc1 1000   21.9

my_tuple = list("Mc1", 1000)
do.call(r, c(list(d = carbDT), my_tuple))

#  Plant conc uptake
#    Mc1 1000   21.9

# example data
exDT = data.table(CO2)[c(1:2,60,65)]

# programming
newcols = c("ha", "egad")
newvals = list("1", "2")

for (k in newcols) set(DT, j = k, v = newvals[[k]])

# Warning in set(DT, j = k, v = newvals[[k]]): Adding new column 'ha' then assigning NULL
# (deleting it).

# Warning in set(DT, j = k, v = newvals[[k]]): Adding new column 'egad' then assigning NULL
# (deleting it).

# example data
exDT = data.table(CO2)[c(1:2,60,65)]

my_bycol = "Type"
my_sumcol = "uptake"
myj = substitute(sum(my_sumcol), list(my_sumcol = as.name(my_sumcol)))
myj

# sum(uptake)

# eval j expression
exDT[ , eval(myj), by=my_bycol]

#           Type   V1
# 1:      Quebec 46.4
# 2: Mississippi 42.8

# eval entire expression
mycall = as.call(list(as.symbol("["), as.symbol("exDT"), j = myj, by = my_bycol))
mycall

# exDT[j = sum(uptake), by = "Type"]

eval(mycall)

#           Type   V1
# 1:      Quebec 46.4
# 2: Mississippi 42.8

my_subs = list(my_sumcol = "uptake", my_maxcol = "uptake") %>% lapply(as.name)

# with setNames
myj = setNames(
  substitute(.(sum(my_sumcol), max(my_maxcol)), my_subs),
  c("", "mysum", "mymax")
)

# or with a list
prep_expr = function(e, L = my_subs) do.call(substitute, list(e, L))

es = list()
es[["mysum"]] = quote(sum(my_sumcol)) %>% prep_expr
es[["mymax"]] = quote(max(my_maxcol)) %>% prep_expr
myj = as.call(c(as.symbol("."), es))

exDT[, eval(myj), by=Type]

#           Type mysum mymax
# 1:      Quebec  46.4  30.4
# 2: Mississippi  42.8  27.9

exDT[, sum(get(my_sumcol)), by=my_bycol]

#           Type   V1
# 1:      Quebec 46.4
# 2: Mississippi 42.8

dat = copy(carbDT)
up_cols = setdiff(names(plantDT), "Plant")
dat[plantDT, on=.(Plant), (up_cols) := mget(sprintf("i.%s", up_cols))]

.Options %>% .[grep("datatable", names(.))]

# $datatable.verbose
# [1] FALSE
# 
# $datatable.nomatch
# [1] NA
# 
# $datatable.optimize
# [1] Inf
# 
# $datatable.print.nrows
# [1] 100
# 
# $datatable.print.topn
# [1] 5
# 
# $datatable.print.class
# [1] FALSE
# 
# $datatable.print.rownames
# [1] TRUE
# 
# $datatable.allow.cartesian
# [1] FALSE
# 
# $datatable.dfdispatchwarn
# [1] TRUE
# 
# $datatable.warnredundantby
# [1] TRUE
# 
# $datatable.alloccol
# [1] 1024
# 
# $datatable.integer64
# [1] "integer64"
# 
# $datatable.showProgress
# [1] TRUE
# 
# $datatable.auto.index
# [1] TRUE
# 
# $datatable.use.index
# [1] TRUE
# 
# $datatable.fread.datatable
# [1] TRUE
# 
# $datatable.fread.dec.experiment
# [1] TRUE
# 
# $datatable.fread.dec.locale
# [1] "French_France.1252"
# 
# $datatable.old.unique.by.key
# [1] FALSE

DT[, {
  ...
  do stuff
  ...
}, by=.(x, y, z)]

DT[, if (.GRP == 1L) {
  ...
  do stuff
  ...
}, by=.(x, y, z)]