Changing date format in R
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!
Solution 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()orstrftime():
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.
Solution 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
Solution 3:
You could also use the parse_date_time function from the lubridate package:
library(lubridate)
day<-"31/08/2011"
as.Date(parse_date_time(day,"dmy"))
[1] "2011-08-31"
parse_date_time returns a POSIXct object, so we use as.Date to get a date object. The first argument of parse_date_time specifies a date vector, the second argument specifies the order in which your format occurs. The orders argument makes parse_date_time very flexible.