In R, can you separate text to columns so that values align?

user_10894412 0
Viewed 109

I have a vector of text values, something like:

Col1
camel; cow; giraffe; panda; zebra
camel; giraffe; zebra
panda; zebra

And I would like to separate these values into an equal number of columns so that the values align vertically in the same columns, with NA where there are gaps so that I get:

Col1, Col2, Col3, Col4, Col5
camel, cow, giraffe, panda, zebra
camel, NA, giraffe, NA, zebra
NA, NA, NA, panda, zebra

How can I accomplish this? I've tried using the 'separate' function in dplyr but this returns error messages, whilst using the 'split' function in splitshapestack allows me to separate the columns but they don't return values that are aligned vertically. I have also tried using the 'lapply' and 'match' in a similar way to this post but again, I keep getting error messages.

dplyr csv r
1 Answers

You can pivot your data a few times to align these values:

library(dplyr)

df %>% 
  tibble::rowid_to_column("id") %>% 
  tidyr::separate_rows(Col1, sep = "; ") %>% 
  tidyr::pivot_wider(id_cols = id,
                     names_from = Col1,
                     values_from = Col1) %>% 
  dplyr::select(-id) %>% 
  magrittr::set_colnames(paste0("Col", 1:ncol(.)))

Output

 Col1  Col2  Col3    Col4  Col5 
  <chr> <chr> <chr>   <chr> <chr>
1 camel cow   giraffe panda zebra
2 camel NA    giraffe NA    zebra
3 NA    NA    NA      panda zebra

How it works

  1. rowid_to_column keeps track of the row numbers so when the data are pivoted to a longer format, we don't lose track of which values belong in which rows.
  2. separate_rows will separate Col1 and pivot the data to a longer format. Suggested by @Adam as an improvement.
  3. pivot_wider aligns everything into the columns you specified.

Data

structure(list(Col1 = c("camel; cow; giraffe; panda; zebra", 
"camel; giraffe; zebra", "panda; zebra")), class = "data.frame", row.names = c(NA, 
-3L))
user_63824340
Related