使用时间阈值统计未来/过去NoShow事件时slider包报错求助

背景 #

此前找到类似问题的解决方案，但应用到自身数据集时运行失败并报错。

模拟数据集 #

df <- data.frame(Person = c('111','334','334','334','334','334','888','888','888','888','888','888','888','888'), 
                 NoShow = c(0,0,0,1,1,0,0,0,0,0,0,1,0,1), 
                 date = c('2017-03-04','2017-11-14','2018-04-26','2020-01-24','2020-01-25','2020-02-29','2018-08-09',
                          '2018-08-09','2018-04-10','2019-09-20','2020-06-30','2020-11-18','2021-08-13','2021-11-10')
)

原统计代码 #

library(lubridate); library(dplyr); library(slider)

### 统计未来2年的NoShow事件
df <- df %>%
  mutate(date = ymd(date)) %>%
  group_by(Person) %>%
  arrange(Person, date) %>% 
  mutate(no_shows_next2yr = slider::slide_index_dbl(
    NoShow, date, sum, .before = days(-1), .after = years(2)
  ))

### 统计过去2年的NoShow事件
df <- df %>%
  mutate(date = ymd(date)) %>%
  group_by(Person) %>%
  arrange(Person, date) %>% 
  mutate(no_shows_past2yr = slider::slide_index_dbl(
    NoShow, date, sum, .before = years(2), .after = days(-1)
  ))

报错信息 #

未来统计报错 #

Error in `mutate()`:
! Problem while computing `no_shows_next2yr = slider::slide_index_dbl(...)`.
ℹ The error occurred in group 2: Person = "334".
Caused by error in `slider::slide_index_dbl()`:
ℹ In locations: 5
! Endpoints generated by `.after` must be in ascending order.
Run `rlang::last_trace()` to see where the error occurred.
> rlang::last_trace()
<error/dplyr:::mutate_error>
Error in `mutate()`:
! Problem while computing `no_shows_next2yr = slider::slide_index_dbl(...)`.
ℹ The error occurred in group 2: Person = "334".
Caused by error in `slider::slide_index_dbl()`:
ℹ In locations: 5
! Endpoints generated by `.after` must be in ascending order.
---
Backtrace:
    ▆
 1. ├─... %>% ...
 2. ├─dplyr::mutate(...)
 3. ├─dplyr:::mutate.data.frame(...)
 4. │ └─dplyr:::mutate_cols(.data, dplyr_quosures(...), caller_env = caller_env())
 5. │   ├─base::withCallingHandlers(...)
 6. │   └─mask$eval_all_mutate(quo)
 7. └─slider::slide_index_dbl(NoShow, date, sum, .before = days(-1), .after = years(2))

过去统计报错 #

Error in `mutate()`:
! Problem while computing `no_shows_past2yr = slider::slide_index_dbl(...)`.
ℹ The error occurred in group 2: Person = "334".
Caused by error in `slider::slide_index_dbl()`:
ℹ In locations: 5
! Endpoints generated by `.before` must be in ascending order.
Run `rlang::last_trace()` to see where the error occurred.
> rlang::last_trace()
<error/dplyr:::mutate_error>
Error in `mutate()`:
! Problem while computing `no_shows_past2yr = slider::slide_index_dbl(...)`.
ℹ The error occurred in group 2: Person = "334".
Caused by error in `slider::slide_index_dbl()`:
ℹ In locations: 5
! Endpoints generated by `.before` must be in ascending order.
---
Backtrace:
    ▆
 1. ├─... %>% ...
 2. ├─dplyr::mutate(...)
 3. ├─dplyr:::mutate.data.frame(...)
 4. │ └─dplyr:::mutate_cols(.data, dplyr_quosures(...), caller_env = caller_env())
 5. │   ├─base::withCallingHandlers(...)
 6. │   └─mask$eval_all_mutate(quo)
 7. └─slider::slide_index_dbl(NoShow, date, sum, .before = years(2), .after = days(-1))

问题原因 #

报错核心是同一Person下存在重复日期的记录，slider包的slide_index_*函数要求索引（此处为date）必须能生成严格递增的窗口端点。当存在重复日期时，计算窗口边界会出现端点顺序混乱的情况，触发排序错误。

解决方案 #

步骤1：处理重复日期 #

给同一Person同一日期的记录添加微小时间差（毫秒级），确保每个日期唯一，同时不影响天级时间范围的统计逻辑。

步骤2：修正统计代码 #

调整窗口边界参数，用更精准的数值替代days(-1)，避免日期精度导致的边界问题。

修正后的完整代码 #

library(lubridate); library(dplyr); library(slider)

# 预处理：转换日期并添加微秒级差异消除重复
df <- df %>%
  mutate(date = ymd(date)) %>%
  group_by(Person, date) %>%
  mutate(date = date + milliseconds(row_number())) %>% # 给同日期记录加不同毫秒数
  ungroup()

# 统计未来2年的NoShow事件（当前时间之后到2年内，不含当天）
df <- df %>%
  group_by(Person) %>%
  arrange(Person, date) %>%
  mutate(no_shows_next2yr = slider::slide_index_dbl(
    NoShow, date, sum,
    .before = -1e-9, # 精准表示当前时间之后的最小单位
    .after = years(2)
  ))

# 统计过去2年的NoShow事件（2年前到当前时间之前，不含当天）
df <- df %>%
  group_by(Person) %>%
  arrange(Person, date) %>%
  mutate(no_shows_past2yr = slider::slide_index_dbl(
    NoShow, date, sum,
    .before = years(2),
    .after = -1e-9 # 精准表示当前时间之前的最小单位
  )) %>%
  ungroup()

# 可选：将日期转回纯日期格式（去掉毫秒）
df <- df %>%
  mutate(date = as_date(date))

View(df)

说明 #

• 用milliseconds(row_number())给同日期记录添加差异，既保证日期严格递增，又不会改变原日期的天级统计逻辑。
• 用-1e-9替代days(-1)，更精准地控制窗口边界，避免因日期精度问题导致的错误。

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