r summarized hours format

Question

I am working with summarized data that counts the time in minutes and then hours (no days). How do I make it numeric in a way that it can be plotted on a chart.

Below is a sample of how the data looks like in the format of hh:mm

set.seed(121)

df <- data.frame(hspt =  letters[1:3],
                 Jan = paste0(sample(1:1000, 3, replace=TRUE),":", 
                     stringr::str_pad(sample(0:60, 3, replace=TRUE), 2, pad = "0")),
                 Feb = paste0(sample(1:1000, 3, replace=TRUE),":", 
                     stringr::str_pad(sample(0:60, 3, replace=TRUE), 2, pad = "0")), 
                 Mar = paste0(sample(1:1000, 3, replace=TRUE),":", 
                     stringr::str_pad(sample(0:60, 3, replace=TRUE), 2, pad = "0")), 
                 stringsAsFactors = F)

df
  hspt    Jan    Feb    Mar
 1    a 763:28 255:37 289:49
 2    b 551:37 947:07 136:46
 3    c 422:14 783:29 618:56

Possible duplicate of [How to convert time (mm:ss) to decimal form in R](https://stackoverflow.com/questions/5186972/how-to-convert-time-mmss-to-decimal-form-in-r) — lebelinoz, Jun 18 '17 at 00:28
The other question is minutes and seconds, instead of hours and minutes. But the logic in the answers are all valid here. — lebelinoz, Jun 18 '17 at 00:29

score 1 · Accepted Answer · answered Jun 18 '17 at 01:37

If you reshape to long form, both converting and plotting are easier:

library(tidyverse)
set.seed(121)

df <- data.frame(hspt =  letters[1:3],
                 Jan = paste0(sample(1:1000, 3, replace=TRUE),":", stringr::str_pad(sample(0:60, 3, replace=TRUE), 2, pad = "0")),
                 Feb = paste0(sample(1:1000, 3, replace=TRUE),":", stringr::str_pad(sample(0:60, 3, replace=TRUE), 2, pad = "0")), 
                 Mar = paste0(sample(1:1000, 3, replace=TRUE),":", stringr::str_pad(sample(0:60, 3, replace=TRUE), 2, pad = "0")), 
                 stringsAsFactors = F)

df2 <- df %>% 
    gather(month, time, -hspt) %>%    # reshape to long
    separate(time, c('hours', 'minutes'), convert = TRUE) %>% 
    mutate(month = factor(month, month.abb[1:3], ordered = TRUE),    # for x-axis order
           time = 60 * hours + minutes) 

df2
#>   hspt month hours minutes  time
#> 1    a   Jan   400      46 24046
#> 2    b   Jan   952      33 57153
#> 3    c   Jan   544      25 32665
#> 4    a   Feb   468      15 28095
#> 5    b   Feb   614      57 36897
#> 6    c   Feb   238      47 14327
#> 7    a   Mar   617      17 37037
#> 8    b   Mar   124       8  7448
#> 9    c   Mar   478      37 28717

ggplot(df2, aes(month, time, color = hspt, group = hspt)) + geom_line()

AkselA · Answer 2 · 2017-06-18T00:02:21.270

0

This will give the times in minutes

library(lubridate)

df
#   hspt    Jan    Feb    Mar
# 1    a 400:46 468:15 617:17
# 2    b 952:33 614:57 124:08
# 3    c 544:25 238:47 478:37

df[, -1] <- sapply(df[, -1], function(x) hour(hm(x))*60 + minute(hm(x)))
df
#   hspt   Jan   Feb   Mar
# 1    a 24046 28095 37037
# 2    b 57153 36897  7448
# 3    c 32665 14327 28717

edited Jun 18 '17 at 00:02

answered Jun 17 '17 at 23:51

AkselA

8,153
2
21
34

r summarized hours format

2 Answers2