as.Date returning NA while converting from 'ddmmmyyyy'

Question

I am trying to convert the string "2013-JAN-14" into a Date as follow :

sdate1 <- "2013-JAN-14"
ddate1 <- as.Date(sdate1,format="%Y-%b-%d")
ddate1

but I get :

[1] NA

What am I doing wrong ? should I install a package for this purpose (I tried installing chron) .

Solution 1

Works for me. The reasons it doesn't for you probably has to do with your system locale.

?as.Date has the following to say:

## This will give NA(s) in some locales; setting the C locale
## as in the commented lines will overcome this on most systems.
## lct <- Sys.getlocale("LC_TIME"); Sys.setlocale("LC_TIME", "C")
x <- c("1jan1960", "2jan1960", "31mar1960", "30jul1960")
z <- as.Date(x, "%d%b%Y")
## Sys.setlocale("LC_TIME", lct)

Worth a try.

Solution 2

This can also happen if you try to convert your date of class factor into a date of class Date. You need to first convert into POSIXt otherwise as.Date doesn't know what part of your string corresponds to what.

Wrong way: direct conversion from factor to date:

a<-as.factor("24/06/2018")
b<-as.Date(a,format="%Y-%m-%d")

You will get as an output:

a
[1] 24/06/2018
Levels: 24/06/2018
class(a)
[1] "factor"
b
[1] NA

Right way, converting factor into POSIXt and then into date

a<-as.factor("24/06/2018")
abis<-strptime(a,format="%d/%m/%Y") #defining what is the original format of your date
b<-as.Date(abis,format="%Y-%m-%d") #defining what is the desired format of your date

You will get as an output:

abis
[1] "2018-06-24 AEST"
class(abis)
[1] "POSIXlt" "POSIXt"
b
[1] "2018-06-24"
class(b)
[1] "Date"

Solution 3

My solution below might not work for every problem that results in as.Date() returning NA's, but it does work for some, namely, when the Date variable is read in in factor format.

Simply read in the .csv with stringsAsFactors=FALSE

data <- read.csv("data.csv", stringsAsFactors = FALSE)
data$date <- as.Date(data$date)

After trying (and failing) to solve the NA problem with my system locale, this solution worked for me.

Comments

[LearneR]

I faced a similar NA problem today and when I went back to my CSV file, I found out that the Date cell's formating was in 'Custom' mode instead of Date mode (in Excel). After I changed it to date, the as.Date command worked just fine. I'm aware that your problem is resolved, but I just wanted to present another possible situation for this problem.

[J.Rojas]

Thank you @Nakx using the method factor to POSIXt works !