calculate the time difference. R

Question

I have a a dataframe with 2 column:

   StartTime             EndTime
1. 2019-05-06 08:34:15   2019-05-06 08:48:30
2. 2019-05-06 10:07:18   2019-05-06 10:21:34
3. 2019-05-06 15:13:10   2019-05-06 15:27:26
4. 2019-05-06 15:35:52   2019-05-06 15:50:07
5. 2019-05-06 16:14:18   2019-05-06 16:33:58
6. 2019-05-06 16:58:14   2019-05-06 17:13:36
7. 2019-05-06 20:28:50   2019-05-06 21:05:11
8. 2019-05-06 21:55:53   2019-05-06 22:16:50
9. 2019-05-06 22:42:21   2019-05-06 22:57:46
10.2019-05-06 23:26:59   2019-05-07 00:03:46
11.2019-05-07 00:36:43   2019-05-07 00:53:44

I want to add a new column(IdlingTime) to calculate the idle time. let's say the first row IdlingTime is: 2nd row of StartTime - 1st row of EndTime = 1 HR 18 min 48 sec and convert to minutes.

Thank you advance.

TarJae · Answer 1 · 2021-09-08T13:51:00.683

Update if your data is already POSIXct: then

library(dplyr)
df %>%
  mutate(IdlingTime = round(lead(StartTime)-EndTime))

Output:

  StartTime           EndTime             IdlingTime
   <dttm>              <dttm>              <drtn>    
 1 2019-05-06 08:34:15 2019-05-06 08:48:30  79 mins  
 2 2019-05-06 10:07:18 2019-05-06 10:21:34 292 mins  
 3 2019-05-06 15:13:10 2019-05-06 15:27:26   8 mins  
 4 2019-05-06 15:35:52 2019-05-06 15:50:07  24 mins  
 5 2019-05-06 16:14:18 2019-05-06 16:33:58  24 mins  
 6 2019-05-06 16:58:14 2019-05-06 17:13:36 195 mins  
 7 2019-05-06 20:28:50 2019-05-06 21:05:11  51 mins  
 8 2019-05-06 21:55:53 2019-05-06 22:16:50  26 mins  
 9 2019-05-06 22:42:21 2019-05-06 22:57:46  29 mins  
10 2019-05-06 23:26:59 2019-05-07 00:03:46  33 mins  
11 2019-05-07 00:36:43 2019-05-07 00:53:44  NA mins

First answer: After getting the correct class datetime we could use lead function from dplyr package:

library(dplyr) library(lubridate)

df %>% 
 #mutate(across(contains("Time"), ~ymd_hms(.))) 
         IdlingTime = lead(StartTime)-EndTime)

output:

   StartTime           EndTime             IdlingTime     
   <dttm>              <dttm>              <drtn>         
 1 2019-05-06 08:34:15 2019-05-06 08:48:30  78.800000 mins
 2 2019-05-06 10:07:18 2019-05-06 10:21:34 291.600000 mins
 3 2019-05-06 15:13:10 2019-05-06 15:27:26   8.433333 mins
 4 2019-05-06 15:35:52 2019-05-06 15:50:07  24.183333 mins
 5 2019-05-06 16:14:18 2019-05-06 16:33:58  24.266667 mins
 6 2019-05-06 16:58:14 2019-05-06 17:13:36 195.233333 mins
 7 2019-05-06 20:28:50 2019-05-06 21:05:11  50.700000 mins
 8 2019-05-06 21:55:53 2019-05-06 22:16:50  25.516667 mins
 9 2019-05-06 22:42:21 2019-05-06 22:57:46  29.216667 mins
10 2019-05-06 23:26:59 2019-05-07 00:03:46  32.950000 mins
11 2019-05-07 00:36:43 2019-05-07 00:53:44         NA mins

Warning message: Problem with `mutate()` input `..1`. ℹ `..1 = across(contains("Time"), ~ymd_hms(.))`. ℹ All formats failed to parse. No formats found. ...the idlingTime column added with your code.but get this warning message.is that still work? — ken, Sep 08 '21 at 13:23

score 0 · Answer 2 · answered Sep 08 '21 at 13:05

0

This is how I would do it.

df %>%
  mutate_all(dmy_hm)  %>%
  mutate(
    IdlingTime = as.double(lead(StartTime)-EndTime, units = "mins")
  )

answered Sep 08 '21 at 13:05

Quixotic22

2,894
1
6
14

Dirk Eddelbuettel · Answer 3 · 2021-09-08T18:42:06.347

You just subtract the columns.

Here is the (reproducible, with your data !!) code given the updated question. I use data.table which parse the data columns to POSIXct when reading, and makes lead/lag easy too

Code

library(data.table)
data <- fread(text="StartTime,EndTime
2019-05-06 08:34:15,2019-05-06 08:48:30
2019-05-06 10:07:18,2019-05-06 10:21:34
2019-05-06 15:13:10,2019-05-06 15:27:26
2019-05-06 15:35:52,2019-05-06 15:50:07
2019-05-06 16:14:18,2019-05-06 16:33:58
2019-05-06 16:58:14,2019-05-06 17:13:36
2019-05-06 20:28:50,2019-05-06 21:05:11
2019-05-06 21:55:53,2019-05-06 22:16:50
2019-05-06 22:42:21,2019-05-06 22:57:46
2019-05-06 23:26:59,2019-05-07 00:03:46
2019-05-07 00:36:43,2019-05-07 00:53:44")

data[, minElapsed := as.numeric(EndTime - StartTime)]
data[, idleMin := as.numeric(StartTime - shift(EndTime))]
data

Output

> library(data.table)
> data <- fread(text="StartTime,EndTime
+ 2019-05-06 08:34:15,2019-05-06 08:48:30
+ 2019-05-06 10:07:18,2019-05-06 10:21:34
+ 2019-05-06 15:13:10,2019-05-06 15:27:26
+ 2019-05-06 15:35:52,2019-05-06 15:50:07
+ 2019-05-06 16:14:18,2019-05-06 16:33:58
+ 2019-05-06 16:58:14,2019-05-06 17:13:36
+ 2019-05-06 20:28:50,2019-05-06 21:05:11
+ 2019-05-06 21:55:53,2019-05-06 22:16:50
+ 2019-05-06 22:42:21,2019-05-06 22:57:46
+ 2019-05-06 23:26:59,2019-05-07 00:03:46
+ 2019-05-07 00:36:43,2019-05-07 00:53:44")
> 
> data[, minElapsed := as.numeric(EndTime - StartTime)]
> data[, idleMin := as.numeric(StartTime - shift(EndTime))]
> data
              StartTime             EndTime minElapsed   idleMin
 1: 2019-05-06 08:34:15 2019-05-06 08:48:30    14.2500        NA
 2: 2019-05-06 10:07:18 2019-05-06 10:21:34    14.2667  78.80000
 3: 2019-05-06 15:13:10 2019-05-06 15:27:26    14.2667 291.60000
 4: 2019-05-06 15:35:52 2019-05-06 15:50:07    14.2500   8.43333
 5: 2019-05-06 16:14:18 2019-05-06 16:33:58    19.6667  24.18333
 6: 2019-05-06 16:58:14 2019-05-06 17:13:36    15.3667  24.26667
 7: 2019-05-06 20:28:50 2019-05-06 21:05:11    36.3500 195.23333
 8: 2019-05-06 21:55:53 2019-05-06 22:16:50    20.9500  50.70000
 9: 2019-05-06 22:42:21 2019-05-06 22:57:46    15.4167  25.51667
10: 2019-05-06 23:26:59 2019-05-07 00:03:46    36.7833  29.21667
11: 2019-05-07 00:36:43 2019-05-07 00:53:44    17.0167  32.95000
>

Original Answer below

Code

data <- read.csv(text="StartTime,EndTime
2019-05-06 08:34:15,2019-05-06 08:48:30
2019-05-06 10:07:18,2019-05-06 10:21:34
2019-05-06 15:13:10,2019-05-06 15:27:26
2019-05-06 15:35:52,2019-05-06 15:50:07
2019-05-06 16:14:18,2019-05-06 16:33:58
2019-05-06 16:58:14,2019-05-06 17:13:36
2019-05-06 20:28:50,2019-05-06 21:05:11
2019-05-06 21:55:53,2019-05-06 22:16:50
2019-05-06 22:42:21,2019-05-06 22:57:46
2019-05-06 23:26:59,2019-05-07 00:03:46
2019-05-07 00:36:43,2019-05-07 00:53:44")

data$StartTime     <- as.POSIXct(data$StartTime)
data$EndTime       <- as.POSIXct(data$EndTime)

data$IdlingTime    <- data$EndTime - data$StartTime
data$IdlingTimeMin <- as.numeric(data$EndTime - data$StartTime)

Output

> data
             StartTime             EndTime   IdlingTime IdlingTimeMin
1  2019-05-06 08:34:15 2019-05-06 08:48:30 14.2500 mins       14.2500
2  2019-05-06 10:07:18 2019-05-06 10:21:34 14.2667 mins       14.2667
3  2019-05-06 15:13:10 2019-05-06 15:27:26 14.2667 mins       14.2667
4  2019-05-06 15:35:52 2019-05-06 15:50:07 14.2500 mins       14.2500
5  2019-05-06 16:14:18 2019-05-06 16:33:58 19.6667 mins       19.6667
6  2019-05-06 16:58:14 2019-05-06 17:13:36 15.3667 mins       15.3667
7  2019-05-06 20:28:50 2019-05-06 21:05:11 36.3500 mins       36.3500
8  2019-05-06 21:55:53 2019-05-06 22:16:50 20.9500 mins       20.9500
9  2019-05-06 22:42:21 2019-05-06 22:57:46 15.4167 mins       15.4167
10 2019-05-06 23:26:59 2019-05-07 00:03:46 36.7833 mins       36.7833
11 2019-05-07 00:36:43 2019-05-07 00:53:44 17.0167 mins       17.0167
>

this is not i want.let say the row 10 idling time is 2019-05-07 00:36:43 deduct 2019-05-07 00:03:46 = 32.95 mins — ken, Sep 08 '21 at 13:20
Well experience here at StackOverflow taught us that it helps to be precise and (ideally, reproducible) in your question. We all live and learn, maybe next time you will ask it in such a way it is clearer what you actually want. A mosk answer helps too. I will probably just delete this answer as I do not have time now to refine. But both the `xts` and `zoo` package help with lagging, and `data.table` is very good too. — Dirk Eddelbuettel, Sep 08 '21 at 13:56
Update answer below; easy enough in `data.table` though there are _many_ other ways. — Dirk Eddelbuettel, Sep 08 '21 at 18:35

calculate the time difference. R

3 Answers3

Code

Output

Original Answer below

Code

Output