r - 使用 dplyr 计算共享相似名称的列的按行汇总统计信息，例如平均值、最大值、最小值

Question

使用 R 分析数据时，我需要为具有相似名称(例如，相同前缀)的列计算按行汇总的统计信息(平均值、最小值、最大值、标准差)。虽然我可以使用诸如 matrixStats 之类的包来实现它，但我想知道是否有更优雅的方法来使用 dplyr 来实现它。下面附上了生成示例数据集的代码和我计算按行汇总统计的解决方案。谢谢!

######SAMPEL CODE#####
library("tidyverse")
library("matrixStats")

# create a sample dataset
sample_data <- data.frame(matrix(nrow = 10, ncol = 6))
names(sample_data) <- c("ID", "score_1", "score_2", "score_3", "score_4", "score_5")
sample_data <- sample_data %>%
  mutate(ID = seq(1:10),
         score_1 = round(runif(10, 1, 5)),
         score_2 = round(runif(10, 1, 5)),
         score_3 = round(runif(10, 1, 5)),
         score_4 = round(runif(10, 1, 5)),
         score_5 = round(runif(10, 1, 5)))

score_columns <- grep("score_", names(sample_data))

sample_data <- sample_data %>% 
  mutate(mean_score = rowMeans(select(., starts_with("score_")), na.rm = TRUE),
         max_score = rowMaxs(as.matrix(sample_data[,c(score_columns)]), na.rm = TRUE),
         sd_score = rowSds(as.matrix(sample_data[,c(score_columns)]), na.rm = TRUE)) 

Answer 1

您可以使用 dplyr row-wise operations 按行计算这些统计数据结合 c_across function ，例如

library(tidyverse)
library(matrixStats)
#> 
#> Attaching package: 'matrixStats'
#> The following object is masked from 'package:dplyr':
#> 
#>     count

# create a sample dataset
sample_data <- data.frame(matrix(nrow = 10, ncol = 6))
names(sample_data) <- c("ID", "score_1", "score_2", "score_3", "score_4", "score_5")
sample_data <- sample_data %>%
  mutate(ID = seq(1:10),
         score_1 = round(runif(10, 1, 5)),
         score_2 = round(runif(10, 1, 5)),
         score_3 = round(runif(10, 1, 5)),
         score_4 = round(runif(10, 1, 5)),
         score_5 = round(runif(10, 1, 5)))

score_columns <- grep("score_", names(sample_data))

output <- sample_data %>% 
  mutate(mean_score = rowMeans(select(., starts_with("score_")), na.rm = TRUE),
         max_score = rowMaxs(as.matrix(sample_data[,c(score_columns)]), na.rm = TRUE),
         sd_score = rowSds(as.matrix(sample_data[,c(score_columns)]), na.rm = TRUE)) 
output
#>    ID score_1 score_2 score_3 score_4 score_5 mean_score max_score  sd_score
#> 1   1       1       5       4       3       2        3.0         5 1.5811388
#> 2   2       3       5       1       5       2        3.2         5 1.7888544
#> 3   3       2       5       3       2       4        3.2         5 1.3038405
#> 4   4       3       4       3       3       5        3.6         5 0.8944272
#> 5   5       2       3       2       2       3        2.4         3 0.5477226
#> 6   6       4       2       4       3       2        3.0         4 1.0000000
#> 7   7       2       2       1       3       1        1.8         3 0.8366600
#> 8   8       3       4       1       3       4        3.0         4 1.2247449
#> 9   9       3       2       3       4       2        2.8         4 0.8366600
#> 10 10       5       2       3       3       4        3.4         5 1.1401754

output2 <- sample_data %>%
  rowwise() %>%
  mutate(mean_score = mean(c_across(starts_with("score_"))),
         max_score = max(c_across(!!score_columns)),
         sd_score = sd(c_across(!!score_columns)))
output2
#> # A tibble: 10 × 9
#> # Rowwise: 
#>       ID score_1 score_2 score_3 score_4 score_5 mean_score max_score sd_score
#>    <int>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>      <dbl>     <dbl>    <dbl>
#>  1     1       1       5       4       3       2        3           5    1.58 
#>  2     2       3       5       1       5       2        3.2         5    1.79 
#>  3     3       2       5       3       2       4        3.2         5    1.30 
#>  4     4       3       4       3       3       5        3.6         5    0.894
#>  5     5       2       3       2       2       3        2.4         3    0.548
#>  6     6       4       2       4       3       2        3           4    1    
#>  7     7       2       2       1       3       1        1.8         3    0.837
#>  8     8       3       4       1       3       4        3           4    1.22 
#>  9     9       3       2       3       4       2        2.8         4    0.837
#> 10    10       5       2       3       3       4        3.4         5    1.14

^{由 reprex package 创建于 2022-01-31 (v2.0.1)}

由于这是一个特定于 dplyr 的解决方案，您可以合并进一步的数据操作(例如 filter() 以选择特定的行)，而其他解决方案将失败或需要笨拙的解决方法，例如

library(tidyverse)
library(matrixStats)
#> 
#> Attaching package: 'matrixStats'
#> The following object is masked from 'package:dplyr':
#> 
#>     count

# create a sample dataset
sample_data <- data.frame(matrix(nrow = 10, ncol = 6))
names(sample_data) <- c("ID", "score_1", "score_2", "score_3", "score_4", "score_5")
sample_data <- sample_data %>%
  mutate(ID = seq(1:10),
         score_1 = round(runif(10, 1, 5)),
         score_2 = round(runif(10, 1, 5)),
         score_3 = round(runif(10, 1, 5)),
         score_4 = round(runif(10, 1, 5)),
         score_5 = round(runif(10, 1, 5)))

score_columns <- grep("score_", names(sample_data))

output <- sample_data %>%
  filter(ID < 6) %>%
  mutate(mean_score = rowMeans(select(., starts_with("score_")), na.rm = TRUE),
         max_score = rowMaxs(as.matrix(sample_data[,c(score_columns)]), na.rm = TRUE),
         sd_score = rowSds(as.matrix(sample_data[,c(score_columns)]), na.rm = TRUE)) 
#> Error: Problem with `mutate()` column `max_score`.
#> ℹ `max_score = rowMaxs(as.matrix(sample_data[, c(score_columns)]), na.rm = TRUE)`.
#> ℹ `max_score` must be size 5 or 1, not 10.


output2 <- sample_data %>%
  filter(ID < 6) %>%
  rowwise() %>%
  mutate(mean_score = mean(c_across(starts_with("score_"))),
         max_score = max(c_across(!!score_columns)),
         sd_score = sd(c_across(!!score_columns)))
output2
#> # A tibble: 5 × 9
#> # Rowwise: 
#>      ID score_1 score_2 score_3 score_4 score_5 mean_score max_score sd_score
#>   <int>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>      <dbl>     <dbl>    <dbl>
#> 1     1       5       2       5       5       1        3.6         5     1.95
#> 2     2       1       2       4       1       2        2           4     1.22
#> 3     3       1       2       2       4       5        2.8         5     1.64
#> 4     4       5       4       3       3       5        4           5     1   
#> 5     5       4       2       2       5       4        3.4         5     1.34

^{由 reprex package 创建于 2022-01-31 (v2.0.1)}