data.table

RaukR 2026 • Data Science With R

Markus Mayrhofer

18-Aug-2026

This session

data.table is an R package for working with rectangular / tabular data.

  1. Demonstrate the core table operations: choose rows, choose columns, add columns, group, summarise, join.
  2. Compare the same task in three styles: base R, tidyverse, and data.table.
  3. See what happens under the hood: copies, memory, indexing, keys.
  4. Simulate large biological data where the differences are visible.

Work through the companion lab, which contains the executable examples, benchmarks, and exercises.

Where data.table comes from

  • Created by Matt Dowle; version 1.0 on CRAN in April 2006. Long co-developed with Arun Srinivasan, now maintained by a wider team.
  • Born from a practical problem: base R’s data.frame copies data on many operations, which is slow and memory-hungry on large tables.
  • Written largely in C, multi-threaded, changes columns by reference (no copy), and ships fast I/O (fread / fwrite).
  • Deliberately few dependencies (just base R), part of why it stays fast and stable.

data.table has a terse DT[i, j, by] syntax, which is what we will demonstrate.

A table is just rows, columns, groups, joins

Rows are observations, columns are variables, a cell is one value. Almost every data-wrangling question is one of four actions:

Question Operation
Which rows do I keep? filter rows
Which columns do I keep or compute? select / mutate columns
Should I calculate separately for each group? group by
Do I need information from another table? join

Today we express each of these three ways and compare them.

A bioinformatics data example.

Genetic data produces large tables frequently. We’ll simulate a data set.

Term Meaning in this session
sample one person or specimen
variant one genomic position where samples may differ
genotype alternate copies: 0, 1, or 2; -1 = missing
read depth how many sequencing reads cover a site
genotype quality confidence score for the call
allele frequency average alternate-copy frequency across samples

Three linked tables

  • samples: one row per sample (population, sex, batch, case/control)
  • variants: one row per variant (chrom, position, gene, consequence, frequency)
  • genotypes: one row per sample x variant (genotype, read depth, quality) – the tall table
dat <- simulate_genotyping(
  n_samples = 500, 
  n_variants = 4000
)
samples <- dat$samples
variants <- dat$variants

# 500 x 4000 = 2,000,000 rows
genotypes <- dat$genotypes
sample_id population sex batch phenotype
S00001 AMR M batch02 0
S00002 AMR M batch01 1
S00003 EUR F batch02 0
S00004 SAS F batch04 1
S00005 EAS M batch08 0
S00006 AFR F batch01 1
S00007 SAS M batch02 0
S00008 EUR F batch04 0
S00009 EAS M batch08 0
S00010 EAS M batch08 0
S00011 AFR F batch06 0
S00012 EAS F batch06 0
S00013 AMR F batch06 1
S00014 EUR M batch06 0
S00015 AFR M batch03 1
S00016 AMR M batch02 0
S00017 AMR F batch08 0
S00018 EUR M batch01 0
S00019 AFR F batch02 0
S00020 EAS F batch07 1
S00021 AMR M batch01 1
S00022 EUR M batch08 0
S00023 AMR M batch07 0
S00024 AMR F batch02 1
S00025 EUR M batch04 0
S00026 AFR M batch04 0
S00027 AFR M batch08 0
S00028 AMR F batch07 1
S00029 AFR M batch06 1
S00030 SAS M batch01 1
S00031 SAS M batch02 0
S00032 SAS F batch05 1
S00033 AFR F batch01 0
S00034 EAS F batch01 1
S00035 EUR F batch08 0
S00036 SAS M batch08 1
S00037 EUR F batch02 0
S00038 EUR M batch04 0
S00039 AMR M batch01 0
S00040 EAS F batch06 1
S00041 AFR M batch06 1
S00042 AFR M batch03 1
S00043 EUR F batch06 0
S00044 AMR F batch01 0
S00045 AFR M batch08 0
S00046 AMR M batch04 1
S00047 AMR M batch07 0
S00048 EAS F batch01 1
S00049 AMR F batch08 1
S00050 EAS M batch06 1
variant_id chrom pos ref alt gene consequence maf
rs0000001 15 111135951 A G GENE1396 synonymous 0.2884
rs0000002 2 10625958 C A GENE0574 intron 0.0026
rs0000003 13 135971202 A T GENE1463 UTR 0.1125
rs0000004 12 101422249 A G GENE0484 missense 0.0942
rs0000005 4 143031874 A T GENE0708 UTR 0.0591
rs0000006 7 181175595 A G GENE0704 intron 0.0042
rs0000007 13 225373737 T A GENE1149 synonymous 0.0012
rs0000008 19 10848723 T G GENE0324 synonymous 0.0864
rs0000009 13 154522921 C A GENE1028 UTR 0.0099
rs0000010 16 91071495 T G GENE1103 intron 0.0874
rs0000011 10 153277053 A G GENE1364 intron 0.0150
rs0000012 1 100276956 A G GENE0822 synonymous 0.0108
rs0000013 16 159517668 T C GENE1209 UTR 0.2404
rs0000014 9 216972735 G C GENE0897 missense 0.0604
rs0000015 3 249064281 A G GENE0948 intron 0.0003
rs0000016 1 210817043 C T GENE0214 UTR 0.0379
rs0000017 12 53373471 G T GENE0646 missense 0.1148
rs0000018 7 134224354 A G GENE0721 intron 0.0311
rs0000019 15 132977020 T A GENE0863 missense 0.0324
rs0000020 9 153977025 G T GENE0540 synonymous 0.1463
rs0000021 2 147145040 C A GENE0835 splice_region 0.0652
rs0000022 14 153043575 G A GENE0989 intron 0.0348
rs0000023 21 86310888 G T GENE0955 intron 0.4010
rs0000024 12 156734205 A G GENE0268 UTR 0.1066
rs0000025 18 57988561 G T GENE1269 splice_region 0.0226
rs0000026 12 124086243 G A GENE1319 synonymous 0.0195
rs0000027 22 39823716 G T GENE0077 intron 0.0073
rs0000028 11 151488330 T G GENE0266 missense 0.0174
rs0000029 9 139916046 A G GENE0879 synonymous 0.1047
rs0000030 3 185266005 A T GENE0240 UTR 0.3632
rs0000031 9 249235125 C A GENE0274 UTR 0.1469
rs0000032 1 85977598 C T GENE1039 missense 0.1816
rs0000033 14 128104822 A G GENE0681 synonymous 0.0585
rs0000034 19 131021380 C T GENE1201 intron 0.0294
rs0000035 12 143593405 G C GENE0782 UTR 0.0005
rs0000036 22 245611570 A C GENE0985 missense 0.0612
rs0000037 14 177156395 C G GENE0326 stop_gained 0.2537
rs0000038 12 73985740 T A GENE0317 missense 0.0099
rs0000039 20 76593177 C T GENE0457 intron 0.0460
rs0000040 16 105605523 T C GENE0818 synonymous 0.0018
rs0000041 X 128771315 T A GENE0217 missense 0.0085
rs0000042 22 152958161 C G GENE0978 splice_region 0.0166
rs0000043 4 148276502 C A GENE1182 missense 0.0004
rs0000044 13 96561914 C G GENE1125 intron 0.1140
rs0000045 5 55269828 A G GENE1067 synonymous 0.1336
rs0000046 4 133657392 G A GENE0050 splice_region 0.5933
rs0000047 20 2563553 T A GENE0158 missense 0.0315
rs0000048 1 166129758 C T GENE1266 intron 0.1518
rs0000049 13 71004039 A G GENE0017 UTR 0.1029
rs0000050 9 4804813 T G GENE0511 intron 0.0118
sample_id variant_id genotype read_depth genotype_quality
S00001 rs0000001 0 27 48
S00001 rs0000002 0 25 53
S00001 rs0000003 1 33 43
S00001 rs0000004 0 27 44
S00001 rs0000005 -1 36 29
S00001 rs0000006 0 34 42
S00001 rs0000007 0 25 47
S00001 rs0000008 0 29 45
S00001 rs0000009 0 38 46
S00001 rs0000010 0 31 55
S00001 rs0000011 0 23 40
S00001 rs0000012 0 28 51
S00001 rs0000013 0 35 50
S00001 rs0000014 0 28 45
S00001 rs0000015 0 38 43
S00001 rs0000016 1 41 51
S00001 rs0000017 1 32 37
S00001 rs0000018 0 25 33
S00001 rs0000019 0 34 53
S00001 rs0000020 0 35 39
S00001 rs0000021 0 23 46
S00001 rs0000022 0 24 45
S00001 rs0000023 1 31 35
S00001 rs0000024 0 37 46
S00001 rs0000025 0 34 37
S00001 rs0000026 0 20 33
S00001 rs0000027 0 36 54
S00001 rs0000028 0 23 45
S00001 rs0000029 0 28 60
S00001 rs0000030 2 30 51
S00001 rs0000031 1 33 49
S00001 rs0000032 1 32 49
S00001 rs0000033 0 29 43
S00001 rs0000034 0 36 50
S00001 rs0000035 0 38 42
S00001 rs0000036 0 28 33
S00001 rs0000037 0 34 54
S00001 rs0000038 0 29 48
S00001 rs0000039 0 35 54
S00001 rs0000040 0 30 35
S00001 rs0000041 0 23 49
S00001 rs0000042 0 22 48
S00001 rs0000043 0 24 46
S00001 rs0000044 0 28 53
S00001 rs0000045 0 28 46
S00001 rs0000046 1 33 45
S00001 rs0000047 0 32 48
S00001 rs0000048 1 36 49
S00001 rs0000049 0 21 53
S00001 rs0000050 0 26 48

A table is just rows, columns, groups, joins

Rows are observations, columns are variables, a cell is one value. Almost every data-wrangling question is one of four actions:

Question Operation
Which rows do I keep? filter rows
Which columns do I keep or compute? select / mutate columns
Should I calculate separately for each group? group by
Do I need information from another table? join

Today we express each of these three ways and compare them.

data.table syntax: DT[i, j, by]

DT[ i ,  j ,  by ]
|  |    |    `-- group by these columns
|  |    `------- compute, select, or update columns
|  `------------ keep or match these rows
`--------------- the data.table object

Read it as:

Take rows i, do j, separately within each by group.

No grouping? Leave by out. No row filter? Leave i empty but keep the comma: DT[, j].

.N = rows in the current group. .() = shorthand for list().

Filter rows

Task: keep called homozygous-alt rows with adequate depth.

# base R
genotypes_df[genotypes_df$genotype == 2 & genotypes_df$read_depth >= 20, ]

# tidyverse
genotypes_df |> filter(genotype == 2, read_depth >= 20)

# data.table -- the row condition goes in i
genotypes[genotype == 2 & read_depth >= 20]

Under the hood: all three build a TRUE/FALSE vector over the rows and keep the TRUE ones. All three return a new table – the original is untouched – so you pay memory for the rows you keep. You typically assign the returned table a name.

Select columns

Task: keep only a few of the variant columns.

# select a few columns
variants_df[, c("variant_id", "gene", "maf")]      # base R
variants_df |> select(variant_id, gene, maf)       # tidyverse
variants[, .(variant_id, gene, maf)]               # data.table

Under the hood: all three return a new table object – but data vectors are not duplicated until necessary.

Create columns

# add a column

# base R: adds the column, rebinds the name
variants_df$is_rare <- variants_df$maf < 0.01               

# tidyverse: you must assign/name the result
variants_df <- variants_df |> mutate(is_rare = maf < 0.01)  

# data.table: modifies in place, no assignment
variants[, is_rare := maf < 0.01]
Style Adding a column
base R shallow copy: columns are shared until modified, then only that column is duplicated
tidyverse same, but returns a new object – you must assign it
data.table := no new object at all; every reference sees the change

:= needs no assignment and changes the original in place. Convenient, but every other reference to that table sees the change. Use copy(variants) to avoid.

Grouped summaries

Task: per sample, mean read depth among called genotypes.

# base R
called <- genotypes_df[genotypes_df$genotype >= 0, ]
aggregate(read_depth ~ sample_id, data = called, FUN = mean)

# tidyverse
genotypes_df |> filter(genotype >= 0) |>
  group_by(sample_id) |> summarise(mean_depth = mean(read_depth))

# data.table -- i filters, j computes, by groups
genotypes[genotype >= 0, .(mean_depth = mean(read_depth)), by = sample_id]

Split by group, compute within each group, combine. data.table does this with low overhead, especially when the data are already keyed.

Joins: add columns from another table

# base R
merge(genotypes_df, variants_df[, c("variant_id","consequence","gene")], 
        by = "variant_id")

# tidyverse
genotypes_df |> inner_join(select(variants_df, variant_id, consequence, gene), 
        by = "variant_id")

# data.table -- x[y, on = "key"]
variants[genotypes, on = "variant_id",
         .(sample_id, variant_id, genotype, consequence, gene)]

Keys: sort once, look up fast

A key is one or more columns a data.table is physically sorted by.

setkey(genotypes, sample_id)   # reorders genotypes in place, by reference
key(genotypes)                 # "sample_id"

Because the rows are sorted, data.table can use binary search and sorted-merge joins instead of scanning every row:

genotypes["S00001"]            # fast lookup of one sample's rows
setkey(variants, variant_id)   # then a keyed join is a fast sorted merge

A key speeds up lookups, repeated joins, and grouping on the key column. You do not always need one (on = joins ad-hoc), but set a key when you will hit the same column many times, so the sort is paid once.

Benchmarks: time and memory

bench::mark() records elapsed time, memory allocated, and garbage collections.

bench::mark(
  base_R     = { ... tapply(...) ... },
  tidyverse  = genotypes_df |> filter(genotype >= 0) |>
                 group_by(variant_id) |> summarise(af = sum(genotype)/(2*n())),
  data.table = genotypes[genotype >= 0, .(af = sum(genotype)/(2*.N)), by = variant_id],
  check = FALSE, iterations = 3
)
  • Time matters because slow code interrupts your work.
  • Memory matters because large tables can fail when R runs out of RAM.

What to expect

  • On a few thousand rows, all three are fast enough: pick your preference.
  • The gap grows with row count, repeated joins, grouped calculations, and in-place updates.
  • data.table often wins on time and/or memory at scale, and fread/fwrite are the most reliable everyday win.

Takeaways

  • Every table operation is about rows, columns, groups, or joins.
  • data.table packs them into DT[i, j, by].
  • := modifies by reference: saves memory, changes the original.
  • Keys sort/index tables for fast repeated joins and lookups.

Next: the lab, then vignette("datatable-intro") and ?data.table.

Thank you!

Questions?

2026 • SciLifeLab • NBIS • RaukR