I want to subsidize the data set to just one row by spreading a key field over concatenated values but I keep running into an error using the tidyr::spread() function. Here is a sample of my data set along with the concatenation:
dta <- data.frame(id = c("A001","A001","A001","A001","A001"),
name = c("one", "one","one","one","one"),
group=c("a","b","b","c","c"),
category=c("I","N","N","N","N"), stringsAsFactors=F)
dta %>%
group_by(category) %>%
mutate(gps = paste0(unique(group), collapse = "; "))
id name group category gps
<chr> <chr> <chr> <chr> <chr>
1 A001 one a I a
2 A001 one b N b; c
3 A001 one b N b; c
4 A001 one c N b; c
5 A001 one c N b; c
dta %>%
group_by(category) %>%
mutate(gps = paste0(unique(group), collapse = "; ")) %>%
tidyr::spread(category, gps)
Error: Each row of output must be identified by a unique combination of keys.
Keys are shared for 4 rows:
* 2, 3
* 4, 5
I want to achieve:
id name I N
<chr> <chr> <chr> <chr>
1 A001 on a b; c
Is this possible using the spread function? I tried to use the pivot_wider function but it also resulted in an error. I am just curious if I am missing something to boil the data down to 1 row.
Thanks!