组中每一行的Dplyr时间差

Question

我试图计算我的电子邮件持续时间。我有按ID分组的电子邮件。在这个示例中，我已经将我的电子邮件按组A分组了。我希望计算A组的电子邮件读取时间。我正在使用的代码计算它的最后一次和第一次(以秒为单位)。

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"))

然而，我不认为这是一个准确的显示我的电子邮件阅读。总的来说，我希望通过每一行的时差来进行更准确的阅读。所需的输出将是(以下)，因为它显示了每一行的时间差，允许我进一步加总整个diff列，以确定我的电子邮件持续时间(秒)。

        Starttime               Endtime                     ID         diff

        12/18/2019 4:06:59PM    12/18/2019 4:07:05 PM        A        6 secs
        12/18/2019 4:07:26PM    12/18/2019 4:07:28 PM        A        1 secs
        12/17/2019 6:48:06PM    12/17/2019 6:48:07PM         A        1 sec
        12/17/2019 6:25:16PM    12/17/2019 6:25:22PM         A        6 secs

任何帮助都是非常感谢的。我会继续研究这个！

Answer 1

如果您希望电子邮件的开始时间和结束时间之间的差异，您可以这样做。

library(dplyr)

rawdata %>%
  mutate_at(vars(ends_with('time')), lubridate::mdy_hms) %>%
  mutate(diff = difftime(Endtime, Starttime, units = "secs"))

#            Starttime             Endtime ID   diff
#1 2019-12-18 16:06:59 2019-12-18 16:07:05  A 6 secs
#2 2019-12-18 16:07:26 2019-12-18 16:07:28  A 2 secs
#3 2019-12-17 18:48:06 2019-12-17 18:48:07  A 1 secs
#4 2019-12-17 18:25:16 2019-12-17 18:25:22  A 6 secs

或在R基：

transform(transform(rawdata, 
     Starttime = as.POSIXct(Starttime, format = "%m/%d/%Y %I:%M:%S %p"), 
     Endtime = as.POSIXct(Endtime, format = "%m/%d/%Y %I:%M:%S %p")), 
               diff = difftime(Endtime, Starttime, units = "secs"))

数据

rawdata <- structure(list(Starttime = structure(c(3L, 4L, 2L, 1L), 
.Label = c("12/17/2019 6:25:16PM", "12/17/2019 6:48:06PM", "12/18/2019 4:06:59PM", 
"12/18/2019 4:07:26PM"), class = "factor"), Endtime = structure(c(3L, 4L, 2L, 1L), 
.Label = c("12/17/2019 6:25:22PM", "12/17/2019 6:48:07PM", "12/18/2019 4:07:05 PM", 
"12/18/2019 4:07:28 PM"), class = "factor"), ID = structure(c(1L, 1L, 1L, 1L), 
.Label = "A", class = "factor")), row.names = c(NA, -4L), class = "data.frame")