Conversion of large dataframe column of string to datetime - unexplainable behavior

Question

I have a file containing date and time information as a character string. I want to convert it to a datetime POSIXlt object and to that end I use the strftime() base function in R.

While the command works fine when I am passing it a character string, or when I apply it a small slice of the dataframe, when I apply it in a larger slice or in the entire datadrame it fails to capture the Hour. Please see below:

d = "2017-11-18 01:00:00"
t = strftime(d, format = "%Y-%m-%d %H:%M")
t
'2017-11-18 01:00'

head(data %>% dplyr::slice(1:1000) %>% mutate(DateTime1 = strftime(DateTime, format = "%Y-%m-%d %H:%M")))
DateTime    Junction    Vehicles    ID  DateTime1
2015-11-01 00:00:00 1   15  20151101001 2015-11-01 00:00
2015-11-01 01:00:00 1   13  20151101011 2015-11-01 01:00
2015-11-01 02:00:00 1   10  20151101021 2015-11-01 02:00
2015-11-01 03:00:00 1   7   20151101031 2015-11-01 03:00
2015-11-01 04:00:00 1   9   20151101041 2015-11-01 04:00
2015-11-01 05:00:00 1   6   20151101051 2015-11-01 05:00

head(data %>% mutate(DateTime1 = strftime(DateTime, format = "%Y-%m-%d %H:%M")))
DateTime    Junction    Vehicles    ID  DateTime1
2015-11-01 00:00:00 1   15  20151101001 2015-11-01 00:00
2015-11-01 01:00:00 1   13  20151101011 2015-11-01 00:00
2015-11-01 02:00:00 1   10  20151101021 2015-11-01 00:00
2015-11-01 03:00:00 1   7   20151101031 2015-11-01 00:00
2015-11-01 04:00:00 1   9   20151101041 2015-11-01 00:00
2015-11-01 05:00:00 1   6   20151101051 2015-11-01 00:00

How this erratic behavior is to be explained and how can I convert the datetime column for the entire dataset?

Your advice will be appreciated.

Use `dput()` to print the structure and content of your dataset. — Martin Schmelzer, Nov 18 '17 at 12:48
Have you tried using lubridate functions instead? For example: data %>% mutate(DateTime1 = lubridate::ymd_hms(DateTime)). — Peter K, Nov 18 '17 at 18:20
You're saying the type of `data$DateTime` is string? Did you ever get a reproducible example? Typically what causes grief is missing, incomplete or extra text like timezone, milliseconds etc. Anyway, third-party date libraries like lubridate are more fault-tolerant. — smci, May 21 '18 at 00:34
And when you say *"I have a file containing date and time information"*, you didn't show us the `read.csv()` or whatever command you used to convert it to the dataframe you're showing us, or `dput()` on that dataframe. I presume you had `stringsAsFactors = FALSE` — smci, May 21 '18 at 00:39

VFreguglia · Accepted Answer · 2017-11-18T13:10:35.850

I can't tell exactly, but there might be some inconsistency in your DateTime variable. Since it seems to be a character variable, when the vectorized version of your conversion finds an unexpected behaviour it won't handle it well because the same conversion is applied to every entry (it'll use a format that works in all cases), that is probably the reason why it worked in a slice. I'd suggest either gradually increasing your slice size in order to track where the inconsistency appears (there might be a smarter way to check it).

Another note is that strftime requires its first argument to be convertible to POSIXlt, so I'd recommend passing a POSIXlt object to avoid your function choosing how to convert your original string. You could use for example:

t = strftime(strptime(d, format = "%Y-%m-%d %H:%M:%OS"), format = "%Y-%m-%d %H:%M")

Then, hopefully, when converting your character variable to a POSIXlt the way you expect it to be, via strptime, it will throw an error due to inconsistent rows.

A third, but not as good solution would be to use rowwise(), what would convert each row separately, producing expected results except for the inconsistencies (It'd look for appropriate format in each row, instead of one format that works for everyone).

Conversion of large dataframe column of string to datetime - unexplainable behavior

1 Answers1