Paste column content by group into a new group

user_8471364 0
Viewed 266

Here is my data frame:

a <- data.frame(x=c(rep("A",2),rep("B",4)),
                y=c("AA","BB","CC","AA","DD","AA"))

What I want is group the data frame by x and for each member of the group (here A or B), I would like to paste the content of column y into a single element, separated by _. I would like to sort it by alphabetical order and remove identical characters. Here is the desired result:

out <- data.frame(x=c(rep("A",1),rep("B",1)),
                y=c("AA_BB","AA_CC_DD"))

I tried the following code, which produces an error message:

library(dplyr)

a %>% group_by(x) %>% mutate(y_comb=paste(as.character(sort(unique(y))))) %>% 
      slice(1) %>% ungroup()
dplyr r
3 Answers

We get the distinct element of 'x', 'y' column (as there is only two columns, simply use distinct on the entire data), then arrange the rows by 'x', 'y' column, grouped by 'x', paste (str_c) the 'y' elements into a single string by collapseing with _

library(dplyr)
library(stringr)
a %>% 
   distinct %>% 
   arrange(x, y) %>% 
   group_by(x) %>%
   summarise(y = str_c(y, collapse="_"))

-output

# A tibble: 2 x 2
#  x     y       
#* <chr> <chr>   
#1 A     AA_BB   
#2 B     AA_CC_DD

The error in OP's code is because of the difference in length after doing the unique and paste by itself doesn't do anything. We need to either collapse (or sep - in this case it is collapse). mutate is particular about returning the same length as the number of rows of original data while summarise is not

user_37322710

Perhaps we can do like this

a %>%
  group_by(x) %>%
  summarise(y = paste0(sort(unique(y)), collapse = "_"))

which gives

# A tibble: 2 x 2
  x     y       
  <chr> <chr>
1 A     AA_BB
2 B     AA_CC_DD
user_121587570

Base R option with aggregate :

aggregate(y~x, unique(a), function(x) paste0(sort(x), collapse = '_'))

#  x        y
#1 A    AA_BB
#2 B AA_CC_DD
user_39629140
Related