Flatten a data frame, combine the values of a column into lists to populate individual cells

Question

I have the following data frame in r:

Color      Value
Red            1
Red            3
Red            4
Red            7
Blue           2
Blue           5
Green          1
Green          2
Green          3

What I would like to do is combine the values into a list that I can place into an individual cell, pivoted on color. That is, I want a table that looks like this:

  Color        Value
  Red      [1,3,4,7]
  Blue         [2,5]
  Green      [1,2,3]

I have some success approaching this problem with a for loop, but I'm finding that it is taking considerable time to execute. Is there a more expeditious data wrangling function in the tidyverse that can perform this transformation? I thought the purrr package might contain the answer but am having difficulty navigating.

Thanks!

score 1 · Answer 1 · answered Apr 04 '20 at 00:07

library('data.table')
setDT(df)[, .(Value = paste0("[", paste0( Value, collapse = ","), "]")), by = .(Color)]
#    Color     Value
# 1:   Red [1,3,4,7]
# 2:  Blue     [2,5]
# 3: Green   [1,2,3]

Data:

df <- read.table(text='Color      Value
Red            1
                 Red            3
                 Red            4
                 Red            7
                 Blue           2
                 Blue           5
                 Green          1
                 Green          2
                 Green          3', header = TRUE, stringsAsFactors = FALSE)

score 0 · Accepted Answer · answered Apr 03 '20 at 23:34

We can use aggregate

aggregate(Value ~ Color, df1, FUN = toString)

If we need a list

aggregate(Value ~ Color, df1, FUN = list)

Or with dplyr

library(dplyr)
df1 %>%
   group_by(Color) %>%
   summarise(Value = toString(Value))

Or as a list

df1 %>%
   group_by(Color) %>%
   summarise(Value = list(Value))

Flatten a data frame, combine the values of a column into lists to populate individual cells

2 Answers2