Concatenate columns in R, but only keep separator if value exists

user_11370582 0
Viewed 94

Does anyone know of a method in R to concatenate n columns but only keep the separator if there is a value in that row? If you run the example below:

df <- data.frame(
                  name1 = c("Jim","Bob","Sue"),
                  name2 = c("Jane","","Bane"),
                  name3 = c('Conor',"",""),
                  name4 = c("","","Bonor")
                )

df$names <- paste(df$name1,df$name2,df$name3, sep=";")

You will see that separators are included at the end and in-between values even though the cells are empty, with the output:

df =

name1 name2 name3 name4  names
Jim   Jane  Conor        Jim;Jane;Conor;
Bob                      Bob;;;
Sue   Bane        Bonor  Sue;Bane;;Bonor

Is there any method to either not include or drop separators in the case where the cells are empty? With the desired outcome:

df =

name1 name2 name3 name4  names
Jim   Jane  Conor        Jim;Jane;Conor
Bob                      Bob
Sue   Bane        Bonor  Sue;Bane;Bonor
separator paste dataframe concatenation r
1 Answers
library(dplyr)
library(tidyr)

df %>% 
  mutate_all(na_if,"") %>% 
  unite("names", everything(), sep = ";", remove = F, na.rm = T)

#>            names name1 name2 name3 name4
#> 1 Jim;Jane;Conor   Jim  Jane Conor  <NA>
#> 2            Bob   Bob  <NA>  <NA>  <NA>
#> 3 Sue;Bane;Bonor   Sue  Bane  <NA> Bonor

Update: applying this solution on specific columns.

I am modifying akrun's answer from the comment below;

df %>% 
  mutate(across(c("name1", "name2", "name3", "name4"), na_if, "", 
                .names = "{.col}_changed")) %>% 
  unite(names, ends_with('_changed'), na.rm = TRUE, sep = ";")

#>   name1 name2 name3 name4          names
#> 1   Jim  Jane Conor       Jim;Jane;Conor
#> 2   Bob                              Bob
#> 3   Sue  Bane       Bonor Sue;Bane;Bonor
user_64614620
Related