Data Table

data.table

High-performance data manipulation.

Basics

library(data.table)

# Create
dt  3]
dt[x > 3 & y == "a"]
dt[x %in% c(1, 2, 3)]
dt[x %between% c(2, 4)]
dt[y %like% "^a"]

# Order
dt[order(x)]
dt[order(-x)]
dt[order(x, -y)]

# First/last rows
dt[1:5]
dt[.N]  # Last row
dt[(.N-4):.N]  # Last 5 rows

Column Operations (j)

# Select columns
dt[, .(x, y)]
dt[, x]  # Returns vector
dt[, .(x)]  # Returns data.table
dt[, c("x", "y"), with = FALSE]

# Compute
dt[, .(mean_x = mean(x), sum_y = sum(y))]
dt[, .(total = x + y)]

# Modify in place (:=)
dt[, z := x * 2]
dt[, c("a", "b") := .(x + 1, y)]
dt[, `:=`(a = x + 1, b = y)]

# Remove columns
dt[, z := NULL]
dt[, c("a", "b") := NULL]

# Special symbols
dt[, .N]  # Number of rows
dt[, .I]  # Row indices
dt[, .SD]  # Subset of data
dt[, .SDcols]  # Columns in .SD

Grouping (by)

# Group by
dt[, .(mean = mean(x)), by = category]
dt[, .(mean = mean(x)), by = .(cat1, cat2)]

# keyby (sorted)
dt[, .(mean = mean(x)), keyby = category]

# .SD operations
dt[, lapply(.SD, mean), by = category]
dt[, lapply(.SD, mean), by = category, .SDcols = c("x", "y")]
dt[, lapply(.SD, mean), by = category, .SDcols = is.numeric]

# .N by group
dt[, .N, by = category]

Keys and Joins

# Set key
setkey(dt, id)
setkeyv(dt, c("id1", "id2"))

# Join
dt1[dt2, on = "id"]  # Right join
dt2[dt1, on = "id"]  # Left join
dt1[dt2, on = "id", nomatch = 0]  # Inner join

# Multiple keys
dt1[dt2, on = .(a, b)]
dt1[dt2, on = .(a = x, b = y)]

# Non-equi joins
dt1[dt2, on = .(x >= a, x  0][order(-y)][, .(mean = mean(x)), by = cat]

# Or with line breaks
dt[x > 0
  ][order(-y)
  ][, .(mean = mean(x)), by = cat]

Performance

# Set index (secondary key)
setindex(dt, col)

# fread/fwrite (fast I/O)
dt <- fread("file.csv")
fwrite(dt, "output.csv")

# Modify by reference
set(dt, i = 1L, j = "x", value = 100)
setnames(dt, "old", "new")
setcolorder(dt, c("b", "a", "c"))

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: LeoLin990405
• Source: LeoLin990405/r-analytics-skill
• License: MIT

Install and usage instructions live in the source repository linked above.