How to convert time (not date-time) variable into numeric?

Question

I have a data of swimming times that I would like to be able to plot over time. I was wondering if there was a quick way to change these variables from character to numeric?

I started by trying to convert the times to a POSIX date-time format, but that proved to not be helpful, especially because I would like to do some ARIMA predictions on the data.

Here is my data

times <- c("47.45","47.69",  
           "47.69","47.82",  
           "47.84","47.92",  
           "47.96","48.13",  
           "48.16","48.16",  
           "48.16","48.31",  
           "49.01","49.27",  
           "49.33","49.40",  
           "49.48","49.51",  
           "52.85","52.89",  
           "53.14","54.31",  
           "54.63","56.91",  
           "1:18.39","1:20.26",
           "1:38.30")

dates <- c("2017-02-24 MST",
           "2017-02-24 MST",
           "2016-02-26 MST",
           "2018-02-23 MST",
           "2015-12-04 MST",
           "2015-03-06 MST",
           "2015-03-06 MST",
           "2016-12-02 MST",
           "2016-02-26 MST",
           "2017-11-17 MST",
           "2016-12-02 MST",
           "2017-11-17 MST",
           "2014-11-22 MST",
           "2017-01-13 MST",
           "2017-01-21 MST",
           "2015-10-17 MDT",
           "2017-01-27 MST",
           "2016-01-29 MST",
           "2017-10-20 MDT",
           "2016-11-05 MDT",
           "2015-11-07 MST",
           "2015-10-30 MDT",
           "2014-11-22 MST",
           "2016-11-11 MST",
           "2014-02-28 MST",
           "2014-02-28 MST",
           "2014-02-28 MST",)

df <- cbind(as.data.frame(dates),as.data.frame(times))

I hope to get a column for time, probably in seconds, so the first 24 obs would stay the same, but the last 3 obs would change to 78.39,80.26, and 98.30

Answer 1

as.difftime, and a quick regex to add the minutes when they are not present, should handle it:

as.difftime(sub("(^\\d{1,2}\\.)", "0:\\1", times), format="%M:%OS")
#Time differences in secs
# [1] 47.45 47.69 47.69 47.82 47.84 47.92 47.96 48.13 48.16 48.16 48.16 48.31
#[13] 49.01 49.27 49.33 49.40 49.48 49.51 52.85 52.89 53.14 54.31 54.63 56.91
#[25] 78.39 80.26 98.30

Answer 2

One way is to pre-pend those times that don't have minutes with "00:".

Then you can use lubridate::ms to do the time conversion.

library(dplyr)
library(lubridate)

data.frame(times = times,
           stringsAsFactors = FALSE) %>% 
  mutate(times2 = ifelse(grepl(":", times), times, paste0("00:", times)), 
         seconds = as.numeric(ms(times2)))

Result:

     times   times2 seconds
1    47.45 00:47.45   47.45
2    47.69 00:47.69   47.69
3    47.69 00:47.69   47.69
4    47.82 00:47.82   47.82
5    47.84 00:47.84   47.84
6    47.92 00:47.92   47.92
7    47.96 00:47.96   47.96
8    48.13 00:48.13   48.13
9    48.16 00:48.16   48.16
10   48.16 00:48.16   48.16
11   48.16 00:48.16   48.16
12   48.31 00:48.31   48.31
13   49.01 00:49.01   49.01
14   49.27 00:49.27   49.27
15   49.33 00:49.33   49.33
16   49.40 00:49.40   49.40
17   49.48 00:49.48   49.48
18   49.51 00:49.51   49.51
19   52.85 00:52.85   52.85
20   52.89 00:52.89   52.89
21   53.14 00:53.14   53.14
22   54.31 00:54.31   54.31
23   54.63 00:54.63   54.63
24   56.91 00:56.91   56.91
25 1:18.39  1:18.39   78.39
26 1:20.26  1:20.26   80.26
27 1:38.30  1:38.30   98.30

Answer 3

You can use separate in the Tidyverse tidyr package to split the strings into minutes and seconds:

library(tidyr)
library(dplyr)

separate(tibble(times = times), times, sep = ":",
         into = c("min", "sec"), fill = "left", convert = T) %>%
  mutate(min = ifelse(is.na(min), 0, min),
         seconds = 60 * min + sec)

# A tibble: 27 x 3
     min   sec seconds
   <dbl> <dbl>   <dbl>
 1     0  47.4    47.4
 2     0  47.7    47.7
 3     0  47.7    47.7
 4     0  47.8    47.8
 5     0  47.8    47.8
 6     0  47.9    47.9
 7     0  48.0    48.0
 8     0  48.1    48.1
 9     0  48.2    48.2
10     0  48.2    48.2
# ... with 17 more rows

The new column seconds is the number of seconds, multiplying the number of minutes by 60.