How to 'unlist' a column in a data.table

user_1707278 0
Viewed 5394

in my table, some cells are vectors instead of single value, i.e. the column is a list instead of vector:

dt1 <- data.table(
  colA=   c('A1','A2','A3'), 
  colB=list('B1',c('B2a','B2b'),'B3'),
  colC=   c('C1','C2','C3'), 
  colD=   c('D1','D2','D3')
)

dt1
#   colA    colB colC colD
#1:   A1      B1   C1   D1
#2:   A2 B2a,B2b   C2   D2
#3:   A3      B3   C3   D3

I need to reshape it to a long format unlisting that column colB. So far I do it like this:

dt1[,.(colB=unlist(colB)),by=.(colA,colC,colD)]
#   colA colC colD colB
#1:   A1   C1   D1   B1
#2:   A2   C2   D2  B2a
#3:   A2   C2   D2  B2b
#4:   A3   C3   D3   B3

it does the job but I don't like that I have to indicate all other column names explicitly in by=. Is there better way to do this?
(I'm sure it's already answered elsewhere but I couldn't find it so far)

P.S. ideally I would like to manage without any external packages

data.table r
3 Answers

A bit late to the game, but I was looking for a solution that accepts multiple column names. Adapted from MichaelChirico's answer with some additional safeguards and cleanups, this is what I came up with:

funwrap <- function(dt, cols) {
  un <- dt[, cols, with = FALSE][, .ID := .I]
  un <- un[,
    append(
      list(.ID = rep(.ID, lengths(get(cols[[1]])))),
      stats::setNames(lapply(cols, function(vv) unlist(get(vv))), cols)
    )]
  
  # safeguard against empty values
  if (!all(cols %in% names(un))) un[, c(cols) := NA]
  
  rr <- un[dt[, !cols, with = FALSE][, .ID := .I], on = ".ID"][, .ID := NULL]
  setcolorder(rr, names(dt))
  rr[]
}

library(data.table)

dt1 <- data.table(
  colA =   c('A1','A2','A3'), 
  colB =list('B1',c('B2a','B2b'),'B3'),
  colC =   c('C1','C2','C3'), 
  colD =   c('D1','D2','D3')
)
r1 <- funwrap(dt1, "colB")
r1
#>    colA colB colC colD
#> 1:   A1   B1   C1   D1
#> 2:   A2  B2a   C2   D2
#> 3:   A2  B2b   C2   D2
#> 4:   A3   B3   C3   D3
str(r1)
#> Classes 'data.table' and 'data.frame':   4 obs. of  4 variables:
#>  $ colA: chr  "A1" "A2" "A2" "A3"
#>  $ colB: chr  "B1" "B2a" "B2b" "B3"
#>  $ colC: chr  "C1" "C2" "C2" "C3"
#>  $ colD: chr  "D1" "D2" "D2" "D3"
#>  - attr(*, ".internal.selfref")=<externalptr>

# allows multiple colums to unwrap at the same time
dt2 <- data.table(
  colA =   c('A1','A2','A3'), 
  colB =list('B1',c('B2a','B2b'),'B3'),
  colC =list('C1',c('C2a', 'C2b'),'C3'), 
  colD =   c('D1','D2','D3')
)
r2 <- funwrap(dt2, c("colB", "colC"))
r2
#>    colA colB colC colD
#> 1:   A1   B1   C1   D1
#> 2:   A2  B2a  C2a   D2
#> 3:   A2  B2b  C2b   D2
#> 4:   A3   B3   C3   D3
str(r2)
#> Classes 'data.table' and 'data.frame':   4 obs. of  4 variables:
#>  $ colA: chr  "A1" "A2" "A2" "A3"
#>  $ colB: chr  "B1" "B2a" "B2b" "B3"
#>  $ colC: chr  "C1" "C2a" "C2b" "C3"
#>  $ colD: chr  "D1" "D2" "D2" "D3"
#>  - attr(*, ".internal.selfref")=<externalptr>

# allows empty elements
dt3 <- data.table(
  colA =   c('A1','A2','A3'), 
  colB =list(NULL, c('B2a','B2b'), NULL),
  colC =   c('C1','C2','C3'), 
  colD =   c('D1','D2','D3')
)
r3 <- funwrap(dt3, "colB")
r3
#>    colA colB colC colD
#> 1:   A1 <NA>   C1   D1
#> 2:   A2  B2a   C2   D2
#> 3:   A2  B2b   C2   D2
#> 4:   A3 <NA>   C3   D3
str(r3)
#> Classes 'data.table' and 'data.frame':   4 obs. of  4 variables:
#>  $ colA: chr  "A1" "A2" "A2" "A3"
#>  $ colB: chr  NA "B2a" "B2b" NA
#>  $ colC: chr  "C1" "C2" "C2" "C3"
#>  $ colD: chr  "D1" "D2" "D2" "D3"
#>  - attr(*, ".internal.selfref")=<externalptr>

# allows fully empty lists
dt4 <- data.table(
  colA =   c('A1','A2','A3'), 
  colB =list(NULL, NULL, NULL),
  colC =   c('C1','C2','C3'), 
  colD =   c('D1','D2','D3')
)
r4 <- funwrap(dt4, "colB")
r4
#>    colA colB colC colD
#> 1:   A1   NA   C1   D1
#> 2:   A2   NA   C2   D2
#> 3:   A3   NA   C3   D3
str(r4)
#> Classes 'data.table' and 'data.frame':   3 obs. of  4 variables:
#>  $ colA: chr  "A1" "A2" "A3"
#>  $ colB: logi  NA NA NA
#>  $ colC: chr  "C1" "C2" "C3"
#>  $ colD: chr  "D1" "D2" "D3"
#>  - attr(*, ".internal.selfref")=<externalptr>

Created on 2021-10-05 by the reprex package (v0.3.0)

user_30484530
Related