R language: problems computing "group by" or split with ff package

Question

I am nearly new to R, so sorry if I make some basic questions, but I can not find a solution to this "simple" problem: Having a database (big one, 25 million rows, 14 cols) of patients, I have several rows for each "id", with for example this structure:

"id" "birth_date"  "treatment"  "date_treatment"
123   2002-01-01    2            2011-01-03
123   2002-01-01    3            2011-10-03
124   2002-01-01    6            2009-11-07
124   2002-01-01    NA           NA
...   .....         ......       ........ 
1022  2007-01-01    4            2011-01-06

I have to use ff package to be able to work with little amount of RAM, so ALL the processes should be into ff functions. And I want to know, for each single "id", which is the minimum "age" when he/she received a treatment = 2 or 4. so, that would be, in each single id, in generic code :

if(treatment in c(2,4)) then min(date_treatment - birth_date)

I only want to keep those minimum "ages" data and perhaps the ids.

One solution is to do:

age_c <- (data$date_treatment - data$birth_date)/365.25;
data$age_c <- age_c;
idx <- ffwhich( data, treatment %in% c(2,4) );
result  <- data[idx,];

This keeps all the process into ff, and no memory problems, but... I still need to find a way to take those minimums ages for each id... ffdfdply seems to be able to do that:

age_fun <- function(x){ 
  min_ <- min.ff(x$age_c); 
  data.frame( age = min_);  
}

 result2 <- ffdfdply(x = data,
               split = data$id,
               FUN = function(x) age_fun(x),
               BATCHBYTES = 5000,
               trace=TRUE
 ); 

Which takes looooong time and also gives me a lot of different errors....

Any solution to that?
It is a general problem that in SAS or SQL are easy to do, but i do not find the right combination in R. So the general question would be:

how to compute row-column functions for identical values (groups) of a variable (row) in very big data sets ???

Thanks !!

Answer 1

ffdfdply is the function you need to solve your question but you are using it wrong and inefficiently. Think about ffdfdply as getting in each FUN, the maximum number of data R allows you to put in RAM but still making sure you get all your data by each id in RAM (or possibly several id's if it fits into RAM).

So taking BATCHBYTES 5000 is rather small (do you really only have 5 kilobytes of RAM - I guess not - did you install R on a Commodore from the 90's?) Next, your FUN age_fun is written wrongly. To see what you get in the FUN you can print it out. as in FUN=function(x){ print(head(x))); x}. In FUN, you get data in RAM, so you don't need to use min.ff, min will do.

Also note the remark of joran: you get multiple id's in each chunk if your RAM allows to. Make sure your FUN does a split-apply-combine strategy or use dply in FUN. And another remark to speed things up. Do you really need to pass the whole ffdf. You only need the columns you use in the function and the split. So ffdfdply(x = data[c("id","age_c","treatment")], split = ...) will do otherwise you get data in RAM which is not needed.

So to be short, something like this will do the trick

require(doBy)
result2 <- ffdfdply(
  x = data[c("id","age_c","treatment")], split = data$id,
  FUN = function(x) summaryBy(age_c ~ id, data=subset(x, treatment %in% c(2,4)), FUN=min))

If you also want to have your persons who did not have treatment 2 and 4 whatsover, do like this.

require(doBy)
result2 <- ffdfdply(
  x = data[c("id","age_c","treatment")], split = data$id,
  FUN = function(x) {
   persons <- unique(x[, "id", drop=FALSE])
   result <- merge(
     persons,
     summaryBy(age_c ~ id, data=subset(x, treatment %in% c(2,4)), FUN=min),
     by.x="id", by.y="id", all.x=TRUE, all.y=FALSE
     )
   result
})