R - unpivot list in data.table rows

Question

I have a dataset that contains several columns, including 1 with list entries:

DT = data.table(
  x = c(1:5),
  y = seq(2, 10, 2),
  z = list(list("a","b","a"), list("a","c"), list("b","c"), list("a","b","c"), list("b","c","b"))
)

Basically, I'm trying to unlist a, b, c from column z, and aggregate the data based on the x & y values.

Desired output:

    z x sum(y)
 1: a 1  4
 2: b 1  2
 3: a 2  4
 4: c 2  4
 5: b 3  6
 6: c 3  6
 7: a 4  8
 8: b 4  8
 9: c 4  8
10: b 5 20
11: c 5 10

My current method is rather round-about; I created 2 other columns with x and y values in lists of the same length as the list entry in z column, then unlisted all 3 columns simultaneously before aggregating - i.e. sum y values, grouped by z & x.

Code (before unlisting & aggregation):

DT[, listlen := sapply(z, function(x) length(x))]
for (a in c(1:nrow(DT))){
  DT[a, x1:= list(list(rep(DT[a, x], DT[a, listlen])))]
  DT[a, y1:= list(list(rep(DT[a, y], DT[a, listlen])))]}
DT_out = data.table(x = unlist(DT[,x1]), y = unlist(DT[,y1]), z = unlist(DT[,z]))

   x  y      z listlen    x1       y1
1: 1  2 <list>       3 1,1,1    2,2,2
2: 2  4 <list>       2   2,2      4,4
3: 3  6 <list>       2   3,3      6,6
4: 4  8 <list>       3 4,4,4    8,8,8
5: 5 10 <list>       3 5,5,5 10,10,10

Is there a method through data.table or reshape packages that can help me melt the dataset / do this much simpler? As I'm working with a lot more rows than this and this step seems to be very inefficient.

Any other help regarding the aggregation step would be much appreciated too!

thelatemail thelatemail · Accepted Answer · 2016-09-07T03:05:42

unlist your z column first and then just aggregate as per normal via by=:

DT[, .(z=unlist(z)), by=.(x,y)][, .(sumy=sum(y)), by=.(x,z)]

#    x z sumy
# 1: 1 a    4
# 2: 1 b    2
# 3: 2 a    4
# 4: 2 c    4
# 5: 3 b    6
# 6: 3 c    6
# 7: 4 a    8
# 8: 4 b    8
# 9: 4 c    8
#10: 5 b   20
#11: 5 c   10

R - unpivot list in data.table rows

1 Answers