R - conversion from chr to POSIXct date/time format in imported csv file ends in N/A

Question

I have imported a csv file (excel) with a timestamp and value. All my efforts to convert the timestamp column to usable time in R result in N/A. I have looked at several threads (SO and elsewhere) and tried many suggestions but somehow not managed to get it right. I have also tried various simpler examples from e.g. R-bloggers and they have worked fine.

> dframe <- read.csv2("file.csv", dec=".", colClasses=c("character","numeric"), as.is=TRUE)

> str(dframe)

    'data.frame':   424 obs. of  2 variables:
     $ d: chr  "2016.08.02 03:59:45" "2016.08.02 04:11:16" "2016.08.02 04:22:45" "2016.08.02 04:34:13" ...
     $ h: num  30 33.3 35.6 35.6 48.9 48.9 48.9 47.8 46.7 46.7 ...

This I believe is a good start. Then:

> dframe$d <- as.POSIXct(dframe$d, tz="GMT", format="%Y.%M.%D %H:%M:%S")
> str(dframe)

    'data.frame':   424 obs. of  2 variables:
     $ d: POSIXct, format: NA NA NA NA ...
     $ h: num  30 33.3 35.6 35.6 48.9 48.9 48.9 47.8 46.7 46.7 ...

Any suggestions are welcome. I am aware of lubridate but will not be trying it, for a while at least.

Try: as.POSIXct(dframe$d, tz="GMT", format="%Y.%m.%d %H:%M:%S"). One needs to use lower case m and d for month and day. — Dave2e, Aug 08 '16 at 21:11

Jon · Accepted Answer · 2016-10-14T22:15:10.377

Try lubridate

Multithreaded BLAS/LAPACK libraries detected. Using 8 cores for math algorithms.
> library(lubridate)

Attaching package: ‘lubridate’

The following object is masked from ‘package:base’:

    date

> ymd_hms("2016.08.02 03:59:45")
[1] "2016-08-02 03:59:45 UTC"
> str(ymd_hms("2016.08.02 03:59:45"))
POSIXct[1:1], format: "2016-08-02 03:59:45"

"I am aware of lubridate but will not be trying it, for a while at least." -- is there a reason you do not want to/can't use lubridate? It seems to be an easy fix.

EDIT

I was bored at work today, so I decided to give this another shot. The reason your POSIXct function failed was mainly because of the "." you have as seperators. A quick fix is to use gsub to replace those "." with "-". Here is an example:

> s = c("2016.08.02 03:59:45", "2016.08.02 04:11:16", "2016.08.02 04:22:45", "2016.08.02 04:34:13")
> dates = as.POSIXct(gsub(pattern="\\.", replacement="-", x=s)) 
> print(dates)
[1] "2016-08-02 03:59:45 PDT" "2016-08-02 04:11:16 PDT"
[3] "2016-08-02 04:22:45 PDT" "2016-08-02 04:34:13 PDT"

Only that I had spent a lot of time on this and I wanted to get to the bottom of it. I will look at lubridate in the near future. As often happens (for me at least) one starts out with a mixed bag of issues and gradually reduces the nr. of problems until there is only one left, something that was introduced inadvertently somewhere along the way, — bjarg, Aug 08 '16 at 21:32

R - conversion from chr to POSIXct date/time format in imported csv file ends in N/A

1 Answers1