Extract duration in years and months from string and convert to months

user_13068233 0
Viewed 194

I have a string column with the length of a period, formated as "xx years yy months". I want to express these periods in months only, i.e. 12 * number of years + number of months.

A small example:

x = c("2 years 5 months", "10 years 10 months")

Here the desired result is 2 * 12 + 5 = 29, and 10 * 12 + 10 = 130 respectively.

I tried the substr function, but I didn't manage to handle the fact that month and year may be either one or two digits.

12 * as.numeric(substr(x, 1, 2)) + as.numeric(substr(x, 6, 7)))

And then I tried the sprintf as below, but it didn't give the intended result.

sprintf("%1.0f", x))
substr r time date
2 Answers

Using regular expressions to extract the number of years and months this could be achieved like so:

tomonths <- function(x) {
  sum(as.numeric(regmatches(x, gregexpr("\\d+", x))[[1]]) * c(12, 1))  
}
tomonths("10 years 10 months")
#> [1] 130

For vectors you colud use e.g. sapply(c("2 years 5 months", "10 years 10 months"), tomonths).

EDIT: Following the comment by @thelatemail (Thanks!) a vectorized and more efficient approach would look like so:

tomonths2 <- function(x) {
  sapply(regmatches(x, gregexpr("\\d+", x)), function(x) sum(as.numeric(x) * c(12,1)) )  
}
user_129938610

To build on your substr attempt: for months, you could define start and stop from the end of the string to avoid the issue with different start/stop positions depending on number of digits in month and year

as.integer(substr(x, 1, 2)) * 12 + as.integer(substr(x, nchar(x) - 8, nchar(x) - 6))
# [1]  29 130

Another non-regex alternative:

sapply(strsplit(x, " "), function(v) sum(as.integer(v[c(1, 3)]) * c(12, 1)))
# [1]  29 130

Using lubridate convenience functions:

library(lubridate)
time_length(duration(x), unit = "months")
# [1]  29 130
user_18517120
Related