as.date cannot work for my dataset because character string is not in a standard unambiguous format-CodePudding

So I am trying to convert the string to date, here's the dataset: . Be careful that the first date is actually 2007.1 (January not October), somehow it just turns into "10" when I read in the csv.

Then my code looks like this:

n.time <- paste(df$date, ".01", sep = '')         # add day 
n.time
gsub(".", "-", n.time, fixed = TRUE) # replace period with dash line 
n.time <- as.Date(n.time)            # make Date class

The error is: Error in charToDate(x) : character string is not in a standard unambiguous format

I think it is because my date is 2007-1-01 but it should be 2007-01-01 so it can be turned to date. Can anyone help?

CodePudding user response：

You can use the anydate() function of the anytime package:

 > anytime::anydate(c("2007.10", "2007.11", "2007.12"))
 [1] "2007-10-01" "2007-11-01" "2007-12-01" 
 >

You will have to turn your numeric input into character first:

 > anytime::anydate(as.character(c(2007.10, 2007.11, 2007.12)))  
 [1] "2007-10-01" "2007-11-01" "2007-12-01" 
 >

The anydate() function can convert from numeric but it assumes a numeric representation (i.e.

> as.numeric(Sys.Date())   
[1] 19294                  
>

Generally speaking your input is of course incomplete as a date needs a day besides a month and a year.

CodePudding user response：

The root of the issue is the date column is being read in as numeric, and 2007.1 becomes 2007.10 because some values have two places after the decimal. There is no way to distinguish these values from “true Octobers” — e.g., 2007.10 in the original data file — once the data have been read in. Instead, you have to read them in as character from the get-go. This example does so using the col_types argument to readr::read_csv(). I then converts to date using lubridate::ym(), since the values are in year-month format.

library(readr)
library(lubridate)

# stand in for raw data file

df_raw <- "date,val
2007.1,1
2007.11,2
2007.12,3
2008.01,4
2008.02,5
2008.10,6
"

df1 <- read_csv(
  df_raw,
  col_types = cols(date = col_character())
)

ym(df1$date)
# [1] "2007-01-01" "2007-11-01" "2007-12-01" "2008-01-01" "2008-02-01"
# [6] "2008-10-01"