I am wondering if anyone has insight on how to approach this problem.
I have a dataframe with the columns: Startime, Endtime and ID (dataframe below) I have already grouped the IDs. I am trying to figure out the time difference in seconds of a specific ‘grouped by’ ID (specifically A)
Starttime Endtime ID 12/18/2019 4:06:59 PM 12/18/2019 4:07:05 PM A 12/18/2019 4:07:26 PM 12/18/2019 4:07:28 PM A 12/17/2019 6:48:06 PM 12/17/2019 6:48:07 PM A 12/17/2019 6:25:16 PM 12/17/2019 6:25:22 PM A
I went about this by using the following code within R and utilizing the dplyr package:
data <-rawdata %>% group_by(ID) %>% summarize(diff = difftime(last(as.POSIXct(Endtime, format =
"%m/%d/%Y %I:%M:%S %p")),first(as.POSIXct(Starttime, format = "%m/%d/%Y %I:%M:%S %p" )), units = "secs"))
However, the code above only calculates the difference between start and end time but not the differences in between. For instance, the code above calculates:
12/18/2019 4:06:59 PM and 12/17/2019 6:25:22 PM
I see this as inaccurate, because it doesn’t account for when 12/17 changes to 12/18, its simply taking the first and last value within the grouped ID.
Is there a way to subgroup data within already grouped data? (further group by time: month day and year and then take its difference in seconds?
Is there a way to do this? Further subgroup the data so that when the day changes, it will know to calculate this difference as well, versus only calculating the first and last values.
Starttime Endtime ID 12/18/2019 4:06:59PM 12/18/2019 4:07:05 PM A 12/18/2019 4:07:26PM 12/18/2019 4:07:28 PM A 12/17/2019 6:48:06PM 12/17/2019 6:48:07PM A 12/17/2019 6:25:16PM 12/17/2019 6:25:22PM A
Any suggestions will help! I feel like I am stuck on this. I will continue to research. Thank you!