如何用R的tidyverse匹配数据框变量生成指标变量

问题背景 #

需要用R的tidyverse工具包处理数据框，生成指标变量ind_law。数据包含State、state_abbr、age、year以及Y2012至Y2015等列，其中Yxxxx列对应某州在xxxx年生效的年龄限制阈值（非NA值为有效阈值）。当同时满足以下两个条件时，ind_law取1，否则取0：

• 当前行的age ≤ 任意Y2013:Y2015列中的非NA阈值
• 该Yxxxx列对应的年份 ≤ 当前行的year

示例数据集 #

ds_ex <- structure(list(State = c("Alabama", "Alabama", "Alabama", "Alabama", 
"Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", 
"Alabama", "Alabama"), state_abbr = c("AL", "AL", "AL", "AL", 
"AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL"), age = c("18", 
"18", "18", "18", "19", "19", "19", "19", "20", "20", "20", "20"
), year = c("2012", "2013", "2014", "2015", "2012", "2013", "2014", 
"2015", "2012", "2013", "2014", "2015"), Y2012 = c(NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_), Y2013 = c("19", "19", "19", "19", "19", "19", 
"19", "19", "19", "19", "19", "19"), Y2014 = c(NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_), Y2015 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_)), row.names = c(NA, 
-12L), class = c("tbl_df", "tbl", "data.frame"))

解决方案 #

核心思路是将宽格式的Yxxxx列转换为长格式，统一处理年份与阈值的对应关系，再逐行判断条件是否成立。

代码实现 #

library(tidyverse)

# 数据处理流程
ds_ex_ans <- ds_ex %>%
  # 提取唯一的州-阈值对应关系（同一州的Y列值一致，避免重复计算）
  distinct(State, starts_with("Y")) %>%
  # 转换为长格式：拆分年份列与阈值
  pivot_longer(starts_with("Y"), 
               names_to = "law_year", 
               values_to = "age_threshold",
               values_drop_na = TRUE) %>%
  # 从law_year中提取数字年份并转为整数
  mutate(law_year = str_extract(law_year, "\\d{4}") %>% as.integer()) %>%
  # 合并回原数据集
  right_join(ds_ex, by = "State") %>%
  # 将age和year转为整数类型，方便数值比较
  mutate(across(c(age, year), as.integer)) %>%
  # 按行分组，判断是否存在满足条件的阈值
  group_by(State, state_abbr, age, year, Y2012, Y2013, Y2014, Y2015) %>%
  summarize(ind_law = as.integer(any(age <= age_threshold & law_year <= year)),
            .groups = "drop")

# 查看结果
ds_ex_ans

结果验证 #

运行代码后得到的结果与预期一致：

# A tibble: 12 × 9
   State   state_abbr   age  year Y2012 Y2013 Y2014 Y2015 ind_law
   <chr>   <chr>      <int> <int> <chr> <chr> <chr> <chr>   <int>
 1 Alabama AL            18  2012 NA    19    NA    NA          0
 2 Alabama AL            18  2013 NA    19    NA    NA          1
 3 Alabama AL            18  2014 NA    19    NA    NA          1
 4 Alabama AL            18  2015 NA    19    NA    NA          1
 5 Alabama AL            19  2012 NA    19    NA    NA          0
 6 Alabama AL            19  2013 NA    19    NA    NA          1
 7 Alabama AL            19  2014 NA    19    NA    NA          1
 8 Alabama AL            19  2015 NA    19    NA    NA          1
 9 Alabama AL            20  2012 NA    19    NA    NA          0
10 Alabama AL            20  2013 NA    19    NA    NA          0
11 Alabama AL            20  2014 NA    19    NA    NA          0
12 Alabama AL            20  2015 NA    19    NA    NA          0

多场景适配说明 #

该方案可直接适配多州、多年份的复杂场景：

• 自动识别所有以Y开头的年份列，无需手动指定列范围
• 按州分组处理阈值，不同州的规则会自动区分
• distinct步骤避免同一州的阈值重复计算，提升处理效率

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