How to solve as.POSIXct return in NA?

Question

I have one data.frame with an column time with 05:39:18 23-Oct-2016. And I tried run as.POSIXct to create another column, but returns NA. I did a lot of things, but nothing changes.

structure
(list(id = c(111868L, 111868L, 111868L, 111868L, 111868L, 
111868L), 
time = c("05:39:18 23-Oct-2016", "08:08:56 23-Oct-2016", "08:50:41 23-Oct-2016", "09:39:41 23-Oct-2016", "10:30:41 23-Oct-2016", "11:11:11 23-Oct-2016"), 
lq = c("3", "B", "A", "2", "B", "B"), 
lat = c(-20.3108, -20.3103, -20.3108, -20.3098, -20.3091, -20.3087),
lon = c(-40.2825, -40.2822, -40.2861, -40.2815, -40.2804, -40.2802), 
error_semimajor_axis = c(770L, 33105L, 4046L, 651L, 1282L, 10379L), 
error_semiminor_axis = c(48L, 42L, 31L, 123L, 1077L, 83L), 
error_ellipse_orientation = c(109L, 110L, 77L, 147L, 83L, 94L)), 
row.names = c(NA, 6L), 
class = "data.frame")

srt(l)

'data.frame':   26462 obs. of  8 variables:
 $ id                       : int  111868 111868 111868 111868 111868 111868 111868 111868 111868 111868 ...
 $ time                     : chr  "05:39:18 23-Oct-2016" "08:08:56 23-Oct-2016" "08:50:41 23-Oct-2016" "09:39:41 23-Oct-2016" ...
 $ lq                       : chr  "3" "B" "A" "2" ...
 $ lat                      : num  -20.3 -20.3 -20.3 -20.3 -20.3 ...
 $ lon                      : num  -40.3 -40.3 -40.3 -40.3 -40.3 ...
 $ error_semimajor_axis     : int  770 33105 4046 651 1282 10379 2281 698 8101 1577 ...
 $ error_semiminor_axis     : int  48 42 31 123 1077 83 808 124 126 110 ...
 $ error_ellipse_orientation: int  109 110 77 147 83 94 93 14 105 165 ...

My data is of telemetry data, covers an ruge area on South Atlantic Ocean, I don't know if is that the problem. I tried to set the location with Sys.setlocale, but nothing worked.

I need to run this code, the first one works, but when I run the others, the columns turns NA.

Changing the date format: add column date which include be only the date and not the hour::min::sec

Time is initially in GMT in the raw files retrieved from the portal.

l$date <- sapply(strsplit(l$time, split=' ', fixed=TRUE), function(x) (x[2]))

l$date <- as.POSIXct(l$date, format = "%d-%b-%Y",tz = "GMT", usetz = TRUE)

l$time <- as.POSIXct(l$time, format = "%H:%M:%S %d-%b-%Y",tz = "GMT", usetz = TRUE)

Thanks!!

please provide sample data with `dput` if I run your code on your sample string it works fine. — Julian_Hn, Mar 27 '19 at 18:57
You can see some ideas for how to include a small amount of your data [in this SO question/answer](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) about how to make a great reproducible example. For example, you could give us the first six lines by copying the output of `dput( head(l) )` into your question. — aosmith, Mar 27 '19 at 19:02

score 2 · Accepted Answer · answered Mar 27 '19 at 19:04

The only thing I can think is going wrong has to do with your system's locale. The example below shows what happens with my locale.

The posted data string.

x <- "05:39:18 23-Oct-2016"

The error reproduced.

as.POSIXct(x, format = "%H:%M:%S %d-%b-%Y", tz = "GMT")
#[1] NA

This solution is locale independent.

old_loc <- Sys.getlocale("LC_TIME")
Sys.setlocale("LC_TIME", "en_US.UTF-8")

as.POSIXct(x, format = "%H:%M:%S %d-%b-%Y", tz = "GMT")
#[1] "2016-10-23 05:39:18 GMT"

And back to the original

Sys.setlocale("LC_TIME", old_loc)

What is wrong is the month abbreviation, in my country the correct one would be "out" ("outubro"). So the following works at the first try, without fiddling with locale settings.

y <- "05:39:18 23-out-2016"
as.POSIXct(y, format = "%H:%M:%S %d-%b-%Y", tz = "GMT")
#[1] "2016-10-23 05:39:18 GMT"

How to solve as.POSIXct return in NA?

1 Answers1