在dplyr中如何选择要使用中位数函数的列？

Question

我有以下数据集：

df <- tribble(
 ~id,  ~name,  ~day_1,   ~day_2,  ~day_3,  ~day_4,  ~rank,
 "101",  "a",     5,          2,      1,       8,     '1',
 "202",  "b",     8,          4,      5,       5,     '2',
 "303",  "c",    10,          6,      9,       6,     '3',
 "404",  "d",    12,          8,      5,       7,     '4',
 "505",  "e",    14,         10,      7,       9,     '5',
 "607",  "f",     5,          2,      1,       8,     '6',
 "707",  "g",     8,          4,      5,       5,     '7',    
 "808",  "h",    10,          6,      9,       6,     '8',
 "909",  "k",    12,          8,      5,       7,     '9',
"1009",  "l",    14,         10,      7,       9,    '10',
)

在感谢@Edward的帮助下创建了top变量，并按照top分组数据后，我对以day开头的每一列的值取了中位数。以下是代码：

df %>%
 mutate(top = ifelse(rank <= 1, 1,
                     ifelse(rank <= 3, 3,
                            ifelse(rank <= 5, 5,
                                   ifelse(rank <= 7, 7,
                                          ifelse(rank <= 8, 8, 10)))))) %>%
 group_by(top) %>%
 summarize(day_1 = median(as.numeric(day_1), na.rm = TRUE),
           day_2 = median(as.numeric(day_2), na.rm = TRUE),
           day_3 = median(as.numeric(day_3), na.rm = TRUE),
           day_4 = median(as.numeric(day_4), na.rm = TRUE)) 

这里是结果：

# A tibble: 6 x 5
   top day_1 day_2 day_3 day_4
 <dbl> <dbl> <dbl> <dbl> <dbl>
1     1   5       2     1   8  
2     3  10       6     7   6  
3     5  13       9     6   8  
4     7   6.5     3     3   6.5
5     8  10       6     9   6  
6    10  12       8     5   7

然而，由于我的真实数据集中有近40列以“day”开头，因此我希望使用函数更有效地完成它，而不是像这样编写所有列名：summarize(day_1 = median(as.numeric(day_1), na.rm = TRUE)。

有什么好的想法吗？

- datazang

---

请在dplyr的开发版本中查看summarise_at或across；还可以参考https://dev59.com/dmkw5IYBdhLWcg3wrshS - arg0naut91

谢谢您的建议。我已经添加了这个：summarise_at(vars(starts_with('day')), median) 但是它出现了以下错误：期望一个单侧公式、一个函数或一个函数名。@arg0naut91 - datazang

Answer 1

这适用于您的测试数据：

 df %>%
  mutate(top = ifelse(rank <= 1, 1,
                      ifelse(rank <= 3, 3,
                             ifelse(rank <= 5, 5,
                                    ifelse(rank <= 7, 7,
                                           ifelse(rank <= 8, 8, 10)))))) %>%
  group_by(top) %>%
  summarise_at(vars(starts_with("day")), ~median(as.numeric(.x), na.rm = TRUE))


# A tibble: 6 x 5
    top day_1 day_2 day_3 day_4
  <dbl> <dbl> <dbl> <dbl> <dbl>
1     1   5       2     1   8  
2     3  10       6     7   6  
3     5  13       9     6   8  
4     7   6.5     3     3   6.5
5     8  10       6     9   6  
6    10  12       8     5   7  

- Cettt

---

在使用mutate之前，我们必须指定df$rank=as.numeric(df$rank)。如果没有这个步骤，在rank大于等于10的行（两位数值）中，会输入"3"（而不是10）。@jay.sf得到了正确的结果。 - E.Wiest

Answer 2

在基本的R中，您可以使用aggregate。我将从您的起始数据框架开始，并实现一个cut方法来创建top列。

res <- aggregate(cbind(day_1, day_2, day_3, day_4) ~ top, 
                 transform(df, top=cut(as.numeric(df$rank), c(0, 1, 3, 5, 7, 8, 10),
                                       c(1, 3, 5, 7, 8, 10))), 
                 FUN=function(x) median(as.numeric(x)))
res
#   top day_1 day_2 day_3 day_4
# 1   1   5.0     2     1   8.0
# 2   3   9.0     5     7   5.5
# 3   5  13.0     9     6   8.0
# 4   7   6.5     3     3   6.5
# 5   8  10.0     6     9   6.0
# 6  10  13.0     9     6   8.0

as_tibble(res)
# # A tibble: 6 x 5
# top   day_1 day_2 day_3 day_4
# <fct> <dbl> <dbl> <dbl> <dbl>
#   1 1       5       2     1   8  
# 2 3       9       5     7   5.5
# 3 5      13       9     6   8  
# 4 7       6.5     3     3   6.5
# 5 8      10       6     9   6  
# 6 10     13       9     6   8  

- jay.sf

Answer 3

 df %>%
    mutate(top = ifelse(rank <= 1, 1,
                        ifelse(rank <= 3, 3,
                               ifelse(rank <= 5, 5,
                                      ifelse(rank <= 7, 7,
                                             ifelse(rank <= 8, 8, 10)))))) %>%
    group_by(top) %>%
    summarise_at(3:6, median, na.rm = TRUE) #columns from 3 to 6 change it if you have more of them (days) e.g. 3:40

# A tibble: 6 x 5
    top day_1 day_2 day_3 day_4
  <dbl> <dbl> <dbl> <dbl> <dbl>
1     1   5       2     1   8  
2     3  10       6     7   6  
3     5  13       9     6   8  
4     7   6.5     3     3   6.5
5     8  10       6     9   6  
6    10  12       8     5   7 

- Adamm