concatenating values in data frame

i am trying to concatenating values in my data frame for column names start with any number

df <- data.frame(AA=c(72,62,43,66,54,64,47,47,27,68),
                 BB=c("AMK","KAMl","HAJ","NHS","KUL","GAF","BGA","NHU","VGY","NHU"),
                 CC=c("TAMAN","GHUSI","KELVIN","DEREK","LOKU","MNDHUL","JASMIN","BINNY","BURTAM","DAVID"),
                 DD=c(62,41,37,41,32,74,52,75,59,36),
                 EE=c("CA","NY","GA","DE","MN","LA","GA","VA","TM","BA"),
                 FF=c("ENGLISH","FRENCH","ENGLISH","FRENCH","ENGLISH","ENGLISH","SPANISH","ENGLISH","SPANISH","RUSSIAN"),
                 GG=c(33,44,51,51,37,58,24,67,41,75),
                 "1. Ard"=c("","D","",NA,"","D","",NA,"D",""),
                 "2. Bank"=c("","A",NA,"","A","A","A","A","",""),
                 "3. Call"=c("","","","","","","","",NA,""),
                 "4. Division"=c("","G","G","G","G","G","G","G","",""))

setnames(df,"X1..Ard","1. Ard")
setnames(df,"X2..Bank","2. Bank")
setnames(df,"X3..Call","3. Call")
setnames(df,"X4..Division","4. Division")

df <- df %>%  
  mutate(Concat_tot = apply(df[, colnames(select(df,!matches("^[A-Z]"))), drop = F], MARGIN = 1, FUN = function(i) paste(i, collapse = ",")) )

but i want to exclude "," from Concat_tot column the output should be like below

use the .merge() function or .concat() to do this thing!

can you please explain

So you have a "," in the Concat_tot column you want to remove?
Actually you introduce it with

FUN = function(i) paste(i, collapse = ",")

Simply replace the collapse = "," with something else, e.g. collapse =" ".

also how can i handle NA's, i also want to exclude NA's from my concat column

library(tidyverse)

df <- tibble(
  AA = c(72, 62, 43, 66, 54, 64, 47, 47, 27, 68),
  BB = c("AMK", "KAMl", "HAJ", "NHS", "KUL", "GAF", "BGA", "NHU", "VGY", "NHU"),
  CC = c("TAMAN", "GHUSI", "KELVIN", "DEREK", "LOKU", "MNDHUL", "JASMIN", "BINNY", "BURTAM", "DAVID"),
  DD = c(62, 41, 37, 41, 32, 74, 52, 75, 59, 36),
  EE = c("CA", "NY", "GA", "DE", "MN", "LA", "GA", "VA", "TM", "BA"),
  FF = c("ENGLISH", "FRENCH", "ENGLISH", "FRENCH", "ENGLISH", "ENGLISH", "SPANISH", "ENGLISH", "SPANISH", "RUSSIAN"),
  GG = c(33, 44, 51, 51, 37, 58, 24, 67, 41, 75),
  "1. Ard" = c("", "D", "", NA, "", "D", "", NA, "D", ""),
  "2. Bank" = c("", "A", NA, "", "A", "A", "A", "A", "", ""),
  "3. Call" = c("", "", "", "", "", "", "", "", NA, ""),
  "4. Division" = c("", "G", "G", "G", "G", "G", "G", "G", "", "")
)

(df1 <- df %>% mutate(across(
  where(~ any(is.na(.)) & is.character(.)),
  ~ replace_na(., replace = "")
)))

(df2 <-
  mutate(df1,
    concat = paste0(!!!syms(colnames(select(df,
                                            !matches("^[A-Z]")))
                            )
                    )
        )
  )

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.