aggregate data.frame with list column

Question

There is one column that contains vectors in each row of the data.frame. I would like to aggregate and combine the vectors. However, it seems I cannot do this with this kind of data. How would you combine those vectors?

"Error: invalid type (list) for variable 'dv'"

#Problem: aggregate data.frame with list-column

#reproducible code
set.seed(1)
some_list <- replicate(40, sample(c(1:8), size=sample(1:6, 1), replace=TRUE))
exdf <- expand.grid(id=c(1:10), content=c(1:4))
exdf$dv <- some_list


#this throws error
aggregate(
formula=dv~id,
data=exdf,
FUN=c
)

Answer 1

Here we use some unreadable base R. If using dplyr pipes to make the code readable, might as well use group/summarise

data.frame(id = unique(exdf$id),
           dv = cbind(lapply(split(exdf, exdf$id),
                             function(x) unlist(x$dv))))

   id                                                      dv
1   1                   3, 5, 6, 4, 7, 4, 2, 1, 6, 5, 5, 8, 5
2   2    2, 8, 8, 6, 6, 1, 1, 7, 7, 4, 4, 7, 5, 5, 2, 3, 6, 4
3   3                            2, 6, 5, 6, 3, 3, 8, 6, 6, 1
4   4                7, 4, 6, 8, 3, 4, 2, 4, 5, 5, 3, 4, 5, 2
5   5    4, 7, 8, 2, 6, 2, 6, 3, 5, 8, 6, 3, 4, 2, 1, 3, 2, 3
6   6                      4, 1, 7, 1, 8, 6, 4, 7, 8, 4, 1, 3
7   7                      7, 3, 4, 7, 3, 3, 4, 3, 6, 7, 7, 4
8   8                4, 2, 7, 6, 8, 7, 4, 8, 4, 4, 2, 8, 6, 6
9   9 1, 6, 4, 7, 6, 8, 4, 6, 4, 3, 4, 5, 2, 2, 5, 8, 3, 2, 8
10 10    5, 5, 7, 1, 4, 2, 6, 1, 2, 2, 1, 1, 6, 8, 8, 2, 7, 6

And if we are dead set on using 'aggregate', you can do the following by changing the list of numeric into character. Then use regex to extract those numbers.

exdf$dv <- as.character(exdf$dv)
aggregate(
  formula=dv~id,
  data=exdf,
  FUN = function(x) regmatches(paste0(x, collapse = ""),
                               gregexpr('[0-9]', paste0(x, collapse = ""))))

   id                                                      dv
1   1                   3, 5, 6, 4, 7, 4, 2, 1, 6, 5, 5, 8, 5
2   2    2, 8, 8, 6, 6, 1, 1, 7, 7, 4, 4, 7, 5, 5, 2, 3, 6, 4
3   3                            2, 6, 5, 6, 3, 3, 8, 6, 6, 1
4   4                7, 4, 6, 8, 3, 4, 2, 4, 5, 5, 3, 4, 5, 2
5   5    4, 7, 8, 2, 6, 2, 6, 3, 5, 8, 6, 3, 4, 2, 1, 3, 2, 3
6   6                      4, 1, 7, 1, 8, 6, 4, 7, 8, 4, 1, 3
7   7                      7, 3, 4, 7, 3, 3, 4, 3, 6, 7, 7, 4
8   8                4, 2, 7, 6, 8, 7, 4, 8, 4, 4, 2, 8, 6, 6
9   9 1, 6, 4, 7, 6, 8, 4, 6, 4, 3, 4, 5, 2, 2, 5, 8, 3, 2, 8
10 10    5, 5, 7, 1, 4, 2, 6, 1, 2, 2, 1, 1, 6, 8, 8, 2, 7, 6

Answer 2

You can use dplyr summarize with unlist and list:

library(dplyr)
df1 <- exdf %>% group_by(id) %>% summarise(dv = list(unlist(dv))) 

df1
# Source: local data frame [10 x 2]

#      id         dv
#   <int>     <list>
#1      1 <int [13]>
#2      2 <int [15]>
#3      3 <int [13]>
#4      4 <int [15]>
#5      5 <int [13]>
#6      6 <int [15]>
#7      7 <int [13]>
#8      8 <int [15]>
#9      9 <int [13]>
#10    10 <int [15]>

df1$dv[[1]]
# [1] 3 5 2 6 4 7 8 2 6 2 7 3 4

Or alternatively data.table:

library(data.table)
setDT(exdf)[, .(list(unlist(dv))), id]

#    id           V1
# 1:  1 3,5,2,6,4,7,
# 2:  2 2,8,8,6,6,1,
# 3:  3 2,6,4,7,8,2,
# 4:  4 7,4,6,4,1,4,
# 5:  5 4,7,8,2,6,2,
# 6:  6 4,1,4,2,7,6,
# 7:  7 7,3,4,3,5,2,
# 8:  8 4,2,7,6,2,8,
# 9:  9 3,5,2,6,4,7,
#10: 10 2,8,8,6,6,1,