How to convert multiple date format to ```as.posixct```?

Question

Hi I have Multiple format of date in my data. How do I convert in to Date format as.posixct
Below are the sample data.

df <- data.frame(date=c('01-12-2019','44166','12-08-2019','01/27/2020'))

any help will much appreciated.

Is `01-12-2019` day-month-year and `12-08-2019` month-day-year? — Sotos, Jan 28 '20 at 13:36
You might get an answer, but I would suggest trying to standardize your date data before even bringing it into R. — Tim Biegeleisen, Jan 28 '20 at 13:36
Relevant: https://stackoverflow.com/questions/25463523/convert-variable-with-mixed-date-formats-to-one-format — Sotos, Jan 28 '20 at 13:39
You need to get rid of these dates first('44166') to enable you to change dates at once. — NelsonGon, Jan 28 '20 at 13:55

score 2 · Accepted Answer · answered Jan 28 '20 at 14:07

One possibility could be to handle different date formats separately(this might be possible with the tryFormats argument in as.Date but the lack of uniformity with respect to the date formats makes it less possible to use).:

df$date <-stringr::str_replace_all(df$date,"/","-") #format won't work without this
good_formats <-df[grepl("\\D",df$date),]
bad_formats<- df[!grepl("\\D",df$date),] #They're not bad, just think they're less programmer friendly
good_formats<-as.Date(good_formats,
       format=c("%d-%m-%Y","%d-%m-%Y","%m-%d-%Y"))
bad_formats <- as.Date(as.numeric(bad_formats),origin="1970-01-01")
data.frame(date=c(good_formats,bad_formats))

Result:

   date
1 2019-12-01
2 2019-08-12
3 2020-01-27
4 2090-12-03

Structure of the result:

'data.frame':   4 obs. of  1 variable:
 $ date: Date, format: "2019-12-01" ...

Data:

data.frame(date=c('01-12-2019','44166','12-08-2019','01/27/2020')

This answer also very helpful to me. Thank you every one for help. as this type of challenges may come to every one when they are working with time series data. — Tushar Lad, Jan 29 '20 at 04:25

How to convert multiple date format to ```as.posixct```?

1 Answers1