CODEWITHSUNDEEP
r
WoeIs
WoeIs

Reputation: 1083

Merging multiple columns together while keeping each element row-separated?

I have the following data frame:

Code:

DF <- data.frame(lapply(data.frame(Col1 = c("test", "", "test3"),
                               Col2 = c("", "test2", ""),
                               Col3 = c("", "", "test4")), as.character), stringsAsFactors=FALSE)

What it outputs:

   Col1  Col2  Col3
1  test            
2       test2      
3 test3       test4

What I want to do, is create a new column which merges the 3 columns together. I have this following code:

DF$Merged <- do.call(paste0, DF[c("Col1", "Col2", "Col3")])

This outputs the following:

   Col1  Col2  Col3     Merged
1  test                   test
2       test2            test2
3 test3       test4 test3test4

This works somewhat great, as I get a column that I've named "Merged". However, a problem arises in row 3, where the element value is test3 and test4 merged together. I want those two elements to be under the same column (Merged) but not in the same element. An ideal solution would be if R created an extra row and put on of them in there instead, so it ends up like this:

   Col1  Col2  Col3 Merged
1  test              test1
2       test2        test2
3 test3       test4  test3
4                    test4

Upvotes: 1

Views: 59

Answers (1)

akrun
akrun

Reputation: 887881

One option is to paste the elements together and then with separate_rows, split the rows into 'long' format by splitting at one or more spaces (\\s+) before a letter ((?=[A-Za-z])) to essentially rule out splitting at any space before a number i.e. 'test 3' or 'test 2'

library(tidyverse)
DF %>% 
  mutate(Merged = trimws(do.call(paste, .))) %>% 
  separate_rows(Merged, sep="\\s+(?=[A-Za-z])")

If the patterns are unclear, then we create the sep while pasteing

DF %>% 
    mutate(Merged = gsub("^,+|,+$", "", gsub(",{2,}", ",", 
       do.call(paste, c(., list(sep=",")))))) %>% 
    separate_rows(Merged, sep=",\\s*")

Upvotes: 1

Related Questions