R语言mutate(across())报错：无法子集不存在的列

我是编程新手，有两个列结构相同但行数不同的DataFrame（unique_pt_all、denom_2011）。需求是：当denom_2011中的DESY_SORT_KEY与unique_pt_all匹配，且unique_pt_all中C_CORNEAL_ULCER_COUNT<=2时，把denom_2011的第8至18列数值加到unique_pt_all对应列中。

尝试用left_join+mutate(across())实现，但报错，相关代码和信息如下：

指定列代码 #

columns_to_add <- c(
  "C_DRY_EYE_COUNT",
  "C_SJOGRENS_COUNT",
  "C_AA_KERATACONJUNCTIVITIS_COUNT",
  "C_DIABETES_MELLITUS_COUNT",
  "C_POA_GLAUCOMA_COUNT",
  "C_CICATRIZING_COUNT",
  "C_CORNEAL_DISORDER_LENS_COUNT",
  "C_CORNEAL_ULCER_COUNT",
  "C_CATARACT_COUNT",
  "C_KERATOPLASTY_COUNT",
  "C_PTERYGIUM_COUNT"
)

更新逻辑代码 #

unique_pt_all <- unique_pt_all %>%
  left_join(denom_2011 %>% filter(C_CORNEAL_ULCER_COUNT <= 2),
            by = "DESY_SORT_KEY",
            suffix = c("_unique", "_denom")) %>%
  mutate(across(all_of(columns_to_add), 
                ~ifelse(is.na(.x_unique), .x, .x_unique + .x_denom))) %>%
  select(-ends_with("_denom"))

报错信息 #

Error in mutate():
ℹ In argument: across(...).
Caused by error in all_of():
! Can't subset columns that don't exist.
✖ Columns C_DRY_EYE_COUNT, C_SJOGRENS_COUNT, C_AA_KERATACONJUNCTIVITIS_COUNT, C_DIABETES_MELLITUS_COUNT, C_POA_GLAUCOMA_COUNT, etc. don't exist.

关联后的列名 #

colnames(unique_pt_all)
[1] "DESY_SORT_KEY"                          "STATE_CODE_unique"
[3] "COUNTY_CODE_unique"                     "SEX_CODE_unique"
[5] "RACE_CODE_unique"                       "AGE_unique"
[7] "REFERENCE_YEAR_unique"                  "C_DRY_EYE_COUNT_unique"
[9] "C_SJOGRENS_COUNT_unique"                "C_AA_KERATACONJUNCTIVITIS_COUNT_unique"
[11] "C_DIABETES_MELLITUS_COUNT_unique"       "C_POA_GLAUCOMA_COUNT_unique"
[13] "C_CICATRIZING_COUNT_unique"             "C_CORNEAL_DISORDER_LENS_COUNT_unique"
[15] "C_CORNEAL_ULCER_COUNT_unique"           "C_CATARACT_COUNT_unique"
[17] "C_KERATOPLASTY_COUNT_unique"            "C_PTERYGIUM_COUNT_unique"
[19] "C_CORNEAL_ULCER_MIN_DT_unique"          "C_CORNEAL_ULCER_MAX_DT_unique"
[21] "C_CATARACT_MIN_DT_unique"               "C_KERATOPLASTY_MIN_DT_unique"
[23] "C_PTERYGIUM_MIN_DT_unique"              "STATE_CODE_denom"
[25] "COUNTY_CODE_denom"                      "SEX_CODE_denom"
[27] "RACE_CODE_denom"                        "AGE_denom"
[29] "REFERENCE_YEAR_denom"                   "C_DRY_EYE_COUNT_denom"
[31] "C_SJOGRENS_COUNT_denom"                 "C_AA_KERATACONJUNCTIVITIS_COUNT_denom"
[33] "C_DIABETES_MELLITUS_COUNT_denom"        "C_POA_GLAUCOMA_COUNT_denom"
[35] "C_CICATRIZING_COUNT_denom"              "C_CORNEAL_DISORDER_LENS_COUNT_denom"
[37] "C_CORNEAL_ULCER_COUNT_denom"            "C_CATARACT_COUNT_denom"
[39] "C_KERATOPLASTY_COUNT_denom"             "C_PTERYGIUM_COUNT_denom"
[41] "C_CORNEAL_ULCER_MIN_DT_denom"           "C_CORNEAL_ULCER_MAX_DT_denom"
[43] "C_CATARACT_MIN_DT_denom"                "C_KERATOPLASTY_MIN_DT_denom"
[45] "C_PTERYGIUM_MIN_DT_denom"

错误原因 #

1. left_join后原列名被添加了_unique/_denom后缀，但你在across中仍使用原始列名，导致找不到对应列报错。
2. 筛选条件位置错误：需求是筛选unique_pt_all中C_CORNEAL_ULCER_COUNT<=2的行，而非denom_2011的行。

修正后的代码 #

library(stringr)

columns_to_add <- c(
  "C_DRY_EYE_COUNT",
  "C_SJOGRENS_COUNT",
  "C_AA_KERATACONJUNCTIVITIS_COUNT",
  "C_DIABETES_MELLITUS_COUNT",
  "C_POA_GLAUCOMA_COUNT",
  "C_CICATRIZING_COUNT",
  "C_CORNEAL_DISORDER_LENS_COUNT",
  "C_CORNEAL_ULCER_COUNT",
  "C_CATARACT_COUNT",
  "C_KERATOPLASTY_COUNT",
  "C_PTERYGIUM_COUNT"
)

unique_pt_all <- unique_pt_all %>%
  left_join(denom_2011, by = "DESY_SORT_KEY", suffix = c("_unique", "_denom")) %>%
  # 仅对符合条件的行执行加法操作
  mutate(across(all_of(paste0(columns_to_add, "_unique")), 
                ~ifelse(C_CORNEAL_ULCER_COUNT_unique <= 2 & !is.na(get(str_replace(cur_column(), "_unique", "_denom"))),
                        .x + get(str_replace(cur_column(), "_unique", "_denom")),
                        .x))) %>%
  # 移除denom后缀列，并恢复原列名
  select(-ends_with("_denom")) %>%
  rename_with(~str_remove(.x, "_unique"), ends_with("_unique"))

代码说明 #

1. 调整关联逻辑：不再提前过滤denom_2011，而是在mutate中判断unique_pt_all的C_CORNEAL_ULCER_COUNT_unique <=2，确保仅符合条件的行执行加法。
2. 列名适配：用paste0生成带_unique后缀的目标列，通过str_replace匹配对应的_denom列。
3. 结构还原：移除_denom后缀列，并将_unique后缀列改回原始列名，保持DataFrame结构与初始一致。

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