Benchmarking datatables in R using lapply, is it slower?

user_10205259 0
Viewed 222

I have numerous datasets that will eventually be compared against one another. I've read that data.table and using lapply was the fastest way to analyze the data and saw some benchmark comparisons in the posts. I've noticed it lag a little as the datasets get larger and did some benchmarking and it was considerably slower than just using "summarize". Including a very basic table and code in case I am missing something on how to optimize the data.table speed.

ARM <- rep(seq(1:10), 10000)
TEST <- rep(seq(1:100), 1000)
TIME <- rep(seq(1:5), 20000)
SEX <- rep(1:2, 50000)
RESULTS <- rnorm(100000, mean = 50, sd = 10)

tst <- as.data.frame(cbind(ARM, TEST, TIME, SEX, RESULTS))
tstdt <- as.data.table(tst)
setDT(tstdt)

microbenchmark(tstdt[,.(n = .N), by = list(ARM, TEST, TIME, SEX)],
               tst %>% group_by(ARM, TEST, TIME, SEX) %>% summarize(mean = mean(TEST),n = n()),
               tstdt[,(lapply(.SD, mean)), by = .(ARM, TEST, TIME, SEX), .SDcols = "TEST"])

That results with the summarize method being the fastest by quite a bit.

       min        lq     mean    median        uq      max neval
1 19.65800 21.841651 23.75156 22.585946 23.572930 43.24424   100
2  8.01111  8.487736  9.36991  8.852225  9.451928 19.27691   100
3 22.43461 23.452590 25.07495 24.179198 25.047663 85.74855   100

Any information on how to optimize data.tables or help with where I went wrong would be appreciated.

Suggested larger data and code for benchmarking, including configurably larger data, fewer runs, not using .SDcols, and taking the mean of the RESULTS column. 

library(dplyr)
library(data.table)

n = 5e6
ARM <- rep(seq(1:10), n / 10)
TEST <- rep(seq(1:100), n / 100)
TIME <- rep(seq(1:5), n / 5)
SEX <- rep(1:2, n / 2)
RESULTS <- rnorm(n, mean = 50, sd = 10)

tst <- data.frame(ARM, TEST, TIME, SEX, RESULTS)
tstdt <- as.data.table(tst)
setDT(tstdt)

microbenchmark::microbenchmark(
  dplyr = tst %>% group_by(ARM, TEST, TIME, SEX) %>% summarize(mean = mean(RESULTS),n = n()),
  dt = tstdt[, .(mean = mean(RESULTS), n = .N), by = .(ARM, TEST, TIME, SEX)],
  times = 10
)
data.table lapply r
1 Answers

In this case, as.data.frame(cbind()) was causing the slow down. cbind() creates a matrix and a matrix can only have one class. Due to the RESULTS being a double, this caused everything, even the IDs, as a double:

> tst <- as.data.frame(cbind(ARM, TEST, TIME, SEX, RESULTS))
> str(tst)
'data.frame':   100000 obs. of  5 variables:
 $ ARM    : num  1 2 3 4 5 6 7 8 9 10 ...
 $ TEST   : num  1 2 3 4 5 6 7 8 9 10 ...
 $ TIME   : num  1 2 3 4 5 1 2 3 4 5 ...
 $ SEX    : num  1 2 1 2 1 2 1 2 1 2 ...
 $ RESULTS: num  39.6 43.7 55.2 56.4 57.3 ...

If we instead look at the data.frame() direct call, the grouping IDs are now integers. 

> tst <- data.frame(ARM, TEST, TIME, SEX, RESULTS)
> str(tst)
'data.frame':   100000 obs. of  5 variables:
 $ ARM    : int  1 2 3 4 5 6 7 8 9 10 ...
 $ TEST   : int  1 2 3 4 5 6 7 8 9 10 ...
 $ TIME   : int  1 2 3 4 5 1 2 3 4 5 ...
 $ SEX    : int  1 2 1 2 1 2 1 2 1 2 ...
 $ RESULTS: num  39.6 43.7 55.2 56.4 57.3 ...

Timing w/ 100,000 rows:

Unit: milliseconds
         expr       min        lq      mean    median        uq       max neval
  dplyr_cbind 12.731302 12.986901 13.194071 13.089201 13.274701 14.319901    10
 dtplyr_cbind 21.231401 21.414101 24.898921 21.887852 23.049401 50.377402    10
     dt_cbind 18.195001 18.594502 22.280561 19.014401 27.485001 31.494400    10
    dplyr_tib  8.678001  8.720201  8.847401  8.808801  8.967401  9.092001    10
    dtplyr_dt  7.426001  7.518801  8.073871  7.964500  8.181101  9.204701    10
        dt_df  4.875301  5.064301  5.314011  5.235101  5.514301  6.148501    10

Timing w/ 5M rows:

Unit: milliseconds
         expr       min        lq      mean    median        uq       max neval
  dplyr_cbind  811.6987  818.4339  832.8765  830.2082  837.1726  889.3064    10
 dtplyr_cbind 1259.2805 1270.3635 1353.1941 1314.1047 1387.2763 1525.5491    10
     dt_cbind 1150.1462 1266.2028 1308.9606 1309.3525 1345.1788 1446.7356    10
    dplyr_tib  576.7411  582.4006  596.1008  587.3667  596.0505  650.3751    10
    dtplyr_dt  200.4274  211.2533  238.1208  221.9134  247.0926  334.4624    10
        dt_df  170.8695  175.2081  181.6870  177.9083  179.1992  227.1371    10

Summary: One way to improve data.table performance is convert any ID columns that may be floating point into an integer. Here we get a small increase in performance in lower datasets and a larger increase in bigger data sets.

user_113550660
Related