使用时间间隔从另一个数据帧计算平均值。

Question

我有两个数据帧。一个数据帧包含网集(开始)和提升(结束)的时间。我需要使用这个时间段从我的第二个数据框架中计算出平均DO。我遇到了与时间匹配的问题，因为捕获数据帧中的时间是特定的，而DO数据帧中的时间是每小时的。我试着匹配最近的时间，但无法做到这一点，所以我的下一个想法是在时间间隔内使用任何东西。平均需要是特定于一艘船和网络在时间间隔内。我正在为几个非常大的数据集这样做，所以我需要能够循环这个函数，而不是每一行的代码。

集水区f：

Boat    Net   Set                    Lift
Dawn    26    2016-05-19 12:20:11    2016-05-27 11:48:36
Nip     26    2016-07-28 07:25:47    2016-08-07 06:13:10
Dawn    26.3  2016-08-01 13:24:51    2016-08-03 07:48:52

迪夫：

time                   DO      Boat    Net
2016-05-19 13:00:00    10.2    Dawn    26
2016-05-21 15:00:00    10.4    Dawn    26
2016-05-26 09:00:00    10.9    Dawn    26
2016-05-28 10:00:00    9.4     Dawn    26
2016-07-28 09:00:00    11.9    Nip     26
2016-07-28 19:00:00    12.4    Nip     26
2016-08-04 04:00:00    5.4     Nip     26
2016-08-01 05:00:00    13.2    Dawn    26.3
2016-08-02 16:00:00    12.3    Dawn    26.3
2016-08-05 22:00:00    2.4     Dawn    26.3

我的目标是在每个时间段使用meanDO将一列插入到跟踪to中。

Boat    Net   Set                    Lift                  MeanDO
Dawn    26    2016-05-19 12:20:11    2016-05-27 11:48:36   10.50
Nip     26    2016-07-28 07:25:47    2016-08-07 06:13:10   9.90
Dawn    26.3  2016-08-01 13:24:51    2016-08-03 07:48:52   12.3

这是我用来制作示例数据帧的代码：

catchdf <- structure(list(Boat = c("Dawn", "Nip", "Dawn"), Net = c("26", "26", "26.3"), Set = c("2016-05-19 12:20:11", "2016-07-28 07:25:47", "2016-08-01 13:24:51"), Lift = c("2016-05-27 11:48:36", "2016-08-07 06:13:10", "2016-08-03 07:48:52")), .Names = c("Boat", "Net", "Set", "Lift"), class = "data.frame", row.names = c(NA, -3L))

dodf <- structure(list(time = c("2016-05-19 13:00:00", "2016-05-21 15:00:00", "2016-05-26 09:00:00", "2016-05-28 10:00:00", "2016-07-28 09:00:00", "2016-07-28 19:00:00", "2016-08-04 04:00:00", "2016-08-01 05:00:00", "2016-08-02 16:00:00", "2016-08-05 22:00:00"), DO = c("10.2", "10.4", "10.9", "9.4", "11.9", "12.4", "5.4", "13.2", "12.3", "2.4"), Boat = c("Dawn", "Dawn", "Dawn", "Dawn", "Nip", "Nip", "Nip", "Dawn", "Dawn", "Dawn"), Net = c("26", "26", "26", "26", "26", "26", "26", "26.3", "26.3", "26.3")), .Names = c("time", "DO", "Boat", "Net"), class = "data.frame", row.names = c(NA, -10L))

我在这个问题上已经坚持了很长一段时间了，一直没能取得任何进展。任何帮助都将不胜感激。

Answer 1

这里有可能的data.table解决方案。首先，我们将修复列的格式。

library(data.table) #v1.10.4
cols <- c("Set", "Lift")
setDT(catchdf)[, (cols) := lapply(.SD, as.POSIXct), .SDcols = cols]
setDT(dodf)[, `:=`(time = as.POSIXct(time), DO = as.numeric(DO))]

然后，我们可以在to数据集之间建立一个非均数连接，同时计算动态平均值。

dodf[catchdf, .(MeanDO = mean(DO)), on = .(Boat, Net, time > Set, time < Lift), by = .EACHI]
#    Boat  Net                time                time MeanDO
# 1: Dawn   26 2016-05-19 12:20:11 2016-05-27 11:48:36   10.5
# 2:  Nip   26 2016-07-28 07:25:47 2016-08-07 06:13:10    9.9
# 3: Dawn 26.3 2016-08-01 13:24:51 2016-08-03 07:48:52   12.3

Answer 2

请注意，data.frame将DO值作为我建议的因素输入，只需使用data.frame并从get开始将值更改为数字。

dodf <- data.frame(time = c("2016-05-19 13:00:00", "2016-05-21 15:00:00", 
"2016-05-26 09:00:00", "2016-05-28 10:00:00", "2016-07-28 09:00:00", "2016-
07-28 19:00:00", "2016-08-04 04:00:00", "2016-08-01 05:00:00", "2016-08-02 
16:00:00", "2016-08-05 22:00:00"), 
DO = c(10.2, 10.4, 10.9, 9.4, 11.9, 12.4, 5.4, 13.2, 12.3, 2.4), 
Boat = c("Dawn", "Dawn", "Dawn", "Dawn", "Nip", "Nip", "Nip", "Dawn", "Dawn", "Dawn"), 
Net = c("26", "26", "26", "26", "26", "26", "26", "26.3", "26.3", "26.3"))

library(tidyverse)
library(lubridate)

dodf %>% 
  left_join(catchdf, by=c('Boat', 'Net')) %>% # join the data.frames
  mutate(time=ymd_hms(time), # assign the values to a data format
         Set = ymd_hms(Set), 
         Lift = ymd_hms(Lift), y = 
           ifelse(time>=Set & time<=Lift,'in', 'out')) %>% # create a "test column"
  filter(y=='in') %>% # filter out values outside of the sample periods
  group_by(Boat, Net) %>% 
  summarise(meanDO = mean(DO))