How do I separate a string with different (& repeated) separators into multiple columns?

user_5157636 0
Viewed 117

I am having difficulty separating data in one column into multiple columns -- the data has multiple separators. I checked the stackoverflow solutions but could not find one that matched my situation.

Input enter image description here

My attempt

df %>%
  separate(col = game, into = c("year", "day", "month", "monthday", "site", "team", "decision", "runs1", "runs2", "pitcher"), sep = "[. ,-]", remove = TRUE)

Desired output (see input image)

Dput

structure(list(game = c("2021. Mon, Apr 5 at PHI L (3-5)#", "2020. Fri, Jul 24 vs ATL W (1-0)", 
"2019. Thu, Mar 28 at WSN W (2-0)", "2018. Thu, Mar 29 vs STL W (9-4)", 
"2017. Mon, Apr 3 vs ATL W (6-0)"), pitcher = c("deGrom", "deGrom", 
"deGrom", "Syndergaard", "Syndergaard")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -5L))

I would prefer a dplyr result but am open to others.

dplyr r
5 Answers

I'm not sure if that is any existing function that allows you to separate using multiple separator, therefore I just substituted all separator into @ for separate to work.

library(tidyverse)

df %>% 
  mutate(game = gsub("\\.\\s|,\\s|\\s+|-", "@", game) %>% 
           gsub("\\(|\\)|#", "", .)) %>% 
  separate(game, 
           into = c("year", "day", "month", "monthday", "site", "team", "decision", "runs1", "runs2"), 
           sep = "@")

# A tibble: 5 × 10
  year  day   month monthday site  team  decision runs1 runs2 pitcher    
  <chr> <chr> <chr> <chr>    <chr> <chr> <chr>    <chr> <chr> <chr>      
1 2021  Mon   Apr   5        at    PHI   L        3     5     deGrom     
2 2020  Fri   Jul   24       vs    ATL   W        1     0     deGrom     
3 2019  Thu   Mar   28       at    WSN   W        2     0     deGrom     
4 2018  Thu   Mar   29       vs    STL   W        9     4     Syndergaard
5 2017  Mon   Apr   3        vs    ATL   W        6     0     Syndergaard
user_166474960

We may use extract which can be used to capture substring in group

library(dplyr)
library(tidyr)
df %>% 
  extract(game, into = c("year", "day", "month", "monthday", "site",
     "team", "decision", "runs1", "runs2"), 
    "^(\\d{4})\\.\\s+(\\w+),\\s+(\\w+)\\s+(\\d+)\\s+(\\w+)\\s+(\\w+)\\s+(\\w+)\\s+\\((\\d+)-(\\d+)\\).*", convert = TRUE)

-output

# A tibble: 5 × 10
   year day   month monthday site  team  decision runs1 runs2 pitcher    
  <int> <chr> <chr>    <int> <chr> <chr> <chr>    <int> <int> <chr>      
1  2021 Mon   Apr          5 at    PHI   L            3     5 deGrom     
2  2020 Fri   Jul         24 vs    ATL   W            1     0 deGrom     
3  2019 Thu   Mar         28 at    WSN   W            2     0 deGrom     
4  2018 Thu   Mar         29 vs    STL   W            9     4 Syndergaard
5  2017 Mon   Apr          3 vs    ATL   W            6     0 Syndergaard

or with separate

df %>% 
  separate(col = game, into = c("year", "day", "month", "monthday", 
    "site", "team", "decision", "runs1", "runs2"),
     sep = "\\s*[.,\\(\\)-]\\s*|\\s+", remove = TRUE, convert = TRUE)

-output

# A tibble: 5 × 10
   year day   month monthday site  team  decision runs1 runs2 pitcher    
  <int> <chr> <chr>    <int> <chr> <chr> <chr>    <int> <int> <chr>      
1  2021 Mon   Apr          5 at    PHI   L            3     5 deGrom     
2  2020 Fri   Jul         24 vs    ATL   W            1     0 deGrom     
3  2019 Thu   Mar         28 at    WSN   W            2     0 deGrom     
4  2018 Thu   Mar         29 vs    STL   W            9     4 Syndergaard
5  2017 Mon   Apr          3 vs    ATL   W            6     0 Syndergaard
user_37322710

Another option is to perform the clean-up in steps:

temp <- df %>%
   separate(col = game, into = c("year", "day", "month", "monthday", "site", "team", "decision", "runs1"), sep = " ", remove = TRUE)

#remove the decimal
temp$year<- as.integer(temp$year)
#remove the comma after the day
temp$day <- gsub(",", "", temp$day)

#clean up and split the runs column
temp$runs1<- gsub( ".+(\\d+)-(\\d+).+", "\\1 \\2", temp$runs1)
answer <-temp %>% separate(col = runs1, into = c("runs1", "runs2"), sep = " ", remove = TRUE)

A little longer but easier to debug in the future.

user_57922440

many good answers, one other variation below

#replace all punctuation with a space then seperate
  df %>% 
    mutate(game=str_replace_all(game,"[:punct:]"," ")) %>% 
    separate(col = game,into = c("year", "day", "month", "monthday", "site", "team", "decision", "runs1", "runs2"))
user_176482140

This could be another approach:

library(tidyverse)

df %>%
  mutate(game2 = strsplit(game, split = "\\.\\s|,\\s|\\s", perl = TRUE)) %>%
  unnest_wider(game2) %>%
  rename_with(., ~ str_replace(., "(\\.){3}\\d", 
                               c("year", "day", "month", "monthday", "site", "team", "decision", "runs1")), !c(game, pitcher)) %>%
  extract(runs1, c('runs1', 'runs2'), regex = "^.(\\d+).*(\\d+).*") %>%
  select(!game) %>%
  relocate(pitcher, .after = last_col())

# A tibble: 5 x 10
  year  day   month monthday site  team  decision runs1 runs2 pitcher    
  <chr> <chr> <chr> <chr>    <chr> <chr> <chr>    <chr> <chr> <chr>      
1 2021  Mon   Apr   5        at    PHI   L        3     5     deGrom     
2 2020  Fri   Jul   24       vs    ATL   W        1     0     deGrom     
3 2019  Thu   Mar   28       at    WSN   W        2     0     deGrom     
4 2018  Thu   Mar   29       vs    STL   W        9     4     Syndergaard
5 2017  Mon   Apr   3        vs    ATL   W        6     0     Syndergaard
user_143145200
Related