基于年限终止技能插值：为人员技能数据集添加X年过期规则

背景与现有实现 #

现有人员技能获取数据集，已实现基于中点假设的技能累积插值逻辑，原始代码如下：

原始数据集 #

have <- data.frame(
  c("A", "A","A","B","B","C","C"),
  c("S1", "S4", "S2", "S1,S2","S3","S1,S3","S1,S2" ),
  c(2015,2015, 2018,2016,2018,2016,2024)
)
colnames(have) <- c("person", "skill", "year")

现有插值函数与处理代码 #

skill_interp <- function(x, y) {
  x <- cumany(x) + 0
  function(z) {
    i <- findInterval(z, y)
    ifelse(i==length(y), x[i], ifelse(z-y[i] == y[i+1]-z, pmax(x[i], x[i+1]),
                                      ifelse(z-y[i] < y[i+1]-z, x[i], x[i+1])))
  }
}

have %>% 
  separate_longer_delim(skill, ",") %>% 
  mutate(present=1) %>% 
  group_by(person) %>% 
  complete(skill, year, fill=list(present=0)) %>%
  ungroup() %>% 
  reframe(, present=skill_interp(present, year)(first(year):last(year)), year=first(year):last(year), .by = c(person, skill)) %>% 
  filter(present==1) %>% 
  summarize(skill=paste(skill, collapse=","), .by=c(person, year))

新增需求：技能失效规则 #

需添加规则：技能获取后5年未被使用（无后续记录），则停止插值并从累积技能中移除，期望输出数据集如下：

want <- data.frame(
  c("A","A","A","A","B","B","B","C","C","C","C","C","C","C","C","C"),
  c("S1,S4","S1,S4","S1,S2,S4","S1,S2,S4","S1,S2","S1,S2,S3","S1,S2,S3","S1,S3","S1,S3","S1,S3","S1,S3","S1,S2,S3","S1,S2","S1,S2","S1,S2"),
  c(2015,2016,2017,2018,2016,2017,2018,2016,2017,2018, 2019, 2020, 2021, 2022, 2023, 2024)
)
colnames(want) <- c("person", "skills", "year")

实现方案（含失效规则） #

修改原有逻辑，加入技能失效年份判断，核心步骤为：

1. 记录每个技能的最后活跃年份（即该技能最后出现的年份）
2. 计算技能失效年份：最后活跃年份 + 5
3. 在插值时，若目标年份超过失效年份，则标记该技能为未持有

修改后的完整代码：

have <- data.frame(
  c("A", "A","A","B","B","C","C"),
  c("S1", "S4", "S2", "S1,S2","S3","S1,S3","S1,S2" ),
  c(2015,2015, 2018,2016,2018,2016,2024)
)
colnames(have) <- c("person", "skill", "year")

# 可灵活调整的技能失效年限参数
SKILL_EXPIRY_YEARS <- 5

skill_interp_with_expiry <- function(x, y, expiry_year) {
  x <- cumany(x) + 0
  function(z) {
    # 保留原有中点插值逻辑
    i <- findInterval(z, y)
    present <- ifelse(i==length(y), x[i], ifelse(z-y[i] == y[i+1]-z, pmax(x[i], x[i+1]),
                                      ifelse(z-y[i] < y[i+1]-z, x[i], x[i+1])))
    # 叠加失效判断：超过失效年份则标记为未持有
    ifelse(z > expiry_year, 0, present)
  }
}

have %>% 
  separate_longer_delim(skill, ",") %>% 
  mutate(present=1) %>% 
  # 按人员-技能分组，计算最后活跃年份与失效年份
  group_by(person, skill) %>%
  mutate(last_active_year = max(year),
         expiry_year = last_active_year + SKILL_EXPIRY_YEARS) %>%
  ungroup() %>%
  group_by(person) %>% 
  complete(skill, year, fill=list(present=0)) %>%
  ungroup() %>% 
  # 传入失效年份执行插值
  reframe(
    present = skill_interp_with_expiry(present, year, first(expiry_year))(first(year):last(year)),
    year = first(year):last(year),
    .by = c(person, skill)
  ) %>% 
  filter(present==1) %>% 
  summarize(skills=paste(skill, collapse=","), .by=c(person, year))

代码说明 #

• 新增SKILL_EXPIRY_YEARS参数，可按需修改技能失效年限
• 保留原有中点插值逻辑，仅在超过失效年份时将技能标记为未持有
• 按person-skill维度计算失效年份，确保每个技能的失效时间精准对应其最后使用记录

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