在R中基于课程问卷响应数生成Dashboard发布权限变量

我用Tableau仪表盘给课程讲师分享学生问卷数据，出于保密要求，只有当某课程的问卷响应数达到10份时才分享数据，之后每新增10份响应再更新分享内容。

现在要在R数据管道里创建ReleasedToDashboard变量：按SurveyEndDate时间顺序排序后，只要某课程已达到最近的向上取整10的倍数响应数（比如有10份时，第1-10、11-20份都标记"Yes"，直到到20份后继续），所有对应记录标记"Yes"，否则"No"。

我试了下面的代码，但只能把10的倍数位置的记录标记为"Yes"，达不到需求，求帮忙：

library(dplyr)
library(tidyr)
DF <- DF %>%
  arrange(CourseName, SurveyEndDate) %>%
  group_by(CourseName) %>%
  mutate(
    count = row_number(),
    nearest_mult_10 = ceiling(count / 10) * 10,
    ReleasedToDashboard = ifelse(nearest_mult_10 <= count, "Yes", "No")
  ) %>%
  ungroup() %>%
  select(-count, -nearest_mult_10)

附带示例数据结构：

structure(list(ReleasedToDashboard = c("Yes", "Yes", "Yes", "Yes", 
"Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", 
"Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "No", "No", 
"No", "No", "No", "No", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", 
"Yes", "Yes", "Yes", "Yes", "No", "No", "No", "No", "Yes", "Yes", 
"Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "No", 
"No", "No"), CourseName = c("Anatomy", "Anatomy", "Anatomy", 
"Anatomy", "Anatomy", "Anatomy", "Anatomy", "Anatomy", "Anatomy", 
"Anatomy", "Anatomy", "Anatomy", "Anatomy", "Anatomy", "Anatomy", 
"Anatomy", "Anatomy", "Anatomy", "Anatomy", "Anatomy", "Anatomy", 
"Biology", "Biology", "Biology", "Biology", "Biology", "Chemistry", 
"Chemistry", "Chemistry", "Chemistry", "Chemistry", "Chemistry", 
"Chemistry", "Chemistry", "Chemistry", "Chemistry", "Chemistry", 
"Physics", "Physics", "Physics", "Psychology", "Psychology", 
"Psychology", "Psychology", "Psychology", "Psychology", "Psychology", 
"Psychology", "Psychology", "Psychology", "Psychology", "Psychology", 
"Psychology"), SurveyEndDate = structure(c(1705449600, 1705536000, 
1705622400, 1705708800, 1706659200, 1706745600, 1706832000, 1707955200, 
1708041600, 1708128000, 1708214400, 1708300800, 1708387200, 1708473600, 
1708560000, 1708646400, 1708732800, 1708819200, 1708905600, 1708992000, 
1709078400, 1706054400, 1706140800, 1706227200, 1706313600, 1706400000, 
1705795200, 1705881600, 1706140800, 1706227200, 1706572800, 1706918400, 
1707004800, 1707264000, 1707350400, 1707436800, 1707523200, 1705363200, 
1705968000, 1706054400, 1705017600, 1705104000, 1705190400, 1705276800, 
1706313600, 1706400000, 1706486400, 1707091200, 1707177600, 1707609600, 
1707696000, 1707782400, 1707868800), class = c("POSIXct", "POSIXt"
), tzone = "UTC")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-53L))

核心思路是先计算每个课程的总响应数，确定该课程当前已解锁的最大10的倍数阈值，只要记录的行号小于等于这个阈值，就标记为"Yes"。

修改后的代码如下：

library(dplyr)

DF <- DF %>%
  arrange(CourseName, SurveyEndDate) %>%
  group_by(CourseName) %>%
  mutate(
    total_responses = n(),
    # 计算当前课程已解锁的最大10的倍数：总响应15则解锁10，总响应22则解锁20
    unlocked_threshold = floor(total_responses / 10) * 10,
    # 行号小于等于解锁阈值且阈值≥10时标记Yes，否则No
    ReleasedToDashboard = ifelse(row_number() <= unlocked_threshold & unlocked_threshold >= 10, "Yes", "No")
  ) %>%
  ungroup() %>%
  select(-total_responses, -unlocked_threshold)

代码解释 #

1. arrange(CourseName, SurveyEndDate)：按课程名称和问卷结束日期排序，保证记录顺序符合时间逻辑。
2. group_by(CourseName)：按课程分组，单独处理每个课程的响应数据。
3. total_responses = n()：统计每个课程的总问卷响应数。
4. unlocked_threshold = floor(total_responses / 10) * 10：计算该课程当前可公开的最大10的倍数响应数，比如总响应17则解锁10份，总响应25则解锁20份。
5. ReleasedToDashboard判断逻辑：只有当记录的行号小于等于解锁阈值，且解锁阈值至少为10时，标记为"Yes"；不足10份的课程全部标记"No"，超过阈值的新记录也标记"No"，完全匹配需求。

用你提供的示例数据测试，结果会和示例中的ReleasedToDashboard字段完全一致。

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