concatenating values in data frame

Question

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

score 0 · Answer 1 · answered Nov 17 '20 at 11:22

I can't get your code to work, you forgot to mention a package, but here is a solution to a very similar data set, you should be able to adjust accordingly.

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","",""))

df[is.na(df)]=""

trimws(
  gsub("\\s+"," ",
    do.call(paste,df[,grepl("^.+\\d+",colnames(df))])
  )
)
 [1] ""      "D A G" "G"     "G"     "A G"   "D A G" "A G"   "A G"   "D"     ""

score 0 · Accepted Answer · answered Nov 17 '20 at 11:35

0

You can use tidyr's unite :

tidyr::unite(df, Concat_tot, !matches("^[A-Z]"), 
             sep = ' ', na.rm = TRUE, remove = FALSE)

answered Nov 17 '20 at 11:35

Ronak Shah

377,200
20
156
213

this is changing the order of column, "Concat_tot" should always comes at last – sanuali0123 Nov 17 '20 at 11:52
If the order of columns is important you can add `%>% select(-Concat_tot, Concat_tot)` to the above. – Ronak Shah Nov 17 '20 at 11:56

concatenating values in data frame

2 Answers2