Geek asks and answers

R reformatting molten data. Column List Table

I have a large (millions of rows) melting data.tablewith the usual meltsterile unfolding in variableand columns value. I need to lay out the table in a wide form (turning the variables up). The problem is that the data table also has a list column datathat I need to save. This makes use impossible reshape2because it dcastcannot deal with non-atomic columns. Therefore, I need to do self-fulfillment.

The answer from the previous question about working with tables of molten data is not applied here because of the list column.

I am not satisfied with the solution I came up with. I am looking for suggestions for a simpler / faster implementation.

x <- LETTERS[1:3]
dt <- data.table(
  x=rep(x, each=2),
  y='d',
  data=list(list(), list(), list(), list(), list(), list()),
  variable=rep(c('var.1', 'var.2'), 3),
  value=seq(1,6)
  )

# Column template set up
list_template <- Reduce(
  function(l, col) { l[[col]] <- col; l }, 
  unique(dt$variable),
  list())

# Expression set up
q <- substitute({
  l <- lapply(
    list_template, 
    function(col) .SD[variable==as.character(col)]$value)
  l$data = .SD[1,]$data
  l
}, list(list_template=list_template))

# Roll up
dt[, eval(q), by=list(x, y)]

   x y var.1 var.2   data
1: A d     1     2 <list>
2: B d     3     4 <list>
3: C d     5     6 <list>
+5
1

, - , , , , ! , , .

, , - . , .

require(data.table)
require(stringr)
require(reshape2)

x <- LETTERS[1:3]
dt <- data.table(
  x=rep(x, each=2),
  y='d',
  data=list(list("a","b"), list("c","d")),
  variable=rep(c('var.1', 'var.2'), 3),
  value=seq(1,6)
  )


# First create the dcasted datatable without the pesky list objects:
dt_nolist <- dt[,list(x,y,variable,value)]
dt_dcast <- data.table(dcast(dt_nolist,x+y~variable,value.var="value")
                       ,key=c("x","y"))


# Second: create a datatable with only unique "groups" of x,y, list
dt_list <- dt[,list(x,y,data)]

# Rows are duplicated so I'd like to use unique() to get rid of them, but
# unique() doesn't work when there list objects in the data.table.
# Instead so I cheat by applying a value to each row within an x,y "group" 
# that is unique within EACH group, but present within EVERY group.
# Then just simply subselect based on that unique value.
# I've chosen rank(), but no doubt there other options

dt_list <- dt_list[,rank:=rank(str_c(x,y),ties.method="first"),by=str_c(x,y)]

# now keep only one row per x,y "group"
dt_list <- dt_list[rank==1]
setkeyv(dt_list,c("x","y"))

# drop the rank since we no longer need it
dt_list[,rank:=NULL]

# Finally just merge back together
dt_final <- merge(dt_dcast,dt_list)
0

More articles:


All Articles