3

I have the following data set:

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',
)

After creating the top variable thanks to @Edward and grouping the data by top, I've taken the median of values for each column that starts with day. Here is the code:

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))

And here is the result:

# 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

However, since I have almost forty columns that start with day in my real data set, I would like to use a function to do it more efficiently instead of writing all column names like summarize(day_1 = median(as.numeric(day_1), na.rm = TRUE).

Any idea for this?

Cettt
  • 11,460
  • 7
  • 35
  • 58
datazang
  • 989
  • 1
  • 7
  • 20
  • Look into `summarise_at` or `across` in development version of `dplyr`; check also https://stackoverflow.com/questions/9723208/aggregate-summarize-multiple-variables-per-group-e-g-sum-mean – arg0naut91 Mar 13 '20 at 11:26
  • Thanks for your suggestion. I've added this: ``` summarise_at(vars(starts_with('day')), median) ``` But it gives the following error: expecting a one sided formula, a function, or a function name. @arg0naut91 – datazang Mar 13 '20 at 11:32

3 Answers3

3

This works for your test data:

 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
  • 11,460
  • 7
  • 35
  • 58
  • 1
    We have to specify `df$rank=as.numeric(df$rank)` before mutate. Without it, for the rows where rank >=10 (value with two digits number), "3" is inputed (instead of 10). @jay.sf has the correct result. – E.Wiest Mar 14 '20 at 15:10
2

In base R you could use aggregate. I begin with your starting data frame and implement a cut approach to create the top column.

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
  • 60,139
  • 8
  • 53
  • 110
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(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
  • 2,150
  • 22
  • 30