Memory issues with data.table for big data (dcast, merge, NA)

[sjain777]

Hi,
I am having memory issues with data.table on big data. By big data, I mean when number of rows is 200K and number of columns is 12K. I have an example below with very small data to show the steps I am doing, and where with my actual data the steps fail. If you could please verify if I am using data.table in the optimal way, or if not, suggest the best way, that would be very much appreciated!

I am using data.table (version 1.9.6) on R 3.2.2 (64-bit) on a 64-bit Windows 7 machine (with 16 GB of RAM):

Example:

1. I have 3 data.table: Dt1, Dt2, Dt3:

RowId = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) # unique values Location = c("Chicago", "Amsterdam", "New York", "LA", "Chicago", "California", "Florida", "New York", "New Jersey", "LA") Dt1 <- data.table(RowId, Location) setkey(Dt1, "RowId") RowId = c(1, 1, 3, 4, 4) DayOfMonth = c("Feb", "Mar", "Apr", "Mar", "May") Dt2 <- data.table(RowId, DayOfMonth) RowId = c(2, 3, 6, 1, 6, 8) DayOfWeek = c("Mon", "Fri", "Wed", "Wed", "Tue", "Mon") Dt3 <- data.table(RowId, DayOfWeek)

2. I need to create one table from the above three tables, with the RowId column having unique values, so that this table can be fed as input to classification methods, like rpart (Decision Tree modeling).

2a) I, therefore, use dcast.data.table for Dt2 and Dt3 in order to create the respective RowId column with unique values:

Dt2Wide <- dcast.data.table(Dt2, RowId ~ DayOfMonth, function(x) TRUE, fill = FALSE) Dt3Wide <- dcast.data.table(Dt3, RowId ~ DayOfWeek, function(x) TRUE, fill = FALSE)

2b) Then I do a LEFT JOIN of Dt1 with Dt3Wide and Dt2Wide (so I get all the RowId values from Dt1):
temp <- Dt3Wide[Dt2Wide[Dt1]]

3. I then need to remove all NA from the LEFT JOIN in the merged data.table from step 2b.

temp <- replace(temp, is.na(temp), FALSE)

Problems:

In step 2a, when my actual data has 200K rows, and 5K and 7K columns (from the unique values of Dt2::DayOfMonth and Dt3::DayOfWeek, respectively), the resulting space allocated for Dt2Wide and Dt3Wide is 4 GB and 5 GB (when Dt2 and Dt3 are 10-14 MB in size). I can create the 4GB sized Dt2Wide. But get memory allocation error for the 5 GB Dt3Wide object (RAM being 16 GB).

Error: cannot allocate vector of size 5.0 Gb

Furthermore, Step 2b (merging of all 3 tables) results in an object of size 10 GB which again results in memory allocation error during Step 3 (removal of NA).

So is there a way to get around this problem when the data has 200K or more rows, and 12K or more columns?

I have tried using ffdf, and merge.ffdf. But merge.ffdf is much slower than the merge from data.table, and also removal of NA from data.table is super fast compared to that from an ffdf object. So if possible, I would really like to use data.table for my application.

Thanks,
Supriya

[jangorecki]

@sjain777 Did you try to chunk your data during processing?
In case of lack of memory there isn't much that can be done. You will probably need to use a machine with more memory, or chunk your data. If you have more machines available you can try big.data.table but it is quite limited in scope comparing to data.table, it basically handles chunking on remote machines.

[sjain777]

Hi, how does one chunk the data during dcast.data.table? Are not all the unique values that exist in the entire data need to be known when processing any one chunk? How does one specify all unique values in the dcast operation over any one chunk?

Getting more RAM is one option, but does not seem as a solution for long term, since any amount of memory may be small for a larger dataset. So handling the processing of data more efficiently is what I am looking for, if possible.

Thanks!

[jangorecki]

@sjain777 Creating well reproducible example (copy+paste) would definitely helps to address your problem.
You can try following commands on your Dt2Wide and Dt3Wide (just don't post exact output of your 7K here):

Dt2Wide[, lapply(lapply(.SD, sum), `/`, .N), .SDcols = -1L]

(assuming you have logical values in your real Dt2Wide and Dt3Wide)
This will check how sparse are the data in columns, if they are heavily sparse (close to 0), then it means it is very inefficient way to store the data in that data model. If close to 1 then your are not wasting much memory due to model of your data, that is good.
I don't know rpart input structure requirement but if you have mostly a sparse data then maybe good way would be to look for some other api to rpart which allows sparse data on input.

[arunsrinivasan]

Please read support, and provide a reproducible example.

Feel free to reopen once you've edited the post with the example.

[Ic3fr0g]

I faced a similar problem when trying to create an identity matrix using dcast.
Here is a reproducible example -

library(data.table)
coco <- data.table(x = 1:145603)
popo <- dcast(coco, x~x, fun.aggregate = function(i){ sum( !is.na(i) ) } )
# Using 'x' as value column. Use 'value.var' to override
# Error: cannot allocate vector of size 79.0 Gb

So my hypothesis is -

object.size(as.character(coco$x))
# 8153808 bytes

Also these are my system particulars(though I tried the same code on a UBUNTU system and got the same error) -

sessionInfo()
# R version 3.3.2 (2016-10-31)
# Platform: x86_64-w64-mingw32/x64 (64-bit)
# Running under: Windows >= 8 x64 (build 9200)
# 
# locale:
# [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
# [3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
# [5] LC_TIME=English_United States.1252    
# 
# attached base packages:
# [1] stats     graphics  grDevices utils     datasets  methods   base     
# 
# other attached packages:
#  [1] data.table_1.10.4