Fast Data with data.table

Reviewed & published by Brayan K

data.table is a high-performance R package that supercharges the data frame, using a compact dt[i, j, by] bracket syntax to filter, compute, and group huge datasets quickly and with very little memory.

Part of the free R course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

By the end of this lesson you'll read the dt[i, j, by] grammar, aggregate by group, update columns in place with :=, use .N and .SD, load files fast with fread, and know when data.table beats dplyr.

What You'll Learn in This Lesson

• How the dt[i, j, by] grammar maps to filter/compute/group
• How to aggregate by group inside j
• How := updates columns in place by reference
• What .N and .SD give you per group
• How fread loads big files fast
• When data.table outpaces dplyr

1️⃣ The dt[i, j, by] Grammar

Everything in data.table happens inside square brackets with up to three slots. The first, i, filters rows. The second, j, selects or computes columns. The third, by, groups the computation. Read dt[i, j, by] as "take rows i, do j, for each by."

# data.table extends data.frame with a fast, concise bracket syntax.
library(data.table)

# Build one directly, or convert with as.data.table(df)
dt <- data.table(
  city  = c("NYC", "LA", "NYC", "LA", "NYC"),
  month = c("Jan", "Jan", "Feb", "Feb", "Mar"),
  sales = c(100, 80, 120, 90, 110)
)

# The grammar is  dt[ i , j , by ]:
#   i  = which ROWS  (filter)
#   j  = what to DO with columns (select / compute)
#   by = grouping variable(s)
#
# Read it as: "take rows i, compute j, grouped by by."

dt[city == "NYC"]            # i only: rows where city is NYC
dt[, sales]                  # j only: pull the sales column
dt[city == "NYC", sales]     # i + j: NYC sales

2️⃣ Aggregating with by, .N, and .SD

Put a summary function in j and a grouping column in by to aggregate. Wrap results in .() to name them. The special symbol .N counts rows per group, and .SD lets you apply a function across many columns at once.

library(data.table)

# Aggregation lives in j, grouped by 'by'. .N is a built-in row count.

# Total sales per city
dt[, sum(sales), by = city]

# Name the result column and add a count
dt[, .(total = sum(sales), n = .N), by = city]

# Filter THEN aggregate in one expression:
dt[month != "Jan", .(avg = mean(sales)), by = city]

# Sort with order() inside i:
dt[order(-sales)]            # highest sales first

# .SD is the Subset of Data for each group — apply a function to many columns:
dt[, lapply(.SD, mean), by = city, .SDcols = "sales"]

3️⃣ In-Place Updates and Fast I/O

The := operator adds or changes columns by reference — modifying the table in place with no copy, which is what makes data.table so memory-efficient on large data. For getting big files in and out, fread() and fwrite() are far faster than base R.

library(data.table)

# := updates columns IN PLACE — no copy, very fast and memory-light.

# Add a new column
dt[, tax := sales * 0.08]

# Update only some rows (note: combine i and := )
dt[city == "LA", sales := sales * 1.1]   # 10% bump for LA

# Add several columns at once
dt[, c("hi", "lo") := .(sales * 1.2, sales * 0.8)]

# Remove a column by assigning NULL
dt[, tax := NULL]

# Because := changes dt by reference, you do NOT write  dt <- dt[...].
# Just  dt[..., := ...]  and dt is already updated.

library(data.table)

# fread() reads CSV/TSV files dramatically faster than read.csv,
# auto-detecting separators, types, and headers.

# big <- fread("transactions.csv")           # from a file
# web <- fread("https://example.com/data.csv")  # straight from a URL

# It returns a data.table, ready for dt[i, j, by] immediately:
# big[amount > 1000, .(total = sum(amount)), by = region]

# fwrite() is the matching fast writer:
# fwrite(dt, "out.csv")

# On millions of rows, fread + data.table aggregation can be many times
# faster and use far less memory than base R or dplyr — that's the whole point.

Your turn. Fill in the ___ blank and run it.

# 🎯 YOUR TURN — replace each ___ then run it.
library(data.table)

dt <- data.table(
  team  = c("A", "B", "A", "B", "A"),
  pts   = c(10, 7, 12, 9, 8)
)

# 1) Filter to team A only (i slot)
aOnly <- dt[team == "A"]

# 2) Total points per team: name it 'total', group by team
totals <- dt[, .(total = sum(pts)), by = ___]   # 👉 replace ___ with team

# 3) Add an in-place column 'bonus' equal to pts + 5 (use :=)
dt[, bonus := pts + 5]

print(totals)
print(dt)

# 👉 totals should show team A = 30, team B = 16,
#    and dt should now have a 'bonus' column.

Mini-Challenge: Sales Pipeline

Build a small end-to-end pipeline: add a computed column in place, aggregate by group, filter, and count with .N — all in data.table's bracket grammar.

# 🎯 MINI-CHALLENGE: a mini sales pipeline with data.table.
#
# library(data.table)
# orders <- data.table(
#   region = c("N","S","N","S","N","S"),
#   item   = c("pen","pen","cup","cup","pen","cup"),
#   qty    = c(5, 3, 2, 4, 6, 1),
#   price  = c(2, 2, 5, 5, 2, 5)
# )
#
# 1. Add a 'revenue' column in place:  orders[, revenue := qty * price]
# 2. Total revenue per region (name it 'rev', group by region).
# 3. Filter to qty > 2, then average price per item.
# 4. Use .N to count how many orders each region placed.
#
# Bonus: chain operations  dt[...][...]  to filter then aggregate
#        in a single readable line.

# your code here

Common Errors (and the fix)

• Re-assigning a := result — ❌ dt <- dt[, x := 1] is redundant and confusing. ✅ Just dt[, x := 1].
• Forgetting .() to name columns — ❌ dt[, sum(sales), by = city] names the column V1. ✅ dt[, .(total = sum(sales)), by = city].
• Treating it exactly like a data.frame — ❌ dt[, "col"] behaves differently. ✅ Use dt[, col] or dt[["col"]].
• Unexpected shared edits — ❌ two names pointing at one table both change. ✅ Use copy() when you need an independent table.

Pro Tips

• 💡 Set a key with setkey(dt, city) for lightning-fast filtering and joins on that column.
• 💡 Chain brackets: dt[i, j, by][order(...)] filters, aggregates, and sorts in one line.
• 💡 Best of both: dtplyr lets you write dplyr verbs that execute on a data.table backend.

📋 Quick Reference — data.table

🎉 Lesson Complete!

• ✅ dt[i, j, by] = filter rows, compute columns, group
• ✅ Put summary functions in j, grouping in by
• ✅ := updates columns in place, by reference
• ✅ .N counts rows; .SD applies functions across columns
• ✅ fread() loads big files fast; data.table wins on speed and memory

Practice quiz

Continue this course

• Previous: Time Series (ts & forecasting basics)
• Next: Interactive Apps with Shiny — Build reactive web apps with a ui, a server, and inputs/outputs
• Quick reference: R cheat sheet

Frequently asked questions

What does the dt[i, j, by] syntax really mean?

It's data.table's whole grammar in one bracket. The first slot i selects rows (like a WHERE clause), the second slot j says what to compute or which columns to return (like SELECT), and the third slot by groups the calculation (like GROUP BY). So dt[sales > 100, mean(price), by = city] reads as 'among rows where sales exceed 100, compute the mean price, for each city.' Once this clicks, most data manipulation collapses into a single, fast expression.

Why does := change my table without an assignment arrow?

The := operator updates a data.table by reference, meaning it modifies the existing object in memory rather than building a copy. That's why you write dt[, newcol := x] and not dt <- dt[, newcol := x] — the original dt is already changed. This is the source of data.table's speed and low memory use on large data, but it also means you should be deliberate: if you need to preserve the original, copy() it first.

What are .N and .SD?

.N is a built-in giving the number of rows in the current group (or the whole table), so dt[, .N, by = city] counts rows per city. .SD ('Subset of Data') is the data.table of the other columns for each group, letting you apply a function across many columns at once: dt[, lapply(.SD, mean), by = city] takes the mean of every column within each city. Pair it with .SDcols to limit which columns .SD includes.

When should I choose data.table over dplyr?

Reach for data.table when performance matters: it shines on large datasets (millions of rows), heavy grouping and aggregation, and tight-memory situations, thanks to in-place updates and a highly optimized C backend. fread is also the fastest CSV reader in R. dplyr, by contrast, prioritizes readability and a consistent verb-based pipeline that's easier to teach and read. Many analysts learn dplyr first and switch to data.table for speed-critical work; the dtplyr package even lets you write dplyr code that runs on a data.table backend.