0

I have a dataset with 228 observations and, one of the three columns specifying "Date" is in the following form: 2 December 1999 4 November 1999 7 October 1999 ..... My aim is to convert it into this format: 1999-12-02 (yyyy-mm-dd). To do so, I use the "as.POSIXct" function but I get "NA" for all the 228 observations.

I tried this code and any possible variance of that (including some hints got from previous questions similar to mine) such as "as.Date", "strptime", etc.:

new_date <- as.POSIXct(ecb_result1$Date, format = "%Y-%m-%d")

As I said, I expected to see the conversion from "2 December 1999" to "1999-12-02". Instead, I got:

[1] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

Is there anyone who can help me understand what's wrong and how to fix it?

EdChum
  • 376,765
  • 198
  • 813
  • 562
Rollo99
  • 1,601
  • 7
  • 15

1 Answers1

2

The format for as.Date or as.POSIXct in this case is "%e %B %Y". But this is locale dependent. In my case October is not transformed as my locale is dutch. And the format is expecting oktober not October. This might be happening in your case as well. I would suggest trying lubridate's dmy function. See examples below.

dates <- c("2 December 1999", "4 November 1999", "7 October 1999")

# goes wrong for my locale
as.Date(dates, "%e %B %Y") # as.Date
[1] "1999-12-02" "1999-11-04" NA

as.POSIXct(dates, format = "%e %B %Y") # as.POSIXct
[1] "1999-12-02 CET" "1999-11-04 CET" NA

But lubridate's dmy function does the trick for me.

lubridate::dmy(dates)
[1] "1999-12-02" "1999-11-04" "1999-10-07"

Or messing around with your Sys.setlocale will also work:

Sys.setlocale("LC_TIME", "English_United Kingdom")

as.POSIXct(dates, format = "%e %B %Y")
[1] "1999-12-02 CET"  "1999-11-04 CET"  "1999-10-07 CEST"
phiver
  • 23,048
  • 14
  • 44
  • 56
  • Thank you very much! Solved the problem with 'Sys.setlocale("LC_TIME", "English_United Kingdom")'. Thanks again a lot! – Rollo99 Apr 21 '19 at 19:03