如何创建计算下一次连任年份与当前年份差值的新列？

我有如下数据框：

structure(list(name = c("jones", "williams", "jones", 
                              "williams", "williams", "jones", "williams", "williams", "jones", 
                              "williams", "williams", "jones", "williams", "williams", "jones", 
                              "williams", "williams", "jones", "williams", "williams", "jones", 
                              "williams", "jones", "jones", "jones", "jones", 
                              "jones", "jones", "jones", "jones"), state = c("NY", 
                                                                             "NC", "NY", "NC", "TX", "NY", "NC", "TX", "NY", "TX", "NC", "NY", 
                                                                             "TX", "NC", "NY", "TX", "NC", "NY", "TX", "NC", "NY", "NC", "NY", 
                                                                             "NY", "NY", "NY", "NY", "NY", "NY", "NY"), year = structure(c(1995, 
                                                                                                                                           1995, 1996, 1996, 1996, 1997, 1997, 1997, 1998, 1998, 1998, 1999, 
                                                                                                                                           1999, 1999, 2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2003, 
                                                                                                                                           2004, 2005, 2006, 2007, 2008, 2009, 2010), format.stata = "%8.0g"), 
             year_of_election = c(NA, 1992, NA, 1992, 1996, NA, 1992, 
                                  1996, NA, 1998, 1998, 1999, 1998, 1998, 1999, 1998, 1998, 
                                  1999, 1998, 1998, 1999, 1998, 1999, 1999, 1999, 1999, 1999, 
                                  1999, 2009, 2009)), class = c("grouped_df", "tbl_df", "tbl", 
                                                                "data.frame"), row.names = c(NA, -30L), groups = structure(list(
                                                                  name = c("williams", "williams", "jones"), state = c("NC", 
                                                                                                                               "TX", "NY"), .rows = structure(list(c(2L, 4L, 7L, 11L, 14L, 
                                                                                                                                                                     17L, 20L, 22L), c(5L, 8L, 10L, 13L, 16L, 19L), c(1L, 3L, 
                                                                                                                                                                                                                      6L, 9L, 12L, 15L, 18L, 21L, 23L, 24L, 25L, 26L, 27L, 28L, 
                                                                                                                                                                                                                      29L, 30L)), ptype = integer(0), class = c("vctrs_list_of", 
                                                                                                                                                                                                                                                                "vctrs_vctr", "list"))), row.names = c(NA, -3L), .drop = TRUE, class = c("tbl_df", 
                                                                                                                                                                                                                                                                                                                                         "tbl", "data.frame")))

需要新增一列years_until_reelection，计算下一次选举年份减去当前年份，具体规则：

• 示例：jones-NY-1995对应值为4（1999-1995）；jones-NY-1999对应值为0；jones-NY-2000对应值为9（2009-2000）；jones-NY-2009对应值为0
• 若无符合条件的下一次选举年份，设为NA

我当前使用的代码逻辑错误，计算方向不符合需求：

df <- df %>%
  arrange(name, state, year) %>%
  group_by(name, state) %>%
  mutate(
    years_until_reelection = lead(year_of_election) - year
  ) %>%
  ungroup()

原代码的问题在于：lead(year_of_election)仅取当前行的下一行选举年份，但实际我们需要的是当前分组中，第一个大于等于当前年份的选举年份，而非简单的下一行值。

修正后的代码实现：

df <- df %>%
  arrange(name, state, year) %>%
  group_by(name, state) %>%
  mutate(
    # 提取当前分组内所有非NA的选举年份
    election_years = list(na.omit(year_of_election)),
    # 找到第一个 >= 当前年份的选举年份
    next_election = map2_dbl(year, election_years, ~{
      eligible_elections <- .y[.y >= .x]
      if (length(eligible_elections) == 0) NA_real_ else min(eligible_elections)
    }),
    # 计算间隔年份
    years_until_reelection = next_election - year,
    # 清理临时生成的辅助列
    election_years = NULL,
    next_election = NULL
  ) %>%
  ungroup()

逻辑说明 #

1. 按name和state分组，确保每个个体/地区的选举数据独立处理
2. 对每组提取所有有效的选举年份（排除NA）
3. 针对每行的年份，筛选出所有不小于当前年份的选举年份，取其中最小的作为下一次选举年份
4. 计算该年份与当前年份的差值，若无符合条件的选举年份则赋值为NA
5. 清理辅助列，只保留目标列

此代码可满足所有示例要求，同时处理无后续选举年份的情况。

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