Hi all, I'm having a little bit of trouble with some date conversions and am hoping someone can help me out. Thanks in advance. OK, I have two sources of data that provide date info in a csv file differently. I've attached a small zipped file with two text files that illustrate both. (Is it ok to send attachments to this list? Not sure. It's very small.) I need to be able to check dates in one file against ranges in the other. For comparison I sort of like the format where Jan 3, 2004 would be 1040103 (104 years since 1900, etc.) and since that file seems to convert into R dates I don't think I need too much help there. However the other file is using a pretty standard M/D/Y format and I cannot seem to get it converted into R dates. No complaints from R but I have two issues with the results: 1) The year 2004 is showing up as 2020 after conversion. That's just wrong. 2) Sometimes I get NAs. That I don't understand. Can anyone see what I'm doing wrong here? As I say, I think I'd like to convert 01/03/2004 into 1040103 as this numeric format seems very good for doing comparisons. The code: MyDate1 = read.csv("C:\\Date1.txt",header=TRUE) MyDate2 = read.csv("C:\\Date2.txt",header=TRUE) MyDate1 Date1 = MyDate1$Date class(Date1) mode(Date1) Date1 Date1 = as.Date(Date1, "%m/%d/%y") class(Date1) mode(Date1) Date1 MyDate2 Date2 = MyDate2$EnDate class(Date2) mode(Date2) Date2 Date2 = strptime(Date2 + 19e6L, "%Y%m%d") class(Date2) mode(Date2) Date2 Date2 = as.Date(Date2) class(Date2) mode(Date2) Date2 Results follow. Thanks, Mark> MyDate1 = read.csv("C:\\Date1.txt",header=TRUE) > MyDate2 = read.csv("C:\\Date2.txt",header=TRUE) > > MyDate1Date Time 1 01/02/2003 1315 2 01/03/2003 1315 3 01/06/2003 1315 4 01/07/2003 1315 5 01/08/2003 1315 6 01/09/2003 1315 7 01/10/2003 1315 8 01/13/2003 1315 9 01/14/2003 1315 10 01/15/2003 1315 11 01/16/2003 1315 12 01/17/2003 1315> Date1 = MyDate1$Date > class(Date1)[1] "factor"> mode(Date1)[1] "numeric"> Date1[1] 01/02/2003 01/03/2003 01/06/2003 01/07/2003 01/08/2003 01/09/2003 01/10/2003 01/13/2003 01/14/2003 01/15/2003 01/16/2003 01/17/2003 Levels: 01/02/2003 01/03/2003 01/06/2003 01/07/2003 01/08/2003 01/09/2003 01/10/2003 01/13/2003 01/14/2003 01/15/2003 01/16/2003 01/17/2003> Date1 = as.Date(Date1, "%m/%d/%y") > class(Date1)[1] "Date"> mode(Date1)[1] "numeric"> Date1[1] "2020-01-02" "2020-01-03" "2020-01-06" "2020-01-07" "2020-01-08" "2020-01-09" "2020-01-10" "2020-01-13" "2020-01-14" "2020-01-15" "2020-01-16" [12] "2020-01-17"> > > MyDate2Trade PosType EnDate EnTime ExDate ExTime 1 1 -1 1040127 919 1040127 932 2 2 1 1040127 1155 1040127 1208 3 3 -1 1040127 1300 1040127 1313 4 4 -1 1040128 958 1040128 1313 5 5 -1 1040129 1024 1040129 1129 6 6 1 1040129 1234 1040129 1313 7 7 1 1040202 906 1040202 1129 8 8 -1 1040202 1129 1040202 1313> Date2 = MyDate2$EnDate > class(Date2)[1] "numeric"> mode(Date2)[1] "numeric"> Date2[1] 1040127 1040127 1040127 1040128 1040129 1040129 1040202 1040202> Date2 = strptime(Date2 + 19e6L, "%Y%m%d") > class(Date2)[1] "POSIXt" "POSIXlt"> mode(Date2)[1] "list"> Date2[1] "2004-01-27 GMT" "2004-01-27 GMT" "2004-01-27 GMT" "2004-01-28 GMT" "2004-01-29 GMT" "2004-01-29 GMT" "2004-02-02 GMT" "2004-02-02 GMT"> Date2 = as.Date(Date2) > class(Date2)[1] "Date"> mode(Date2)[1] "numeric"> Date2[1] "2004-01-27" "2004-01-27" "2004-01-27" "2004-01-28" "2004-01-29" "2004-01-29" "2004-02-02" "2004-02-02">
No attachment appeared. I don't think the list allows zip files as attachments. Try plain text. On Sat, Jul 11, 2009 at 1:10 PM, Mark Knecht<markknecht at gmail.com> wrote:> Hi all, > ? I'm having a little bit of trouble with some date conversions and > am hoping someone can help me out. Thanks in advance. > > ? OK, I have two sources of data that provide date info in a csv file > differently. I've attached a small zipped file with two text files > that illustrate both. (Is it ok to send attachments to this list? Not > sure. It's very small.) I need to be able to check dates in one file > against ranges in the other. For comparison I sort of like the format > where Jan 3, 2004 would be 1040103 (104 years since 1900, etc.) and > since that file seems to convert into R dates I don't think I need too > much help there. However the other file is using a pretty standard > M/D/Y format and I cannot seem to get it converted into R dates. No > complaints from R but I have two issues with the results: > > 1) The year 2004 is showing up as 2020 after conversion. That's just wrong. > 2) Sometimes I get NAs. That I don't understand. > > ? Can anyone see what I'm doing wrong here? As I say, I think I'd > like to convert 01/03/2004 into 1040103 as this numeric format seems > very good for doing comparisons. > > ? The code: > > MyDate1 = read.csv("C:\\Date1.txt",header=TRUE) > MyDate2 = read.csv("C:\\Date2.txt",header=TRUE) > > MyDate1 > Date1 = MyDate1$Date > class(Date1) > mode(Date1) > Date1 > Date1 = as.Date(Date1, "%m/%d/%y") > class(Date1) > mode(Date1) > Date1 > > > MyDate2 > Date2 = MyDate2$EnDate > class(Date2) > mode(Date2) > Date2 > Date2 = strptime(Date2 + 19e6L, "%Y%m%d") > class(Date2) > mode(Date2) > Date2 > Date2 = as.Date(Date2) > class(Date2) > mode(Date2) > Date2 > > ? Results follow. > > Thanks, > Mark > > >> MyDate1 = read.csv("C:\\Date1.txt",header=TRUE) >> MyDate2 = read.csv("C:\\Date2.txt",header=TRUE) >> >> MyDate1 > ? ? ? ? Date Time > 1 ?01/02/2003 1315 > 2 ?01/03/2003 1315 > 3 ?01/06/2003 1315 > 4 ?01/07/2003 1315 > 5 ?01/08/2003 1315 > 6 ?01/09/2003 1315 > 7 ?01/10/2003 1315 > 8 ?01/13/2003 1315 > 9 ?01/14/2003 1315 > 10 01/15/2003 1315 > 11 01/16/2003 1315 > 12 01/17/2003 1315 >> Date1 = MyDate1$Date >> class(Date1) > [1] "factor" >> mode(Date1) > [1] "numeric" >> Date1 > ?[1] 01/02/2003 01/03/2003 01/06/2003 01/07/2003 01/08/2003 01/09/2003 > 01/10/2003 01/13/2003 01/14/2003 01/15/2003 01/16/2003 01/17/2003 > Levels: 01/02/2003 01/03/2003 01/06/2003 01/07/2003 01/08/2003 > 01/09/2003 01/10/2003 01/13/2003 01/14/2003 01/15/2003 01/16/2003 > 01/17/2003 >> Date1 = as.Date(Date1, "%m/%d/%y") >> class(Date1) > [1] "Date" >> mode(Date1) > [1] "numeric" >> Date1 > ?[1] "2020-01-02" "2020-01-03" "2020-01-06" "2020-01-07" "2020-01-08" > "2020-01-09" "2020-01-10" "2020-01-13" "2020-01-14" "2020-01-15" > "2020-01-16" > [12] "2020-01-17" >> >> >> MyDate2 > ?Trade PosType ?EnDate EnTime ?ExDate ExTime > 1 ? ? 1 ? ? ?-1 1040127 ? ?919 1040127 ? ?932 > 2 ? ? 2 ? ? ? 1 1040127 ? 1155 1040127 ? 1208 > 3 ? ? 3 ? ? ?-1 1040127 ? 1300 1040127 ? 1313 > 4 ? ? 4 ? ? ?-1 1040128 ? ?958 1040128 ? 1313 > 5 ? ? 5 ? ? ?-1 1040129 ? 1024 1040129 ? 1129 > 6 ? ? 6 ? ? ? 1 1040129 ? 1234 1040129 ? 1313 > 7 ? ? 7 ? ? ? 1 1040202 ? ?906 1040202 ? 1129 > 8 ? ? 8 ? ? ?-1 1040202 ? 1129 1040202 ? 1313 >> Date2 = MyDate2$EnDate >> class(Date2) > [1] "numeric" >> mode(Date2) > [1] "numeric" >> Date2 > [1] 1040127 1040127 1040127 1040128 1040129 1040129 1040202 1040202 >> Date2 = strptime(Date2 + 19e6L, "%Y%m%d") >> class(Date2) > [1] "POSIXt" ?"POSIXlt" >> mode(Date2) > [1] "list" >> Date2 > [1] "2004-01-27 GMT" "2004-01-27 GMT" "2004-01-27 GMT" "2004-01-28 > GMT" "2004-01-29 GMT" "2004-01-29 GMT" "2004-02-02 GMT" "2004-02-02 > GMT" >> Date2 = as.Date(Date2) >> class(Date2) > [1] "Date" >> mode(Date2) > [1] "numeric" >> Date2 > [1] "2004-01-27" "2004-01-27" "2004-01-27" "2004-01-28" "2004-01-29" > "2004-01-29" "2004-02-02" "2004-02-02" >> > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > >
You want %Y, not %y. You might also want to look at the zoo package: library(zoo) z <- read.zoo("Date1.txt", header = TRUE, sep = ",", format = "%m/%d/%Y") or using chron: library(zoo) library(chron) z <- read.zoo("Date1.txt", header = TRUE, sep = ",", FUN = as.chron) There are three vignettes that come with zoo that have more info. On Sat, Jul 11, 2009 at 1:10 PM, Mark Knecht <markknecht@gmail.com> wrote:> Hi all, > I'm having a little bit of trouble with some date conversions and > am hoping someone can help me out. Thanks in advance. > > OK, I have two sources of data that provide date info in a csv file > differently. I've attached a small zipped file with two text files > that illustrate both. (Is it ok to send attachments to this list? Not > sure. It's very small.) I need to be able to check dates in one file > against ranges in the other. For comparison I sort of like the format > where Jan 3, 2004 would be 1040103 (104 years since 1900, etc.) and > since that file seems to convert into R dates I don't think I need too > much help there. However the other file is using a pretty standard > M/D/Y format and I cannot seem to get it converted into R dates. No > complaints from R but I have two issues with the results: > > 1) The year 2004 is showing up as 2020 after conversion. That's just wrong. > 2) Sometimes I get NAs. That I don't understand. > > Can anyone see what I'm doing wrong here? As I say, I think I'd > like to convert 01/03/2004 into 1040103 as this numeric format seems > very good for doing comparisons. > > The code: > > MyDate1 = read.csv("C:\\Date1.txt",header=TRUE) > MyDate2 = read.csv("C:\\Date2.txt",header=TRUE) > > MyDate1 > Date1 = MyDate1$Date > class(Date1) > mode(Date1) > Date1 > Date1 = as.Date(Date1, "%m/%d/%y") > class(Date1) > mode(Date1) > Date1 > > > MyDate2 > Date2 = MyDate2$EnDate > class(Date2) > mode(Date2) > Date2 > Date2 = strptime(Date2 + 19e6L, "%Y%m%d") > class(Date2) > mode(Date2) > Date2 > Date2 = as.Date(Date2) > class(Date2) > mode(Date2) > Date2 > > Results follow. > > Thanks, > Mark > > > > MyDate1 = read.csv("C:\\Date1.txt",header=TRUE) > > MyDate2 = read.csv("C:\\Date2.txt",header=TRUE) > > > > MyDate1 > Date Time > 1 01/02/2003 1315 > 2 01/03/2003 1315 > 3 01/06/2003 1315 > 4 01/07/2003 1315 > 5 01/08/2003 1315 > 6 01/09/2003 1315 > 7 01/10/2003 1315 > 8 01/13/2003 1315 > 9 01/14/2003 1315 > 10 01/15/2003 1315 > 11 01/16/2003 1315 > 12 01/17/2003 1315 > > Date1 = MyDate1$Date > > class(Date1) > [1] "factor" > > mode(Date1) > [1] "numeric" > > Date1 > [1] 01/02/2003 01/03/2003 01/06/2003 01/07/2003 01/08/2003 01/09/2003 > 01/10/2003 01/13/2003 01/14/2003 01/15/2003 01/16/2003 01/17/2003 > Levels: 01/02/2003 01/03/2003 01/06/2003 01/07/2003 01/08/2003 > 01/09/2003 01/10/2003 01/13/2003 01/14/2003 01/15/2003 01/16/2003 > 01/17/2003 > > Date1 = as.Date(Date1, "%m/%d/%y") > > class(Date1) > [1] "Date" > > mode(Date1) > [1] "numeric" > > Date1 > [1] "2020-01-02" "2020-01-03" "2020-01-06" "2020-01-07" "2020-01-08" > "2020-01-09" "2020-01-10" "2020-01-13" "2020-01-14" "2020-01-15" > "2020-01-16" > [12] "2020-01-17" > > > > > > MyDate2 > Trade PosType EnDate EnTime ExDate ExTime > 1 1 -1 1040127 919 1040127 932 > 2 2 1 1040127 1155 1040127 1208 > 3 3 -1 1040127 1300 1040127 1313 > 4 4 -1 1040128 958 1040128 1313 > 5 5 -1 1040129 1024 1040129 1129 > 6 6 1 1040129 1234 1040129 1313 > 7 7 1 1040202 906 1040202 1129 > 8 8 -1 1040202 1129 1040202 1313 > > Date2 = MyDate2$EnDate > > class(Date2) > [1] "numeric" > > mode(Date2) > [1] "numeric" > > Date2 > [1] 1040127 1040127 1040127 1040128 1040129 1040129 1040202 1040202 > > Date2 = strptime(Date2 + 19e6L, "%Y%m%d") > > class(Date2) > [1] "POSIXt" "POSIXlt" > > mode(Date2) > [1] "list" > > Date2 > [1] "2004-01-27 GMT" "2004-01-27 GMT" "2004-01-27 GMT" "2004-01-28 > GMT" "2004-01-29 GMT" "2004-01-29 GMT" "2004-02-02 GMT" "2004-02-02 > GMT" > > Date2 = as.Date(Date2) > > class(Date2) > [1] "Date" > > mode(Date2) > [1] "numeric" > > Date2 > [1] "2004-01-27" "2004-01-27" "2004-01-27" "2004-01-28" "2004-01-29" > "2004-01-29" "2004-02-02" "2004-02-02" > > > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > >[[alternative HTML version deleted]]