Dispatch values in list column to separate columns

Question

I have a data.table with a list column "c":

df <- data.table(a = 1:3, c = list(1L, 1:2, 1:3))
df
   a     c
1: 1     1
2: 2   1,2
3: 3 1,2,3

I want to create separate columns for the values in "c".

I create a set of new columns F_1, F_2, F_3:

mmax <- max(df$a)
flux <- paste("F", 1:mmax, sep = "_")
df[, (flux) := 0]

df
   a     c F_1 F_2 F_3
1: 1     1   0   0   0
2: 2   1,2   0   0   0
3: 3 1,2,3   0   0   0

I want to dispatch values in "c" to columns F_1, F_2, F_3 like this:

df

   a     c F_1 F_2 F_3
1: 1     1   1   0   0
2: 2   1,2   1   2   0
3: 3 1,2,3   1   2   3

What I have tried:

comp_vect <- function(vec, mmax){
  vec   <- vec %>% unlist()
  n     <- length(vec)
  answr <- c(vec, rep(0, l = mmax -n)) 
}


df[ , ..flux := mapply(comp_vect, c, mmax)]

The expected data.table is :

> df

   a     c F_1 F_2 F_3
1: 1     1   1   0   0
2: 2   1,2   1   2   0
3: 3 1,2,3   1   2   3

See also: [How to flatten R data frame that contains lists?](https://stackoverflow.com/questions/34206003/how-to-flatten-r-data-frame-that-contains-lists); [Fastest way to transpose a list in R / Rcpp](https://stackoverflow.com/questions/30164803/fastest-way-to-transpose-a-list-in-r-rcpp) — Henrik, Oct 31 '19 at 09:28
@Henrik shouldn't you post this as the answer? `df[, paste("F_", 1:3) := transpose(d, fill = 0L)]` — s_baldur, Oct 31 '19 at 09:58
You could also use `lengths` to determine the number of column names to be created; `paste0("f_", seq_len(max(lengths(df$c))))` — Henrik, Oct 31 '19 at 14:19

Guillaume Boucher · Answer 1 · 2019-10-31T02:18:12.433

Solution :

for(idx in seq(max(sapply(df$c, length)))){  # maximum number of values according to all the elements of the list
  set(x = df,
      i = NULL,
      j = paste0("F_",idx),  # column's name
      value = sapply(df$c, function(x){
        if(is.na(x[idx])){
          return(0)  # 0 instead of NA
        } else {
          return(x[idx])
        }
      })
  )
}

Explications :

We can extract the values from a list like this :

sapply(df$c, function(ll) return(ll[1])) # first value
[1] 1 1 1
sapply(df$c, function(ll) return(ll[2])) # second value
[1] NA  2  2
sapply(df$c, function(ll) return(ll[3])) # third value
[1] NA NA  3

We see that if there is no value, we have a NA.
We need an iterator to extract all values at the position idx. For that, we'll find the number of values in each element of df$c (the list) and keep the maximum.

max(sapply(df$c, length))
[1] 3

If we want zeros instead of NAs, we need to create a function in the sapply to convert them :

vec <- c(NA, 5, 1, NA)
> sapply(vec, function(x) if(is.na(x)) return(0) else return(x))
[1] 0 5 1 0

score 1 · Answer 2 · answered Oct 30 '19 at 22:21

I followed a radically different approach. I rbinded the list column and then dcasted it, obtaining the desired result. Last part is to set the names.

library(data.table)
df <- data.table(a = 1:3, d = list(1L, c(1L, 2L), c(1L, 2L, 3L)))

df2 <- df[, rbind(d), by = a][, dcast(.SD, a ~ V1, fill = 0)]

setnames(df2, 2:4, flux)[]
   a F_1 F_2 F_3
1: 1   1   0   0
2: 2   1   2   0
3: 3   1   2   3

where flux is the variable of names that you defined in your question.

Please notice that avoided using the column name c, as it may be confused with the function c().

Dispatch values in list column to separate columns

2 Answers2