`data.table` vs. `base` vs. `dplyr`

This page presents a side-by-side comparison of common data manipulation operations in R in three idioms: data.table, base, and dplyr. This allows you to compare syntax and understand how to accomplish tasks across these popular frameworks.

This reference guide covers everything from basic filtering and sorting to advanced operations like joins and reshaping data. Many of these examples were originally crafted by Atrebas. They were then reorganized and augmented with base examples by a team of contributors.

To begin, we create example data. The base R data frame is called DF, the data.table table is called DT, and the dplyr tibble is called TB. Data creation is wrapped in a refresh_data() function, which is called periodically throughout the document to ensure that the data is reset after modifications.

refresh_data = function() {
    DT <<- data.table(
        V1 = rep(1:2, 5)[-10],
        V2 = 1:9,
        V3 = c(0.5, 1.0, 1.5),
        V4 = rep(LETTERS[1:3], 3)
    )

    DF <<- data.frame(
        V1 = rep(1:2, 5)[-10],
        V2 = 1:9,
        V3 = c(0.5, 1.0, 1.5),
        V4 = rep(LETTERS[1:3], 3)
    )

    TB <<- tibble(
        V1 = rep(1:2, 5)[-10],
        V2 = 1:9,
        V3 = rep(c(0.5, 1.0, 1.5), 3),
        V4 = rep(LETTERS[1:3], 3)
    )
}

refresh_data()

When using the let() and set*() functions or := operator modifies a data.table “in place,” which means that it does not copy the object at all. This is more efficient than re-assigning the entire data set. However, when modified in place, the data table will not be printed immediately to the console after modification. You must call the object again to see the changes.

Filter

Integer index

Positive indices keep the specified rows.

data.table

DT[3:4,]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     3   1.5      C
2:     2     4   0.5      A

base

DF[3:4,]

  V1 V2  V3 V4
3  1  3 1.5  C
4  2  4 0.5  A

dplyr

TB |> slice(3:4)

# A tibble: 2 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     3   1.5 C    
2     2     4   0.5 A

Negative indices exclude the specified rows.

data.table

DT[-(3:7),]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     2   1.0      B
3:     2     8   1.0      B
4:     1     9   1.5      C

base

DF[-(3:7),]

  V1 V2  V3 V4
1  1  1 0.5  A
2  2  2 1.0  B
8  2  8 1.0  B
9  1  9 1.5  C

dplyr

TB |> slice(-(3:7))

# A tibble: 4 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     2   1   B    
3     2     8   1   B    
4     1     9   1.5 C

Logical index

Filter rows using a logical vector. Keep the rows where the condition is TRUE. %chin% is a fast version of %in%, optimized for strings. %like% is a convenient operator for regular expression matches.

data.table

DT[V2 > 5]
DT[V4 %chin% c("A", "C")] # faster than %in% for strings
DT[V4 %like% c("A|C")]    # regular expressions

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     2     6   1.5      C
2:     1     7   0.5      A
3:     2     8   1.0      B
4:     1     9   1.5      C

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     1     3   1.5      C
3:     2     4   0.5      A
4:     2     6   1.5      C
5:     1     7   0.5      A
6:     1     9   1.5      C

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     1     3   1.5      C
3:     2     4   0.5      A
4:     2     6   1.5      C
5:     1     7   0.5      A
6:     1     9   1.5      C

base

subset(DF, V2 > 5)
subset(DF, V4 %in% c("A", "C"))
subset(DF, grepl("A|C", V4))

  V1 V2  V3 V4
6  2  6 1.5  C
7  1  7 0.5  A
8  2  8 1.0  B
9  1  9 1.5  C

  V1 V2  V3 V4
1  1  1 0.5  A
3  1  3 1.5  C
4  2  4 0.5  A
6  2  6 1.5  C
7  1  7 0.5  A
9  1  9 1.5  C

  V1 V2  V3 V4
1  1  1 0.5  A
3  1  3 1.5  C
4  2  4 0.5  A
6  2  6 1.5  C
7  1  7 0.5  A
9  1  9 1.5  C

dplyr

TB |> filter(V2 > 5)
TB |> filter(V4 %in% c("A", "C"))
TB |> filter(grepl("A|C", V4))

# A tibble: 4 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     2     6   1.5 C    
2     1     7   0.5 A    
3     2     8   1   B    
4     1     9   1.5 C

# A tibble: 6 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     1     3   1.5 C    
3     2     4   0.5 A    
4     2     6   1.5 C    
5     1     7   0.5 A    
6     1     9   1.5 C

# A tibble: 6 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     1     3   1.5 C    
3     2     4   0.5 A    
4     2     6   1.5 C    
5     1     7   0.5 A    
6     1     9   1.5 C

Filter rows based on multiple conditions.

data.table

DT[V1 == 1 & V4 == "A"]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     1     7   0.5      A

base

subset(DF, V1 == 1 & V4 == "A")

  V1 V2  V3 V4
1  1  1 0.5  A
7  1  7 0.5  A

dplyr

TB |> filter(V1 == 1, V4 == "A")

# A tibble: 2 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     1     7   0.5 A

Unique

data.table

unique(DT)
unique(DT, by = c("V1", "V4"))

Indices: <V4>, <V4__V1>
      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     2   1.0      B
3:     1     3   1.5      C
4:     2     4   0.5      A
5:     1     5   1.0      B
6:     2     6   1.5      C
7:     1     7   0.5      A
8:     2     8   1.0      B
9:     1     9   1.5      C

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     2   1.0      B
3:     1     3   1.5      C
4:     2     4   0.5      A
5:     1     5   1.0      B
6:     2     6   1.5      C

base

DF[!duplicated(DF), ]
DF[!duplicated(DF[c("V1", "V4")]), ]

  V1 V2  V3 V4
1  1  1 0.5  A
2  2  2 1.0  B
3  1  3 1.5  C
4  2  4 0.5  A
5  1  5 1.0  B
6  2  6 1.5  C
7  1  7 0.5  A
8  2  8 1.0  B
9  1  9 1.5  C

  V1 V2  V3 V4
1  1  1 0.5  A
2  2  2 1.0  B
3  1  3 1.5  C
4  2  4 0.5  A
5  1  5 1.0  B
6  2  6 1.5  C

dplyr

TB |> distinct()
TB |> distinct(V1, V4, .keep_all = TRUE)

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     2   1   B    
3     1     3   1.5 C    
4     2     4   0.5 A    
5     1     5   1   B    
6     2     6   1.5 C    
7     1     7   0.5 A    
8     2     8   1   B    
9     1     9   1.5 C

# A tibble: 6 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     2   1   B    
3     1     3   1.5 C    
4     2     4   0.5 A    
5     1     5   1   B    
6     2     6   1.5 C

Missing values

Drop rows with missing values in specified columns.

data.table

na.omit(DT, cols = 1:4)

Indices: <V4>, <V4__V1>
      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     2   1.0      B
3:     1     3   1.5      C
4:     2     4   0.5      A
5:     1     5   1.0      B
6:     2     6   1.5      C
7:     1     7   0.5      A
8:     2     8   1.0      B
9:     1     9   1.5      C

base

DF[complete.cases(DF[, 1:4]), ]

  V1 V2  V3 V4
1  1  1 0.5  A
2  2  2 1.0  B
3  1  3 1.5  C
4  2  4 0.5  A
5  1  5 1.0  B
6  2  6 1.5  C
7  1  7 0.5  A
8  2  8 1.0  B
9  1  9 1.5  C

dplyr

TB |> tidyr::drop_na(1:4)

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     2   1   B    
3     1     3   1.5 C    
4     2     4   0.5 A    
5     1     5   1   B    
6     2     6   1.5 C    
7     1     7   0.5 A    
8     2     8   1   B    
9     1     9   1.5 C

Random sample

Draw a random sample of rows.

data.table

DT[sample(.N, 3)]
DT[sample(.N, .N / 2)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     3   1.5      C
2:     1     7   0.5      A
3:     1     9   1.5      C

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     2     6   1.5      C
2:     1     3   1.5      C
3:     1     1   0.5      A
4:     2     8   1.0      B

base

DF[sample(nrow(DF), 3), ]
DF[sample(nrow(DF), nrow(DF) / 2), ]

  V1 V2  V3 V4
3  1  3 1.5  C
5  1  5 1.0  B
8  2  8 1.0  B

  V1 V2  V3 V4
7  1  7 0.5  A
1  1  1 0.5  A
4  2  4 0.5  A
8  2  8 1.0  B

dplyr

TB |> slice_sample(n = 3)
TB |> slice_sample(prop = 0.5)

# A tibble: 3 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     2     2   1   B    
2     1     5   1   B    
3     1     9   1.5 C

# A tibble: 4 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     2     4   0.5 A    
2     2     8   1   B    
3     1     3   1.5 C    
4     1     5   1   B

Other

data.table

DT[V2 %between% c(3, 5)]
DT[data.table::between(V2, 3, 5, incbounds = FALSE)]
DT[V2 %inrange% list(-1:1, 1:3)]

base

subset(DF, grepl("^B", V4))
subset(DF, V2 >= 3 & V2 <= 5)
subset(DF, V2 > 3 & V2 < 5)
subset(DF, V2 %in% c(-1:1, 1:3))

dplyr

TB |> filter(grepl("^B", V4))
TB |> filter(dplyr::between(V2, 3, 5))
TB |> filter(V2 > 3 & V2 < 5)
TB |> filter(V2 >= -1:1 & V2 <= 1:3)

Sort

Rows

Sort rows in ascending order.

data.table

DT[order(V3)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     4   0.5      A
3:     1     7   0.5      A
4:     2     2   1.0      B
5:     1     5   1.0      B
6:     2     8   1.0      B
7:     1     3   1.5      C
8:     2     6   1.5      C
9:     1     9   1.5      C

base

sort_by(DF, ~V3)

  V1 V2  V3 V4
1  1  1 0.5  A
4  2  4 0.5  A
7  1  7 0.5  A
2  2  2 1.0  B
5  1  5 1.0  B
8  2  8 1.0  B
3  1  3 1.5  C
6  2  6 1.5  C
9  1  9 1.5  C

dplyr

TB |> arrange(V3)

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     4   0.5 A    
3     1     7   0.5 A    
4     2     2   1   B    
5     1     5   1   B    
6     2     8   1   B    
7     1     3   1.5 C    
8     2     6   1.5 C    
9     1     9   1.5 C

Sort rows in decreasing order.

data.table

DT[order(-V3)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     3   1.5      C
2:     2     6   1.5      C
3:     1     9   1.5      C
4:     2     2   1.0      B
5:     1     5   1.0      B
6:     2     8   1.0      B
7:     1     1   0.5      A
8:     2     4   0.5      A
9:     1     7   0.5      A

base

sort_by(DF, ~list(-V3))

  V1 V2  V3 V4
3  1  3 1.5  C
6  2  6 1.5  C
9  1  9 1.5  C
2  2  2 1.0  B
5  1  5 1.0  B
8  2  8 1.0  B
1  1  1 0.5  A
4  2  4 0.5  A
7  1  7 0.5  A

dplyr

TB |> arrange(desc(V3))

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     3   1.5 C    
2     2     6   1.5 C    
3     1     9   1.5 C    
4     2     2   1   B    
5     1     5   1   B    
6     2     8   1   B    
7     1     1   0.5 A    
8     2     4   0.5 A    
9     1     7   0.5 A

Sort rows by multiple columns.

data.table

DT[order(V1, -V2)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     9   1.5      C
2:     1     7   0.5      A
3:     1     5   1.0      B
4:     1     3   1.5      C
5:     1     1   0.5      A
6:     2     8   1.0      B
7:     2     6   1.5      C
8:     2     4   0.5      A
9:     2     2   1.0      B

base

sort_by(DF, ~list(V1, -V2))

  V1 V2  V3 V4
9  1  9 1.5  C
7  1  7 0.5  A
5  1  5 1.0  B
3  1  3 1.5  C
1  1  1 0.5  A
8  2  8 1.0  B
6  2  6 1.5  C
4  2  4 0.5  A
2  2  2 1.0  B

dplyr

TB |> arrange(V1, desc(V2))

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     9   1.5 C    
2     1     7   0.5 A    
3     1     5   1   B    
4     1     3   1.5 C    
5     1     1   0.5 A    
6     2     8   1   B    
7     2     6   1.5 C    
8     2     4   0.5 A    
9     2     2   1   B

Sort dataset and the column passed in argument becomes key. Output is an object the same type as the object indexed. This code is not executed to avoid sorting the original data set in place.

data.table

setorder(DT, V4, -V1)
setorderv(DT, c("V4", "V1"), c(1, -1))

base

DF = DF[order(DF$V4, -DF$V1), ]

dplyr

TB = TB |> arrange(V4, desc(V1))

refresh_data()

Columns

Reorders the columns of a dataset. This code is not executed to avoid reordering columns in the original data set.

data.table

setcolorder(DT, c("V4", "V1", "V2"))
DT

       V4    V1    V2    V3
   <char> <int> <int> <num>
1:      A     1     1   0.5
2:      B     2     2   1.0
3:      C     1     3   1.5
4:      A     2     4   0.5
5:      B     1     5   1.0
6:      C     2     6   1.5
7:      A     1     7   0.5
8:      B     2     8   1.0
9:      C     1     9   1.5

base

DF = DF[, c("V4", "V1", "V2")]
DF

dplyr

TB = TB |> select(V4, V1, V2)
TB

# A tibble: 9 × 3
  V4       V1    V2
  <chr> <int> <int>
1 A         1     1
2 B         2     2
3 C         1     3
4 A         2     4
5 B         1     5
6 C         2     6
7 A         1     7
8 B         2     8
9 C         1     9

refresh_data()

Select

Keep

Extract one column as a vector.

data.table

# DT[["V3"]]
# DT[, V3]
DT[[3]]

[1] 0.5 1.0 1.5 0.5 1.0 1.5 0.5 1.0 1.5

base

# DF[["V3"]]
# DF[, 3, drop = TRUE]
DF[[3]]

[1] 0.5 1.0 1.5 0.5 1.0 1.5 0.5 1.0 1.5

dplyr

# TB[["V3"]]
# TB |> pull(V3)
TB[[3]]

[1] 0.5 1.0 1.5 0.5 1.0 1.5 0.5 1.0 1.5

Extract one column as a data frame.

data.table

# DT[, .SD, .SDcols = "V3"]
DT[, "V3"]

      V3
   <num>
1:   0.5
2:   1.0
3:   1.5
4:   0.5
5:   1.0
6:   1.5
7:   0.5
8:   1.0
9:   1.5

base

# DF[, "V3", drop = FALSE]
DF[, 3, drop = FALSE]

dplyr

# TB |> select(V3)
TB[, "V3"]

# A tibble: 9 × 1
     V3
  <dbl>
1   0.5
2   1  
3   1.5
4   0.5
5   1  
6   1.5
7   0.5
8   1  
9   1.5

Select several columns by column names.

data.table

DT[, .(V2, V3, V4)]
DT[, V2:V4]
DT[, .SD, .SDcols = V2:V4]
DT[, .SD, .SDcols = c("V2", "V3", "V4")]
cols = c("V2", "V3")
DT[, ..cols]

base

DF[, c("V2", "V3", "V4")]
subset(DF, select = c("V2", "V3", "V4"))
cols = c("V2", "V3")
DF[, cols]
DF[ , names(DF) %in% cols]

dplyr

TB |> select(V2, V3, V4)
TB |> select(V2:V4)
TB |> select(any_of(c("V2", "V3", "V4")))
cols = c("V2", "V3")
DF |> select(!!cols)

Drop

Exclude several columns by column name.

data.table

# DT[, .SD, .SDcols = !c("V2", "V3")]
DT[, !c("V2", "V3")]

      V1     V4
   <int> <char>
1:     1      A
2:     2      B
3:     1      C
4:     2      A
5:     1      B
6:     2      C
7:     1      A
8:     2      B
9:     1      C

base

DF[ , !(names(DF) %in% c("V2", "V3"))]

dplyr

TB |> select(-V2, -V3)

# A tibble: 9 × 2
     V1 V4   
  <int> <chr>
1     1 A    
2     2 B    
3     1 C    
4     2 A    
5     1 B    
6     2 C    
7     1 A    
8     2 B    
9     1 C

Remove a column from the data set. Using let() is efficient because it modifies the data set in place. This code is not executed because the V5 column is not present in the data set.

data.table

DT[, let(V1 = NULL)]
DT

      V2    V3     V4
   <int> <num> <char>
1:     1   0.5      A
2:     2   1.0      B
3:     3   1.5      C
4:     4   0.5      A
5:     5   1.0      B
6:     6   1.5      C
7:     7   0.5      A
8:     8   1.0      B
9:     9   1.5      C

      V2    V3     V4
   <int> <num> <char>
1:     1   0.5      A
2:     2   1.0      B
3:     3   1.5      C
4:     4   0.5      A
5:     5   1.0      B
6:     6   1.5      C
7:     7   0.5      A
8:     8   1.0      B
9:     9   1.5      C

base

DF = DF[, !names(DF) %in% "V1"]
DF

  V2  V3 V4
1  1 0.5  A
2  2 1.0  B
3  3 1.5  C
4  4 0.5  A
5  5 1.0  B
6  6 1.5  C
7  7 0.5  A
8  8 1.0  B
9  9 1.5  C

dplyr

TB = TB |> select(-V1)
TB

# A tibble: 9 × 3
     V2    V3 V4   
  <int> <dbl> <chr>
1     1   0.5 A    
2     2   1   B    
3     3   1.5 C    
4     4   0.5 A    
5     5   1   B    
6     6   1.5 C    
7     7   0.5 A    
8     8   1   B    
9     9   1.5 C

Remove several columns from the data set. Using := is efficient because it modifies the data set in place. This code is not executed because the V6 and V7 columns are not present in the data set.

data.table

cols = c("V2", "V3")
DT[, (cols) := NULL]
DT

       V4
   <char>
1:      A
2:      B
3:      C
4:      A
5:      B
6:      C
7:      A
8:      B
9:      C

       V4
   <char>
1:      A
2:      B
3:      C
4:      A
5:      B
6:      C
7:      A
8:      B
9:      C

base

DF = DF[, !(names(DF) %in% c("V2", "V3"))]
DF

[1] "A" "B" "C" "A" "B" "C" "A" "B" "C"

dplyr

TB = TB |> select(-V2, -V3)
TB

# A tibble: 9 × 1
  V4   
  <chr>
1 A    
2 B    
3 C    
4 A    
5 B    
6 C    
7 A    
8 B    
9 C

refresh_data()

Rename

Select and rename.

data.table

DT[, .(X1 = V1, X2 = V2)]

      X1    X2
   <int> <int>
1:     1     1
2:     2     2
3:     1     3
4:     2     4
5:     1     5
6:     2     6
7:     1     7
8:     2     8
9:     1     9

base

setNames(
  DF[, c("V1", "V2")],
  c("X1", "X2"))

dplyr

TB |> select(X1 = V1, X2 = V2)

# A tibble: 9 × 2
     X1    X2
  <int> <int>
1     1     1
2     2     2
3     1     3
4     2     4
5     1     5
6     2     6
7     1     7
8     2     8
9     1     9

Using the data.table::setnames() to rename columns is efficient because it renames column in place. This code is not executed to avoid renaming columns in the original data set.

data.table

setnames(DT, old = c("V1", "V2"), new = c("X1", "X2"))
DT

      X1    X2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     2   1.0      B
3:     1     3   1.5      C
4:     2     4   0.5      A
5:     1     5   1.0      B
6:     2     6   1.5      C
7:     1     7   0.5      A
8:     2     8   1.0      B
9:     1     9   1.5      C

base

colnames(DF)[match(c("V1", "V2"), colnames(DF))] = c("X1", "X2")
DF

  X1 X2  V3 V4
1  1  1 0.5  A
2  2  2 1.0  B
3  1  3 1.5  C
4  2  4 0.5  A
5  1  5 1.0  B
6  2  6 1.5  C
7  1  7 0.5  A
8  2  8 1.0  B
9  1  9 1.5  C

dplyr

TB = TB |> rename(X1 = V1, X2 = V2)
TB

# A tibble: 9 × 4
     X1    X2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     2   1   B    
3     1     3   1.5 C    
4     2     4   0.5 A    
5     1     5   1   B    
6     2     6   1.5 C    
7     1     7   0.5 A    
8     2     8   1   B    
9     1     9   1.5 C

refresh_data()

Advanced selections

Complex selections using regular expressions or dedicated functions.

data.table

DT[, .SD, .SDcols = c("V1", "V2")]
DT[, .SD, .SDcols = patterns("^V[1-2]$")]
DT[, .SD, .SDcols = patterns("V")]
DT[, .SD, .SDcols = patterns("3$")]
DT[, .SD, .SDcols = patterns(".2")]
DT[, .SD, .SDcols = patterns("^V1$|^X$")]
DT[, .SD, .SDcols = patterns("^(?!V2)", perl = TRUE)]

base

DF[, c("V1", "V2")]
DF[ , grep("^V[1-2]$", names(DF))]
DF[ , c("V4", setdiff(names(DF), "V4"))]
DF[ , grep("V", names(DF))]
DF[ , grep("3$", names(DF))]
DF[ , grep(".2", names(DF))]
DF[ , c("V1", "X")]
DF[ , !grepl("^V2", names(DF))]

dplyr

TB |> select(V1, V2)
TB |> select(num_range("V", 1:2))
TB |> select(contains("V"))
TB |> select(ends_with("3"))
TB |> select(matches(".2"))
TB |> select(one_of(c("V1", "X")))
TB |> select(-starts_with("V2"))

Summarize

Single columns

Create a new data frame with a single row and a single column, summarizing the information of one column. Named or unnamed results.

data.table

DT[, sum(V1)]
DT[, .(sumV1 = sum(V1))]

[1] 13

   sumV1
   <int>
1:    13

base

sum(DF$V1)
data.frame(sumV1 = sum(DF$V1))

[1] 13

  sumV1
1    13

dplyr

TB |> pull(V1) |> sum()
TB |> summarise(sumV1 = sum(V1))

[1] 13

# A tibble: 1 × 1
  sumV1
  <int>
1    13

Create a new data frame with a single row and two columns, summarizing the information of two manually specified columns.

data.table

DT[, .(sumV1 = sum(V1), sdV3 = sd(V3))]

   sumV1      sdV3
   <int>     <num>
1:    13 0.4330127

base

data.frame(sumV1 = sum(DF$V1), sdV3 = sd(DF$V3))

  sumV1      sdV3
1    13 0.4330127

dplyr

TB |> summarise(sumV1 = sum(V1), sdV3 = sd(V3))

# A tibble: 1 × 2
  sumV1  sdV3
  <int> <dbl>
1    13 0.433

Multiple columns

Apply a function to each column.

data.table

DT[, lapply(.SD, head, 1)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A

base

data.frame(lapply(DF, head, 1))

  V1 V2  V3 V4
1  1  1 0.5  A

dplyr

TB |> summarize(across(everything(), \(x) head(x, 1)))

# A tibble: 1 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A

Apply a function to each column that matches a specific type.

data.table

DT[, lapply(.SD, mean), .SDcols = is.numeric]

         V1    V2    V3
      <num> <num> <num>
1: 1.444444     5     1

base

data.frame(lapply(DF[sapply(DF, is.numeric)], mean))

        V1 V2 V3
1 1.444444  5  1

dplyr

TB |> summarize(across(where(is.numeric), mean))

# A tibble: 1 × 3
     V1    V2    V3
  <dbl> <dbl> <dbl>
1  1.44     5     1

By group

Count the number of observation by group.

data.table

DT[, .N, by = V4]

       V4     N
   <char> <int>
1:      A     3
2:      B     3
3:      C     3

base

as.data.frame(table(DF$V4))

  Var1 Freq
1    A    3
2    B    3
3    C    3

dplyr

TB |>
  group_by(V4) |>
  tally()

# A tibble: 3 × 2
  V4        n
  <chr> <int>
1 A         3
2 B         3
3 C         3

Multiple named summaries

data.table

DT[, .(nobs = .N, meanV1 = mean(V1)), by = V4]

       V4  nobs   meanV1
   <char> <int>    <num>
1:      A     3 1.333333
2:      B     3 1.666667
3:      C     3 1.333333

base

do.call(rbind, by(DF, ~V4, \(x) {
  data.frame(nobs = nrow(x), meanV1 = mean(x$V1))
  }
))

  nobs   meanV1
A    3 1.333333
B    3 1.666667
C    3 1.333333

dplyr

TB |>
  group_by(V4) |>
  summarize(nobs = n(), meanV1 = mean(V1)) |>
  ungroup()

# A tibble: 3 × 3
  V4     nobs meanV1
  <chr> <int>  <dbl>
1 A         3   1.33
2 B         3   1.67
3 C         3   1.33

Apply a function to the full data frame in each group. Here, we return the first row in each group using the head() function.

data.table

DT[, head(.SD, 1), by = V4]

       V4    V1    V2    V3
   <char> <int> <int> <num>
1:      A     1     1   0.5
2:      B     2     2   1.0
3:      C     1     3   1.5

base

do.call(rbind, by(DF, DF$V4, \(x) head(x, 1)))

  V1 V2  V3 V4
A  1  1 0.5  A
B  2  2 1.0  B
C  1  3 1.5  C

dplyr

TB |>
  group_by(V4) |>
  summarize(across(everything(), head, 1))

# A tibble: 3 × 4
  V4       V1    V2    V3
  <chr> <int> <int> <dbl>
1 A         1     1   0.5
2 B         2     2   1  
3 C         1     3   1.5

There was 1 warning in `summarize()`.
ℹ In argument: `across(everything(), head, 1)`.
ℹ In group 1: `V4 = "A"`.
Caused by warning:
! The `...` argument of `across()` is deprecated as of dplyr 1.1.0.
Supply arguments directly to `.fns` through an anonymous function instead.

  # Previously
  across(a:b, mean, na.rm = TRUE)

  # Now
  across(a:b, \(x) mean(x, na.rm = TRUE))

Get the row number of first (and last) observation by group. In data.table, we use the .I operator, which reports the row number.

data.table

DT[, .I[1], by = V4]
DT[, range(.I), by = V4]

       V4    V1
   <char> <int>
1:      A     1
2:      B     2
3:      C     3

       V4    V1
   <char> <int>
1:      A     1
2:      A     7
3:      B     2
4:      B     8
5:      C     3
6:      C     9

base

do.call(rbind, by(DF, ~V4, \(x) x[1,]))
do.call(rbind, by(DF, ~V4, \(x) x[c(1, nrow(x)),]))

  V1 V2  V3 V4
A  1  1 0.5  A
B  2  2 1.0  B
C  1  3 1.5  C

    V1 V2  V3 V4
A.1  1  1 0.5  A
A.7  1  7 0.5  A
B.2  2  2 1.0  B
B.8  2  8 1.0  B
C.3  1  3 1.5  C
C.9  1  9 1.5  C

dplyr

TB |>
  group_by(V4) |>
  summarize(cur_group_rows()[1]) |>
  ungroup()
TB |>
  group_by(V4) |>
  reframe(cur_group_rows()[c(1, n())]) |>
  ungroup()

# A tibble: 3 × 2
  V4    `cur_group_rows()[1]`
  <chr>                 <int>
1 A                         1
2 B                         2
3 C                         3

# A tibble: 6 × 2
  V4    `cur_group_rows()[c(1, n())]`
  <chr>                         <int>
1 A                                 1
2 A                                 7
3 B                                 2
4 B                                 8
5 C                                 3
6 C                                 9

List-columns are columns where each element is a vector, data frame, or other object.

data.table

DT[, .(.(V1)),  by = V4]
DT[, .(.(.SD)), by = V4]

       V4     V1
   <char> <list>
1:      A  1,2,1
2:      B  2,1,2
3:      C  1,2,1

       V4                V1
   <char>            <list>
1:      A <data.table[3x3]>
2:      B <data.table[3x3]>
3:      C <data.table[3x3]>

base

tapply(DF$V1, DF$V4,
  function(x) list(x))
split(DF, DF$V4)

$A
[1] 1 2 1

$B
[1] 2 1 2

$C
[1] 1 2 1

$A
  V1 V2  V3 V4
1  1  1 0.5  A
4  2  4 0.5  A
7  1  7 0.5  A

$B
  V1 V2 V3 V4
2  2  2  1  B
5  1  5  1  B
8  2  8  1  B

$C
  V1 V2  V3 V4
3  1  3 1.5  C
6  2  6 1.5  C
9  1  9 1.5  C

dplyr

TB |>
  group_by(V4) |>
  summarise(list(V1))
TB |>
  group_by(V4) |>
  group_nest()

# A tibble: 3 × 2
  V4    `list(V1)`
  <chr> <list>    
1 A     <int [3]> 
2 B     <int [3]> 
3 C     <int [3]>

# A tibble: 3 × 2
  V4                  data
  <chr> <list<tibble[,3]>>
1 A                [3 × 3]
2 B                [3 × 3]
3 C                [3 × 3]

Modify

Single columns

Create or modify a column.

data.table

DT[, let(V5 = V1^2)]
DT

      V1    V2    V3     V4    V5
   <int> <int> <num> <char> <num>
1:     1     1   0.5      A     1
2:     2     2   1.0      B     4
3:     1     3   1.5      C     1
4:     2     4   0.5      A     4
5:     1     5   1.0      B     1
6:     2     6   1.5      C     4
7:     1     7   0.5      A     1
8:     2     8   1.0      B     4
9:     1     9   1.5      C     1

      V1    V2    V3     V4    V5
   <int> <int> <num> <char> <num>
1:     1     1   0.5      A     1
2:     2     2   1.0      B     4
3:     1     3   1.5      C     1
4:     2     4   0.5      A     4
5:     1     5   1.0      B     1
6:     2     6   1.5      C     4
7:     1     7   0.5      A     1
8:     2     8   1.0      B     4
9:     1     9   1.5      C     1

base

DF$V5 = DF$V1^2
DF

  V1 V2  V3 V4 V5
1  1  1 0.5  A  1
2  2  2 1.0  B  4
3  1  3 1.5  C  1
4  2  4 0.5  A  4
5  1  5 1.0  B  1
6  2  6 1.5  C  4
7  1  7 0.5  A  1
8  2  8 1.0  B  4
9  1  9 1.5  C  1

dplyr

TB = TB |> mutate(V5 = V1^2)
TB

# A tibble: 9 × 5
     V1    V2    V3 V4       V5
  <int> <int> <dbl> <chr> <dbl>
1     1     1   0.5 A         1
2     2     2   1   B         4
3     1     3   1.5 C         1
4     2     4   0.5 A         4
5     1     5   1   B         1
6     2     6   1.5 C         4
7     1     7   0.5 A         1
8     2     8   1   B         4
9     1     9   1.5 C         1

Create several new columns, each named explicitly.

data.table

DT[, let(
  V5 = sqrt(V1),
  V6 = "X")]
DT

      V1    V2    V3     V4       V5     V6
   <int> <int> <num> <char>    <num> <char>
1:     1     1   0.5      A 1.000000      X
2:     2     2   1.0      B 1.414214      X
3:     1     3   1.5      C 1.000000      X
4:     2     4   0.5      A 1.414214      X
5:     1     5   1.0      B 1.000000      X
6:     2     6   1.5      C 1.414214      X
7:     1     7   0.5      A 1.000000      X
8:     2     8   1.0      B 1.414214      X
9:     1     9   1.5      C 1.000000      X

      V1    V2    V3     V4       V5     V6
   <int> <int> <num> <char>    <num> <char>
1:     1     1   0.5      A 1.000000      X
2:     2     2   1.0      B 1.414214      X
3:     1     3   1.5      C 1.000000      X
4:     2     4   0.5      A 1.414214      X
5:     1     5   1.0      B 1.000000      X
6:     2     6   1.5      C 1.414214      X
7:     1     7   0.5      A 1.000000      X
8:     2     8   1.0      B 1.414214      X
9:     1     9   1.5      C 1.000000      X

base

DF$V5 = sqrt(DF$V1)
DF$V6 = "X"
DF

  V1 V2  V3 V4       V5 V6
1  1  1 0.5  A 1.000000  X
2  2  2 1.0  B 1.414214  X
3  1  3 1.5  C 1.000000  X
4  2  4 0.5  A 1.414214  X
5  1  5 1.0  B 1.000000  X
6  2  6 1.5  C 1.414214  X
7  1  7 0.5  A 1.000000  X
8  2  8 1.0  B 1.414214  X
9  1  9 1.5  C 1.000000  X

dplyr

TB = TB |> mutate(
  V5 = sqrt(V1),
  V6 = "X")
TB

# A tibble: 9 × 6
     V1    V2    V3 V4       V5 V6   
  <int> <int> <dbl> <chr> <dbl> <chr>
1     1     1   0.5 A      1    X    
2     2     2   1   B      1.41 X    
3     1     3   1.5 C      1    X    
4     2     4   0.5 A      1.41 X    
5     1     5   1   B      1    X    
6     2     6   1.5 C      1.41 X    
7     1     7   0.5 A      1    X    
8     2     8   1   B      1.41 X    
9     1     9   1.5 C      1    X

Multiple columns

Take the square of every numeric column.

data.table

DT[, names(.SD) := lapply(.SD, \(x) x^2), .SDcols = is.numeric]
DT

      V1    V2    V3     V4    V5     V6
   <num> <num> <num> <char> <num> <char>
1:     1     1  0.25      A     1      X
2:     4     4  1.00      B     2      X
3:     1     9  2.25      C     1      X
4:     4    16  0.25      A     2      X
5:     1    25  1.00      B     1      X
6:     4    36  2.25      C     2      X
7:     1    49  0.25      A     1      X
8:     4    64  1.00      B     2      X
9:     1    81  2.25      C     1      X

      V1    V2    V3     V4    V5     V6
   <num> <num> <num> <char> <num> <char>
1:     1     1  0.25      A     1      X
2:     4     4  1.00      B     2      X
3:     1     9  2.25      C     1      X
4:     4    16  0.25      A     2      X
5:     1    25  1.00      B     1      X
6:     4    36  2.25      C     2      X
7:     1    49  0.25      A     1      X
8:     4    64  1.00      B     2      X
9:     1    81  2.25      C     1      X

base

TODO

dplyr

TB = TB |> 
  mutate(across(where(is.numeric), \(x) x^2))
TB

# A tibble: 9 × 6
     V1    V2    V3 V4       V5 V6   
  <dbl> <dbl> <dbl> <chr> <dbl> <chr>
1     1     1  0.25 A         1 X    
2     4     4  1    B         2 X    
3     1     9  2.25 C         1 X    
4     4    16  0.25 A         2 X    
5     1    25  1    B         1 X    
6     4    36  2.25 C         2 X    
7     1    49  0.25 A         1 X    
8     4    64  1    B         2 X    
9     1    81  2.25 C         1 X

refresh_data()

Replace

Replace values in rows that match a condition applied to a column.

data.table

DT[V2 <= 4, let(V2 = 0)]
DT

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     0   0.5      A
2:     2     0   1.0      B
3:     1     0   1.5      C
4:     2     0   0.5      A
5:     1     5   1.0      B
6:     2     6   1.5      C
7:     1     7   0.5      A
8:     2     8   1.0      B
9:     1     9   1.5      C

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     0   0.5      A
2:     2     0   1.0      B
3:     1     0   1.5      C
4:     2     0   0.5      A
5:     1     5   1.0      B
6:     2     6   1.5      C
7:     1     7   0.5      A
8:     2     8   1.0      B
9:     1     9   1.5      C

base

DF$V2 = replace(DF$V2, DF$V2 < 4, 0)
DF

  V1 V2  V3 V4
1  1  0 0.5  A
2  2  0 1.0  B
3  1  0 1.5  C
4  2  4 0.5  A
5  1  5 1.0  B
6  2  6 1.5  C
7  1  7 0.5  A
8  2  8 1.0  B
9  1  9 1.5  C

dplyr

TB = DF |> 
  mutate(V2 = base::replace(V2, V2 < 4, 0))
TB

  V1 V2  V3 V4
1  1  0 0.5  A
2  2  0 1.0  B
3  1  0 1.5  C
4  2  4 0.5  A
5  1  5 1.0  B
6  2  6 1.5  C
7  1  7 0.5  A
8  2  8 1.0  B
9  1  9 1.5  C

refresh_data()

If, else, case

Fast version of ifelse(). Return a vector the same length as the vector tested, with specific value for in case where the condition is TRUE, where it is FALSE, and where it is NA.

data.table

x = c(-3:3, NA)
fifelse(test = x < 0,
  yes  = "neg",
  no   = "pos",
  na   = "NA")

[1] "neg" "neg" "neg" "pos" "pos" "pos" "pos" "NA"

base

x = c(-3:3, NA)
result = ifelse(is.na(x),
  "NA",
  ifelse(x < 0, "neg", "pos"))
result

[1] "neg" "neg" "neg" "pos" "pos" "pos" "pos" "NA"

dplyr

x = c(-3:3, NA)
if_else(condition = x < 0,
  true      = "neg",
  false     = "pos",
  missing   = "NA")

[1] "neg" "neg" "neg" "pos" "pos" "pos" "pos" "NA"

Recode several cases at once, based on a vector of conditions.

data.table

x = 1:10
fcase(
  x %% 6 == 0, "fizz buzz",
  x %% 2 == 0, "fizz",
  x %% 3 == 0, "buzz",
  default = as.character(x)
)

 [1] "1"         "fizz"      "buzz"      "fizz"      "5"         "fizz buzz"
 [7] "7"         "fizz"      "buzz"      "fizz"

base

x = 1:10
result = ifelse(x %% 6 == 0, "fizz buzz",
  ifelse(x %% 2 == 0, "fizz",
  ifelse(x %% 3 == 0, "buzz", as.character(x))))
result

 [1] "1"         "fizz"      "buzz"      "fizz"      "5"         "fizz buzz"
 [7] "7"         "fizz"      "buzz"      "fizz"

dplyr

x = 1:10
case_when(
  x %% 6 == 0 ~ "fizz buzz",
  x %% 2 == 0 ~ "fizz",
  x %% 3 == 0 ~ "buzz",
  TRUE ~ as.character(x)
)

 [1] "1"         "fizz"      "buzz"      "fizz"      "5"         "fizz buzz"
 [7] "7"         "fizz"      "buzz"      "fizz"

Modify by group

Group summaries of a column in categories.

data.table

DT[, by = "V4", .(sumV2 = sum(V2))]

       V4 sumV2
   <char> <int>
1:      A    12
2:      B    15
3:      C    18

base

aggregate(V2 ~ V4, data = DF, FUN = sum)

dplyr

TB |>
  group_by(V4) |>
  summarise(sumV2 = sum(V2)) |>
  ungroup()

# A tibble: 3 × 2
  V4    sumV2
  <chr> <int>
1 A        12
2 B        15
3 C        18

Group values of a column in groups while applying a function to each category.

data.table

DT[,
  by = tolower(V4),
  .(sumV1 = sum(V1))]

   tolower sumV1
    <char> <int>
1:       a     4
2:       b     5
3:       c     4

base

aggregate(
  V1 ~ tolower(V4),
  data = DF,
  FUN = sum)

  tolower(V4) V1
1           a  4
2           b  5
3           c  4

dplyr

TB |>
  group_by(tolower(V4)) |>
  summarise(sumV1 = sum(V1))

# A tibble: 3 × 2
  `tolower(V4)` sumV1
  <chr>         <int>
1 a                 4
2 b                 5
3 c                 4

Group values of a column in two categories, TRUE (for rows matching the condition) and FALSE (For rows not matching the condition).

data.table

DT[,
  keyby = V4 == "A",
  sum(V1)]

Key: <V4>
       V4    V1
   <lgcl> <int>
1:  FALSE     9
2:   TRUE     4

base

aggregate(V1 ~ groupA,
  data = transform(DF, groupA = V4 == "A"),
  FUN = sum)

  groupA V1
1  FALSE  9
2   TRUE  4

dplyr

TB |>
  group_by(V4 == "A") |>
  summarise(sum(V1))

# A tibble: 2 × 2
  `V4 == "A"` `sum(V1)`
  <lgl>           <int>
1 FALSE               9
2 TRUE                4

Group values of a column in several categories with some of the rows of the initial dataset removed.

data.table

DT[1:5,
  by = V4,
  .(sumV1 = sum(V1))]

       V4 sumV1
   <char> <int>
1:      A     3
2:      B     3
3:      C     1

base

aggregate(V1 ~ V4,
  data = DF[1:5,],
  FUN = sum)

dplyr

TB |>
  slice(1:5) |>
  group_by(V4) |>
  summarise(sumV1 = sum(V1))

# A tibble: 3 × 2
  V4    sumV1
  <chr> <int>
1 A         3
2 B         3
3 C         1

Add a new column with the number of observations per group.

data.table

DT[, let(n = .N), by = V1]
DT

      V1    V2    V3     V4     n
   <int> <int> <num> <char> <int>
1:     1     1   0.5      A     5
2:     2     2   1.0      B     4
3:     1     3   1.5      C     5
4:     2     4   0.5      A     4
5:     1     5   1.0      B     5
6:     2     6   1.5      C     4
7:     1     7   0.5      A     5
8:     2     8   1.0      B     4
9:     1     9   1.5      C     5

      V1    V2    V3     V4     n
   <int> <int> <num> <char> <int>
1:     1     1   0.5      A     5
2:     2     2   1.0      B     4
3:     1     3   1.5      C     5
4:     2     4   0.5      A     4
5:     1     5   1.0      B     5
6:     2     6   1.5      C     4
7:     1     7   0.5      A     5
8:     2     8   1.0      B     4
9:     1     9   1.5      C     5

base

DF$n <-
  ave(DF$V1,
  DF$V1,
  FUN = length)
DF

  V1 V2  V3 V4 n
1  1  1 0.5  A 5
2  2  2 1.0  B 4
3  1  3 1.5  C 5
4  2  4 0.5  A 4
5  1  5 1.0  B 5
6  2  6 1.5  C 4
7  1  7 0.5  A 5
8  2  8 1.0  B 4
9  1  9 1.5  C 5

dplyr

TB = TB |>
  group_by(V1) |>
  add_tally()
TB

# A tibble: 9 × 5
# Groups:   V1 [2]
     V1    V2    V3 V4        n
  <int> <int> <dbl> <chr> <int>
1     1     1   0.5 A         5
2     2     2   1   B         4
3     1     3   1.5 C         5
4     2     4   0.5 A         4
5     1     5   1   B         5
6     2     6   1.5 C         4
7     1     7   0.5 A         5
8     2     8   1   B         4
9     1     9   1.5 C         5

refresh_data()

Advanced

Summarise all the columns, typically using an aggregation function.

data.table

DT[, lapply(.SD, max)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     2     9   1.5      C

base

apply(DF, 2, max)

   V1    V2    V3    V4 
  "2"   "9" "1.5"   "C"

dplyr

TB |> summarise(across(everything(), max))

# A tibble: 1 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     2     9   1.5 C

Summarise several columns, typically using an aggregation function.

data.table

DT[, lapply(.SD, mean),
  .SDcols = c("V1", "V2")]

         V1    V2
      <num> <num>
1: 1.444444     5

base

data.frame(
  mean_V1 = mean(DF$V1),
  mean_V2 = mean(DF$V2))

   mean_V1 mean_V2
1 1.444444       5

dplyr

TODO

Summarise several columns by groups, typically using an aggregation function.

data.table

DT[, by = V4,
  lapply(.SD, mean),
  .SDcols = c("V1", "V2")]
DT[, by = V4,
  lapply(.SD, mean),
  .SDcols = patterns("V1|V2|Z0")]

       V4       V1    V2
   <char>    <num> <num>
1:      A 1.333333     4
2:      B 1.666667     5
3:      C 1.333333     6

       V4       V1    V2
   <char>    <num> <num>
1:      A 1.333333     4
2:      B 1.666667     5
3:      C 1.333333     6

base

cols = intersect(c("V1", "V2", "Z0"), names(DF))
aggregate(DF[cols], by = list(DF$V4), FUN = mean, na.rm = TRUE)

  Group.1       V1 V2
1       A 1.333333  4
2       B 1.666667  5
3       C 1.333333  6

dplyr

TB |>
  group_by(V4) |>
  summarise(across(c(V1, V2), mean)) |>
  ungroup()

# A tibble: 3 × 3
  V4       V1    V2
  <chr> <dbl> <dbl>
1 A      1.33     4
2 B      1.67     5
3 C      1.33     6

Summarise several columns by group using multiple aggregation functions, grouping by one or more variables.

data.table

DT[, by = V4,
  c(lapply(.SD, sum),
  lapply(.SD, mean))]

       V4    V1    V2    V3       V1    V2    V3
   <char> <int> <int> <num>    <num> <num> <num>
1:      A     4    12   1.5 1.333333     4   0.5
2:      B     5    15   3.0 1.666667     5   1.0
3:      C     4    18   4.5 1.333333     6   1.5

base

aggregate(cbind(DF$V1, DF$V2, DF$V3) ~ V4,
  data = DF,
  FUN = function(x) c(sum = sum(x), mean = mean(x)))

  V4   V1.sum  V1.mean V2.sum V2.mean V3.sum V3.mean
1  A 4.000000 1.333333     12       4    1.5     0.5
2  B 5.000000 1.666667     15       5    3.0     1.0
3  C 4.000000 1.333333     18       6    4.5     1.5

dplyr

TB |>
  group_by(V4) |>
  summarise(across(everything(),
  list(sum = sum, mean = mean)))

# A tibble: 3 × 7
  V4    V1_sum V1_mean V2_sum V2_mean V3_sum V3_mean
  <chr>  <int>   <dbl>  <int>   <dbl>  <dbl>   <dbl>
1 A          4    1.33     12       4    1.5     0.5
2 B          5    1.67     15       5    3       1  
3 C          4    1.33     18       6    4.5     1.5

Summarise a subset of columns by column type or condition.

data.table

DT[, lapply(.SD, mean), .SDcols = is.numeric]
foo = function(x) {is.numeric(x) && mean(x) > 3}
DT[, lapply(.SD, mean), .SDcols = foo]

         V1    V2    V3
      <num> <num> <num>
1: 1.444444     5     1

      V2
   <num>
1:     5

base

sapply(DF[sapply(DF, is.numeric)],
  mean)
sapply(DF[sapply(DF, \(x) {
   is.numeric(x) && mean(x) > 3
})], mean)

      V1       V2       V3 
1.444444 5.000000 1.000000

V2 
 5

dplyr

TB |>
  summarise(across(where(is.numeric),
  mean))
TB |> summarise(across(
  where(~ is.numeric(.x) && mean(.x) > 3), mean))

# A tibble: 1 × 3
     V1    V2    V3
  <dbl> <dbl> <dbl>
1  1.44     5     1

# A tibble: 1 × 1
     V2
  <dbl>
1     5

Modify all the columns using the same function.

data.table

DT[, lapply(.SD, rev)]

      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     9   1.5      C
2:     2     8   1.0      B
3:     1     7   0.5      A
4:     2     6   1.5      C
5:     1     5   1.0      B
6:     2     4   0.5      A
7:     1     3   1.5      C
8:     2     2   1.0      B
9:     1     1   0.5      A

base

data.frame(lapply(DF, rev))

  V1 V2  V3 V4
1  1  9 1.5  C
2  2  8 1.0  B
3  1  7 0.5  A
4  2  6 1.5  C
5  1  5 1.0  B
6  2  4 0.5  A
7  1  3 1.5  C
8  2  2 1.0  B
9  1  1 0.5  A

dplyr

TB |> mutate(across(everything(), rev))

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     9   1.5 C    
2     2     8   1   B    
3     1     7   0.5 A    
4     2     6   1.5 C    
5     1     5   1   B    
6     2     4   0.5 A    
7     1     3   1.5 C    
8     2     2   1   B    
9     1     1   0.5 A

Apply a transformation to each element of the variables selected.

data.table

DT[, lapply(.SD, sqrt), .SDcols = V1:V2]

         V1       V2
      <num>    <num>
1: 1.000000 1.000000
2: 1.414214 1.414214
3: 1.000000 1.732051
4: 1.414214 2.000000
5: 1.000000 2.236068
6: 1.414214 2.449490
7: 1.000000 2.645751
8: 1.414214 2.828427
9: 1.000000 3.000000

base

TODO

dplyr

TODO

Apply a transformation for each element of the variables selected.

data.table

DT[, names(.SD) := lapply(.SD, sqrt), .SDcols = V1:V3]
DT

         V1       V2        V3     V4
      <num>    <num>     <num> <char>
1: 1.000000 1.000000 0.7071068      A
2: 1.414214 1.414214 1.0000000      B
3: 1.000000 1.732051 1.2247449      C
4: 1.414214 2.000000 0.7071068      A
5: 1.000000 2.236068 1.0000000      B
6: 1.414214 2.449490 1.2247449      C
7: 1.000000 2.645751 0.7071068      A
8: 1.414214 2.828427 1.0000000      B
9: 1.000000 3.000000 1.2247449      C

         V1       V2        V3     V4
      <num>    <num>     <num> <char>
1: 1.000000 1.000000 0.7071068      A
2: 1.414214 1.414214 1.0000000      B
3: 1.000000 1.732051 1.2247449      C
4: 1.414214 2.000000 0.7071068      A
5: 1.000000 2.236068 1.0000000      B
6: 1.414214 2.449490 1.2247449      C
7: 1.000000 2.645751 0.7071068      A
8: 1.414214 2.828427 1.0000000      B
9: 1.000000 3.000000 1.2247449      C

base

TODO

dplyr

TODO

Apply a transformation to each element of the variables selected by condition.

data.table

DT[, .SD - 1, .SDcols = is.numeric]

          V1        V2         V3
       <num>     <num>      <num>
1: 0.0000000 0.0000000 -0.2928932
2: 0.4142136 0.4142136  0.0000000
3: 0.0000000 0.7320508  0.2247449
4: 0.4142136 1.0000000 -0.2928932
5: 0.0000000 1.2360680  0.0000000
6: 0.4142136 1.4494897  0.2247449
7: 0.0000000 1.6457513 -0.2928932
8: 0.4142136 1.8284271  0.0000000
9: 0.0000000 2.0000000  0.2247449

base

data.frame(lapply(DF,
  \(x) if (is.numeric(x)) x - 1 else x))

  V1 V2   V3 V4
1  0  0 -0.5  A
2  1  1  0.0  B
3  0  2  0.5  C
4  1  3 -0.5  A
5  0  4  0.0  B
6  1  5  0.5  C
7  0  6 -0.5  A
8  1  7  0.0  B
9  0  8  0.5  C

dplyr

TB |>
  transmute(across(where(is.numeric),
  ~ '-'(., 1L)))

# A tibble: 9 × 3
     V1    V2    V3
  <int> <int> <dbl>
1     0     0  -0.5
2     1     1   0  
3     0     2   0.5
4     1     3  -0.5
5     0     4   0  
6     1     5   0.5
7     0     6  -0.5
8     1     7   0  
9     0     8   0.5

Apply a transformation to each element of the variables selected by condition.

data.table

DT[, names(.SD) := lapply(.SD, as.integer),
  .SDcols = is.numeric]
DT

      V1    V2    V3     V4
   <int> <int> <int> <char>
1:     1     1     0      A
2:     1     1     1      B
3:     1     1     1      C
4:     1     2     0      A
5:     1     2     1      B
6:     1     2     1      C
7:     1     2     0      A
8:     1     2     1      B
9:     1     3     1      C

      V1    V2    V3     V4
   <int> <int> <int> <char>
1:     1     1     0      A
2:     1     1     1      B
3:     1     1     1      C
4:     1     2     0      A
5:     1     2     1      B
6:     1     2     1      C
7:     1     2     0      A
8:     1     2     1      B
9:     1     3     1      C

base

DF[sapply(DF, is.numeric)] <-
  lapply(DF[sapply(DF, is.numeric)],
  as.integer)
DF

  V1 V2 V3 V4
1  1  1  0  A
2  2  2  1  B
3  1  3  1  C
4  2  4  0  A
5  1  5  1  B
6  2  6  1  C
7  1  7  0  A
8  2  8  1  B
9  1  9  1  C

dplyr

TB = TB |>
  mutate(across(where(is.numeric),
  as.integer))
TB

# A tibble: 9 × 4
     V1    V2    V3 V4   
  <int> <int> <int> <chr>
1     1     1     0 A    
2     2     2     1 B    
3     1     3     1 C    
4     2     4     0 A    
5     1     5     1 B    
6     2     6     1 C    
7     1     7     0 A    
8     2     8     1 B    
9     1     9     1 C

Combine multiple functions in a single statement.

data.table

DT[, by = V4,
  .(V1[1:2], "X")]

       V4    V1     V2
   <char> <int> <char>
1:      A     1      X
2:      A     1      X
3:      B     1      X
4:      B     1      X
5:      C     1      X
6:      C     1      X

base

DF = do.call(rbind,
  by(DF, DF$V4, function(sub) {
  head(data.frame(V1 = sub$V1,
  V2 = "X"), 2)
}))

dplyr

TB |>
  group_by(V4) |>
  slice(1:2) |>
  transmute(V1 = V1,
  V2 = "X")

# A tibble: 6 × 3
# Groups:   V4 [3]
  V4       V1 V2   
  <chr> <int> <chr>
1 A         1 X    
2 A         2 X    
3 B         2 X    
4 B         1 X    
5 C         1 X    
6 C         2 X

refresh_data()

Chain (pipe)

Expression chaining allows you to perform multiple operations in sequence without creating intermediate objects.

data.table

# Chain operations using [][]
DT[, by = V4, .(V1sum = sum(V1))][
  V1sum > 4]

# Sort results in descending order
DT[, by = V4, .(V1sum = sum(V1))][
  order(-V1sum)]

       V4 V1sum
   <char> <int>
1:      B     5

       V4 V1sum
   <char> <int>
1:      B     5
2:      A     4
3:      C     4

base

# Chain using intermediate assignment
subset(aggregate(V1 ~ V4,
  data = DF,
  FUN = sum), V1 > 4)

# Sort results
# TODO

  V4 V1
2  B  5

dplyr

# Chain using pipe operator
TB |>
  group_by(V4) |>
  summarise(V1sum = sum(V1)) |>
  filter(V1sum > 4)

# Sort results
TB |>
  group_by(V4) |>
  summarise(V1sum = sum(V1)) |>
  arrange(desc(V1sum))

# A tibble: 1 × 2
  V4    V1sum
  <chr> <int>
1 B         5

# A tibble: 3 × 2
  V4    V1sum
  <chr> <int>
1 B         5
2 A         4
3 C         4

refresh_data()

Join

First, let’s create example datasets for demonstrating joins.

x = data.table(
  Id  = c("A", "B", "C", "C"),
  X1  = c(1L, 3L, 5L, 7L),
  XY  = c("x2", "x4", "x6", "x8")
)
y = data.table(
  Id  = c("A", "B", "B", "D"),
  Y1  = c(1L, 3L, 5L, 7L),
  XY  = c("y1", "y3", "y5", "y7")
)

Basic Joins

There are two basic ways to perform joins in data.table. First, we can use the standard merge() function that should be familiar to most R users.

Second, we can use the square bracket notation with the on argument. Here, the logic is that we select every row from the table outside that match rows from the table inside the bracket. The table inside the brackets is used as an “index”, so x[y] essentially means that we are merging x into y. The benefit of this syntax is that we can efficiently combine it with other operations like summaries, sorts, etc.

Note that, in data.table, the on argument specifies the joining columns, and the i. prefix refers to columns from the right table, while the x. prefix refers to columns from the left table.

data.table

# Left join (keep all rows from x)
# merge(x, y, all.x = TRUE, by = "Id")
y[x, on = "Id"]

# Right join (keep all rows from y)
# merge(x, y, all.y = TRUE, by = "Id")
x[y, on = "Id"]

# Inner join (keep only matching rows)
# merge(x, y, by = "Id"
x[y, on = "Id", nomatch = NULL]

# Anti join (keep rows from x with no match in y)
x[!y, on = "Id"]

# Full join (keep all rows)
merge(x, y, by = "Id", all = TRUE)

       Id    Y1     XY    X1   i.XY
   <char> <int> <char> <int> <char>
1:      A     1     y1     1     x2
2:      B     3     y3     3     x4
3:      B     5     y5     3     x4
4:      C    NA   <NA>     5     x6
5:      C    NA   <NA>     7     x8

       Id    X1     XY    Y1   i.XY
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      D    NA   <NA>     7     y7

       Id    X1     XY    Y1   i.XY
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5

       Id    X1     XY
   <char> <int> <char>
1:      C     5     x6
2:      C     7     x8

Key: <Id>
       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      C     5     x6    NA   <NA>
5:      C     7     x8    NA   <NA>
6:      D    NA   <NA>     7     y7

base

# Left join (keep all rows from x)
merge(x, y, by = "Id", all.x = TRUE)

# Right join (keep all rows from y)
merge(x, y, by = "Id", all.y = TRUE)

# Inner join (keep only matching rows)
merge(x, y, by = "Id")

# Full join (keep all rows)
merge(x, y, by = "Id", all = TRUE)

# Anti join (more complex in base R)
x[!(x$Id %in% y$Id), ]

Key: <Id>
       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      C     5     x6    NA   <NA>
5:      C     7     x8    NA   <NA>

Key: <Id>
       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      D    NA   <NA>     7     y7

Key: <Id>
       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5

Key: <Id>
       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      C     5     x6    NA   <NA>
5:      C     7     x8    NA   <NA>
6:      D    NA   <NA>     7     y7

       Id    X1     XY
   <char> <int> <char>
1:      C     5     x6
2:      C     7     x8

dplyr

# Left join (keep all rows from x)
left_join(x, y, by = "Id")

# Right join (keep all rows from y)
right_join(x, y, by = "Id")

# Inner join (keep only matching rows)
inner_join(x, y, by = "Id")

# Full join (keep all rows)
full_join(x, y, by = "Id")

# Anti join
anti_join(x, y, by = "Id")

       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      C     5     x6    NA   <NA>
5:      C     7     x8    NA   <NA>

       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      D    NA   <NA>     7     y7

       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5

       Id    X1   XY.x    Y1   XY.y
   <char> <int> <char> <int> <char>
1:      A     1     x2     1     y1
2:      B     3     x4     3     y3
3:      B     3     x4     5     y5
4:      C     5     x6    NA   <NA>
5:      C     7     x8    NA   <NA>
6:      D    NA   <NA>     7     y7

       Id    X1     XY
   <char> <int> <char>
1:      C     5     x6
2:      C     7     x8

Reshape

Wide to long

Converting data from wide format (where variables are in columns) to long format (where variables become rows).

data.table

melt(DT,
  id.vars       = "V4",
  variable.name = "Variable",
  value.name    = "Value")

        V4 Variable Value
    <char>   <fctr> <num>
 1:      A       V1   1.0
 2:      B       V1   2.0
 3:      C       V1   1.0
 4:      A       V1   2.0
 5:      B       V1   1.0
 6:      C       V1   2.0
 7:      A       V1   1.0
 8:      B       V1   2.0
 9:      C       V1   1.0
10:      A       V2   1.0
11:      B       V2   2.0
12:      C       V2   3.0
13:      A       V2   4.0
14:      B       V2   5.0
15:      C       V2   6.0
16:      A       V2   7.0
17:      B       V2   8.0
18:      C       V2   9.0
19:      A       V3   0.5
20:      B       V3   1.0
21:      C       V3   1.5
22:      A       V3   0.5
23:      B       V3   1.0
24:      C       V3   1.5
25:      A       V3   0.5
26:      B       V3   1.0
27:      C       V3   1.5
        V4 Variable Value
    <char>   <fctr> <num>

'measure.vars' [V1, V2, V3] are not all of the same type. By order of hierarchy, the molten data value column will be of type 'double'. All measure variables not of type 'double' will be coerced too. Check DETAILS in ?melt.data.table for more on coercion.

base

reshape(DF,
  varying = setdiff(names(DF), "V4"),
  v.names = "value",
  timevar = "variable",
  times = setdiff(names(DF), "V4"),
  direction = "long")

     V4 variable value id
1.V1  A       V1   1.0  1
2.V1  B       V1   2.0  2
3.V1  C       V1   1.0  3
4.V1  A       V1   2.0  4
5.V1  B       V1   1.0  5
6.V1  C       V1   2.0  6
7.V1  A       V1   1.0  7
8.V1  B       V1   2.0  8
9.V1  C       V1   1.0  9
1.V2  A       V2   1.0  1
2.V2  B       V2   2.0  2
3.V2  C       V2   3.0  3
4.V2  A       V2   4.0  4
5.V2  B       V2   5.0  5
6.V2  C       V2   6.0  6
7.V2  A       V2   7.0  7
8.V2  B       V2   8.0  8
9.V2  C       V2   9.0  9
1.V3  A       V3   0.5  1
2.V3  B       V3   1.0  2
3.V3  C       V3   1.5  3
4.V3  A       V3   0.5  4
5.V3  B       V3   1.0  5
6.V3  C       V3   1.5  6
7.V3  A       V3   0.5  7
8.V3  B       V3   1.0  8
9.V3  C       V3   1.5  9

dplyr

TB |> tidyr::pivot_longer(
  cols = c("V1", "V2", "V3"),
  names_to = "Variable",
  values_to = "Value")

# A tibble: 27 × 3
   V4    Variable Value
   <chr> <chr>    <dbl>
 1 A     V1         1  
 2 A     V2         1  
 3 A     V3         0.5
 4 B     V1         2  
 5 B     V2         2  
 6 B     V3         1  
 7 C     V1         1  
 8 C     V2         3  
 9 C     V3         1.5
10 A     V1         2  
# ℹ 17 more rows

Long to wide

Converting data from long format back to wide format.

data.table

# Create example long data
long = CJ(a = 1:2, b = 1:2, c = c("x", "y"))
long[, let(d = rnorm(8))]

dcast(long, a + b ~ c)

Key: <a, b, c>
       a     b      c            d
   <int> <int> <char>        <num>
1:     1     1      x  0.876477061
2:     1     1      y -0.006358806
3:     1     2      x  0.172679834
4:     1     2      y  0.150708765
5:     2     1      x  0.336226675
6:     2     1      y  1.403138640
7:     2     2      x -0.035835838
8:     2     2      y -0.463856629

Key: <a, b>
       a     b           x            y
   <int> <int>       <num>        <num>
1:     1     1  0.87647706 -0.006358806
2:     1     2  0.17267983  0.150708765
3:     2     1  0.33622668  1.403138640
4:     2     2 -0.03583584 -0.463856629

Using 'd' as value column. Use 'value.var' to override

base

# Create example long data
long = expand.grid(a = 1:2, b = 1:2, c = c("x", "y"))
long$d = rnorm(8)

reshape(long, 
  idvar = c("a", "b"),
  timevar = "c",
  direction = "wide")

  a b        d.x        d.y
1 1 1 -0.2455329 0.09714133
2 2 1 -0.3860638 0.79955493
3 1 2 -0.1978210 0.61480686
4 2 2 -0.4133174 0.30234612

dplyr

# Create example long data
long = tidyr::expand_grid(a = 1:2, b = 1:2, c = c("x", "y"))
long$d = rnorm(8)

tidyr::pivot_wider(long,
  id_cols = c("a", "b"),
  names_from = "c",
  values_from = "d")

# A tibble: 4 × 4
      a     b      x        y
  <int> <int>  <dbl>    <dbl>
1     1     1 -0.168 -0.622  
2     1     2  0.775  1.16   
3     2     1  0.726 -0.00248
4     2     2 -0.427  0.873

Split rows

Separating data into groups based on a factor.

data.table

split(DT, by = "V4")

$A
      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     1   0.5      A
2:     2     4   0.5      A
3:     1     7   0.5      A

$B
      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     2     2     1      B
2:     1     5     1      B
3:     2     8     1      B

$C
      V1    V2    V3     V4
   <int> <int> <num> <char>
1:     1     3   1.5      C
2:     2     6   1.5      C
3:     1     9   1.5      C

base

split(DF, DF$V4)

$A
  V1 V2  V3 V4
1  1  1 0.5  A
4  2  4 0.5  A
7  1  7 0.5  A

$B
  V1 V2 V3 V4
2  2  2  1  B
5  1  5  1  B
8  2  8  1  B

$C
  V1 V2  V3 V4
3  1  3 1.5  C
6  2  6 1.5  C
9  1  9 1.5  C

dplyr

TB |> group_split(V4)

<list_of<
  tbl_df<
    V1: integer
    V2: integer
    V3: double
    V4: character
  >
>[3]>
[[1]]
# A tibble: 3 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     1   0.5 A    
2     2     4   0.5 A    
3     1     7   0.5 A    

[[2]]
# A tibble: 3 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     2     2     1 B    
2     1     5     1 B    
3     2     8     1 B    

[[3]]
# A tibble: 3 × 4
     V1    V2    V3 V4   
  <int> <int> <dbl> <chr>
1     1     3   1.5 C    
2     2     6   1.5 C    
3     1     9   1.5 C

Split columns

Separating a single column into multiple columns based on a delimiter.

data.table

# Create example data
tmp = data.table(a = c("A:a", "B:b", "C:c"))

tmp[, c("w", "z") := tstrsplit(a, split = ":")]
tmp

        a      w      z
   <char> <char> <char>
1:    A:a      A      a
2:    B:b      B      b
3:    C:c      C      c

        a      w      z
   <char> <char> <char>
1:    A:a      A      a
2:    B:b      B      b
3:    C:c      C      c

base

# Create example data
tmp = data.frame(a = c("A:a", "B:b", "C:c"))

tmp$w = sapply(strsplit(as.character(tmp$a), ":"), `[`, 1)
tmp$z = sapply(strsplit(as.character(tmp$a), ":"), `[`, 2)
tmp

    a w z
1 A:a A a
2 B:b B b
3 C:c C c

dplyr

# Create example data
tmp = tibble(a = c("A:a", "B:b", "C:c"))

tmp = tidyr::separate(tmp, a, c("w", "z"), remove = FALSE)
tmp

# A tibble: 3 × 3
  a     w     z    
  <chr> <chr> <chr>
1 A:a   A     a    
2 B:b   B     b    
3 C:c   C     c

Bind rows

Combining multiple datasets by rows. To begin, we create example data.

x = data.table(1:3)
y = data.table(4:6)
z = data.table(7:9, 0L)

data.table

# Simple row bind
rbind(x, y)

# Simple row bind
rbindlist(list(x, y, x))

# Row bind with different columns
rbind(x, z, fill = TRUE)

# Bind list of data.tables with ID column
rbindlist(list(x, y), idcol = TRUE)

      V1    V2
   <int> <int>
1:     1    NA
2:     2    NA
3:     3    NA
4:     7     0
5:     8     0
6:     9     0

     .id    V1
   <int> <int>
1:     1     1
2:     1     2
3:     1     3
4:     2     4
5:     2     5
6:     2     6

base

# Simple row bind
rbind(x, y)

# Simple row bind
do.call(rbind, list(x, y, x))

# Bind with ID column
rbind(
  cbind(x, id = 1),
  cbind(y, id = 2)
)

      V1    id
   <int> <num>
1:     1     1
2:     2     1
3:     3     1
4:     4     2
5:     5     2
6:     6     2

dplyr

# Simple row bind
bind_rows(x, y)

# Simple row bind
bind_rows(list(x, y, x))

# Row bind with different columns
bind_rows(x, z)

# Bind list with ID column
bind_rows(list(x, y), .id = "id")

      V1    V2
   <int> <int>
1:     1    NA
2:     2    NA
3:     3    NA
4:     7     0
5:     8     0
6:     9     0

       id    V1
   <char> <int>
1:      1     1
2:      1     2
3:      1     3
4:      2     4
5:      2     5
6:      2     6

Bind columns

data.table

# Column bind
base::cbind(x, y)

      V1    V1
   <int> <int>
1:     1     4
2:     2     5
3:     3     6

base

# Column bind
cbind(x, y)

      V1    V1
   <int> <int>
1:     1     4
2:     2     5
3:     3     6

dplyr

# Column bind
bind_cols(x, y)

   V1...1 V1...2
    <int>  <int>
1:      1      4
2:      2      5
3:      3      6

New names:
• `V1` -> `V1...1`
• `V1` -> `V1...2`

Set Operations

Set operations treat datasets as sets and perform mathematical set operations on them.

x <- data.table(c(1, 2, 2, 3, 3))
y <- data.table(c(2, 2, 3, 4, 4))

data.table

# Intersection (rows present in both x and y)
fintersect(x, y)

# Set difference (rows in x but not in y)
fsetdiff(x, y)
# Include duplicates
fsetdiff(x, y, all = TRUE)

# Union (combine unique rows)
funion(x, y)
# Include duplicates
funion(x, y, all = TRUE)

# Test for equality
fsetequal(x, x[order(-V1),])
all.equal(x, x)

      V1
   <num>
1:     2
2:     3

      V1
   <num>
1:     1

      V1
   <num>
1:     1
2:     3

[1] TRUE

[1] TRUE

base

# Intersection
intersect(x, y)

# Set difference
setdiff(x, y)

# Union (unique rows)
union(x, y)
# With duplicates
unlist(c(x, y))

# Test for equality
identical(x, x)
all.equal(x, x)

      V1
   <num>
1:     2
2:     3

      V1
   <num>
1:     1

V11 V12 V13 V14 V15 V11 V12 V13 V14 V15 
  1   2   2   3   3   2   2   3   4   4

[1] TRUE

[1] TRUE

dplyr

# Intersection
dplyr::intersect(x, y)

# Set difference
dplyr::setdiff(x, y)

# Union (unique rows)
dplyr::union(x, y)
# With duplicates
union_all(x, y)

# Test for equality
setequal(x, x[order(-V1),])
all.equal(x, x)

      V1
   <num>
1:     2
2:     3

      V1
   <num>
1:     1

[1] TRUE

[1] TRUE

Read and write

data.table

# Write to CSV
fwrite(DT, "DT.csv")

# Write tab-delimited
fwrite(DT, "DT.txt", sep = "\t")

# Read CSV or tab-delimited
DT1 = fread("DT.csv")
DT2 = fread("DT.txt", sep = "\t")

# Read specific columns
DT3 = fread("DT.csv", select = c("V1", "V4"))
DT4 = fread("DT.csv", drop = "V4")

# Combine multiple files
rbindlist(lapply(c("DT.csv", "DT.csv"), fread))

       V1    V2    V3     V4
    <int> <int> <num> <char>
 1:     1     1   0.5      A
 2:     2     2   1.0      B
 3:     1     3   1.5      C
 4:     2     4   0.5      A
 5:     1     5   1.0      B
 6:     2     6   1.5      C
 7:     1     7   0.5      A
 8:     2     8   1.0      B
 9:     1     9   1.5      C
10:     1     1   0.5      A
11:     2     2   1.0      B
12:     1     3   1.5      C
13:     2     4   0.5      A
14:     1     5   1.0      B
15:     2     6   1.5      C
16:     1     7   0.5      A
17:     2     8   1.0      B
18:     1     9   1.5      C

base

# Write to CSV
write.csv(DF, "DF.csv", row.names = FALSE)

# Write tab-delimited
write.table(DF, "DF.txt", sep = "\t", 
  row.names = FALSE, col.names = TRUE)

# Read CSV or tab-delimited
DF1 = read.csv("DF.csv")
DF2 = read.table("DF.txt", sep = "\t", header = TRUE)

# Read specific columns
DF3 = read.csv("DF.csv")[, c("V1", "V4")]
DF4 = read.csv("DF.csv")[, !(names(read.csv("DF.csv")) %in% "V4")]

# Combine multiple files
do.call(rbind, lapply(c("DF.csv", "DF.csv"), read.csv))

   V1 V2  V3 V4
1   1  1 0.5  A
2   2  2 1.0  B
3   1  3 1.5  C
4   2  4 0.5  A
5   1  5 1.0  B
6   2  6 1.5  C
7   1  7 0.5  A
8   2  8 1.0  B
9   1  9 1.5  C
10  1  1 0.5  A
11  2  2 1.0  B
12  1  3 1.5  C
13  2  4 0.5  A
14  1  5 1.0  B
15  2  6 1.5  C
16  1  7 0.5  A
17  2  8 1.0  B
18  1  9 1.5  C

dplyr

# Write to CSV
TB |> readr::write_csv("TB.csv")

# Write tab-delimited
TB |> readr::write_delim("TB.txt", delim = "\t")

# Read CSV or tab-delimited
TB1 = readr::read_csv("TB.csv")
TB2 = readr::read_delim("TB.txt", delim = "\t")

# Combine multiple files
c("TB.csv", "TB.csv") |>
  purrr::map_dfr(readr::read_csv)

# A tibble: 18 × 4
      V1    V2    V3 V4   
   <dbl> <dbl> <dbl> <chr>
 1     1     1   0.5 A    
 2     2     2   1   B    
 3     1     3   1.5 C    
 4     2     4   0.5 A    
 5     1     5   1   B    
 6     2     6   1.5 C    
 7     1     7   0.5 A    
 8     2     8   1   B    
 9     1     9   1.5 C    
10     1     1   0.5 A    
11     2     2   1   B    
12     1     3   1.5 C    
13     2     4   0.5 A    
14     1     5   1   B    
15     2     6   1.5 C    
16     1     7   0.5 A    
17     2     8   1   B    
18     1     9   1.5 C

Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): V4
dbl (3): V1, V2, V3

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: "\t"
chr (1): V4
dbl (3): V1, V2, V3

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): V4
dbl (3): V1, V2, V3

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): V4
dbl (3): V1, V2, V3

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Clean up local files.

file.remove(c("DT.csv", "TB.csv", "DF.csv", "DT.txt", "TB.txt", "DF.txt"))

[1] TRUE TRUE TRUE TRUE TRUE TRUE

data.table vs. base vs. dplyr

Filter

Integer index

Logical index

Unique

Missing values

Random sample

Other

Sort

Rows

Columns

Select

Keep

Drop

Rename

Advanced selections

Summarize

Single columns

Multiple columns

By group

Modify

Single columns

Multiple columns

Replace

If, else, case

Modify by group

Advanced

Chain (pipe)

Join

Basic Joins

Reshape

Wide to long

Long to wide

Split rows

Split columns

Bind rows

Bind columns

Set Operations

Read and write

`data.table` vs. `base` vs. `dplyr`