0

For each ID that is the same, I would like to add an end time and then calculate the time differences between each entry for that user ID.

This is the code I have so far:

user <- user %>% group_by(user$userdata.user_id) %>% arrange(user$hours) %>% mutate(time.interval= user$hours - lag(user$hours, default = first(user$hours))) %>% mutate(time.interval = round(time.interval/86400, digits = 2))

I was trying to use the diff time() function, however since I am trying to calculate the time difference with a preset end date ('02-20-2020' = 7), I am unable to attain the following results:

id hours time.decimal time.interval

123 03:32:12 1.200 3.3 (4.5 - 1.2)

123 12:37:56 4.500 2.5 (7 - 4.5)

140 09:46:33 6.300 0.7 (7 - 6.3)

**Note: the above is an example of what I want to achieve. 7 in the time interval column is the time decimal version of the given end date.

Any help would be greatly appreciated.

Phil
  • 7,287
  • 3
  • 36
  • 66
frahim
  • 3
  • 2

1 Answers1

0

You may use lead to get next value of time.decimal with default value as 7.

library(dplyr)

user %>%
  group_by(id) %>%
  mutate(time.interval = lead(time.decimal, default = 7) - time.decimal) %>%
  ungroup() -> user

df

#     id hours    time.decimal time.interval
#  <int> <chr>           <dbl>         <dbl>
#1   123 03:32:12          1.2           3.3
#2   123 12:37:56          4.5           2.5
#3   140 09:46:33          6.3           0.7

Or in data.table :

library(data.table)

setDT(user)[, time.interval := shift(time.decimal, type = 'lead', fill = 7) - time.decimal, id]

data

user <- structure(list(id = c(123L, 123L, 140L), hours = c("03:32:12", 
"12:37:56", "09:46:33"), time.decimal = c(1.2, 4.5, 6.3)), 
class = "data.frame", row.names = c(NA, -3L))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213