Convert select character variables to times in data frame in R

Question

I'm trying to change several variables within a data frame that are currently characters into time. I've imported only the variables I want from a csv file using

ids_dates_times <- read.csv("filename", header=TRUE, na.strings=c(".",NA),
                            stringsAsFactor = FALSE, 
                            colClasses="character")[,c(1,3,8,9,10,15,16,17,22,23,24,29,30,31,36,37,38,43,44,45,50,51)]

Example of ids_dates_times (this is just first 4 variables, note there are 14 that need converted to times):

       id_phresh   D1_Date    D1_Bed_Time  D1_Wake_Time
  1      1097      9/3/2016    15:16:00      8:59:00
  2      1098      7/22/2016    2:00:00      6:30:00
  3      2005      8/25/2016   23:00:00      6:00:00
  4      2007      7/9/2016     1:00:00      7:00:00
  5      2013      6/23/2016   23:45:00      8:35:00

I'd like my next line of code to convert selected columns to times.

times <- chron(times.= ids_dates_times[,c(3,4,6,7,9,10,12,13,15,16,18,19,21,22)], format = "hh:mm:ss")

I receive the following error

Error in convert.times(times., fmt) : format hh:mm:ss may be incorrect

I've tried the following:

itimes <- which(sapply(DF, function(x) all(grepl(":.*:", x))))
DF[itimes] <- lapply(DF[itimes], times)
idates <- which(sapply(DF, function(x) all(grepl("/.*/", x))))
DF[idates] <- lapply(DF[idates], dates)

which results in:

str(lapply(DF, class))

List of 22 $ id_phresh : chr "character" $ D1_Date : chr "character" $ D1_Bed_Time : chr "character" $ D1_Wake_Time: chr "character"

These should should as date and time, not character, right? Any help would be awesome!

Please share part of `ids_dates_times` this so that we can understand problem. You can share `head(ids_dates_times, 5)`. — MKR, Mar 30 '18 at 19:03
Try with `format = "h:m:s"` (or leave the format out, in the examples at the bottom of `?chron` they show it working with this format by default.) — Gregor Thomas, Mar 30 '18 at 19:30
format = "h:m:s" returns the same error but adjusted for the different input code (hh:mm:ss --> h:m:s). Leaving it out returns the same result — Jonathan, Mar 30 '18 at 19:42

G. Grothendieck · Answer 1 · 2018-03-31T11:24:11.443

Assuming that DF is as shown reproducibly in the Note at the end then if we know the indexes of the times and dates columns in advance, i.e. itimes and idates below are known, then it would just be:

library(chron)

itimes <- 3:4
DF[itimes] <- lapply(DF[itimes], times)
idates <- 2
DF[idates] <- lapply(DF[idates], dates)

giving:

> str(lapply(DF, class))
List of 4
 $ id_phresh   : chr "integer"
 $ D1_Date     : chr [1:2] "dates" "times"
 $ D1_Bed_Time : chr "times"
 $ D1_Wake_Time: chr "times"

or we could calculate itimes and idates as those columns with two : and two / respectively and then run the above. lapply statements.

itimes <- which(sapply(DF, function(x) all(grepl(":.*:", x))))
DF[itimes] <- lapply(DF[itimes], times)

idates <- which(sapply(DF, function(x) all(grepl("/.*/", x))))
DF[idates] <- lapply(DF[idates], dates)

Alternately, we could use one lapply like this:

DF[] <- lapply(DF, function(x) {
  if (all(grepl(":.*:", x))) x <- times(x)
  if (all(grepl("/.*/", x))) x <- dates(x)
  x
})

Note

Lines <- "
       id_phresh   D1_Date    D1_Bed_Time  D1_Wake_Time
  1      1097      9/3/2016    15:16:00      8:59:00
  2      1098      7/22/2016    2:00:00      6:30:00
  3      2005      8/25/2016   23:00:00      6:00:00
  4      2007      7/9/2016     1:00:00      7:00:00
  5      2013      6/23/2016   23:45:00      8:35:00"
DF <- read.table(text = Lines, as.is = TRUE)

I've tried all 3 options, no errors pop up, but the variables are still described as characters. There are 14 columns in ids_dates_times that I'm trying to convert from characters to times. — Jonathan, Apr 05 '18 at 03:46
Correction: itimes <- 3:4:6:7:9:10:12:13:15:16:18:19:21:22 DF[itimes] <- lapply(DF[itimes], times) does produce the same error as noted above — Jonathan, Apr 05 '18 at 03:51
I have shown the output in the answer that is given if you copy and paste the code in the Note at the end and then the code from my answer and it does not give an error nor does it give character result. — G. Grothendieck, Apr 05 '18 at 11:14
I've edited my question a bit to help provide more detail. My apologies! I thought I was being clear. I'm not sure why the last suggestion with the lapply isn't working. I get the same results. — Jonathan, Apr 05 '18 at 15:40

Convert select character variables to times in data frame in R

1 Answers1

Note