CODEWITHSUNDEEP
rsubstring
Yu Na
Yu Na

Reputation: 122

Separate numbers from characters in a variable, with inconsistent length -R

I am trying to calculate months of experience, but currently my variable looks something like the below, where years and months are in the same column. 

2 yrs 1 mo
1 yr 1 mo
2 yrs 4 mos
less than a year
10 mos

I'd like to separate the years and months from each other so I can then calculate total months of experience. My attempts so far have been inelegant, and substring hasn't been very helpful, as the length is not consistent. Any idea of how I could do so?

EDIT: For less than a year, I'm thinking to substitute it with 11 months

Upvotes: 1

Views: 81

Answers (1)

akrun
akrun

Reputation: 887213

One option is to do the extraction based on regex lookaround with str_extract and then calculate the 'total_month'. The less than a year is changed to '11 mo' as updated in the OP's post

library(dplyr)
library(stringr)
library(tidyr)
dat %>%
   mutate(col1 = replace(col1, col1 == 'less than a year', '11 mos'),
          month = as.numeric(str_extract(col1, "\\d+(?= mo)")),
          year = replace_na(as.numeric(str_extract(col1, "\\d+(?= yr)")), 0), 
          totalmonth = month + year * 12)
#         col1 month year totalmonth
#1  2 yrs 1 mo     1    2         25
#2   1 yr 1 mo     1    1         13
#3 2 yrs 4 mos     4    2         28
#4      11 mos    11    0         11
#5      10 mos    10    0         10

Or another option is to make use of extract 

dat %>%
    mutate(col1 = case_when(col1 == 'less than a year' ~ '0 yr 11 mos',
           str_detect(col1, '^\\d+\\s+mo')~ str_c('0 yr ', col1), TRUE ~ col1)) %>%
    extract(col1, into = c('year', 'month'),   "^(\\d+)\\s*yrs?\\s*(\\d+).*",
             convert = TRUE, remove = FALSE) %>% 
    mutate(totalmonth = month + year * 12)

data

dat <- structure(list(col1 = c("2 yrs 1 mo", "1 yr 1 mo", "2 yrs 4 mos", 
"less than a year", "10 mos")), row.names = c(NA, -5L), class = "data.frame")

Upvotes: 1

Related Questions