Add each element of a vector as new column in data.table-CodePudding

If a vector has 5 elements, I need to add 5 columns to my data.table. My first new column has for unique value the element 1 of my vector.

This is something I can do with a for, as in my reprex below :

foo <- data.table(col1 = 1:10, col2 = sample(letters[1:5], replace = TRUE))

fun.add <- function(DT) {
  
  v1 <- c(3, 5.5, 9)
  v2 <- c("x", "y", "z")
  
  for (j in 1:3) {
    DT[, paste0("aaa", j) := v1[j]]
    DT[, paste0("bbb", j) := v2[j]]
  }
  
  # DT[, paste0("aaa", 1:3),  := ...]
}

fun.add(foo)

I would prefer to do it without the for and rather like in the comment line. In this case, I can't use lapply and .SD ... Is there a way to ? Another way ?

Thanks !!

CodePudding user response：

We could do this more efficiently instead of doing this in a for loop i.e. convert the vectors to a list with as.list and create the columns by assignment (:=) to the vector of column names created with paste (paste is vectorized)

foo[, paste0("aaa", seq_along(v1)) := as.list(v1)]
foo[, paste0("bbb", seq_along(v2)) := as.list(v2)]

If we wrap it in a function

fun.add <- function(DT) {
     v1 <- c(3, 5.5, 9)
     v2 <- c("x", "y", "z")
     DT[, paste0("aaa", seq_along(v1)) := as.list(v1)]
     DT[, paste0("bbb", seq_along(v2)) := as.list(v2)][]
    return(DT)
}

-testing

> fun.add(foo)
    col1 col2 aaa1 aaa2 aaa3 bbb1 bbb2 bbb3
 1:    1    a    3  5.5    9    x    y    z
 2:    2    e    3  5.5    9    x    y    z
 3:    3    d    3  5.5    9    x    y    z
 4:    4    c    3  5.5    9    x    y    z
 5:    5    b    3  5.5    9    x    y    z
 6:    6    a    3  5.5    9    x    y    z
 7:    7    e    3  5.5    9    x    y    z
 8:    8    d    3  5.5    9    x    y    z
 9:    9    c    3  5.5    9    x    y    z
10:   10    b    3  5.5    9    x    y    z

CodePudding user response：

here is my solution.

INPUT:

v1  <- c(3, 5.5, 9)
v2  <- c("x", "y", "z")
foo <- data.table(col1 = 1:10, col2 = sample(letters[1:5], replace = TRUE))

N_col0 <- NCOL(foo) # used later on to redefine column class

CODE:

v12 <- c(rbind(v1, v2))
m12 <- matrix(rep(v12, each = NROW(foo)), NROW(foo))
foo <- cbind(foo, m12)
numeric_col <- grep("[0-9]", v12)   N_col0
for(j in numer_col){set(foo, j=j, value=as.numeric(foo[[j]]))}

CODE EXPLAINED:

1.Combine v1 & v2.

v12 <- c(rbind(v1, v2)) # note it is a character vector

print(v12)
[1] "3"   "x"   "5.5" "y"   "9"   "z"

2.Create a character matrix of v12. You can set column names at this stage if you like.

m12 <- matrix(rep(v12, each = NROW(foo)), NROW(foo))

print(v12)
      [,1] [,2] [,3]  [,4] [,5] [,6]
 [1,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [2,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [3,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [4,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [5,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [6,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [7,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [8,] "3"  "x"  "5.5" "y"  "9"  "z" 
 [9,] "3"  "x"  "5.5" "y"  "9"  "z" 
[10,] "3"  "x"  "5.5" "y"  "9"  "z"

3.cBind foo & m12.

foo<- cbind(foo, m12)

print(foo)
    col1 col2 V1 V2  V3 V4 V5 V6
 1:    1    b  3  x 5.5  y  9  z
 2:    2    d  3  x 5.5  y  9  z
 3:    3    d  3  x 5.5  y  9  z
 4:    4    e  3  x 5.5  y  9  z
 5:    5    d  3  x 5.5  y  9  z
 6:    6    b  3  x 5.5  y  9  z
 7:    7    d  3  x 5.5  y  9  z
 8:    8    d  3  x 5.5  y  9  z
 9:    9    e  3  x 5.5  y  9  z
10:   10    d  3  x 5.5  y  9  z

4.All new columns are character columns. To fix this we can detect what elements in the vector 12 are numeric and use the function set of data table.

foo[, lapply(.SD, class)]

      col1      col2        V1        V2        V3        V4        V5        V6
1: integer character character character character character character character


numeric_col <- grep("[0-9]", v12)   N_col0
for(j in numer_col){set(foo, j=j, value=as.numeric(foo[[j]]))}

foo[, lapply(.SD, class)]

  col1      col2      V1        V2      V3        V4      V5        V6
1: integer character numeric character numeric character numeric character