R: Incorrect Dates After Using read.csv

Question

This has me stumped. I have tried several solutions that I've found elsewhere on SO and other places to no avail. The closest one to my problem appears to be this question:

Convert factor to date object R without NA

I'm trying to read in a .csv that has date information. The column with the dates in it reads in as a factor. When I convert it to the "date" class, the dates get garbled. Below is a Dropbox link to a simple .csv file that should allow you to reproduce the problem:

https://www.dropbox.com/s/07xuuy6pmw3qctt/dates.csv?dl=0

Code:

dates <- read.csv("dates.csv")


dates
        Name      Date
1   Person 1  1/1/2000
2   Person 2  2/1/2001
3   Person 3  3/1/2002
4   Person 4  4/1/2003
5   Person 5  5/1/2004
6   Person 6  6/1/2005
7   Person 7  7/1/2006
8   Person 8  8/1/2007
9   Person 9  9/1/2008
10 Person 10 10/1/2009



dates$Date <- as.Date(dates$Date, "%m/%d/%y")
dates

dates
        Name       Date
1   Person 1 2020-01-01
2   Person 2 2020-02-01
3   Person 3 2020-03-01
4   Person 4 2020-04-01
5   Person 5 2020-05-01
6   Person 6 2020-06-01
7   Person 7 2020-07-01
8   Person 8 2020-08-01
9   Person 9 2020-09-01
10 Person 10 2020-10-01

All of the years get changed to 2020, when they should start with 2001 and end with 2009. How can I get the dates to display as dates without the values being changed incorrectly?

It has nothing to do with `read.csv`. You are just using `as.Date` incorrectly. Please read carefully `?strptime` first. I don't know what comes first in your data- the day or the month, but you definitely should change `%y` to `%Y`. As a side note, it's quite silly to accuse a single function after running a whole code chunk without checking what each step does. — David Arenburg, Feb 07 '17 at 14:30
Can you provide a sample of the raw values you are trying to convert to date? — Lloyd Christmas, Feb 07 '17 at 14:31
@LloydChristmas, edited to show how the values print right after reading in. — Yallweh, Feb 07 '17 at 14:35
To add to the fair points above, you might also want to let RStudio take *some* of the strain. In the latest version, click *Import Dataset* on the *Environment* tab, select the csv option and follow the GUI. — p0bs, Feb 07 '17 at 14:37
In other words `as.Date(date$Date, "%m/%d/%Y")` or `as.Date(date$Date, "%d/%m/%Y")` (depends if the month or the day comes first) — David Arenburg, Feb 07 '17 at 14:38
The format is mdy, but I get the same result regardless of whether I use %y or %Y. The year gets garbled to 2020. — Yallweh, Feb 07 '17 at 14:47
`as.Date(factor(c("1/1/2000", "2/1/2001")), "%m/%d/%Y")` works just fine for me. — David Arenburg, Feb 07 '17 at 14:52
@DavidArenburg, I got it to work just now. I had to start from the beginning for it to work properly. Thanks. I'd tried changing this before I posted and it didn't work, but I think I was making the same error. — Yallweh, Feb 07 '17 at 15:21

score 0 · Accepted Answer · answered Feb 07 '17 at 15:42

try this:

dbName <- "https://dl.dropboxusercontent.com/s//07xuuy6pmw3qctt/dates.csv” #your file

To read as data.frame:

df <- read.csv(dbName,header=TRUE,stringsAsFactors=F)
df$Date <- as.Date(df$Date,format ='%m/%d/%Y' )
head(df)
        Name       Date
1   Person 1 2000-01-01
2   Person 2 2001-02-01
3   Person 3 2002-03-01
4   Person 4 2003-04-01
5   Person 5 2004-05-01
6   Person 6 2005-06-01

To import the file as xts-object:

dbFile <- as.xts(read.zoo(dbName,sep=',',header=TRUE,index.column = 2,format='%m/%d/%Y',stringsAsFactors=F))
names(dbFile) <- "name"

head(dbFile)
           name       
2000-01-01 "Person 1" 
2001-02-01 "Person 2" 
2002-03-01 "Person 3" 
2003-04-01 "Person 4" 
2004-05-01 "Person 5" 
2005-06-01 "Person 6"

Thanks, that works! The critical thing that I'd been missing is that once you use 'format ='%m/%d/%y' in error, you have to go back to square one for 'format ='%m/%d/%Y' to work. — Yallweh, Feb 07 '17 at 15:50

R: Incorrect Dates After Using read.csv

1 Answers1