Using separate in tidyr to separate column after a certain number of characters?

user_10202149 0
Viewed 399

Sample of data:

  Outcome            
  <chr>              
1 X2016-17Denominator
2 X2014-15Denominator
3 X2014-15Outcome    
4 X2010-11Numerator  
5 X2012-13Denominator

I'm a bit uncertain how to use the sep argument in the separate function provided by the tidyr package. I'm still a newbie at using regex but I figured something like this would work:

sample %<>% separate(Outcome, into=c("date", "metric"), sep="^X[:digit:]{4}[:punct:][:digit:]{2}")

This creates the metric column fine, but leaves the date column empty. I'm guessing the issue is that what is considered the "separator" isn't replicated, but I'm not sure how to specify the characters after X####-## as what I want the columns to be separated by.

stringr tidyr r
3 Answers

You can separate by position.

library(tidyr)

dat %>%
  separate(Outcome, into=c("date", "metric"), sep = 8)
#       date      metric
# 1 X2016-17 Denominator
# 2 X2014-15 Denominator
# 3 X2014-15     Outcome
# 4 X2010-11   Numerator
# 5 X2012-13 Denominator

DATA

dat <- read.table(text = "  Outcome            
1 'X2016-17Denominator'
2 'X2014-15Denominator'
3 'X2014-15Outcome'    
4 'X2010-11Numerator'  
5 'X2012-13Denominator'",
                  header = TRUE, stringsAsFactors = FALSE)
user_76698090

separate is used to separate column given a separator (so you are using your regex as a separator, hence it's only keeping metric, as it thinks the rest is what should separate your columns). In this case you should use extract 

sample = data.frame(Outcome = c(
    'X2016-17Denominator', 
    'X2014-15Denominator',
    'X2014-15Outcome',
    'X2010-11Numerator',
    'X2012-13Denominator'
))

sample %>% 
    extract(
        Outcome, 
        into=c("date", "metric"),
        regex="^X([:digit:]{4}[:punct:][:digit:]{2})(.*)"
    )
#    date      metric
#1 2016-17 Denominator
#2 2014-15 Denominator
#3 2014-15     Outcome
#4 2010-11   Numerator
#5 2012-13 Denominator
user_36914280

We could also a regex lookaround to separate

library(tidyverse)
df1 %>%
    mutate(Outcome = str_remove(Outcome, "^X")) %>% 
    separate(Outcome, into = c("date", "metric"), sep="(?<=[0-9])(?=[A-Z])")
#     date      metric
#1 2016-17 Denominator
#2 2014-15 Denominator
#3 2014-15     Outcome
#4 2010-11   Numerator
#5 2012-13 Denominator

data

df1 <- structure(list(Outcome = c("X2016-17Denominator", "X2014-15Denominator", 
 "X2014-15Outcome", "X2010-11Numerator", "X2012-13Denominator"
 )), class = "data.frame", row.names = c("1", "2", "3", "4", "5"
 ))
user_37322710
Related