从备选列按规则提取值生成新列的R语言实现

原始数据 #

df <- data.frame(id = c("1", "2", "3", "4", "5"),
                 number = c("9", "4", "3", "10", "5"),
                 variable1 = c("80", "43", "2", "91", "22"),
                 variable3 = c("65", "71", "100", "98", "13"),
                 variable6 = c("72", "56", "90", "25", "34"),
                 variable9 = c("99", "1", "68", "39", "66"))

需求说明 #

需要生成名为real_variable的新列，规则为：根据number列的数值，选取列名中数字小于该数值且为最大可用数字的variableX列对应值。期望输出如下：

id number real_variable
1  1      9            72
2  2      4            71
3  3      2             2
4  4     10            39
5  5      5            13

解决方案 #

方法一：基础R实现 #

首先需将字符型数值列转换为数值型（否则无法进行大小比较），再逐行筛选符合条件的列：

# 转换number列为数值型
df$number <- as.numeric(df$number)

# 提取所有variable开头的列，并转换为数值型
var_cols <- grep("^variable", names(df), value = TRUE)
df[var_cols] <- lapply(df[var_cols], as.numeric)

# 提取variable列的数字后缀
var_nums <- as.numeric(sub("variable", "", var_cols))

# 逐行计算real_variable
df$real_variable <- apply(df, 1, function(row) {
  current_num <- row["number"]
  # 找到小于current_num的最大数字后缀
  selected_num <- max(var_nums[var_nums < current_num])
  # 取出对应列的值
  row[paste0("variable", selected_num)]
})

# 查看目标结果
df[, c("id", "number", "real_variable")]

方法二：tidyverse工具包实现 #

使用dplyr和purrr实现管道式简洁操作：

library(dplyr)
library(purrr)

result <- df %>%
  # 转换相关列为数值型
  mutate(across(c(number, starts_with("variable")), as.numeric)) %>%
  # 提取所有variable列的数字后缀，存入列表列
  mutate(var_nums = list(as.numeric(sub("variable", "", names(.)[startsWith(names(.), "variable")])))) %>%
  # 逐行计算real_variable
  mutate(real_variable = pmap_dbl(list(number, var_nums), function(num, nums) {
    selected_num <- max(nums[nums < num])
    pull(., paste0("variable", selected_num))[cur_row()]
  })) %>%
  # 选择目标列
  select(id, number, real_variable)

print(result)

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