Calculating the hours minute secs and millseconds for few groups - Error on conversion

[srini]

Hi Team,
I have a column which has date,time (1/8/2018 12:24:57 AM) with one more column ID. i have to calculate the total number of hours between the time period group by ID. Below is the code i am trying

start.time is the column

foo<-real %>% mutate(time_of_day=hms::hms(second(real$start.time),minute(real$start.time),hour(real$start.time)))

The error i am getting is

Error in mutate_impl(.data, dots) :
Evaluation error: character string is not in a standard unambiguous format.

Thanks for the help.

[FJCC]

I would use the lubridate package to first change your character date-times into numeric date-times of the POSIXct type. Notice that 1/8/2018 12:24:57 AM could be January 8 2018 or August 1 2018. If the month is the first digit in the date, try

real <- real %>% mutate(start.time = lubridate::mdy_hms(start.time)

If the day is the first digit, try

real <- real %>% mutate(start.time = lubridate::dmy_hms(start.time)

[srini]

Thanks. I tried first converting using the POSIXct but it didnt work. It throwing warnings. All formats failed to parse. When i did str(real) its giving

$ start.time: Factor w/ 3600 levels "00:00.0","00:01.0",..: 1 1 2272 2277 2266 2267 2265 2270 2270 2271

library(lubridate)
parse_date_time(real$start.time, orders="mdy hms") -- warning All formats failed to parse. No formats found.

real <- real %>% mutate(start.time = lubridate::mdy_hms(start.time)

[FJCC]

Please post a small sample of your data and code as a reproducible example (reprex). This is done with the reprex package and information about using it is available in these two posts (and many other places):
How to do a minimal reprex
What's a reproducible example?

[srini]

Please find below the code. When i am reading the dataset not sure why the Date is not visible in the str command.

library(lubridate)
df <- data.frame(Category = c(1L, 1L, 3L, 2L, 2L, 1L, 3L, 1L, 3L),
                 Date_Time = as.factor(c("55:15.0", "26:58.0", "42:18.0", "26:58.0",
                                         "55:15.0", "49:57.0", "36:59.0", "53:34.0",
                                         "10:48.0")))

df$Date <- as.POSIXct(df$Date_Time,format="%m/%d/%Y %H:%M:%S",tz=Sys.timezone())
df <- df %>% mutate(Date = lubridate::mdy_hms(df$Date_Time))
str(real)

[andresrcs]

Your sample data doesn't have this format "%m/%d/%Y %H:%M:%S", apparently those are difftime values, not POSIXct values, so you should use hms() function instead.

df <- data.frame(Category = c(1L, 1L, 3L, 2L, 2L, 1L, 3L, 1L, 3L),
                 Date_Time = c("55:15.0", "26:58.0", "42:18.0", "26:58.0",
                               "55:15.0", "49:57.0", "36:59.0", "53:34.0",
                               "10:48.0"))

df %>%
    mutate(Date_Time = hms(Date_Time))
#>   Category  Date_Time
#> 1        1 55H 15M 0S
#> 2        1 26H 58M 0S
#> 3        3 42H 18M 0S
#> 4        2 26H 58M 0S
#> 5        2 55H 15M 0S
#> 6        1 49H 57M 0S
#> 7        3 36H 59M 0S
#> 8        1 53H 34M 0S
#> 9        3 10H 48M 0S