Introduction to data.table

Note that:

• Unlike data.frames, columns of character type are never converted to factors by default.

• Row numbers are printed with a : in order to visually separate the row number from the first column.

• When the number of rows to print exceeds the global option datatable.print.nrows (default = 100), it automatically prints only the top 5 and bottom 5 rows (as can be seen in the Data section). If you’ve had a lot of experience with data.frames, you may have found yourself waiting around while larger tables print-and-page, sometimes seemingly endlessly. You can query the default number like so:

  getOption("datatable.print.nrows")

• data.table doesn’t set or use row names, ever. We will see why in the “Keys and fast binary search based subset” vignette.

The way to read it (out loud) is:

Take DT, subset/reorder rows using i, then calculate j, grouped by by.

Let’s begin by looking at i and j first - subsetting rows and operating on columns.

• Within the frame of a data.table, columns can be referred to as if they are variables, much like in SQL or Stata. Therefore, we simply refer to origin and month as if they are variables. We do not need to add the prefix flights$ each time. Nevertheless, using flights$origin and flights$month would work just fine.

• The row indices that satisfy the condition origin == "JFK" & month == 6L are computed, and since there is nothing else left to do, all columns from flights at rows corresponding to those row indices are simply returned as a data.table.

• A comma after the condition in i is not required. But flights[origin == "JFK" & month == 6L, ] would work just fine. In data.frames, however, the comma is necessary.

• In this case, there is no condition. The row indices are already provided in i. We therefore return a data.table with all columns from flights at rows for those row indices.

order() is internally optimised

• We can use “-” on a character columns within the frame of a data.table to sort in decreasing order.

• In addition, order(...) within the frame of a data.table uses data.table’s internal fast radix order forder(). This sort provided such a compelling improvement over R’s base::order that the R project adopted the data.table algorithm as its default sort in 2016 for R 3.3.0, see ?sort and the R Release NEWS.

We will discuss data.table’s fast order in more detail in the data.table internals vignette.

• Since columns can be referred to as if they are variables within the frame of data.tables, we directly refer to the variable we want to subset. Since we want all the rows, we simply skip i.

• It returns all the rows for the column arr_delay.

• We wrap the variables (column names) within list(), which ensures that a data.table is returned. In case of a single column name, not wrapping with list() returns a vector instead, as seen in the previous example.

• data.table also allows wrapping columns with .() instead of list(). It is an alias to list(); they both mean the same. Feel free to use whichever you prefer; we have noticed most users seem to prefer .() for conciseness, so we will continue to use .() hereafter.

data.tables (and data.frames) are internally lists as well, with the stipulation that each element has the same length and the list has a class attribute. Allowing j to return a list enables converting and returning data.table very efficiently.

• Wrap both columns within .(), or list(). That’s it.

What’s happening here?

• data.table’s j can handle more than just selecting columns - it can handle expressions, i.e., computing on columns. This shouldn’t be surprising, as columns can be referred to as if they are variables. Then we should be able to compute by calling functions on those variables. And that’s what precisely happens here.

• We first subset in i to find matching row indices where origin airport equals "JFK", and month equals 6L. We do not subset the entire data.table corresponding to those rows yet.

• Now, we look at j and find that it uses only two columns. And what we have to do is to compute their mean(). Therefore we subset just those columns corresponding to the matching rows, and compute their mean().

Because the three main components of the query (i, j and by) are together inside [...], data.table can see all three and optimise the query altogether before evaluation, not each separately. We are able to therefore avoid the entire subset (i.e., subsetting the columns besides arr_delay and dep_delay), for both speed and memory efficiency.

Special symbol .N:

.N is a special built-in variable that holds the number of observations in the current group. It is particularly useful when combined with by as we’ll see in the next section. In the absence of group by operations, it simply returns the number of rows in the subset.

So we can now accomplish the same task by using .N as follows:

ans <- flights[origin == "JFK" & month == 6L, .N]
ans
# [1] 8422

• Once again, we subset in i to get the row indices where origin airport equals “JFK”, and month equals 6.

• We see that j uses only .N and no other columns. Therefore the entire subset is not materialised. We simply return the number of rows in the subset (which is just the length of row indices).

• Note that we did not wrap .N with list() or .(). Therefore a vector is returned.

We could have accomplished the same operation by doing nrow(flights[origin == "JFK" & month == 6L]). However, it would have to subset the entire data.table first corresponding to the row indices in i and then return the rows using nrow(), which is unnecessary and inefficient. We will cover this and other optimisation aspects in detail under the data.table design vignette.

• We know .N is a special variable that holds the number of rows in the current group. Grouping by origin obtains the number of rows, .N, for each group.

• By doing head(flights) you can see that the origin airports occur in the order “JFK”, “LGA” and “EWR”. The original order of grouping variables is preserved in the result. This is important to keep in mind!

• Since we did not provide a name for the column returned in j, it was named N automatically by recognising the special symbol .N.

• by also accepts a character vector of column names. This is particularly useful for coding programmatically, e.g., designing a function with the grouping columns as a (character vector) function argument.

• When there’s only one column or expression to refer to in j and by, we can drop the .() notation. This is purely for convenience. We could instead do:

  ans <- flights[, .N, by = origin]
  ans
  #    origin     N
  # 1:    JFK 81483
  # 2:    LGA 84433
  # 3:    EWR 87400

  We’ll use this convenient form wherever applicable hereafter.

• We first obtain the row indices for the expression carrier == "AA" from i.

• Using those row indices, we obtain the number of rows while grouped by origin. Once again no columns are actually materialised here, because the j-expression does not require any columns to be actually subsetted and is therefore fast and memory efficient.

• by accepts multiple columns. We just provide all the columns by which to group by. Note the use of .() again in by – again, this is just shorthand for list(), and list() can be used here as well. Again, we’ll stick with .() in this vignette.

• Since we did not provide column names for the expressions in j, they were automatically generated as V1 and V2.

• Once again, note that the input order of grouping columns is preserved in the result.

Now what if we would like to order the result by those grouping columns origin, dest and month?

• All we did was to change by to keyby. This automatically orders the result by the grouping variables in increasing order. In fact, due to the internal implementation of by first requiring a sort before recovering the original table’s order, keyby is typically faster than by because it doesn’t require this second step.

Keys: Actually keyby does a little more than just ordering. It also sets a key after ordering by setting an attribute called sorted.

We’ll learn more about keys in the Keys and fast binary search based subset vignette; for now, all you have to know is that you can use keyby to automatically order the result by the columns specified in by.

• Recall that we can use - on a character column in order() within the frame of a data.table. This is possible to due data.table’s internal query optimisation.

• Also recall that order(...) with the frame of a data.table is automatically optimised to use data.table’s internal fast radix order forder() for speed.

But this requires having to assign the intermediate result and then overwriting that result. We can do one better and avoid this intermediate assignment to a temporary variable altogether by chaining expressions.

ans <- flights[carrier == "AA", .N, by = .(origin, dest)][order(origin, -dest)]
head(ans, 10)
#     origin dest    N
#  1:    EWR  PHX  121
#  2:    EWR  MIA  848
#  3:    EWR  LAX   62
#  4:    EWR  DFW 1618
#  5:    JFK  STT  229
#  6:    JFK  SJU  690
#  7:    JFK  SFO 1312
#  8:    JFK  SEA  298
#  9:    JFK  SAN  299
# 10:    JFK  ORD  432

• We can tack expressions one after another, forming a chain of operations, i.e., DT[ ... ][ ... ][ ... ].

• Or you can also chain them vertically:

  DT[ ...
     ][ ...
       ][ ...
         ]

• The last row corresponds to dep_delay > 0 = TRUE and arr_delay > 0 = FALSE. We can see that 26593 flights started late but arrived early (or on time).

• Note that we did not provide any names to by-expression. Therefore, names have been automatically assigned in the result. As with j, you can name these expressions as you would elements of any list, e.g. DT[, .N, .(dep_delayed = dep_delay>0, arr_delayed = arr_delay>0)].

• You can provide other columns along with expressions, for example: DT[, .N, by = .(a, b>0)].

Special symbol .SD:

data.table provides a special symbol, called .SD. It stands for Subset of Data. It by itself is a data.table that holds the data for the current group defined using by.

Recall that a data.table is internally a list as well with all its columns of equal length.

Let’s use the data.table DT from before to get a glimpse of what .SD looks like.

DT
#    ID a  b  c
# 1:  b 1  7 13
# 2:  b 2  8 14
# 3:  b 3  9 15
# 4:  a 4 10 16
# 5:  a 5 11 17
# 6:  c 6 12 18

DT[, print(.SD), by = ID]
#    a b  c
# 1: 1 7 13
# 2: 2 8 14
# 3: 3 9 15
#    a  b  c
# 1: 4 10 16
# 2: 5 11 17
#    a  b  c
# 1: 6 12 18
# Empty data.table (0 rows and 1 cols): ID

• .SD contains all the columns except the grouping columns by default.

• It is also generated by preserving the original order - data corresponding to ID = "b", then ID = "a", and then ID = "c".

To compute on (multiple) columns, we can then simply use the base R function lapply().

DT[, lapply(.SD, mean), by = ID]
#    ID   a    b    c
# 1:  b 2.0  8.0 14.0
# 2:  a 4.5 10.5 16.5
# 3:  c 6.0 12.0 18.0

• .SD holds the rows corresponding to columns a, b and c for that group. We compute the mean() on each of these columns using the already-familiar base function lapply().

• Each group returns a list of three elements containing the mean value which will become the columns of the resulting data.table.

• Since lapply() returns a list, so there is no need to wrap it with an additional .() (if necessary, refer to this tip).

We are almost there. There is one little thing left to address. In our flights data.table, we only wanted to calculate the mean() of two columns arr_delay and dep_delay. But .SD would contain all the columns other than the grouping variables by default.

.SDcols

Using the argument .SDcols. It accepts either column names or column indices. For example, .SDcols = c("arr_delay", "dep_delay") ensures that .SD contains only these two columns for each group.

Similar to part g), you can also provide the columns to remove instead of columns to keep using - or ! sign as well as select consecutive columns as colA:colB and deselect consecutive columns as !(colA:colB) or -(colA:colB).

Now let us try to use .SD along with .SDcols to get the mean() of arr_delay and dep_delay columns grouped by origin, dest and month.

flights[carrier == "AA",                       ## Only on trips with carrier "AA"
        lapply(.SD, mean),                     ## compute the mean
        by = .(origin, dest, month),           ## for every 'origin,dest,month'
        .SDcols = c("arr_delay", "dep_delay")] ## for just those specified in .SDcols
#      origin dest month  arr_delay  dep_delay
#   1:    JFK  LAX     1   6.590361 14.2289157
#   2:    LGA  PBI     1  -7.758621  0.3103448
#   3:    EWR  LAX     1   1.366667  7.5000000
#   4:    JFK  MIA     1  15.720670 18.7430168
#   5:    JFK  SEA     1  14.357143 30.7500000
#  ---                                        
# 196:    LGA  MIA    10  -6.251799 -1.4208633
# 197:    JFK  MIA    10  -1.880184  6.6774194
# 198:    EWR  PHX    10  -3.032258 -4.2903226
# 199:    JFK  MCO    10 -10.048387 -1.6129032
# 200:    JFK  DCA    10  16.483871 15.5161290

• .SD is a data.table that holds all the rows for that group. We simply subset the first two rows as we have seen here already.

• For each group, head(.SD, 2) returns the first two rows as a data.table, which is also a list, so we do not have to wrap it with .().

• That’s it. There is no special syntax required. All we need to know is the base function c() which concatenates vectors and the tip from before.

• Here, we first concatenate the values with c(a,b) for each group, and wrap that with list(). So for each group, we return a list of all concatenated values.

• Note those commas are for display only. A list column can contain any object in each cell, and in this example, each cell is itself a vector and some cells contain longer vectors than others.

Once you start internalising usage in j, you will realise how powerful the syntax can be. A very useful way to understand it is by playing around, with the help of print().

For example:

## (1) look at the difference between
DT[, print(c(a,b)), by = ID]
# [1] 1 2 3 7 8 9
# [1]  4  5 10 11
# [1]  6 12
# Empty data.table (0 rows and 1 cols): ID

## (2) and
DT[, print(list(c(a,b))), by = ID]
# [[1]]
# [1] 1 2 3 7 8 9
# 
# [[1]]
# [1]  4  5 10 11
# 
# [[1]]
# [1]  6 12
# Empty data.table (0 rows and 1 cols): ID

In (1), for each group, a vector is returned, with length = 6,4,2 here. However (2) returns a list of length 1 for each group, with its first element holding vectors of length 6,4,2. Therefore (1) results in a length of 6+4+2 = 12, whereas (2) returns 1+1+1=3.