Parse date column with mix of date formats

Question

I have to combine several spreadsheets for my project and for some reason they all decided to enter the dates differently. Below is a sample of my data with all of the different date formats I got after reading in and combining all of the spreadsheets.

date
13-MAR-18
2018-08-05
43423
11-Mar-2019
10/16/2018

I'm trying to standardize everything and turn it into a yyyy-mm-dd format. This is my attempt below.

library(lubridate)
parse_date_time(x = df$date,
                orders = c("Y m d, "d m y", "d B Y", "m/d/y", ),
                locale = "eng")

The problem is that the moment I reach a row with a different format, it stops working and just gives me NA's for the rest of the rows. How do I fix this?

Expected output

date        new_date
13-MAR-18   2018-03-13
2018-08-05  2018-08-05
43423       2018-11-19
11-Mar-2019 2019-03-11
10/16/2018  2018-10-16

Try using `parse_date_time` on each spreasheet before rowbinding them together. — TimTeaFan, Jul 27 '22 at 05:23
I did want to try using parse_date_time before rowbinding them, but they had different formats even within each individual spreadsheet. — Emma N, Jul 27 '22 at 13:03

jared_mamrot · Answer 1 · 2022-07-27T06:36:27.877

Edit:

I've just realised that the conversion for "43423" to "2022-07-27 16:02:42" is incorrect using this approach (2022-07-27 is today's date), as further explained in the docs: https://github.com/gaborcsardi/parsedate

For this solution to work, you would need to handle 'integer' date formats first, then parsedate() the remaining dates. I think @TarJae's solution is the way to go.

Original answer:

Here is a potential solution using the parsedate package:

#install.packages("parsedate")
library(parsedate)

df <- read.table(text = "date
13-MAR-18
2018-08-05
43423
11-Mar-2019
10/16/2018", header = TRUE)

df$date_parsed <- parse_date(df$date)
df
#>          date         date_parsed
#> 1   13-MAR-18 2018-03-13 00:00:00
#> 2  2018-08-05 2018-08-05 00:00:00
#> 3       43423 2022-07-27 16:02:42
#> 4 11-Mar-2019 2019-03-11 00:00:00
#> 5  10/16/2018 2018-10-16 00:00:00

^{Created on 2022-07-27 by the reprex package (v2.0.1)}

As I mentioned in my answer there remain some challenges. The integer type and also '2018-08-05'. — TarJae, Jul 27 '22 at 06:18
Yes, absolutely - I think your approach of splitting the 'task' up into smaller chunks makes sense for handling these edge cases, +1 — jared_mamrot, Jul 27 '22 at 06:24

TarJae · Accepted Answer · 2022-07-27T14:16:54.323

Update: Row 2 was wrong! As I mentioned one issue will be the order of the orders argument of parse_date_time:

Now it should work:

library(dplyr)
library(stringr)
library(parsedate)

df %>%   
  mutate(x = parse_date(date)) %>% 
  mutate(y = as.integer(str_extract(date, '^\\d+$')),
         y = as.Date(y, origin = "1899-12-30"),
         x = as.Date(x),
         y = coalesce(y,x)) %>% 
  select(date, new_date=y)

         date   new_date
1   13-MAR-18 2018-03-13
2  2018-08-05 2018-08-05
3       43423 2018-11-19
4 11-Mar-2019 2019-03-11
5  10/16/2018 2018-10-16

First answer: In my point of view there are 3 challenges.

First of all it is difficult to feed the orders argument of parse_date_time. I think after applying arrange we could get some control.

Second, the integer type of date must be handled separately I think.

Finally, it is not clear which is month in "2018-08-05" it could be 8 or 5.

library(lubridate)
library(dplyr)
df %>% 
  arrange(date) %>% 
  mutate(x = parse_date_time(date, orders=c("mdy", "dmy", "ymd")),
         y = as.integer(str_extract(date, '^\\d+$')),
         y = as.Date(y, origin = "1899-12-30"),
         x = coalesce(x,y)) %>% 
  select(date, new_date=x)

 date   new_date
1  10/16/2018 2018-10-16
2 11-Mar-2019 2019-11-20
3   13-MAR-18 2018-03-13
4  2018-08-05 2018-08-05
5       43423 2018-11-19
Warning message:
Problem while computing `x = parse_date_time(date, orders =
c("mdy", "dmy", "ymd"))`.
i  1 failed to parse.

Actually, sorry it seems like this didn't work for the second row. The new date for 11-Mar-2019 should be 2019-03-11 not 2019-11-20. It seems like R thinks that the "11" is a month and not a day? — Emma N, Jul 27 '22 at 13:37
Yes, it works perfectly now and no warning messages either. Thank you! — Emma N, Jul 27 '22 at 14:47

SBMVNO · Answer 3 · 2022-07-27T06:39:30.727

Here is an answer without using any package for reference.

dateV=c(
'13-MAR-18',
'2018-08-05',
'13-Jun-19')

#Note: "pat and formatUsed" is a lookup in pair
pat=c("[0-9]{2}-\\w\\w\\w-[0-9]{2}$", "[0-9]{4}-[0-9]{2}-[0-9]{2}")
formatUsed=c("%d-%B-%y", "%Y-%m-%d")

betterDateV=NULL

for (tryP in 1:length(pat))
{
  dx=which(grepl(pat[tryP], dateV))
  betterDateV[dx] = as.Date( dateV[dx]   , format= formatUsed[tryP] )
}
as.Date(as.numeric(betterDateV))

score 0 · Answer 4 · answered Jul 27 '22 at 05:56

0

You need

as.Date(x, tryFormats = c()

See:

Use as.Date with tryFormats to parse dates with different formats

You need to specify the formats in c() in the best order to run it.

answered Jul 27 '22 at 05:56

CALUM Polwart

497
3
5

Your numerical date may be more of a challenge! It may need to be added to as.Date("1970-01-01")... Which might need to use some logic rules – CALUM Polwart Jul 27 '22 at 05:57

Parse date column with mix of date formats

4 Answers4

Edit:

Original answer: