Using Data with time measured in different formats

[mvogliano]

Hello,

I'm trying to manipulate data that measures swimming times in different events, but the formatting of the data given is giving me trouble. Some events where times are fast are only measured in seconds and appear as numbers in the format 55.97 for example. Other events with longer times are only in the format mm:ss.00 as character strings, for example 1:58.90. And still some events have a combination of both. I want to be able to manipulate the data to from different types of plots so I need R to understand that 59.99 is right next to 1:00.00. Is this possible?

Here's a simpler version of the data I am working with:

df <- data.frame(Name = c("Swimmer 1","Swimmer 2", "Swimmer 3"),
                 Event.1 = c(22.50,25.90,27.56),
                 Event.2 = c(54.23,"1:01.13",59.54),
                 Event.3 = c("1:41.12","1:58.13","2:03.15")
                 )

[FJCC]

I can get all of the values into a consistent character format.

df %>% mutate(across(.cols = -Name, 
                     .fns = ~ifelse(!grepl(":", .), paste0("0:", .),.))) 

       Name Event.1 Event.2 Event.3
1 Swimmer 1  0:22.5 0:54.23 1:41.12
2 Swimmer 2  0:25.9 1:01.13 1:58.13
3 Swimmer 3 0:27.56 0:59.54 2:03.15

I have never had to work with bare times, without a date, so I cannot comment on the best way to transform those character values into useful numeric times. I'm sure you can web search that as well as I can.

[jrkrideau]

I was wondering about times a couple of days ago and I think @mvogliano has a serious problem. I don't know much about time and date functions but it looks like, perhaps, only chron easily handles times but I cannot see how there is anyway to handle that dog's breakfast of formats.

I think the only way might be to separate the times into character values of equal lengths and use paste0() to pad the data to the left to get a consistent X:XX.X format though a XX:XX:XX is likely better.

[andresrcs]

Building on @FJCC 's comment, this turns your sample data into something usable (HH:MM:SS format).

library(tidyverse)
library(hms)

df <- data.frame(Name = c("Swimmer 1","Swimmer 2", "Swimmer 3"),
                 Event.1 = c(22.50,25.90,27.56),
                 Event.2 = c(54.23,"1:01.13",59.54),
                 Event.3 = c("1:41.12","1:58.13","2:03.15")
)

df %>%
    mutate(across(.cols = -Name, 
                  .fns = ~ if_else(!str_detect(., ":"), paste0("00:00:", .), paste0("00:", .))),
           across(.cols = -Name,
                  .fns = parse_hms))
#>        Name     Event.1     Event.2     Event.3
#> 1 Swimmer 1 00:00:22.50 00:00:54.23 00:01:41.12
#> 2 Swimmer 2 00:00:25.90 00:01:01.13 00:01:58.13
#> 3 Swimmer 3 00:00:27.56 00:00:59.54 00:02:03.15

^{Created on 2021-05-01 by the reprex package (v2.0.0)}