Aggregate with Concatenation


#1

i'm looking for solution to aggregate and concatenation on table.

here is the query in Oracle.

select  id, ws_concat(types) 
from table 
group by Id;

i'm looking for the same in R.

please help


#2

It would be helpful if you could provide a bit of additional detail on what you want to do. But given the above I would recommend:

  • for concatenation look at paste or paste0
  • for the select and group by elements look at the dplyr verbs select, group_by and summarise (or use the wrapper count)

e.g., if you want to count the number of results by id:

table %>% select(id) %>% group_by(id) %>% summarise(n = n())

or

table %>% count(id).

e.g., if you want to count the number of results by id and type:

table %>% select(id, type) %>% group_by(id, type) %>% summarise(n = n())

or

table %>% count(id, type)

The select, group_by, summarise method is verbose, but I've given it to compare to the SQL code.


#3

thanks @mark for your response.

i made this using the below

results<-results%>% 
    group_by(id) %>% 
    mutate(types= unique(paste0(type, collapse = ",")))