使用R语言dplyr按指定日期区间汇总降雨数据报错求助

原始数据 #

降雨数据 #

library(dplyr)

rainfall_data <- read.csv(text = "
date,rainfall_daily_mm
01/01/2019,0
01/02/2019,1
01/03/2019,3
01/04/2019,45
01/05/2019,0
01/06/2019,0
01/07/2019,0
01/08/2019,43
01/09/2019,5
01/10/2019,0
01/11/2019,55
01/12/2019,6
01/13/2019,0
01/14/2019,7
01/15/2019,0
01/16/2019,7
01/17/2019,8
01/18/2019,89
01/19/2019,65
01/20/2019,3
01/21/2019,0
01/22/2019,0
01/23/2019,2
01/24/2019,0
01/25/2019,0
01/26/2019,0
01/27/2019,0
01/28/2019,22
01/29/2019,3
01/30/2019,0
01/31/2019,0
") %>% 
  mutate(date = as.Date(date, format = "%d/%m/%Y"))

日期区间数据 #

intervals <- read.csv(text= "
treatment,initial,final
A,01/01/2019,01/05/2019
B,01/13/2019,01/20/2019
C,01/12/2019,01/26/2019
D,01/30/2019,01/31/2019
E,01/11/2019,01/23/2019
F,01/03/2019,01/19/2019
G,01/01/2019,01/24/2019
H,01/26/2019,01/28/2019
") %>%
  mutate(initial = as.Date(initial, format = "%d/%m/%Y"),
         final = as.Date(final, format = "%d/%m/%Y"))

错误代码与问题 #

尝试的代码：

summary_by_date_interval <- rainfall_data %>% 
  mutate(group = cumsum(grepl(intervals$initial|intervals$final, date))) %>%
  group_by(group) %>%
  summarise(rainfall = sum(rainfall_daily_mm))

错误信息：

Error in `mutate()`:
! Problem while computing `group = cumsum(grepl(intervals$initial |
  intervals$final, date))`.
Caused by error in `Ops.Date()`:
! | not defined for "Date" objects
Run `rlang::last_error()` to see where the error occurred.

错误原因：|运算符无法直接作用于Date类型对象，且grepl用于字符串匹配，不适合处理日期区间的判断逻辑，原代码的分组思路实现方式完全错误。

解决方案 #

以下提供三种高效的实现方式，均能完成按指定日期区间汇总降雨量的需求：

方法1：逐行处理每个区间（rowwise） #

summary_result <- intervals %>%
  rowwise() %>%
  mutate(total_rainfall = sum(rainfall_data$rainfall_daily_mm[rainfall_data$date >= initial & rainfall_data$date <= final])) %>%
  ungroup()

print(summary_result)

方法2：交叉连接+筛选+分组汇总 #

逻辑更直观，适合理解数据关联过程：

summary_result <- intervals %>%
  # 生成所有区间与日期的组合
  cross_join(rainfall_data) %>%
  # 筛选落在当前区间内的日期
  filter(date >= initial & date <= final) %>%
  # 按区间分组计算总降雨量
  group_by(treatment, initial, final) %>%
  summarise(total_rainfall = sum(rainfall_daily_mm), .groups = "drop")

print(summary_result)

方法3：使用fuzzyjoin包（灵活处理区间匹配） #

如果需要更复杂的区间匹配规则，可以用fuzzyjoin包：

library(fuzzyjoin)

summary_result <- fuzzy_inner_join(
  intervals,
  rainfall_data,
  by = c("initial" = "date", "final" = "date"),
  match_fun = list(`<=`, `>=`)
) %>%
  group_by(treatment, initial, final) %>%
  summarise(total_rainfall = sum(rainfall_daily_mm), .groups = "drop")

print(summary_result)

结果示例 #

以上方法都会输出如下结构的结果：

内容的提问来源于stack exchange，提问作者Giuseppe Petri