如何在R语言中基于分类变量生成条件复合得分

问题背景 #

数据集 #

我有一个类似如下的数据集：

stack_tib <- structure(list(ID = 1:50, Phase = c("Wave_1", "Wave_2", "Wave_1", 
"Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", 
"Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", 
"Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", 
"Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", 
"Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", 
"Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2", "Wave_1", 
"Wave_2", "Wave_1", "Wave_2", "Wave_1", "Wave_2"), ABC1 = c(NA, 
45, NA, 51, NA, 84, NA, 25, NA, 41, NA, 57, NA, 52, NA, 86, NA, 
11, NA, 41, NA, 46, NA, 35, NA, 16, NA, 53, NA, 75, NA, 44, NA, 
68, NA, 64, NA, 49, NA, 42, NA, 46, NA, 93, NA, 28, NA, 41, NA, 
48), ABC2 = c(NA, 49, NA, 77, NA, 80, NA, 62, NA, 54, NA, 40, 
NA, 30, NA, 56, NA, 51, NA, 91, NA, 4, NA, 36, NA, 71, NA, 26, 
NA, 47, NA, 58, NA, 63, NA, 57, NA, 59, NA, 73, NA, 61, NA, 37, 
NA, 38, NA, 81, NA, 29), ABC3 = c(NA, 55, NA, 43, NA, 49, NA, 
17, NA, 68, NA, 62, NA, 49, NA, 56, NA, 37, NA, 30, NA, 31, NA, 
45, NA, 37, NA, 52, NA, 49, NA, 59, NA, 42, NA, 73, NA, 65, NA, 
21, NA, 45, NA, 20, NA, 39, NA, 64, NA, 24), RABC1 = c(66, NA, 
57, NA, 48, NA, 61, NA, 70, NA, 71, NA, 25, NA, 42, NA, 63, NA, 
60, NA, 46, NA, 49, NA, 35, NA, 51, NA, 59, NA, 29, NA, 43, NA, 
45, NA, 72, NA, 65, NA, 54, NA, 52, NA, 24, NA, 62, NA, 38, NA
), RABC2 = c(94, NA, 45, NA, 42, NA, 34, NA, 83, NA, 52, NA, 
75, NA, 30, NA, 41, NA, 25, NA, 39, NA, 72, NA, 43, NA, 36, NA, 
68, NA, 89, NA, 54, NA, 39, NA, 46, NA, 56, NA, 34, NA, 80, NA, 
46, NA, 48, NA, 37, NA), RABC3 = c(42, NA, 43, NA, 82, NA, 72, 
NA, 48, NA, 40, NA, 51, NA, 96, NA, 47, NA, 42, NA, 67, NA, 64, 
NA, 67, NA, 53, NA, 59, NA, 17, NA, 58, NA, 52, NA, 54, NA, 46, 
NA, 73, NA, 73, NA, 90, NA, 87, NA, 50, NA)), row.names = c(NA, 
-50L), class = c("tbl_df", "tbl", "data.frame"))

数据结构与需求 #

数据结构如下：

# A tibble: 50 × 8
      ID Phase   ABC1  ABC2  ABC3 RABC1 RABC2 RABC3
   <int> <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1 Wave_1    NA    NA    NA    66    94    42
 2     2 Wave_2    45    49    55    NA    NA    NA
 3     3 Wave_1    NA    NA    NA    57    45    43
 4     4 Wave_2    51    77    43    NA    NA    NA
 5     5 Wave_1    NA    NA    NA    48    42    82
 6     6 Wave_2    84    80    49    NA    NA    NA
 7     7 Wave_1    NA    NA    NA    61    34    72
 8     8 Wave_2    25    62    17    NA    NA    NA
 9     9 Wave_1    NA    NA    NA    70    83    48
10    10 Wave_2    41    54    68    NA    NA    NA
# … with 40 more rows
# ℹ Use `print(n = ...)` to see more rows

数据包含3个ABC项和3个RABC项。Wave_1的ABC值为NA，Wave_2的RABC值为NA（实际数据并非完全一致，但不同测试阶段的对象使用了不同量表）。需要根据分类变量Phase（Wave_1/Wave_2），为每个ID生成对应的复合得分Sum_ABC，理想输出如下：

# A tibble: 50 × 9
      ID Phase   ABC1  ABC2  ABC3 RABC1 RABC2 RABC3 Sum_ABC
   <int> <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>   <dbl>
 1     1 Wave_1    NA    NA    NA    66    94    42     149
 2     2 Wave_2    45    49    55    NA    NA    NA     138
 3     3 Wave_1    NA    NA    NA    57    45    43     144
 4     4 Wave_2    51    77    43    NA    NA    NA     150
 5     5 Wave_1    NA    NA    NA    48    42    82     157
 6     6 Wave_2    84    80    49    NA    NA    NA     133
 7     7 Wave_1    NA    NA    NA    61    34    72     147
 8     8 Wave_2    25    62    17    NA    NA    NA     158
 9     9 Wave_1    NA    NA    NA    70    83    48     145
10    10 Wave_2    41    54    68    NA    NA    NA     152

解决方案 #

可以使用dplyr包中的mutate结合case_when和rowSums实现需求：

library(dplyr)

stack_tib <- stack_tib %>%
  mutate(Sum_ABC = case_when(
    Phase == "Wave_1" ~ rowSums(select(., starts_with("RABC")), na.rm = TRUE),
    Phase == "Wave_2" ~ rowSums(select(., starts_with("ABC")), na.rm = TRUE)
  ))

代码说明 #

• select(., starts_with("RABC"))：筛选所有以RABC开头的列
• rowSums(..., na.rm = TRUE)：对选中的列按行求和，自动忽略NA值
• case_when：根据Phase的取值，匹配对应的求和逻辑

如果需要更简洁的写法，也可以用if_else替代case_when：

stack_tib <- stack_tib %>%
  mutate(Sum_ABC = if_else(Phase == "Wave_1",
                           rowSums(select(., RABC1:RABC3), na.rm = TRUE),
                           rowSums(select(., ABC1:ABC3), na.rm = TRUE)))

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