How to: use data.table for more efficient data management

· 5 min read · Tutorials

How to: use data.table for more efficient data management

TL;DR: data.table is a high-performance R package for fast data manipulation. Offers concise syntax, minimal memory footprint, and 10-100x speedups over base R and dplyr for large datasets. Essential for genomics and single-cell analysis workflows.

What is data.table?

data.table is an R package that extends data.frame with:

  • Speed: In-place operations avoid unnecessary copies
  • Memory efficiency: Minimal allocation overhead
  • Concise syntax: DT[i, j, by] does filtering, computing, and grouping in one line
  • Large data support: Handles datasets larger than RAM with on-disk processing

When to use data.table

Use Case data.table Alternatives
Large files (>1GB) ✅ Fast fread() base R: slow
Grouped operations ✅ Optimized by dplyr: similar
Memory-constrained ✅ In-place updates tidyverse: copies
Interactive analysis ⚠️ Less readable tidyverse: pipe-friendly

Speed Benchmark

Installation

Standard Installation (Linux/Windows)

install.packages("data.table")

macOS Installation (with OpenMP Support)

macOS requires manual OpenMP configuration for multi-threading:

Step 1: Install OpenMP Runtime

brew update && brew install libomp

Step 2: Configure R Makevars

mkdir -p ~/.R
nano ~/.R/Makevars

Add to the end of the file:

LDFLAGS += -L/opt/homebrew/opt/libomp/lib -lomp
CPPFLAGS += -I/opt/homebrew/opt/libomp/include -Xclang -fopenmp

Step 3: Install data.table

# Remove old version if present
remove.packages("data.table")

# Install stable version
install.packages("data.table")

# Or development version:
# install.packages("data.table", type = "source",
#   repos = "https://Rdatatable.gitlab.io/data.table")

Verify installation:

library(data.table)
getDTthreads()  # Should show >1 if OpenMP enabled

Core Operations

Reading and Writing Files

data.table uses fread() and fwrite() for fast I/O:

library(data.table)

# Fast read (auto-detects separator, headers, types)
dt <- fread("counts.csv")

# Fast write
fwrite(dt, "output.csv")

# Read compressed files
dt <- fread("data.csv.gz")

# Control column types
dt <- fread("data.csv", colClasses = c(gene_id = "character", count = "integer"))

Speed comparison:

  • fread() is 5-10x faster than read.csv()
  • fwrite() handles millions of rows in seconds

Basic Syntax: DT[i, j, by]

Part Purpose Example
i Filter rows DT[count > 10]
j Select/compute columns DT[, .(gene, logFC)]
by Group operations DT[, mean(count), by = sample]

Filtering Rows

# Single condition
dt[count > 100]

# Multiple conditions
dt[count > 100 & padj < 0.05]

# By row number
dt[1:10]

# Remove rows
dt[gene_id != "ERCC-00002"]

Selecting and Computing Columns

# Select columns
dt[, .(gene_id, count)]

# Compute new column
dt[, log_count := log2(count + 1)]  # := modifies in-place

# Multiple computations
dt[, `:=`(
  log_count = log2(count + 1),
  norm_count = count / sum(count)
)]

# Summary statistics
dt[, .(mean_count = mean(count), n_genes = .N)]

Key difference: := modifies the table in-place (no copy), .() returns a new table.

Grouping Operations

# Group by single column
dt[, .(mean_expr = mean(count)), by = cell_type]

# Multiple grouping variables
dt[, .(total = sum(count)), by = .(sample, cell_type)]

# Group and filter
dt[, .SD[count == max(count)], by = gene_id]  # Top expressed cell per gene

Joining Tables

# Load annotation
anno <- fread("gene_annotations.csv")

# Left join (keeps all dt rows)
dt_anno <- anno[dt, on = "gene_id"]

# Inner join (only matching rows)
dt_anno <- dt[anno, on = "gene_id", nomatch = NULL]

# Join on multiple keys
merge(dt, anno, by = c("gene_id", "transcript_id"))

Reshaping Data

# Wide to long
dt_long <- melt(dt, id.vars = "gene_id", 
                measure.vars = c("sample1", "sample2", "sample3"),
                variable.name = "sample", value.name = "count")

# Long to wide
dt_wide <- dcast(dt_long, gene_id ~ sample, value.var = "count")

Common Bioinformatics Tasks

Normalize RNA-seq Counts

# TPM normalization
dt[, tpm := (count / gene_length) / sum(count / gene_length) * 1e6, 
   by = sample]

# DESeq2-style size factors
dt[, size_factor := sum(count) / median(sum(count)), by = sample]
dt[, norm_count := count / size_factor]

Filter Low-Expression Genes

# Keep genes with >10 counts in at least 3 samples
dt[, n_detected := sum(count > 10), by = gene_id]
dt_filt <- dt[n_detected >= 3]

Calculate Fold Changes

# Compare treatment vs control
dt_wide <- dcast(dt, gene_id ~ condition, value.var = "count")
dt_wide[, log2FC := log2((treatment + 1) / (control + 1))]

Performance Tips

  1. Use := for in-place updates (avoid copies)

    dt[, new_col := value]  # Fast
dt <- dt[, .(old_col, new_col = value)]  # Slower (creates copy)

  2. Set keys for fast subsetting

    setkey(dt, gene_id)
dt["GAPDH"]  # Binary search, O(log n)

  3. Enable multi-threading

    setDTthreads(8)  # Use 8 cores

  4. Use .SD for subsets within groups

    dt[, .SD[1], by = gene_id]  # First row per gene

Troubleshooting

OpenMP not working on macOS

Symptom: getDTthreads() returns 1
Fix: Reinstall following the macOS installation steps above

"Column X not found"

Cause: Typo or trying to use a column before creating it
Fix: Check column names with names(dt)

Memory errors with large files

Solutions:

# Read subset of rows
dt <- fread("big.csv", nrows = 1000)

# Read subset of columns
dt <- fread("big.csv", select = c("gene_id", "count"))

# Increase memory limit
options(datatable.fread.memory = 16)  # GB

data.table vs dplyr

Feature data.table dplyr
Speed Faster (especially large data) Fast
Memory Minimal (in-place updates) Higher (copies)
Syntax Compact DT[i, j, by] Pipe-friendly %>%
Learning curve Steeper Gentler
Best for Performance-critical workflows Readable scripts

Use both: setDT() converts tibbles to data.tables without copying.

Resources

See also:

Originally posted on WeChat Official Account: bioinfo_studyroom

R Data Management