1

I am still learning R, and get very confused when using various data types, classes, etc. I have run into this issue of "Dates" not being in the right format for xts countless times now, and find a solution each time after searching long and hard for (what I consider) complicated solutions.

I am looking for a way to load a CSV into R and convert the date upon loading it each time I want to load a csv into R. 99% of my files contain Date as the first column, in format 01-31-1900 (xts wants YYYY-mm-dd).

Right now I have the following:

FedYieldCurve <- read.csv("Yield Curve.csv", header = TRUE, sep = ",", stringsAsFactors = FALSE)
FedYieldCurve$Date <- format(as.Date(FedYieldCurve$Date), "%Y/%m/%d")

and i am getting: Error in charToDate(x) : character string is not in a standard unambiguous format

user2662565
  • 509
  • 2
  • 9
  • 22

2 Answers2

1

The format argument must be inside as.Date. Try this (if the dates in the files are stored in the 01-31-1900 format):

  as.Date(FedYieldCurve$Date,format="%m-%d-%Y")

When you try to coerce a string to a Date object you have to specify the format of the string as the format argument in the as.Date call. You have the error you reported when you try to coerce a string which has a format other than the standard YYYY-mm-dd.

nicola
  • 24,005
  • 3
  • 35
  • 56
  • as.Date(as.character(FedYieldCurve$Date),format="%d-%m-%Y") [1] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA [39] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA – user2662565 Aug 29 '14 at 14:33
  • Is the format `%d-%m-%Y` correct? Is there a / instead of - in the string? Could you write some elements of FedYieldCurve$Date? – nicola Aug 29 '14 at 14:43
  • Ops, maybe the first two digits refer to the month, so the correct format should be %m-%d-%Y – nicola Aug 29 '14 at 14:45
  • Great -- got it.. "%m/%d/%Y" worked.. so format means it wants you to put in what the current format of the column is and it converts it by default to YYY-MM-DD. I thought that in format you had to specify what format you wanted the end result in. Thanks! – user2662565 Aug 29 '14 at 15:04
  • Yes. Keep in mind that a Date object internally is stored as an integer number indicating how many days have passed since Jan 1st 1970. When you print a date object, R shows it in the YYYY-mm-dd format. You can verify this by applying as.integer to a Date object. Please accept my answer if you found it useful. – nicola Aug 29 '14 at 15:12
0

Provide a few lines of the file when asking questions like this. In the absence of this we have supplied some data below in a self contained example.

Use read.zoo from the zoo package (which xts loads) specifying the format. (Replace the read.zoo line with the commented line to read from a file.)

Lines <- "Date,Value
01-31-1900,3"

library(xts)
# z <- read.zoo("myfile.csv", header = TRUE, sep = ",", format = "%m-%d-%Y")
z <- read.zoo(text = Lines, header = TRUE, sep = ",", format = "%m-%d-%Y")
x <- as.xts(z)

See ?read.zoo and Reading Data in zoo.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341