R: Update a column in a data.table

Question

I have a big data.table of the following structure

DT = data.table(Year=c("1993","1994"), "1"=c(NA,10), "2"=c(50, 40))

and I want to update the 2nd column "1". Each entry with "NA" shall be replaced by "0". But either

DT[is.na(1), 1:=0]

nor

DT[is.na("1"), "1":=0]

work. The problem is, that the column names - except "Year" - are numbers. Of course, via

setnames(DT, "1", "X1")
DT[is.na(X1), X1:=0]

I can solve this problem for this small example, but the columns names shall be numbers and the huge data.table has more than 50 columns. Has anybody an idea, what I have to do?

Answer 1

You could use backticks

DT[is.na(`1`), `1`:=0]
DT
#   Year  1  2
#1: 1993  0 50
#2: 1994 10 40

If there are more columns,

nm1 <- names(DT1)[-1]
DT1[,(nm1):= lapply(.SD, function(x) replace(x, is.na(x), 0)), .SDcols=nm1]

DT1
#   Year  1  2  3 4
#1: 1993  0 50 10 0
#2: 1994 10 40  0 4

Or based on comments from @Arun, efficient way for multiple columns would be using set. When compared to the replace method, this updates by reference.

for(j in 2:ncol(DT1)){
  indx <- which(is.na(DT1[[j]]))
  set(DT1, i=indx, j=j, value=0)
}

data

 DT1 <-  data.table(Year=c("1993","1994"), "1"=c(NA,10), 
             "2"=c(50, 40), "3"=c(10, NA), "4"=c(NA, 4))