如何在R中基于条件用其他列填充指定列（避免循环）

输入数据 #

input <- structure(
  list(individual = c(1, 2, 3, 4), 
       age = c(20, 34, 29, 30), 
       earnings_2020 = c(0, 0, 1, 0), 
       earnings_2021 = c(1, 0, 2, 0), 
       earnings_2022 = c(2, 1, 3, 1), 
       earnings1 = c(20000, 25000, 28000, 30000), 
       earnings2 = c(30000, 36000, 39000, 40000), 
       earnings3 = c(40000, 40000, 42000, 50000)), 
  class = "data.frame", 
  row.names = c(NA, -4L)
)

需求 #

将earnings_2020、earnings_2021、earnings_2022列的值按规则替换：

• 值为1 → 对应行的earnings1值
• 值为2 → 对应行的earnings2值
• 值为3 → 对应行的earnings3值
• 值为0 → 保留原值
• 最终不需要保留earnings1/earnings2/earnings3列

期望输出 #

尝试代码及报错 #

以下代码运行报错：

earnings_columns <- c("earnings_2020", "earnings_2021", "earnings_2022")
earnings_input_columns <- c("earnings1", "earnings2", "earnings3")

df <- df %>%
  mutate(
    across(
      .cols = all_of(earnings_columns), 
      .fns = ~ {
        case_when(
          . >= 1 & . <= 3 ~ {
            input_column <- earnings_input_columns[.]
            if (!is.null(input_column) && input_column %in% colnames(df)) {
              df[[input_column]]
            } else {
              .
            }
          },
          TRUE ~ . 
        )
      },
      .names = "{.col}"
    )
  )

错误信息：

Error in `mutate()`:
! Problem while computing `..1 = across(...)`.
Caused by error in `across()`:
! Problem while computing column `earnings_2021`.
Caused by error in `!is.null(input_column) && input_column %in% colnames(df)`:
! 'length = 2' in coercion to 'logical(1)'
Run `rlang::last_trace()` to see where the error occurred.

错误原因 #

1. earnings_input_columns[.]返回的是长度大于1的向量（当列中存在多种值时），而if条件要求单个逻辑值，导致类型不匹配。
2. 直接引用df[[input_column]]会获取整列数据，无法按行匹配对应个体的值。

方法1：dplyr矩阵索引法（高效适合大型数据集） #

利用矩阵索引实现行级匹配，避免循环：

library(dplyr)

earnings_year_cols <- c("earnings_2020", "earnings_2021", "earnings_2022")
earnings_val_cols <- c("earnings1", "earnings2", "earnings3")

result <- input %>%
  mutate(
    across(all_of(earnings_year_cols), ~ {
      # 构建行号+列索引的矩阵
      idx <- cbind(row_number(), .)
      # 0值保留，非0值取对应earnings列的行值
      ifelse(. == 0, 0, as.matrix(pick(all_of(earnings_val_cols)))[idx])
    })
  ) %>%
  select(-all_of(earnings_val_cols))

print(result)

方法2：data.table列循环法（超大型数据集首选） #

data.table的列级循环效率远高于行级循环，适合处理千万级以上数据：

library(data.table)

dt <- as.data.table(input)
earnings_year_cols <- c("earnings_2020", "earnings_2021", "earnings_2022")
earnings_val_cols <- c("earnings1", "earnings2", "earnings3")

# 列级循环，仅处理非0值
for (col in earnings_year_cols) {
  dt[get(col) != 0, (col) := .SD[[get(col)]], .SDcols = earnings_val_cols]
}

# 删除冗余列
dt[, (earnings_val_cols) := NULL]

print(dt)

方法3：dplyr case_match简洁法 #

如果年份列数量不多，直接用case_match逐个处理更直观：

library(dplyr)

result <- input %>%
  mutate(
    earnings_2020 = case_match(
      earnings_2020,
      1 ~ earnings1, 2 ~ earnings2, 3 ~ earnings3, .default = earnings_2020
    ),
    earnings_2021 = case_match(
      earnings_2021,
      1 ~ earnings1, 2 ~ earnings2, 3 ~ earnings3, .default = earnings_2021
    ),
    earnings_2022 = case_match(
      earnings_2022,
      1 ~ earnings1, 2 ~ earnings2, 3 ~ earnings3, .default = earnings_2022
    )
  ) %>%
  select(-earnings1, -earnings2, -earnings3)

print(result)

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