r aggregate and collapse several cells into one

Question

I have a data frame:

x <- data.frame(id = 1:18,
                super = c(rep("A", 12), rep("B", 6)),
                category = c(rep("one", 6), rep("two", 6), rep("three", 6)),
                root = sort(rep(letters[1:6], 3)),
                coldefs = letters[1:18], stringsAsFactors = F)
x

I am creating a new column by concatenating 3 columns:

myvars <- c("super", "category", "root")
library(tidyverse)
x <- x %>% unite(col = concat, myvars, sep = "_", remove = F)
x

Now, for each unique value of column 'concat' the values of column 'super' are the same, the values of column 'category' are the same, and the values of column "root" are the same. However, for each unique value of column 'concat' the values of column 'id' are different. The same is true for column 'coldefs'.

I would like to collapse (aggregate) x so that it has only as many rows as there are unique values in column 'concat' (i.e., 6 rows). In each row, I want one value from column 'super', one value from column 'category', one value from column 'root'; and then 3 values of column 'id' (concatenated like this: 1;2;3) and 3 values of column 'coldefs' (concatenated like this: a;b;c).

What's the best way of doing it? I am trying the following, but it's not working:

x %>% group_by(concat) %>% summarize(id = paste(id, collapse = ";"), 
                                     super = unique(super), category = unique(category), root = unique(root),
                                     coldefs = paste(coldefs, collapse = ";"))

I am clearly doing something wrong. Thanks a lot for your help!

Is that sample code with the `unite` correct? I get the following error when I run it: `Error: All select() inputs must resolve to integer column positions. The following do not: * myvars` — lebelinoz, Aug 08 '17 at 21:56
I shut down my R, opened a clean new R session, and ran my code above (the top 2 sections in grey - including unite). Everything runs without errors. I am using R version 3.4.1 on a Windows PC. unite just concatenates — user3245256, Aug 09 '17 at 00:09
"Turn it off and turn it on again": the solution to many IT problems :P — lebelinoz, Aug 09 '17 at 00:28

score 0 · Answer 1 · answered Aug 09 '17 at 00:19

0

I must say this is a bit (or completely) crazy! I tried my code (the one at the bottom) piece by piece and it worked. I merged it all together - and it worked. I don't understand why was I getting an error before. Here is the correct code that works (at least now):

x %>% group_by(concat) %>% summarize(id = paste(id, collapse = ";"), super = unique(super), 
                                     category = unique(category), root = unique(root),
                                     coldefs = paste(coldefs, collapse = ";"))

answered Aug 09 '17 at 00:19

user3245256

1,842
4
24
51

you can delete your question if there wasn't any issue in the first place :). you may have had another package loaded after `tidyverse` and one function was giving an inadequate output – moodymudskipper Aug 09 '17 at 15:03
I can't figure out how to delete this question – user3245256 Aug 09 '17 at 20:36
just below the tags of the question, next to edit etc – moodymudskipper Aug 09 '17 at 20:40

r aggregate and collapse several cells into one

1 Answers1