The data.table Format

DT[i, j, by] 

Using i:

• We can subset rows similar to a data.frame- except you don't have to use DT$ repetitively since columns within the frame of a data.table are seen as if they are variables.

• We can also sort a data.table using order(), which internally uses data.table's fast order for performance.

We can do much more in i by keying a data.table, which allows blazing fast subsets and joins. We will see this in the "Keys and fast binary search based subsets" and "Joins and rolling joins" vignette.

Using j:

1. Select columns the data.table way: DT[, .(colA, colB)].

2. Select columns the data.frame way: DT[, c("colA", "colB")].

3. Compute on columns: DT[, .(sum(colA), mean(colB))].

4. Provide names if necessary: DT[, .(sA =sum(colA), mB = mean(colB))].

5. Combine with i: DT[colA > value, sum(colB)].
   
   

Using by:

• Using by, we can group by columns by specifying a list of columns or a character vector of column names or even expressions. The flexibility of j, combined with by and i makes for a very powerful syntax.

• by can handle multiple columns and also expressions.

• We can keyby grouping columns to automatically sort the grouped result.

• We can use .SD and .SDcols in j to operate on multiple columns using already familiar base functions. Here are some examples:

  1. DT[, lapply(.SD, fun), by = ..., .SDcols = ...] - applies fun to all columns specified in .SDcols while grouping by the columns specified in by.

  2. DT[, head(.SD, 2), by = ...] - return the first two rows for each group.

  3. DT[col > val, head(.SD, 1), by = ...] - combine i along with j and by.
     
     

And remember the tip:

As long as j returns a list, each element of the list will become a column in the resulting data.table.

We will see how to add/update/delete columns by reference and how to combine them with i and by in the next vignette.