时间戳

首页 > 解决方案 > 如何根据连续的行值聚合数据？

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

问题描述

我正在对来自跟踪相机的动物照片进行数据分析。我的数据包括拍摄照片的相机、拍摄照片的日期和时间以及照片中的动物。我希望根据动物在镜头前花费的时间来汇总我的数据。就我们的目的而言，相遇是指我们在拍摄同一物种的另一只动物 10 分钟后拍摄动物的任何时候。在某些情况下，相遇的时间可能超过 10 分钟，例如，如果我们以 7 分钟的间隔拍摄同一动物的 3 张照片，则为 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 和有机体。我一直在尝试不同的 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))

标签： rdplyr

解决方案

我认为这会让你得到你想要的结果：

几个关键的变化：当我们计算时，除了 之外，分组timediff是有意义的，因为分组始终存在。camera_idorganism

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

under_10timediff对于小于 10的所有值，以及何时timediff为0 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

此外，如果您希望使用 H:MM:SS 格式，encounter_time可以像这样到达这里，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会存储为字符，因此这可能有用，也可能没有用

推荐阅读