Collapsing dataframe based on id, storing multiple values for variables into lists

user_16134227 0
Viewed 77

I have a data frame in which I have an id variable fruit in the example. For some of the other variables, there is only one corresponding value to each id variable taste, ranking while for others, there are multiple color,origin.

I would like to collapse the data frame so that each id variable has one row. For the variables that have multiple values, I could ideally store them as a list.

However, I can't figure out a way to do this. Here is what I tried using summarise and unique for the variables with multiple values. However, I just got back the original data:

df %>%
  group_by(fruit) %>%
  summarise(ranking = mean(ranking),
            taste = first(taste),
            origin = unique(origin),
            color = unique(color))

example data:

ranking <- c(1, 1, 2, 2, 3)
fruit <- c("apple", "apple", "pear", "pear", "banana")
color <- c("red", "green", "red", "green", "yellow")
taste <- c("good", "good", "good", "good", "okay")
origin <- c("WA", "CA", "OR", "MX", "PR")

df <- data.frame(fruit, ranking, color, taste, origin)

enter image description here

desired data"

ranking <- c(1, 2, 3)
fruit <- c("apple", "pear", "banana")
color <- list((c("red", "green")), (c("red", "green")), (c("yellow")))
taste <- c("good", "good", "okay")
origin <- list(c("WA","CA"), c("OR", "MX"), c("PR"))
desired_df <- data.frame(fruit, ranking, taste, I(color), I(origin))

enter image description here

Is there an easy way to get this transformation done?

dplyr r
2 Answers

We can use summarise with across, store the unique elements in a list after doing a grouping by the columns of interest

library(dplyr)
out <- df %>%
      group_by(fruit, ranking, taste) %>% 
      summarise(across(c(color, origin), ~ list(unique(.))), .groups = 'drop')

-output

 out
# A tibble: 3 x 5
  fruit  ranking taste color     origin   
  <chr>    <dbl> <chr> <list>    <list>   
1 apple        1 good  <chr [2]> <chr [2]>
2 banana       3 okay  <chr [1]> <chr [1]>
3 pear         2 good  <chr [2]> <chr [2]>

If the 'taste' should be the first element and ranking the mean

out <- df %>%
         group_by(fruit) %>%
         summarise(ranking = mean(ranking),
                   taste = first(taste),
                   across(c(color, origin), ~ list(unique(.))), .groups = 'drop')

Or use base R

aggregate(. ~ fruit + ranking + taste, unique(df), FUN = c)

-output

    fruit ranking taste      color origin
1  apple       1  good red, green WA, CA
2   pear       2  good red, green OR, MX
3 banana       3  okay     yellow     PR
user_37322710

We can also use the following solution:

library(dplyr)
library(purrr)

df %>%
  group_split(fruit) %>%
  map_dfr(~ .x %>% map_at(c("color", "origin"), ~ paste(.x, collapse = "-"))) %>%
  distinct() %>%
  arrange(ranking)

# A tibble: 3 x 5
  fruit  ranking color     taste origin
  <chr>    <dbl> <chr>     <chr> <chr> 
1 apple        1 red-green good  WA-CA 
2 pear         2 red-green good  OR-MX 
3 banana       3 yellow    okay  PR
user_143145200
Related