如何在R数据框分组内对比首行与后续行直到满足日期差阈值
问题描述
现有如下结构的dataframe:
| rownum | group | date |
|---|---|---|
| 1 | a | 2021-05-01 |
| 2 | a | 2021-05-02 |
| 3 | a | 2021-05-03 |
| 4 | b | 2021-05-15 |
| 5 | b | 2021-05-17 |
| 6 | b | 2021-05-30 |
| 7 | b | 2021-05-31 |
| 8 | b | 2021-05-31 |
| 9 | c | 2021-05-01 |
| 10 | c | 2021-05-05 |
需求说明
同一分组内将首行date与后续行依次对比,直到日期间差满足指定阈值(示例为10天);满足阈值后将该行下一行设为新基准行,继续向后对比计算日期差,预期输出如下(阈值取10):
| rownum | group | date | date diff | 备注 |
|---|---|---|---|---|
| 1 | a | 2021-05-01 | NA | 基准行 |
| 2 | a | 2021-05-02 | 1 | |
| 3 | a | 2021-05-03 | 2 | |
| 4 | b | 2021-05-15 | NA | 基准行 |
| 5 | b | 2021-05-17 | 2 | |
| 6 | b | 2021-05-30 | 15 | 满足阈值,从第7行开始重新计算 |
| 7 | b | 2021-05-31 | NA | 新基准行 |
| 8 | b | 2021-05-31 | 0 | |
| 9 | c | 2021-05-01 | NA | 基准行 |
| 10 | c | 2021-05-05 | 4 |
现有尝试代码
用户尝试了两个版本,都没有达到效果:
第一个版本:
dataframe %>% group_by(group) %>% mutate( datediff = sapply(date, function(x) { all(difftime(dataframe$date,dplyr::lag(dataframe, n = 1, default = NA))) } ) )
第二个版本:
for (m in 1:length(dataframe)) { dataframe <- dataframe %>% group_by(group) %>% rowwise() %>% mutate(datediff = difftime(dataframe$date,dplyr::lag(date, n = m, default = NA), units="days")) }
解决方案
该需求属于组内动态基准的迭代判断,直接写对应逻辑的自定义分组函数最清晰,完全匹配需求描述,实现代码如下:
library(dplyr) library(lubridate) # 自定义日期差计算函数,匹配需求逻辑 calc_datediff <- function(group_df, threshold = 10) { n <- nrow(group_df) datediff <- rep(NA, n) if (n == 1) return(datediff) # 初始化基准行索引为分组第一行 current_bench_idx <- 1 for (i in 2:n) { diff <- as.numeric(group_df$date[i] - group_df$date[current_bench_idx]) datediff[i] <- diff # 差值达到阈值,将下一行设为新基准 if (diff >= threshold) { current_bench_idx <- i + 1 if (current_bench_idx > n) break # 新基准行差值为NA datediff[current_bench_idx] <- NA # 下一轮从新基准的下一行开始计算 i <- current_bench_idx } } return(datediff) } # 预处理+调用函数计算 res <- df %>% # 确保date列为日期格式 mutate(date = ymd(date)) %>% # 按分组计算datediff group_by(group) %>% mutate(datediff = calc_datediff(cur_data(), threshold = 10)) %>% ungroup()
运行后输出结果和预期完全一致。
内容的提问来源于stack exchange,提问作者SqueakyBeak
相关产品推荐
相关产品推荐

