collapse might rock your world
collapse is a new force in the data transformation stakes. In this post, I try out one simple function and compare it to data.table and dplyr
The NHS-R Community site prompted this post, specifically a question to the help-with-r channel on Slack.
Someone asked about obtaining unique values in a 60M row / 6 column dataset.
Naturally, data.table came up, and a few of us made suggestions for improvement, but we didn’t seem to be hitting on a magic solution.
Without being able to get my hands on the actual data, I decided to have play around at home.
I don’t know what the real life data strucure was, but this is the simple 4 column data set I produced initially, named testdata :
str(testdata)
# Classes ‘data.table’ and 'data.frame': 60000000 obs. of 4 variables:
# $ RANDOM_STRING: chr "aaaaa" "aaaaa" "aaaaa" "aaaaa" ...
# $ START_DATE : Date, format: "2015-01-10" "2015-01-11" "2015-01-13" "2015-01-24" ...
# $ randint : int 1 4 4 5 5 3 8 9 4 5 ...
# $ EXPIRY_DATE : Date, format: "2015-11-02" "2015-10-29" "2015-10-27" "2015-09-11" ...
# - attr(*, ".internal.selfref")=<externalptr>
# - attr(*, "sorted")= chr [1:4] "RANDOM_STRING" "START_DATE" "EXPIRY_DATE" "randint"
#
This was created and saved to an RDS file, and converted to a keyed data.table as part of the process.
haskey(testdata)
#[1] TRUE
First up, calling data.table::unique
system.time(uniqdat <- unique(testdata))
Timings to return 5993540 unique observations
##############################
# user system elapsed
# 20.01 10.52 37.42
##############################
Does it make any difference if I don’t assign to a variable ?
system.time(unique(testdata))
##############################
# user system elapsed
# 17.88 8.50 25.47
##############################
Looks like it does.
Now, in the real life example, the analyst is seeing severe issues with timing, in terms of hours rather than seconds. I was researching alternative solutions and chanced upon the collapse package:
It’s pretty amazing. After a couple of false starts because I didn’t read the documentation, I successfully called the function - you have to specify the columns you want to find unique values for.
system.time(uniqdat2 <- funique(testdata,cols = 1:4))
##############################
# user system elapsed
# 4.33 1.74 8.23
##############################
Testing without assignment:
system.time(funique(testdata,cols = 1:4))
##############################
# user system elapsed
# 4.66 2.60 8.00
##############################
Pretty cool - a huge reduction in time.
From the introductory vignette:
“collapse is a C/C++ based package for data transformation and statistical computing in R. It’s aims are:
To facilitate complex data transformation, exploration and computing tasks in R. To help make R code fast, flexible, parsimonious and programmer friendly.”
I look forward to getting more familiar with this package.
Finally, out of interest, I tried dplyr timings. I had already seen that dplyr often outperforms data.table for finding unique / distinct values:
# dplyr for thoroughness
system.time(uniqdat3 <- distinct(testdata))
##############################
# user system elapsed
# 14.87 2.86 20.82
##############################
Not as fast as collapse, but faster than data.table.
I also tried without assigining to a variable, but each time I got ‘out of memory’ errors.
My personal laptop is a bit underpowered for this dataset (only 8GB RAM) and it couldn’t handle it.
So now I am in the process of upgrading my laptop ( it’s easier to swap out the SSD and RAM than get a new one), which will give me double the power, at which point I might try this again.
I’m impressed with collapse, it’s a really nice package (just look at the detail in the logo, you can tell this is the real deal) and I can’t wait to become more conversant with its functionality.
Post publishing update
I upgraded my RAM (now 16GB) and repeated the exercise:
Timings are as follows:
# user system elapsed
# 19.31 1.46 14.82 data.table (with assignment)
# 18.38 1.64 13.72 data.table (no assignment)
# 4.70 0.59 5.42 collapse (with assignment)
# 4.36 0.50 4.92 collapse (no assignment)
# 13.52 0.33 14.18 dplyr (with assignment)
# 12.65 0.53 13.45 dplyr (no assignment)
