data.table Syntax & Keys

Maya's single bakery has grown into a chain. Her till now logs hundreds of thousands of sales a week across every shop, and the tidy dplyr pipelines that felt instant on one shop's data have started to crawl. data.table is R's tool for exactly this moment: the same filter, compute and group you already know, written in one compact bracket and engineered to fly on millions of rows.

Below is a week of sales from two of her shops. Press Run to keep just the Austin rows, your first look at the bracket in action.

By the end of this lesson you will be able to:

• Read the DT[i, j, by] grammar and name what each of the three slots does
• Filter rows in i, compute and select columns in j, and aggregate per group with by
• Set a key with setkey() and use it for lightning-fast lookups and joins

Prerequisites: you can run R, and you know the dplyr verbs and the pipe from The dplyr Verbs. We define every data.table idea by translating its dplyr equivalent, so no prior data.table experience is needed.

DT[i, j, by]: take rows, do something, by group

Almost everything in data.table happens inside one square bracket with three slots, read left to right like a sentence: DT[i, j, by] means "take the rows i, compute j, grouped by by."

• **i* = which rows* (a condition, like dplyr's filter()).
• **j* = what to compute* (select columns, build new ones, or summarise them, like select() plus mutate() plus summarise()).
• **by* = grouped how* (split into groups and run j inside each, like group_by()).

Let us build Maya's sales as a data.table. Each lesson runs in a fresh R session, so we create the data right here. The one setup line that matters is setDTthreads(1): it just tells data.table to use a single core, which is all the in-browser R has.

A data.table is also a data frame, so anything that expects a data frame still works. The difference is everything you can now do inside that bracket. The flow below is the map we will follow for the rest of the lesson.

i: keep the rows you want

The i slot is the row filter. Put a condition there and data.table keeps the rows where it is TRUE. For a pure row filter you do not even need the comma: sales[shop == "Austin"] is complete on its own.

It is the same idea as filter(sales, shop == "Austin") in dplyr, only shorter: the table name sits outside the bracket, the condition goes inside. Use == (two equals signs) to test a value, & for "and", | for "or", and %in% to test membership, for example sales[shop %in% c("Austin", "Denver")].

Drag your eye down the widget: the rows that fail units > 25 are struck out, the busy three remain.

j: select and compute columns

Where i picks rows, the j slot says what to do with the columns. Wrap your request in .(), data.table's shorthand for "make a table from these". Name bare columns to select them, or write an expression to compute a new one:

The leading comma matters: it leaves i empty (all rows) and moves your request into j. So sales[, .(item, revenue)] is "all rows, these two columns", the data.table way of writing select(sales, item, revenue).

There is also a second, faster way to add a column. The := operator (read "colon-equals") adds or updates a column by reference, meaning it writes straight into the existing table instead of building a fresh copy. On a million-row table that saved copy is a large chunk of the speed and memory you came to data.table for.

Which slot does which job?

Maya wants the total revenue of the Austin shops only, a single number. In DT[i, j, by], where does the Austin filter go, and where does sum(revenue) go?

by: one answer per group

So far j has worked on the whole table at once. Add the by slot and data.table runs your j separately inside each group, then stacks the answers. This is the classic split-apply-combine pattern, and it is dplyr's group_by() plus summarise() folded into the same bracket.

Two shops in, two rows out: each is one shop's totals. The special symbol .N is the number of rows in the current group, the quickest way to count, so n = .N tells us each shop made four sales. You can group by several columns at once with by = .(shop, item), and if you want the result sorted by the grouping column, use keyby = instead of by =.

Put i, j and by together

Now combine all three slots in one bracket. Maya wants total revenue per item, for the Austin shops only. The i filter and the j summary are written for you; fill in the grouping slot so the sum is computed once per item.

sales[shop == "Austin", .(revenue = sum(revenue)), by = item]
#>         item revenue
#> 1: Sourdough     171
#> 2:     Bagel      60
#> 3: Croissant     108

Keys: sort once, then search

Everything so far works on any size of table. But Maya looks up one shop's sales over and over, and on a million-row table each sales[shop == "Austin"] has to scan every row to find the matches. A key removes that cost.

setkey(DT, shop) sorts the whole table by shop, one time. Once the rows are sorted, finding a shop is like finding a name in a phone book: you do not read every entry, you open to the middle, decide which half to keep, and repeat. That is a binary search.

Here is the difference made concrete. An unsorted scan checks all \(n\) rows, so its cost grows in step with the data: \(O(n)\). A binary search on the sorted key throws away half the remaining rows at every step, so it finds the block in about \(\log_2 n\) hops: \(O(\log n)\). For a million rows that is the difference between a million comparisons and about twenty.

Your exact times will vary, but the shape holds: the keyed lookup stays fast as the table grows, while the plain scan slows in step with the row count. The sort is a one-time cost you pay with setkey; every lookup afterwards is cheap.

Instant lookups and fast joins

With a key set, two everyday jobs get much faster. The first is lookup: pass the key value straight into i and data.table binary-searches for it. sales["Austin"] means "the rows whose key is Austin", and sales["Austin", sum(revenue)] filters and sums in one fast step.

The second is joins. A keyed join, written X[Y], looks each row of Y up in the keyed table X by binary search. Give every sale its shop's region by keying a small lookup table and joining:

Because the key is sorted, that join never scans; it binary-searches, which is what makes data.table joins scale to tables far too large for a plain merge.

What happens on each lookup?

You run setkey(sales, shop) once, then look up sales["Austin"] many times over the course of your analysis. What does data.table do on each of those lookups?

References

A few authoritative places to take this further:

• Introduction to data.table (CRAN vignette) - the canonical first tour of the DT[i, j, by] grammar, by the package authors.
• Keys and fast binary-search subset (CRAN vignette) - exactly the keys topic from this lesson, with the binary-search story in full.
• Reference semantics (CRAN vignette) - what := (modify by reference) really does, and why it saves the copy.
• data.table official documentation - the complete function reference, news and cheat sheet.

Lesson 1 complete

You can now read and write the data.table bracket. You learned the DT[i, j, by] grammar, i to filter rows, j to select and compute columns (and := to add one by reference, no copy), and by to summarise per group, plus keys: setkey() sorts a table once so lookups and joins become a fast binary search instead of a full scan.

Next, Lesson 2: dplyr vs data.table, head to head. You will write the same task in both, see exactly where data.table's speed and memory savings come from, and learn how to keep dplyr's readable syntax while getting data.table's speed underneath with dtplyr.