dplyr按列行汇总,按名称分组

llmtgqce  于 2023-01-28  发布在  其他
关注(0)|答案(5)|浏览(177)

让我们考虑一下这个简单的数据集

set.seed(12345)
df <- data.frame(a1 = rnorm(5), a2 = rnorm(5), a3 = rnorm(5), 
                 b1 = rnorm(5), b2 = rnorm(5), b3 = rnorm(5),
                 c1 = rnorm(5), c2 = rnorm(5), c3 = rnorm(5))

看起来像是

a1         a2         a3         b1         b2         b3        c1          c2         c3
1  0.5855288 -1.8179560 -0.1162478  0.8168998  0.7796219  1.8050975 0.8118732  0.49118828  1.1285108
2  0.7094660  0.6300986  1.8173120 -0.8863575  1.4557851 -0.4816474 2.1968335 -0.32408658 -2.3803581
3 -0.1093033 -0.2761841  0.3706279 -0.3315776 -0.6443284  0.6203798 2.0491903 -1.66205024 -1.0602656
4 -0.4534972 -0.2841597  0.5202165  1.1207127 -1.5531374  0.6121235 1.6324456  1.76773385  0.9371405
5  0.6058875 -0.9193220 -0.7505320  0.2987237 -1.5977095 -0.1623110 0.2542712  0.02580105  0.8544517

现在,我想得到以一个特定字母开头的列的平均值,在一个向量中指定。
比如说如果我有

cols <- c("a", "c")

我想输出一个 Dataframe ,其中两列(a和c)分别包含a1/a2/a3和c1/c2/c3列的平均值。

a          c
1 -0.449558319  0.8105241
2  1.052292204 -0.1692037
3 -0.004953185 -0.2243752
4 -0.072480153  1.4457733
5 -0.354655514  0.3781747

我一直在尝试使用starts_withrow_wise,但我不能完全得到正确的语法。

ubby3x7f

ubby3x7f1#

selectstarts_with a或c,然后使用split.default拆分列,并对每个组应用rowMeans

library(dplyr)
library(purrr)
select(df, starts_with(cols)) %>% 
  split.default(gsub("\\d", "", names(.))) %>% 
  map_dfc(rowMeans)

         a      c
1 -0.450    0.811
2  1.05    -0.169
3 -0.00495 -0.224
4 -0.0725   1.45 
5 -0.355    0.378

请注意,根据列名的结构,可能需要更改gsub部分。

x9ybnkn6

x9ybnkn62#

计算所有列组的 * 平均值 *,然后计算子集:

data.frame(lapply(split.default(df, gsub("\\d", "", colnames(df))), rowMeans))[, cols]

根据实际数据,获取均值然后获取子集可能更有效。

**编辑:**对于较大的数据,先子集化再取平均值的速度更快。

ix <- gsub("\\d", "", colnames(df)) %in% cols
x1 <- data.frame(lapply(split.default(df[, ix], gsub("\\d", "", colnames(df)[ ix ])), rowMeans))
2eafrhcq

2eafrhcq3#

您可以在mutate()调用中迭代选择器值:

library(dplyr)
library(purrr)

df %>%
  mutate(map_df(set_names(cols), ~ rowMeans(across(starts_with(.x)))), .keep = "none")

             a          c
1 -0.449558319  0.8105241
2  1.052292204 -0.1692037
3 -0.004953185 -0.2243752
4 -0.072480153  1.4457733
5 -0.354655514  0.3781747
wydwbb8l

wydwbb8l4#

基准测试

数据:

library(dplyr)
library(purrr)

n = 100000 * 10 * 10
set.seed(12345); df <- data.frame(matrix(runif(n), ncol = 100))
colnames(df) <- make.unique(rep(letters[1:10], each = 10), sep = "")
cols <- letters[c(1,3,7,9)]

基准:

microbenchmark::microbenchmark(
  base1 = {
    ix <- gsub("\\d", "", colnames(df)) %in% cols
    data.frame(lapply(split.default(df[, ix], gsub("\\d", "", colnames(df)[ ix ])), rowMeans))
  },
  base2 = {
    data.frame(lapply(split.default(df, gsub("\\d", "", colnames(df))), rowMeans))[, cols]    
  },
  tidy1 = {
    select(df, starts_with(cols)) %>% 
      split.default(gsub("\\d", "", names(.))) %>% 
      map_dfc(rowMeans)
  },
  tidy2 = {
    split.default(df, gsub("\\d", "", names(df))) %>% 
      map_dfc(rowMeans) %>% 
      select({{cols}})
  },
  tidy3 = {
    df %>%
      mutate(map_df(set_names(cols), ~ rowMeans(across(starts_with(.x)))), .keep = "none")
  },
  check = "equivalent"
)
  
# Unit: milliseconds
#  expr     min       lq      mean   median       uq      max neval
# base1 26.8613 29.42070  36.75314 33.28410  40.9296 114.7524   100
# base2 67.9460 80.95455  94.08483 86.47900 100.4189 200.9891   100
# tidy1 33.8004 36.53475  44.94198 44.44600  49.8111  76.5831   100
# tidy2 73.4420 89.79525 101.52422 97.48275 108.5292 294.6857   100
# tidy3 39.8900 44.37110  53.25316 52.23770  56.4041  99.9845   100
pqwbnv8z

pqwbnv8z5#

cbind.data.frame(df %>% select(starts_with("a")) %>% 
  rowMeans() %>% 
  cbind.data.frame() %>% 
  rename("a"="."),
  df %>% select(starts_with("b")) %>% 
                       rowMeans() %>% 
                       cbind.data.frame()%>% 
                     rename("b"="."))

相关问题