What methods can we use to reshape VERY large data sets?
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 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 the various number of rows to obtain different sizes (see the 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 the 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)
If your real data is as regular as your sample data we can be quite efficient by noticing that reshaping a matrix is really just changing its dim attribute.
1st on very small data
library(data.table)
library(microbenchmark)
library(tidyr)
matrix_spread <- function(df1, key, value){
unique_ids <- unique(df1[[key]])
mat <- matrix( df1[[value]], ncol= length(unique_ids),byrow = TRUE)
df2 <- data.frame(unique(df1["tms"]),mat)
names(df2)[-1] <- paste0(value,".",unique_ids)
df2
}
n <- 3
t1 <- 4
df1 <- expand.grid(id=1:n, tms=as.POSIXct(1:t1, origin="1970-01-01"))
df1$y <- rnorm(nrow(df1))
reshape(df1, idvar="tms", timevar="id", direction="wide")
# tms y.1 y.2 y.3
# 1 1970-01-01 01:00:01 0.3518667 0.6350398 0.1624978
# 4 1970-01-01 01:00:02 0.3404974 -1.1023521 0.5699476
# 7 1970-01-01 01:00:03 -0.4142585 0.8194931 1.3857788
# 10 1970-01-01 01:00:04 0.3651138 -0.9867506 1.0920621
matrix_spread(df1, "id", "y")
# tms y.1 y.2 y.3
# 1 1970-01-01 01:00:01 0.3518667 0.6350398 0.1624978
# 4 1970-01-01 01:00:02 0.3404974 -1.1023521 0.5699476
# 7 1970-01-01 01:00:03 -0.4142585 0.8194931 1.3857788
# 10 1970-01-01 01:00:04 0.3651138 -0.9867506 1.0920621
all.equal(check.attributes = FALSE,
reshape(df1, idvar="tms", timevar="id", direction="wide"),
matrix_spread (df1, "id", "y"))
# TRUE
Then on bigger data
(sorry I can't afford to make a huge computation now)
n <- 100
t1 <- 5000
df1 <- expand.grid(id=1:n, tms=as.POSIXct(1:t1, origin="1970-01-01"))
df1$y <- rnorm(nrow(df1))
DT1 <- as.data.table(df1)
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),
matrix_spread = matrix_spread(df1, "id", "y"),
times=3L)
# Unit: milliseconds
# expr min lq mean median uq max neval
# reshape 4197.08012 4240.59316 4260.58806 4284.10620 4292.34203 4300.57786 3
# dcast 57.31247 78.16116 86.93874 99.00986 101.75189 104.49391 3
# dcast.dt 114.66574 120.19246 127.51567 125.71919 133.94064 142.16209 3
# tidyr 55.12626 63.91142 72.52421 72.69658 81.22319 89.74980 3
# matrix_spread 15.00522 15.42655 17.45283 15.84788 18.67664 21.50539 3
Not too bad!
About memory usage, I guess if reshape handles it my solution will, if you can work with my assumptions or preprocess the data to meet them:
- data is sorted
- we have 3 columns only
- for all id values we find all tms values