Extract year, month and day when dates are non-standard format

Question

I have a column of dates, I want to extract the year, month and day into separate columns. Unfortunately there are inconsistent entries in the dates column, so the normal solution of using format(as.Date(),"%Y") or lubridate::year() doesn't work.

Here is an example dataframe:

dates_df <- data.frame(dates = c("1985-03-23", "", "1983", "1984-01"))

And here is the desired result:

       dates year month  day
1 1985-03-23 1985     3   23
2            <NA>  <NA> <NA>
3       1983 1983  <NA> <NA>
4    1984-01 1984     1 <NA>

I can achieve the desired result with the following code, but it is very slow on large datasets (>100,000 rows):

dates_df$year <- sapply(dates_df$dates, function(x) unlist(strsplit(x, "\\-"))[1])
dates_df$month <- sapply(dates_df$dates, function(x) unlist(strsplit(x, "\\-"))[2])
dates_df$day <- sapply(dates_df$dates, function(x) unlist(strsplit(x, "\\-"))[3])

My question:

Is there a more efficient (fast) way to extract year, month, day columns from messy date data?

A few options here. If you really need speed maybe try the `data.table` option. https://stackoverflow.com/q/4350440 — nniloc, Oct 06 '22 at 02:51
Thanks. Makes sense to think of it as a character splitting problem rather than a date problem. — flee, Oct 06 '22 at 03:01
Can you be sure in cases like `1984-01`, the `01` always denotes the month? Is there always a year? — jay.sf, Oct 06 '22 at 04:58
would you ever have a scenario where you have year and day but not month — Mike, Oct 06 '22 at 18:52

score 2 · Accepted Answer · answered Oct 06 '22 at 05:09

Using strsplit and adapting the lengths.

cbind(dates_df, t(sapply(strsplit(dates_df$dates, '-'), `length<-`, 3)))
#        dates    1    2    3
# 1 1985-03-23 1985   03   23
# 2            <NA> <NA> <NA>
# 3       1983 1983 <NA> <NA>
# 4    1984-01 1984   01 <NA>

With nice names:

cbind(dates_df, `colnames<-`(
  t(sapply(strsplit(dates_df$dates, '-'), `length<-`, 3)), c('year', 'month', 'day')))
#        dates year month  day
# 1 1985-03-23 1985    03   23
# 2            <NA>  <NA> <NA>
# 3       1983 1983  <NA> <NA>
# 4    1984-01 1984    01 <NA>

nniloc · Answer 2 · 2022-10-06T19:39:18.933

My first thought would have been to try tidyr::separate. Untested for speed and might break down if there are date formats not represented in the example data.

tidyr::separate(dates_df, 
                dates, 
                into = c('year', 'month', 'day'), 
                remove = FALSE)

#-----
       dates year month  day
1 1985-03-23 1985    03   23
2                  <NA> <NA>
3       1983 1983  <NA> <NA>
4    1984-01 1984    01 <NA>

Extract year, month and day when dates are non-standard format

2 Answers2