Split character string into three columns when number of pieces differs between rows

user_12908375 0
Viewed 84

I have a data frame similar to the following reprex:

test <- data.frame(
  age = c("6 hours", "2 days, 4 hours", "1 months3 days, 7 hours")
)

Unfortunately, as you can see by row 3, the three pieces of the string are not all separated by a comma.

My intended output is as follows:

                        age agem aged ageh
1                   6 hours   NA   NA   6
2           2 days, 4 hours   NA    2   4
3 1 months3 days, 7 hours      1    3   7

I have tried using dplyr::separate like this 

new_test <- test %>%
  separate(age, c("agem", "ageh", "aged"), sep = "[^[\\d]]+", convert = TRUE,
           remove = FALSE, fill = "left")

This works great for the entries with just 'hours', but if there are 'days and hours' or 'months, days and hours', then the order of the output is wrong.

Any help is greatly appreciated.

r
2 Answers

First, include a separator wherever there is none between alphabetic characters and numeric digits. Then tidyr::separate into columns.

library(tidyverse)

test %>%
  mutate(age = gsub("([[:alpha:]])([[:digit:]])", "\\1, \\2", age)) %>%
  separate(age, into = c('agem', 'aged', 'ageh'), 
           sep = ', ',
           convert = TRUE,
           remove = FALSE, fill = "left") %>%
  mutate_at(vars(matches('age[[:alpha:]]')), function(x) as.numeric(gsub('[^[:digit:]]', '', x)))
#                        age agem aged ageh
#1                   6 hours   NA   NA    6
#2           2 days, 4 hours   NA    2    4
#3 1 months, 3 days, 7 hours    1    3    7
user_82454060

You can use str_extractfrom the package stringr and positive lookahead in (?= ...):

test$agem <- str_extract(test$age, "\\d+(?=\\smonth)")
test$aged <- str_extract(test$age, "\\d+(?=\\sday)")
test$ageh <- str_extract(test$age, "\\d+(?=\\shour)")

Result:

test
                      age agem aged ageh
1                 6 hours <NA> <NA>    6
2         2 days, 4 hours <NA>    2    4
3 1 months3 days, 7 hours    1    3    7
user_80399780
Related