r - 如何根据连续的行值聚合数据？

Question

我正在对 trail 相机拍摄的动物照片进行数据分析。我的数据包括拍摄照片的相机、拍摄照片的日期和时间以及照片中的动物。我希望根据动物在镜头前花费的时间来汇总我的数据。就我们的目的而言，遭遇是指我们在拍摄同一物种的另一只动物后超过 10 分钟又拍摄了一只动物。在某些情况下，遭遇可能会超过 10 分钟，例如如果我们为同一只动物拍了 3 张相隔 7 分钟的照片，那么一次遭遇 21 分钟。我希望我的输出将我的数据汇总到所有拍摄动物的个人遭遇中，并包括每个遭遇照片系列的开始时间和结束时间。

---

到目前为止我的代码

library(dplyr)

#Data 
df <- structure(list(camera_id = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L, 2L, 3L, 3L, 3L, 3L), date = c("11-May-21", "11-May-21", "11-May-21", 
"15-May-21", "15-May-21", "10-May-21", "10-May-21", "12-May-21", 
"12-May-21", "12-May-21", "12-May-21", "12-May-21", "13-May-21", 
"13-May-21"), time = c("5:23:46", "5:23:50", "5:32:34", "9:35:20", 
"9:35:35", "23:11:16", "23:11:17", "11:06:08", "11:15:09", "11:24:10", 
"2:04:01", "2:04:03", "1:15:00", "1:15:50"), organism = c("mouse", 
"mouse", "bird", "squirrel", "squirrel", "mouse", "mouse", "woodchuck", 
"woodchuck", "woodchuck", "mouse", "mouse", "mouse", "mouse")), class = "data.frame", row.names = c(NA, 
-14L))

#Combining date and time
df$datetime <- as.POSIXct(paste(df$date, df$time), format ="%d-%B-%y %H:%M:%S")

#Time differences in minutes, based on organism
df <- df %>% group_by(organism) %>%
  mutate(timediff = (datetime - lag(datetime))/60
  )

#Round minutes to 2 decimal points
df$timediff <- round(df$timediff, digits=2)

#Make negative and NA values = 0. Negative values appear when going from one camera to the next. R thinks it is going back in time, rather than 
#swapping cameras
df$timediff[df$timediff<0] <- 0
df$timediff[is.na(df$timediff)] <- 0

---

此时，我想使用 timediff 作为我的聚合条件，并聚合 timediff < 10 的任何后续数据行，只要该行具有相同的 camera_id 和 organism。我一直在尝试不同的 dplyr 方法，但没能破解这个问题。输出应如下所示。

structure(list(camera_id = c(1L, 1L, 1L, 2L, 2L, 3L, 3L), start_datetime = c("5/11/2021 5:23", 
"5/11/2021 5:32", "5/15/2021 9:35", "5/10/2021 23:11", "5/10/2021 11:06", 
"5/12/2021 2:04", "5/13/2021 1:15"), end_datetime = c("5/11/2021 5:23", 
"5/11/2021 5:32", "5/15/2021 9:35", "5/10/2021 23:11", "5/10/2021 11:24", 
"5/12/2021 2:04", "5/13/2021 1:15"), organism = c("mouse", "bird", 
"squirrel", "mouse", "woodchuck", "mouse", "mouse"), encounter_time = c("0:00:04", 
"0:00:00", "0:00:15", "0:00:01", "0:18:00", "0:00:02", "0:00:50"
)), class = "data.frame", row.names = c(NA, -7L))

Answer 1

我认为这可以让您获得想要的结果:

几个关键变化:当我们计算 timediff 时，除了 organism 之外，还按 camera_id 分组是有意义的，因为分组始终存在.

然后，我们需要创建一些辅助列来根据 10 秒条件生成我们的分组。

under_10 对于小于 10 的所有 timediff 值都是 0，而且当 timediff 是 NA 时(当一行是组中的第一行时)。当 timelapsed > 10 时，小于 10 是 1。

然后我们创建一个分组变量，当耗时 > 10 时递增。然后我们简单地总结，根据最小/最大日期时间计算开始和结束，并删除分组列。

library(tidyverse)

df$datetime <- as.POSIXct(paste(df$date, df$time), format ="%d-%B-%y %H:%M:%S")

#Time differences in minutes, based on organism
df <- df %>% group_by(organism, camera_id) %>%
  mutate(timediff = (datetime - lag(datetime))/60
  )

#Round minutes to 2 decimal points
df$timediff <- round(df$timediff, digits=2)

df %>% mutate(under_10 = ifelse(timediff < 10 | is.na(timediff), 0, 1)) %>% 
  arrange(camera_id, datetime) %>%
  mutate(grouping = cumsum(under_10)) %>%
  group_by(camera_id, organism, grouping) %>% 
  summarize(start_datetime = min(datetime), end_datetime = max(datetime),
    encounter_time = end_datetime-start_datetime) %>%
  select(-grouping)



camera_id organism  start_datetime      end_datetime        encounter_time
      <int> <chr>     <dttm>              <dttm>              <drtn>        
1         1 bird      2021-05-11 05:32:34 2021-05-11 05:32:34    0 secs     
2         1 mouse     2021-05-11 05:23:46 2021-05-11 05:23:50    4 secs     
3         1 squirrel  2021-05-15 09:35:20 2021-05-15 09:35:35   15 secs     
4         2 mouse     2021-05-10 23:11:16 2021-05-10 23:11:17    1 secs     
5         2 woodchuck 2021-05-12 11:06:08 2021-05-12 11:24:10 1082 secs     
6         3 mouse     2021-05-12 02:04:01 2021-05-12 02:04:03    2 secs     
7         3 mouse     2021-05-13 01:15:00 2021-05-13 01:15:50   50 secs

此外，如果您更愿意为 encounter_time 使用 H:MM:SS 格式，您可以像这样到达此处，在 summarize 调用之后添加以下内容以上代码:

library(lubridate)
  ...
  mutate(encounter_time = seconds_to_period(as.character(encounter_time))) %>%
  select(-grouping) %>%
  mutate(encounter_time = sprintf("%1i:%02i:%02i", 
    lubridate::hour(encounter_time), 
    lubridate::minute(encounter_time), 
    lubridate::second(encounter_time)))

  camera_id organism  start_datetime      end_datetime        encounter_time
      <int> <chr>     <dttm>              <dttm>              <chr>         
1         1 bird      2021-05-11 05:32:34 2021-05-11 05:32:34 0:00:00       
2         1 mouse     2021-05-11 05:23:46 2021-05-11 05:23:50 0:00:04       
3         1 squirrel  2021-05-15 09:35:20 2021-05-15 09:35:35 0:00:15       
4         2 mouse     2021-05-10 23:11:16 2021-05-10 23:11:17 0:00:01       
5         2 woodchuck 2021-05-12 11:06:08 2021-05-12 11:24:10 0:18:02       
6         3 mouse     2021-05-12 02:04:01 2021-05-12 02:04:03 0:00:02       
7         3 mouse     2021-05-13 01:15:00 2021-05-13 01:15:50 0:00:50 

但是你最终将 encounter_time 存储为角色，所以这可能有用也可能没用