r - 根据R中第二个数据框中的日期范围对数据框中的数据进行分组

Question

我有以下两个数据框:

Date <- seq(as.Date("2013/1/1"), by = "day", length.out = 46)
x <-data.frame(Date)
x$discharge <- c("1000","1100","1200","1300","1400","1200","1300","1300","1200","1100","1200","1200","1100","1400","1200","1100","1400","1000","1100","1200","1300","1400","1200","1300","1300","1200","1100","1200","1200","1100","1400","1200","1100","1400","1000","1100","1200","1300","1400","1200","1300","1300","1200","1100","1200","1200")
x$discharge <- as.numeric(x$discharge)

和

Date_from <- c("2013-01-01","2013-01-15","2013-01-21","2013-02-10")
Date_to <- c("2013-01-07","2013-01-20","2013-01-25","2013-02-15")
y <- data.frame(Date_from,Date_to)
y$concentration <- c("1.5","2.5","1.5","3.5")
y$Date_from <- as.Date(y$Date_from)
y$Date_to <- as.Date(y$Date_to)
y$concentration <- as.numeric(y$concentration)

我正在尝试根据日期范围 Date_from< 计算数据框 y 中每一行的数据框 x 中的每日排放量的平均排放量 到数据框 y 中的 Date_to。请注意，在 2013-01-08 到 2013-01-14 和 2013-01-26 到 2013-02-09 之间，数据帧 y 中的测量值存在差距。这个差距是由于在这段时间内没有进行任何测量。这个差距让我很头疼，因为我使用以下代码计算 y 中每个日期范围的平均排放量:

rng <- cut(x$Date, breaks=c(y$Date_from, max(y$Date_to), 
                    include.lowest=T))
range<-cbind(x,rng)
discharge<-aggregate(cbind(mean=x$discharge)~rng, FUN=mean)

但是，如果您检查数据框 range 中的范围，则 2013-01-01 到 2013-01-07 的范围会扩展到 2013-01-14，但我只需要它2013-01-07，然后在下一个范围从 2013-01-15 开始之前休息。

Answer 1

你可以试试 tidyverse。

library(tidyverse)
y %>% 
  split(seq_along(1:nrow(.))) %>% 
  map(~filter(x, between(Date, .$Date_from, .$Date_to)) %>% 
        summarise(Mean=mean(discharge))) %>% 
  bind_rows() %>% 
  bind_cols(y,.)
   Date_from    Date_to concentration     Mean
1 2013-01-01 2013-01-07           1.5 1214.286
2 2013-01-15 2013-01-20           2.5 1166.667
3 2013-01-21 2013-01-25           1.5 1300.000
4 2013-02-10 2013-02-15           3.5 1216.667

仅使用此代码，您就可以看到值和组。

y %>% 
  split(seq_along(1:nrow(.))) %>% 
  map(~filter(x, between(Date, .$Date_from, .$Date_to)))