as.Date fails in R: 'character string is not in a standard unambiguous'

2
votes

I am working with data in R that I imported form excel format.

I have a column (isas1_3b) in the dataframe (LPAv1.1.1) which is in the character format. Upon importing, the dates have changed from the format dd/mm/yy, to days (e.g., 41268).

I have tried to convert this as below: 

as.Date(LPAv1.1.1$isas1_3b, origin = "1899-12-30")

However, I get the following error:

Error in charToDate(x) : character string is not in a standard unambiguous format 4. stop("character string is not in a standard unambiguous format") 3. charToDate(x) 2. as.Date.character(LPAv1.1.1$isas1_3b, origin = "1899-12-30") 1. as.Date(LPAv1.1.1$isas1_3b, origin = "1899-12-30")

I'm not sure what I am doing wrong. After numerous searches, the above conversion is what was recommended.

I should also add, that there are two other date columns in the original excel document. But they have both been read in as 'POSIXct''POSIXt'.

Other info that may be relevant: 

macOS 13.13.3 R 3.3.3 RStudio 1.1.419

Can someone please help resolve this issue... I am assuming it is something that I am doing. Please let me know if you need any more info.

3
rexceldatestatistics
Your isas1_3b column is not numeric, it's a character column currently. Convert it using as.numeric first and then try again. – thelatemail

3 Answers

2
votes

As thelatemail rightly pointed out, the column with the days information must be in numeric format.

d <- 41268

as.Date(d, origin = "1899-12-30")
#[1] "2012-12-25"

On your dataset, this will fix it:

library(dplyr)

mutate(LPAv1.1.1, isas1_3b = as.Date(as.numeric(isas1_3b), 
                                     origin = "1899-12-30"))
1
votes

The variable class was not consistent within the column/vector. There was a mix of dates, strings, and four digit numbers. Once I corrected these, it worked as expected. Thank you all for your help.

0
votes

How did you import it from excel? Is it possible to adjust the import method so it imports it the way you are expecting?

Importing the data from excel with the package "xlsx" gives you two options read.xlsx which will detect/guess the class type based on the data in that column, or read.xlsx2 where you have to/get to set the class types manually with the colClasses option. (more info here: http://www.sthda.com/english/wiki/r-xlsx-package-a-quick-start-guide-to-manipulate-excel-files-in-r)

Another useful option is XLConnect.

A possible downside of both of these packages is they rely on Java to do the work of importing, so you must have Java installed for these to work.