Combine character strings into a single string by group in R

user_1968484 0
Viewed 72

I have data that looks like this:

library(dplyr)  

Input <- tibble(
      UID = c("Code001", "Code001","Code002","Code002","Code002","Code002","Code003","Code003","Code003","Code004","Code005","Code005"),
      Name = c("NameABC", "Name JHB", "NameYDB", "Name & KBC","Name-S-D-T", "Name DF-R","NameMEY", "Name PeU","Name Eed", "NameBOR", "Name-Ptg","NameLKD"))

For each of the unique UID's I would like to combine the Name values into a single string. If there are two entries for the UID I would like an & to separate them. If it is more than 2, then a comma to separate with an & for the last two. I would like to create a new df that looks like this:

Output <- tibble(
      UID = c("Code001", "Code002","Code003","Code004","Code005"),
      Name = c("NameABC & Name JHB", "NameYDB, Name & KBC, Name-S-D-T & Name DF-R", "NameMEY, Name PeU & Name Eed", "NameBOR", "Name-Ptg & NameLKD"))
dplyr r
4 Answers
  • We can use toString which concatenating vector of characters with , , then using sub with pattern to replace the last , with &
library(dplyr)

Input |> group_by(UID) |> 
   summarise(Name = sub("(.*)," , "\\1 &" , toString(Name)))
  • Output
# A tibble: 5 × 2
  UID     Name 
  <chr>   <chr>                                      
1 Code001 NameABC & Name JHB                         
2 Code002 NameYDB, Name & KBC, Name-S-D-T & Name DF-R
3 Code003 NameMEY, Name PeU & Name Eed               
4 Code004 NameBOR                                    
5 Code005 Name-Ptg & NameLKD
user_191983370

I would define a function to concatenate your character vector as you want, then call it in a grouped summarize():

library(dplyr)

concat_names <- function(x) {
  len_x <- length(x)
  if (len_x < 3) {
    paste(x, collapse = " & ")
  } else {
    paste(paste(x[-len_x], collapse = ", "), "&", x[len_x])
  }
}

Input %>%
  group_by(UID) %>%
  summarize(Name = concat_names(Name))

Result:

# A tibble: 5 × 2
  UID     Name                                       
  <chr>   <chr>                                      
1 Code001 NameABC & Name JHB                         
2 Code002 NameYDB, Name & KBC, Name-S-D-T & Name DF-R
3 Code003 NameMEY, Name PeU & Name Eed               
4 Code004 NameBOR                                    
5 Code005 Name-Ptg & NameLKD
user_173038050

You can create a helper function f that creates the pasted values:

f <- function(x) {
  if(length(x)==1) return(x)
  if(length(x)==2) return(paste0(x,collapse=" & "))
  paste0(paste0(x[1:(length(x)-1)], collapse=", "), " & ", x[length(x)])
}

Then, apply the function in summarize(), by UID

group_by(Input, UID) %>% summarize(Name = f(Name))

Output:

# A tibble: 5 × 2
  UID     Name                                       
  <chr>   <chr>                                      
1 Code001 NameABC & Name JHB                         
2 Code002 NameYDB, Name & KBC, Name-S-D-T & Name DF-R
3 Code003 NameMEY, Name PeU & Name Eed               
4 Code004 NameBOR                                    
5 Code005 Name-Ptg & NameLKD
user_44475400

You can use if-else by group:

Input %>%
  group_by(UID) %>%
  summarise(Name = if(length(Name)>1) paste0(toString(head(Name, -1)), " & ", last(Name)) else Name)

# A tibble: 5 x 2
  UID     Name                                       
  <chr>   <chr>                                      
1 Code001 NameABC & Name JHB                         
2 Code002 NameYDB, Name & KBC, Name-S-D-T & Name DF-R
3 Code003 NameMEY, Name PeU & Name Eed               
4 Code004 NameBOR                                    
5 Code005 Name-Ptg & NameLKD
user_108488980
Related