CODEWITHSUNDEEP
rdataframedplyrdata.tabletidyr
Sharath
Sharath

Reputation: 2267

Calculate the difference in start and end times of different steps in R

I have a df like this 

ID <- c("A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B")
MEASUREMENT <- c("Length","Length","Length","Length","Width","Width","Width","Width","Length","Length","Length","Length","Width","Width","Width","Width") 
Step <- c("1","2","3","4","1","2","3","4","1","2","3","4","1","2","3","4")    
StartDatetime <- c("2015-09-29 00:00:13","2015-09-29 00:02:13","2015-09-29 00:04:13","2015-09-29 00:06:16","2015-09-29 00:07:12","2015-09-29 00:10:13","2015-09-29 00:10:23","2015-09-29 00:12:15","2015-09-29 00:14:13","2015-09-29 00:16:13","2015-09-29 00:19:14","2015-09-29 00:20:13","2015-09-29 00:24:13","2015-09-29 00:26:19","2015-09-29 00:27:20","2015-09-29 00:29:13")    
EndDatetime <- c("2015-09-29 00:01:13","2015-09-29 00:03:13","2015-09-29 00:05:23","2015-09-29 00:07:11","2015-09-29 00:08:12","2015-09-29 00:10:23","2015-09-29 00:11:13","2015-09-29 00:13:13","2015-09-29 00:15:13","2015-09-29 00:17:19","2015-09-29 00:20:13","2015-09-29 00:22:13","2015-09-29 00:26:13","2015-09-29 00:27:13","2015-09-29 00:28:13","2015-09-29 00:32:13")

df1 <- data.frame(ID,MEASUREMENT,Step,StartDatetime,EndDatetime)
df1$StartDatetime <- as.POSIXct(df1$StartDatetime)
df1$EndDatetime <- as.POSIXct(df1$EndDatetime)

I am trying to calculate the elapsed time (secs) between steps grouped by ID & Measurement 

If Step = 1 then the elapsed time = 0
If Step = 2 then elapsed time = StartDatetime (Step 2) - EndDatetime (Step 1)

The above logic applies for Step = 3 & 4 as well.

My desired output is 

    ID MEASUREMENT Step       StartDatetime         EndDatetime ElapsedTime
    A      Length    1 2015-09-29 00:00:13 2015-09-29 00:01:13           0
    A      Length    2 2015-09-29 00:02:13 2015-09-29 00:03:13          60
    A      Length    3 2015-09-29 00:04:13 2015-09-29 00:05:23          60
    A      Length    4 2015-09-29 00:06:16 2015-09-29 00:07:11          53
    A       Width    1 2015-09-29 00:07:12 2015-09-29 00:08:12           0
    A       Width    2 2015-09-29 00:10:13 2015-09-29 00:10:23         121
    A       Width    3 2015-09-29 00:10:23 2015-09-29 00:11:13           0
    A       Width    4 2015-09-29 00:12:15 2015-09-29 00:13:13          62
    B      Length    1 2015-09-29 00:14:13 2015-09-29 00:15:13           0
    B      Length    2 2015-09-29 00:16:13 2015-09-29 00:17:19          60
    B      Length    3 2015-09-29 00:19:14 2015-09-29 00:20:13         115
    B      Length    4 2015-09-29 00:20:13 2015-09-29 00:22:13           0
    B       Width    1 2015-09-29 00:24:13 2015-09-29 00:26:13           0
    B       Width    2 2015-09-29 00:26:19 2015-09-29 00:27:13           6
    B       Width    3 2015-09-29 00:27:20 2015-09-29 00:28:13           7
    B       Width    4 2015-09-29 00:29:13 2015-09-29 00:32:13          60

I am trying it this way but not getting it right

library(plyr)
df <- ddply(df1, .(ID,MEASUREMENT,Step),
            summarize,
            ElapsedTime=as.numeric(difftime(df1$StartDatetime, df1$EndDatetime, 
                                            units = "secs")))

How do I accomplish my desired output? Could someone point me in the right direction?

Upvotes: 1

Views: 81

Answers (1)

jasbner
jasbner

Reputation: 2283

You can do this with some tidyverse functions. First you group_by the columns that you're interested in. Then with mutate you can add another column with elapsed time calculated with a lag (default lag of 1) which will essentially give you the value of the previous EndDatetime. Last, since the lag will default to NA, we set all NA to zero like in your example with replace_na.

library(dplyr)
library(tidyr)
df1 %>% 
  group_by(ID,MEASUREMENT) %>% 
  mutate(ElapsedTime = StartDatetime - lag(EndDatetime)) %>% 
  replace_na(list(ElapsedTime =0))
# A tibble: 16 x 6
# Groups: ID, MEASUREMENT [4]
#   ID    MEASUREMENT Step  StartDatetime       EndDatetime         ElapsedTime
#   <fct> <fct>       <fct> <dttm>              <dttm>              <time>     
# 1 A     Length      1     2015-09-29 00:00:13 2015-09-29 00:01:13 0          
# 2 A     Length      2     2015-09-29 00:02:13 2015-09-29 00:03:13 60         
# 3 A     Length      3     2015-09-29 00:04:13 2015-09-29 00:05:23 60         
# 4 A     Length      4     2015-09-29 00:06:16 2015-09-29 00:07:11 53         
# 5 A     Width       1     2015-09-29 00:07:12 2015-09-29 00:08:12 0          
# 6 A     Width       2     2015-09-29 00:10:13 2015-09-29 00:10:23 121        
# 7 A     Width       3     2015-09-29 00:10:23 2015-09-29 00:11:13 0          
# 8 A     Width       4     2015-09-29 00:12:15 2015-09-29 00:13:13 62         
# 9 B     Length      1     2015-09-29 00:14:13 2015-09-29 00:15:13 0          
#10 B     Length      2     2015-09-29 00:16:13 2015-09-29 00:17:19 60         
#11 B     Length      3     2015-09-29 00:19:14 2015-09-29 00:20:13 115        
#12 B     Length      4     2015-09-29 00:20:13 2015-09-29 00:22:13 0          
#13 B     Width       1     2015-09-29 00:24:13 2015-09-29 00:26:13 0          
#14 B     Width       2     2015-09-29 00:26:19 2015-09-29 00:27:13 6          
#15 B     Width       3     2015-09-29 00:27:20 2015-09-29 00:28:13 7          
#16 B     Width       4     2015-09-29 00:29:13 2015-09-29 00:32:13 60

Upvotes: 1

Related Questions