What methods can we use to reshape VERY large data

Question

When due to very large data calculations will take a long time and, hence, we don't want them to crash, it would be valuable to know beforehand which reshape method to use.

Lately, methods for reshaping data have been further developed regarding performance, e.g. data.table::dcast and tidyr::spread. Especially dcast.data.table seems to set the tone ^{[1], [2], [3], [4]}. This makes other methods as base R's reshape in benchmarks seem outdated and almost useless ^[5].

Theory

However, I've heard that reshape was still unbeatable when it comes to very large datasets (probably those exceeding RAM) because it's the only method that can handle them and therefore it still has it's right to exist. A related crash report using reshape2::dcast supports this point ^[6]. At least one reference gives a hint that reshape() might indeed had advantages over reshape2::dcast for really "big stuff" ^[7].

Method

Seeking for evidence for that, I thought it was worth the time to do some research. So I did a benchmark with simulated data of different size which increasingly exhaust the RAM to compare reshape, dcast, dcast.data.table, and spread. I looked at simple datasets with three columns, with various number of rows to obtain different sizes (see code at the very bottom).

> head(df1, 3)
  id                 tms         y
1  1 1970-01-01 01:00:01 0.7463622
2  2 1970-01-01 01:00:01 0.1417795
3  3 1970-01-01 01:00:01 0.6993089

The RAM size was just 8 GB, which was my threshold to simulate "very large" datasets. In order to keep the time for the calculations reasonable, I made only 3 measurements for each method and focused on reshaping from long to wide.

Results

unit: seconds
       expr       min        lq      mean    median        uq       max neval size.gb size.ram
1  dcast.DT        NA        NA        NA        NA        NA        NA     3    8.00    1.000
2     dcast        NA        NA        NA        NA        NA        NA     3    8.00    1.000
3     tidyr        NA        NA        NA        NA        NA        NA     3    8.00    1.000
4   reshape 490988.37 492843.94 494699.51 495153.48 497236.03 499772.56     3    8.00    1.000
5  dcast.DT   3288.04   4445.77   5279.91   5466.31   6375.63  10485.21     3    4.00    0.500
6     dcast   5151.06   5888.20   6625.35   6237.78   6781.14   6936.93     3    4.00    0.500
7     tidyr   5757.26   6398.54   7039.83   6653.28   7101.28   7162.74     3    4.00    0.500
8   reshape  85982.58  87583.60  89184.62  88817.98  90235.68  91286.74     3    4.00    0.500
9  dcast.DT      2.18      2.18      2.18      2.18      2.18      2.18     3    0.20    0.025
10    tidyr      3.19      3.24      3.37      3.29      3.46      3.63     3    0.20    0.025
11    dcast      3.46      3.49      3.57      3.52      3.63      3.74     3    0.20    0.025
12  reshape    277.01    277.53    277.83    278.05    278.24    278.42     3    0.20    0.025
13 dcast.DT      0.18      0.18      0.18      0.18      0.18      0.18     3    0.02    0.002
14    dcast      0.34      0.34      0.35      0.34      0.36      0.37     3    0.02    0.002
15    tidyr      0.37      0.39      0.42      0.41      0.44      0.48     3    0.02    0.002
16  reshape     29.22     29.37     29.49     29.53     29.63     29.74     3    0.02    0.002

^{(Note: Benchmarks were performed on a secondary MacBook Pro with Intel Core i5 2.5 GHz, 8GB DDR3 RAM 1600 MHz.)}

Obviously, dcast.data.table seems to be always fastest. As expected, all packaged approaches failed with very large data sets, probably because the calculations then exceeded the RAM memory:

Error: vector memory exhausted (limit reached?)
Timing stopped at: 1.597e+04 1.864e+04 5.254e+04

Only reshape handled all data sizes, albeit very slowly.

Conclusion

Package methods like dcast and spread are invaluable for data sets that are smaller than the RAM or whose calculations do not exhaust the RAM. If the data set is larger than the RAM memory, package methods will fail and we should use reshape.

Question

Could we conclude like this? Could someone clarify a little why the data.table/reshape and tidyr methods fail and what their methodological differences are to reshape? Is the only alternative for vast data the reliable but slow horse reshape? What can we expect from methods that have not been tested here as tapply, unstack, and xtabs approaches ^{[8], [9]}?

Or, in short: What faster alternative is there if anything but reshape fails?

---

Data/Code

# 8GB version
n <- 1e3      
t1 <- 2.15e5  # approx. 8GB, vary to increasingly exceed RAM

df1 <- expand.grid(id=1:n, tms=as.POSIXct(1:t1, origin="1970-01-01"))
df1$y <- rnorm(nrow(df1))

dim(df1)
# [1] 450000000         3

> head(df1, 3)
id                 tms         y
1  1 1970-01-01 01:00:01 0.7463622
2  2 1970-01-01 01:00:01 0.1417795
3  3 1970-01-01 01:00:01 0.6993089

object.size(df1)
# 9039666760 bytes

library(data.table)
DT1 <- as.data.table(df1)

library(microbenchmark)
library(tidyr)
# NOTE: this runs for quite a while!
mbk <- microbenchmark(reshape=reshape(df1, idvar="tms", timevar="id", direction="wide"),
                      dcast=dcast(df1, tms ~ id, value.var="y"),
                      dcast.dt=dcast(DT1, tms ~ id, value.var="y"),
                      tidyr=spread(df1, id, y),
                      times=3L)