How to split a column into three columns based on separator and/or position in r

user_4880334 0
Viewed 55

I have this fake data set with a column named location.

data.1 <-read.csv(text = "
location
01-Q2-Locate of este (TT)
02-Q2-Green River (OG)
01-Q1-Agabe (PS)
")

I need to partition the location column into three columns named code, name, and region.

This is my intended outcome:

enter image description here

This is the code I am using:

library(dplyr)
library(tidyr)
data.1.new <- data.1 %>%
  tidyr::separate(location, c("code", "name", "region"), extra = "merge", remove = FALSE)

The outcome I am getting is this:

enter image description here

I couldn't find a way to separate to solve this.

tidyr dplyr r
2 Answers
data.1 %>%
+   extract(location, c('code', 'name', 'region'), '(.*)-([^(]+) \\((.*)\\)')

   code           name region
1 01-Q2 Locate of este     TT
2 02-Q2    Green River     OG
3 01-Q1          Agabe     PS
user_83802720

Another possible solution:

EXPLANATION (asked by the OP)

  1. First str_extract: See demo1.

  2. Second str_extract: See demo2.

  3. Third str_extract: See demo3.

library(tidyverse)

data.1 %>% 
  mutate(code = str_extract(location, "^\\d+-\\D\\d+"),
         name = str_extract(location, "(?<=-)\\D+(?=\\s\\()"),
         zegion = str_extract(location, "(?<=\\().*(?=\\))"))

#>                    location  code           name zegion
#> 1 01-Q2-Locate of este (TT) 01-Q2 Locate of este     TT
#> 2    02-Q2-Green River (OG) 02-Q2    Green River     OG
#> 3          01-Q1-Agabe (PS) 01-Q1          Agabe     PS
user_115644870
Related