Calculate hours and minute

[juandmaz]

Hi, I have these columns that in my excel are hours and minutes (for example, '3:00:00') but R imported them incorrectly as dates (following the example above, '1899-12-31 03:00:00:00').

C7.1	C7.2	C7.3
3:00:00	2:00:00	1:00:00
2:00:00	1:00:00	0:00:00
0:00:00	0:00:00	0:00:00
1:00:00	0:45:00	1:00:00
0:00:00	0:00:00	0:00:00
1:00:00	2:00:00	0:30:00

R imported like:

# A tibble: 207 × 3
   C7.1                C7.2                C7.3               
   <dttm>              <dttm>              <dttm>             
 1 1899-12-31 03:00:00 1899-12-31 02:00:00 1899-12-31 01:00:00
 2 1899-12-31 02:00:00 1899-12-31 01:00:00 1899-12-31 00:00:00
 3 1899-12-31 00:00:00 1899-12-31 00:00:00 1899-12-31 00:00:00
 4 1899-12-31 01:00:00 1899-12-31 00:45:00 1899-12-31 01:00:00
 5 1899-12-31 00:00:00 1899-12-31 00:00:00 1899-12-31 00:00:00
 6 1899-12-31 01:00:00 1899-12-31 02:00:00 1899-12-31 00:30:00

I need to extract the hours and minutes correctly so that I can do calculations with them.

I saw this way

format(as.POSIXct(.), format = "%H:%M")

but it doesn't work for me because I need to do calculations later and be able to calculate statistical summaries.

I saw that lubridate also has duration() that allows me to calculate how many minutes in total are but first I need to get the interval and I don't know how to do it.

Any advice?

[jrkrideau]

Is this of any use?

Calculate Sum & Mean of Hours, Minutes & Seconds in R (2 Examples)

[technocrat]

Ways to calculate with time-related objects depend on converting a string representation, such as "3:00:00" to either an integer directly, by parsing the string and multiplying its components by 24, 60 and 60 and converting to numeric, then adding them together to get seconds or manipulating them to get fractional minutes or hours. The other way is to convert to a datetime object. For that you need to tack on a date component.

as_is <- "3:00:00"                    # imported
add_date <- "2023-03-23"              # add an arbitrary date
date_string <- paste(add_date,as_is)  # make a date time string
o <- lubridate::ymd_hms(date_string)  # datetime object
d <- data.frame(event1 = o)            # add to data frame
# repeat for second object
as_is <- "4:34:23"                    # imported
add_date <- "2023-03-23"              # add an arbitrary date
date_string <- paste(add_date,as_is)  # make a date time string
o <- lubridate::ymd_hms(date_string)  # datetime object
d$event2 <- o
e <- d[2] - d[1]
str(e)
#> 'data.frame':    1 obs. of  1 variable:
#>  $ event2: 'difftime' num 1.57305555555556
#>   ..- attr(*, "units")= chr "hours"
# convert to decimal if desired
as.numeric(e[[1]])
#> [1] 1.573056

^{Created on 2023-03-23 with reprex v2.0.2}

[juandmaz]

No, because it does not explain how I can parse my variables so it fails to do the calculations

[juandmaz]

It works, I tried this:

base %>%
  select(where(is.POSIXct)) %>%
  mutate(across(everything(),
                ~as.numeric(.-as_datetime('1899-12-31'))/3600))

and the output was:

# A tibble: 207 × 5
    C7.1  C7.2  C7.3  C7.4  C7.5
   <dbl> <dbl> <dbl> <dbl> <dbl>
 1   3    2      1    0        0
 2   2    1      0    0        0
 3   0    0      0    0        0
 4   1    0.75   1    0.25     0
 5   0    0      0    2        0
 6   1    2      0.5  2        0

I have one more question, there is a way to change the decimal system from output to time? I mean, instead of 0.75 to show 0.45 and instead of 1.5 to show 1.3. So with the results of statistical calculations I don't have to convert it from decimal system to minutes.

[technocrat]

That's just a matter of changing the units from hours to minutes. If that's what the tibble now represents

d <- data.frame(
  A = c(2, 3, 4, 5, 6), 
  B = c(2, 0, 1, 0, 1), 
  C = c(1,0, 0.75, 0, 2), 
  D = c(0, 0, 1, 0, 0.5), 
  E = c(0, 0, 0.25, 2,2),
  F = c(0, 0, 0, 0, 0)
)
d * 60
#>     A   B   C  D   E F
#> 1 120 120  60  0   0 0
#> 2 180   0   0  0   0 0
#> 3 240  60  45 60  15 0
#> 4 300   0   0  0 120 0
#> 5 360  60 120 30 120 0

[juandmaz]

This is not how I need it. I need the unit to be hours, not minutes. that's why in the examples that I gave you 1.5 I need it to be 1.3 and not 90. 45 minutes is 0.45 and not 45

[technocrat]

@technocrat

d * 60

So, how would you adjust this to get hours and fractions of hours?

[nirgrahamuk]

I dont see how going from a straightforward decimal representation of hours like 1.5 hours to a mixed unit where before the decimal is hours and after the decimal is minutes a la 1.3 can possibly help with calculations, surely its definitely less helpful for doing calculations ??

[jrkrideau]

Try

library(data.table); library(tidyverse)
dat1 <- data.table(
             aa = c("3:00:00","2:00:00","0:00:00",
                      "1:00:00","0:00:00","1:00:00"),
             bb = c("2:00:00","1:00:00","0:00:00",
                      "0:45:00","0:00:00","2:00:00"),
             cc = c("1:00:00","0:00:00","0:00:00",
                      "1:00:00","0:00:00","0:30:00")
     )

dat2 <- dat1[, lapply(.SD, hms)]

dat3 <- dat2[, lapply(.SD, period_to_seconds)][, add_time := aa + bb]

dat4 <- dat3[, lapply(.SD, seconds_to_period)]
dat4

We don't need {data.table} but it was easier for me to do it this way than completely in {tidyverse}.

[juandmaz]

I don't know, that's why I'm asking. I don't see an efficient way to move hours with a decimal unit to a sexagesimal unit.

[juandmaz]

I'm trying to understand, what you did was that the add_time column is the sum of aa and bb? and the cc column? and then how do you do statistical calculations with that column?

[juandmaz]

It's not really a mixed representation, it's just that the times are represented as they are in real life. An efficient way to average 2:30 and 3:00 and get 2:45.

[nirgrahamuk]

You are claiming that its easier to average 2.3 and 3 and get 2.45 than it would be to average 2.5 and 3 and get 2.75?

[technocrat]

Minutes: use 105 minutes for calculating, and use 1:45 for display

[juandmaz]

@nirgrahamuk

You are claiming that its easier to average 2.3 and 3 and get 2.45 than it would be to average 2.5 and 3 and get 2.75?

It will always be easier to do some calculations and the ouput will be the desired one and not the output will be an intermediate step to the desired result. Or do you measure time with the decimal system and not with the sexagesimal system on a day-to-day basis?

[juandmaz]

Yes, I was looking for a way where the format for calculations and display would be the same, but it seems that is not possible.

[technocrat]

Well, you could store it as a character and then coerce it back into datetime every time you need a calculation. Or keep a separate var

[nirgrahamuk]

library(tidyverse)
library(hms)
dat1 <- tibble(
  aa = c("3:00:00","2:00:00","0:00:00",
         "1:00:00","0:00:00","1:00:00"),
  bb = c("2:00:00","1:00:00","0:00:00",
         "0:45:00","0:00:00","2:00:00"),
  cc = c("1:00:00","0:00:00","0:00:00",
         "1:00:00","0:00:00","0:30:00")
)

(df_of_hm_times <- mutate(dat1,across(everything(),
                   hms::parse_hms)))

(df_of_time_additions <- mutate(df_of_hm_times,
                                aa_plus_bb = aa + bb,
                                avg_bb_with_cc = (bb+cc)/2,
                                across(everything(),
                                       as_hms)))

# A tibble: 6 × 5
  aa     bb     cc     aa_plus_bb avg_bb_with_cc
  <time> <time> <time> <time>     <time>        
1 03:00  02:00  01:00  05:00      01:30:00      
2 02:00  01:00  00:00  03:00      00:30:00      
3 00:00  00:00  00:00  00:00      00:00:00      
4 01:00  00:45  01:00  01:45      00:52:30      
5 00:00  00:00  00:00  00:00      00:00:00      
6 01:00  02:00  00:30  03:00      01:15:00