identify date format in R before converting
date, r
Solution
> dat$Date <- gsub("[0-9]{2}([0-9]{2})$", "\\1", dat$Date)
> dat$Date <- as.Date(dat$Date, format = "%m/%d/%y")
> dat
Date Market
# 1 2009-12-17 1.703
# 2 2009-12-18 1.700
# 3 2009-12-21 1.700
# 4 2009-12-22 1.590
# 5 2009-12-23 1.568
# 6 2009-12-24 1.520
# 7 2009-12-28 1.500
# 8 2009-12-29 1.450
# 9 2009-12-30 1.450
# 10 2009-12-31 1.450
# 11 2010-01-04 1.440
Problem
I have a simple data set which has a date column and a value column. I noticed that the date sometimes comes in as mmddyy (%m/%d/%y) format and other times in mmddYYYY (%m/%d/%Y) format. What is the best way to standardize the dates so that i can do other calculations without this formatting causing issues? I tried the answers provided here Changing date format in R and here How to change multiple Date formats in same column Neither of these were able to fix the problem. Below is a sample of the data ``` Date, Market 12/17/09,1.703 12/18/09,1.700 12/21/09,1.700 12/22/09,1.590 12/23/2009,1.568 12/24/2009,1.520 12/28/2009,1.500 12/29/2009,1.450 12/30/2009,1.450 12/31/2009,1.450 1/4/2010,1.440 ``` When i read it into a new vector using something like this ``` dt <- as.Date(inp$Date, format="%m/%d/%y") ``` I get the following output for the above segment ``` dt Market 2009-12-17 1.703 2009-12-18 1.700 2009-12-21 1.700 2009-12-22 1.590 2020-12-23 1.568 2020-12-24 1.520 2020-12-28 1.500 2020-12-29 1.450 2020-12-30 1.450 2020-12-31 1.450 2020-01-04 1.440 ``` As you can see we skipped from 2009 to 2020 at 12/23 because of change in formatting. Any help is appreciated. Thanks.