R, csv file, As.Date returns only NAs

Question

I have a csv file containing financial data (i.e. dates with corresponding prices). My goal is to load these data in R and convert the dates from character data to dates. I tried the following:

data<-read.csv("data.csv",sep=";")
attach(data)
as.Date(Date,format="%Y-%b-%d") #'Date' is the column containing the dates

Unfortunately, this only leads to NAs in Date. Things that were proposed in other threads on this issue but did not help me:

reading in the csv file with 'stringsAsFactors=FALSE'
formatting the dates in Excel as dates

Here is a sample of my csv file:

Date;Open;High;Low;Close;Volume;Adj Close
30.10.2015;10842.51953;10850.58008;10748.7002;10850.13965;89270000;10850.13965
29.10.2015;10867.19043;10886.98047;10741.13965;10800.83984;122513100;10800.83984
28.10.2015;10728.16016;10848.41016;10691.62988;10831.95996;0;10831.95996
27.10.2015;10761.37012;10807.41016;10692.19043;10692.19043;0;10692.19043
26.10.2015;10791.17969;10863.08984;10756.83008;10801.33984;73091500;10801.33984
23.10.2015;10610.33008;10847.46973;10586.95996;10794.54004;0;10794.54004
22.10.2015;10213.00977;10508.25;10194.74023;10491.96973;107511600;10491.96973
21.10.2015;10185.41992;10277.58984;10107.91992;10238.09961;70021400;10238.09961
20.10.2015;10174.79981;10194.53027;10080.19043;10147.67969;67235200;10147.67969

1) No one will be able to help you until you post a sample of your csv file. 2) Don't use `attach` in your R code. — nrussell, Nov 27 '15 at 21:51
See [this Q&A on how to give a reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610) — Jaap, Nov 27 '15 at 21:51
It will clutter your environment with unqualified variable names, which can lead to subtle bugs in your program. It's just a messy, unidiomatic way to write R code. — nrussell, Nov 27 '15 at 22:21

score 1 · Accepted Answer · answered Nov 27 '15 at 22:26

Your format argument was incorrect, which is usually the cause of NAs when coercing strings to Date objects. You can use this instead:

R> as.Date(Df$Date, format = "%d.%m.%Y")
#[1] "2015-10-30" "2015-10-29" "2015-10-28" "2015-10-27" "2015-10-26"
#[6] "2015-10-23" "2015-10-22" "2015-10-21" "2015-10-20"

Instead of attach, you can use alternatives such as within to avoid qualifying your column names. For example,

Df <- within(Df, {
  Date <- as.Date(Date, format = "%d.%m.%Y")
})
##
R> class(Df$Date)
#[1] "Date"

Data:

Df <- read.table(
  text = "Date;Open;High;Low;Close;Volume;Adj Close
30.10.2015;10842.51953;10850.58008;10748.7002;10850.13965;89270000;10850.13965
  29.10.2015;10867.19043;10886.98047;10741.13965;10800.83984;122513100;10800.83984
  28.10.2015;10728.16016;10848.41016;10691.62988;10831.95996;0;10831.95996
  27.10.2015;10761.37012;10807.41016;10692.19043;10692.19043;0;10692.19043
  26.10.2015;10791.17969;10863.08984;10756.83008;10801.33984;73091500;10801.33984
  23.10.2015;10610.33008;10847.46973;10586.95996;10794.54004;0;10794.54004
  22.10.2015;10213.00977;10508.25;10194.74023;10491.96973;107511600;10491.96973
  21.10.2015;10185.41992;10277.58984;10107.91992;10238.09961;70021400;10238.09961
  20.10.2015;10174.79981;10194.53027;10080.19043;10147.67969;67235200;10147.67969",
  header = TRUE, stringsAsFactors = FALSE, sep = ";")

R, csv file, As.Date returns only NAs

1 Answers1