data.table apply function on each column

Question

I guess I am pretty close to a solution, but I struggle to combine lapply with data.table. I read a lot about lapply and find some example with data.table, but the way of thinking is new to me and its driving me nuts...

This is my data.table

cells <- c(150, 1,1980,1,1,1,0,0,0,1,2004,3,
       99 , 1,1980,1,1,1,1,0,0,0,2004,4,
       899, 1,1980,0,1,0,1,1,1,1,2007,4,
       789, 1,1982,1,1,1,0,1,1,1,2004,3 )
colname <- c("number","sex", "birthy", "2004","2005", "2006", "2007", "2008", "2009","2010","begy","SeqLen")
rowname <- c("2","3","4","5")
y <- matrix(cells, nrow=4, ncol=12, byrow=TRUE, dimnames = list(rowname,colname))
y <- data.table(y, keep.rownames = TRUE)

I want to step through a vector of column names

cols <- c(paste(2004:2010, sep=" "))

Doing the following operation on just one column works fine!

vec <- "2005"
y[,  (vec) := ifelse((vec) < as.numeric(begy),0, ifelse( ((vec) > as.numeric(begy) + as.numeric(SeqLen) -1) ,0,1)) ]

Creating a function and stepping through the vector seams a good solution, but how? I found this...

dt[ , (cols) := lapply(.SD, "*", -1), .SDcols = cols]

but where can I put my ifelse in this example?

I also read about for-loop and set function, like this...

for (j in cols) set(dt, j = j, value = ifelse((dt[[j]]) < as.numeric(dt[[begy]]),0, ifelse( (dt[[j]] > as.numeric(dt[[begy]]) + as.numeric(dt[[SeqLen]]) -1) ,0,1)))

but this is nonsense.

Thanks Alina

Create a function such as f <- function(x) ifelse(x < as.numeric(begy) etc... and then use it as in the example you found dt[ , (cols) := lapply(.SD, f), .SDcols = cols]. Regarding your function itself, I would recommend you to Google up how to avoid embedded ifelse statements. — David Arenburg
I created a function like you suggested and used this function. But I receive a error message "Object begy not found". I suppose, only the columns in cols are available for the transformation? — Alina Ludewig
So add it as a parameter, e.g. f <- function(x, begy) x * begy ; y[, lapply(.SD, f, begy), .SDcols = cols]. Creating functions and using lapply is really basic R operations and not related to data.table — David Arenburg
Got me, I am a beginner and learning new stuff every day. But still your example does not work ...I tried this...f <- function(x,begy, SeqLen){ ifelse(x < as.numeric(begy),0, ifelse( (x > as.numeric(begy) + as.numeric(SeqLen) -1) ,0,1)) } y[, lapply(.SD, f, begy,SeqLen), .SDcols = cols] but the output is not as expected. — Alina Ludewig

chinsoon12 chinsoon12 · Accepted Answer · 2018-08-28T08:53:11

Seems like you are setting the years columns with 1 if it is between begy and begy + SeqLen - 1 for each row. Here is another way to do this:

y[order(rn), 
    (grep("^20", names(y), value=TRUE)) := 
        dcast(y[, seq(begy, by=1, length.out=SeqLen), by=.(rn)], rn ~ V1, length)[,-1L]]
y

output:

   rn number sex birthy 2004 2005 2006 2007 2008 2009 2010 begy SeqLen
1:  2    150   1   1980    1    1    1    0    0    0    0 2004      3
2:  3     99   1   1980    1    1    1    1    0    0    0 2004      4
3:  4    899   1   1980    0    0    0    1    1    1    1 2007      4
4:  5    789   1   1982    1    1    1    0    0    0    0 2004      3

Explanation:

Create a sequence of years for each row, then use dcast to do a one-hot encoding. Use the output to overwrite the years columns.

order(rn) will ensure that we don't overwrite rows incorrectly after dcast

Frank's method is way faster:

y[, as.character(2004:2010) := 
    lapply(2004:2010, function(x) as.integer(between(x, begy, begy + SeqLen - 1)))]

data.table apply function on each column

1 Answers