Changing date format in R

Question

I have some very simple data in R that needs to have its date format changed:

 date midpoint 1   31/08/2011   0.8378 2   31/07/2011   0.8457 3   30/06/2011   0.8147 4   31/05/2011   0.7970 5   30/04/2011   0.7877 6   31/03/2011   0.7411 7   28/02/2011   0.7624 8   31/01/2011   0.7665 9   31/12/2010   0.7500 10  30/11/2010   0.7734 11  31/10/2010   0.7511 12  30/09/2010   0.7263 13  31/08/2010   0.7158 14  31/07/2010   0.7110 15  30/06/2010   0.6921 16  31/05/2010   0.7005 17  30/04/2010   0.7113 18  31/03/2010   0.7027 19  28/02/2010   0.6973 20  31/01/2010   0.7260 21  31/12/2009   0.7154 22  30/11/2009   0.7287 23  31/10/2009   0.7375 

Rather than %d/%m/%Y, I would like it in the standard R format of %Y-%m-%d

How can I make this change? I have tried:

nzd$date <- format(as.Date(nzd$date), "%Y/%m/%d") 

But that just cut off the year and added zeros to the day:

 [1] "0031/08/20" "0031/07/20" "0030/06/20" "0031/05/20" "0030/04/20"  [6] "0031/03/20" "0028/02/20" "0031/01/20" "0031/12/20" "0030/11/20"  [11] "0031/10/20" "0030/09/20" "0031/08/20" "0031/07/20" "0030/06/20"  [16] "0031/05/20" "0030/04/20" "0031/03/20" "0028/02/20" "0031/01/20"  [21] "0031/12/20" "0030/11/20" "0031/10/20" "0030/09/20" "0031/08/20"  [26] "0031/07/20" "0030/06/20" "0031/05/20" "0030/04/20" "0031/03/20"  [31] "0028/02/20" "0031/01/20" "0031/12/20" "0030/11/20" "0031/10/20"  [36] "0030/09/20" "0031/08/20" "0031/07/20" "0030/06/20" "0031/05/20" 

Thanks!

Answer 1

There are two steps here:

• Parse the data. Your example is not fully reproducible, is the data in a file, or the variable in a text or factor variable? Let us assume the latter, then if you data.frame is called X, you can do

 X$newdate <- strptime(as.character(X$date), "%d/%m/%Y") 

Now the newdate column should be of type Date.

• Format the data. That is a matter of calling format() or strftime():

 format(X$newdate, "%Y-%m-%d") 

A more complete example:

R> nzd <- data.frame(date=c("31/08/2011", "31/07/2011", "30/06/2011"),  +                    mid=c(0.8378,0.8457,0.8147)) R> nzd         date    mid 1 31/08/2011 0.8378 2 31/07/2011 0.8457 3 30/06/2011 0.8147 R> nzd$newdate <- strptime(as.character(nzd$date), "%d/%m/%Y") R> nzd$txtdate <- format(nzd$newdate, "%Y-%m-%d") R> nzd         date    mid    newdate    txtdate 1 31/08/2011 0.8378 2011-08-31 2011-08-31 2 31/07/2011 0.8457 2011-07-31 2011-07-31 3 30/06/2011 0.8147 2011-06-30 2011-06-30 R>  

The difference between columns three and four is the type: newdate is of class Date whereas txtdate is character.

Answer 2

nzd$date <- format(as.Date(nzd$date), "%Y/%m/%d") 

In the above piece of code, there are two mistakes. First of all, when you are reading nzd$date inside as.Date you are not mentioning in what format you are feeding it the date. So, it tries it's default set format to read it. If you see the help doc, ?as.Date you will see

format
A character string. If not specified, it will try "%Y-%m-%d" then "%Y/%m/%d" on the first non-NA element, and give an error if neither works. Otherwise, the processing is via strptime

The second mistake is: even though you would like to read it in %Y-%m-%d format, inside format you wrote "%Y/%m/%d".

Now, the correct way of doing it is:

> nzd <- data.frame(date=c("31/08/2011", "31/07/2011", "30/06/2011"),  +                                       mid=c(0.8378,0.8457,0.8147)) > nzd         date    mid 1 31/08/2011 0.8378 2 31/07/2011 0.8457 3 30/06/2011 0.8147 > nzd$date <- format(as.Date(nzd$date, format = "%d/%m/%Y"), "%Y-%m-%d") > head(nzd)         date    mid 1 2011-08-31 0.8378 2 2011-07-31 0.8457 3 2011-06-30 0.8147