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

#1

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.

0 Likes

#2

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)
0 Likes

#3

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)

0 Likes

#4

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?

0 Likes

#5

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)
0 Likes

#7

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
0 Likes

closed #8

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes