R 从多个可选列中选取一个值到一个新列中

发布于02月22日

假设我有这样的数据帧:

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"定义哪个值被选入新变量的方式.应该从以下列中 Select 值:(1)具有小于"number"的数字(在列名中)，并且(2)具有可用的最高数字(在列名中).最终输出应该如下所示:

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

谢谢你的建议！

pivot, filter

library(dplyr)
library(tidyr) # pivot_longer
df |>
  pivot_longer(cols = -c(id, number)) |>
  mutate(num2 = as.integer(gsub("\\D", "", name))) |>
  filter(num2 < number) |>
  slice_max(n = 1, order_by = num2, by = id) |>
  select(id, real_variable = value) |>
  right_join(df, by = "id")
# # A tibble: 5 × 7
#   id    real_variable number variable1 variable3 variable6 variable9
#   <chr> <chr>         <chr>  <chr>     <chr>     <chr>     <chr>    
# 1 1     72            9      80        65        72        99       
# 2 2     71            4      43        71        56        1        
# 3 3     2             3      2         100       90        68       
# 4 4     91            10     91        98        25        39       
# 5 5     13            5      22        13        34        66

编辑

正如@Ben指出的，我们在第4行中有一个缺陷，在这里比较字符串与比较整数的执行方式不同.

用于演示:

df |>
  pivot_longer(cols = -c(id, number)) |>
  mutate(num2 = as.integer(gsub("\\D", "", name))) |>
  filter(num2 < number, id == 4)
# # A tibble: 1 × 5
#   id    number name      value  num2
#   <chr> <chr>  <chr>     <chr> <int>
# 1 4     10     variable1 91        1
df |>
  mutate(across(-id, as.integer)) |>
  pivot_longer(cols = -c(id, number)) |>
  mutate(num2 = as.integer(gsub("\\D", "", name))) |>
  filter(num2 < number, id == 4)
# # A tibble: 4 × 5
#   id    number name      value  num2
#   <chr>  <int> <chr>     <int> <int>
# 1 4         10 variable1    91     1
# 2 4         10 variable3    98     3
# 3 4         10 variable6    25     6
# 4 4         10 variable9    39     9

您可以将所有类似数字的值(不是id)更改为整数up front:

df |>
  mutate(across(-id, as.integer)) |>
  pivot_longer(cols = -c(id, number)) |>
  mutate(num2 = as.integer(gsub("\\D", "", name))) |>
  filter(num2 < number) |>
  slice_max(n = 1, order_by = num2, by = id) |>
  select(id, real_variable = value) |>
  right_join(df, by = "id")

或者与整数化的number进行比较:

df |>
  pivot_longer(cols = -c(id, number)) |>
  mutate(num2 = as.integer(gsub("\\D", "", name))) |>
  filter(num2 < as.integer(number)) |>
  slice_max(n = 1, order_by = num2, by = id) |>
  select(id, real_variable = value) |>
  right_join(df, by = "id")
# # A tibble: 5 × 7
#   id    real_variable number variable1 variable3 variable6 variable9
#   <chr> <chr>         <chr>  <chr>     <chr>     <chr>     <chr>    
# 1 1     72            9      80        65        72        99       
# 2 2     71            4      43        71        56        1        
# 3 3     2             3      2         100       90        68       
# 4 4     39            10     91        98        25        39       
# 5 5     13            5      22        13        34        66

两者提供相同的输出，其中id==4的新值为39.