calculate summary for more than one column

Question

I am trying to calculate median mean for group of columns but its calculating only for one column. what i am doing wrong here ...??

df <- data.frame(Name = c("ABC",    "DCA",  "GOL",NA,   "MNA",NA,   "VAN"),
                 Goal =c("published",   "pending",  "not designed",NA,  "pending",  "pending",  "not designed"),
                 Target_1 = c(3734, 2639,   2604,   NA, 2793,   2688,   2403),
                 Target_2 = c(3322, 2016,   2310,   NA, 3236,   3898,   2309),
                 Target_3 = c(3785, 2585,   3750,   NA, 2781,   3589,   2830))



df_summary <- df %>% select(contains("Target")) %>% summarise(
  q25 = round(quantile(.,  type=6, probs = seq(0, 1, 0.25), na.rm=TRUE)[2],digits = 0),
  Median = round(quantile(., type=6, probs = seq(0, 1, 0.25), na.rm=TRUE)[3],digits = 0),
  Mean = round( mean(., na.rm=TRUE),digits = 0),
  q75 = round(quantile(., type=6, probs = seq(0, 1, 0.25), na.rm=TRUE)[4],digits = 0),
  N = sum(!is.na(.)))

score 1 · Accepted Answer · answered Jun 08 '21 at 04:33

Use across to apply a function to multiple columns.

library(dplyr)
library(tidyr)

df %>% 
  summarise(across(contains("Target"), list(
    q25 = ~round(quantile(.,  type=6, probs = 0.25, na.rm=TRUE),digits = 0),
    Median = ~round(quantile(., type=6, probs = 0.5, na.rm=TRUE),digits = 0),
    Mean = ~round( mean(., na.rm=TRUE),digits = 0),
    q75 = ~round(quantile(., type=6, probs = 0.75, na.rm=TRUE),digits = 0),
    N = ~sum(!is.na(.)))))

#  Target_1_q25 Target_1_Median Target_1_Mean Target_1_q75 Target_1_N Target_2_q25
#1         2554            2664          2810         3028          6         2236
#  Target_2_Median Target_2_Mean Target_2_q75 Target_2_N Target_3_q25 Target_3_Median
#1            2773          2848         3466          6         2732            3210
#  Target_3_Mean Target_3_q75 Target_3_N
#1          3220         3759          6

Or maybe long format is a better way to display the values.

df %>%
  pivot_longer(cols = contains("Target")) %>%
  group_by(name) %>%
  summarise( q25 = round(quantile(value,  type=6, probs = 0.25, na.rm=TRUE),digits = 0),
             Median = round(quantile(value, type=6, probs = 0.5, na.rm=TRUE),digits = 0),
             Mean = round( mean(value, na.rm=TRUE),digits = 0),
             q75 = round(quantile(value, type=6, probs = 0.75, na.rm=TRUE),digits = 0),
             N = sum(!is.na(value)))

#  name       q25 Median  Mean   q75     N
#  <chr>    <dbl>  <dbl> <dbl> <dbl> <int>
#1 Target_1  2554   2664  2810  3028     6
#2 Target_2  2236   2773  2848  3466     6
#3 Target_3  2732   3210  3220  3759     6

Onyambu · Answer 2 · 2021-06-08T05:02:43.203

Using map:

df %>%
  select(contains('Target'))%>%
  map_dfr(~c(quantile(.x, type=6, probs = c(.25, .5,.75),  na.rm = TRUE),
              mean = mean(.x, na.rm = TRUE), 
              N = length(na.omit(.x))), .id = 'grp')
 grp      `25%` `50%` `75%`  mean     N
  <chr>    <dbl> <dbl> <dbl> <dbl> <dbl>
1 Target_1 2554. 2664. 3028. 2810.     6
2 Target_2 2236. 2773  3466  2848.     6
3 Target_3 2732  3210. 3759. 3220      6

Whatever you are doing seems like a summary:

df %>%
  select(contains('Target'))%>%
  summary()

Another way could be:

df %>%
  summarise(across(contains('Target'), 
      ~list(quantile(.x, type=6, probs = c(.25, .5,.75), na.rm = TRUE),
            mean(.x, na.rm = TRUE), 
            length(na.omit(.x))))
      )%>%
  unnest(everything())

 A tibble: 5 x 3
  Target_1 Target_2 Target_3
     <dbl>    <dbl>    <dbl>
1    2554.    2236.    2732 
2    2664.    2773     3210.
3    3028.    3466     3759.
4    2810.    2848.    3220 
5       6        6        6

If you were to include pivoting:

df %>%
  pivot_longer(contains('Target')) %>%
  group_by(name) %>%
  summarise(a = list(quantile(value, type=6, probs = c(.25, .5,.75),  na.rm = TRUE)),
            mean = mean(value, na.rm = TRUE), N = length(na.omit(value)))%>%
  unnest_wider(a)

# A tibble: 3 x 6
  name     `25%` `50%` `75%`  mean     N
  <chr>    <dbl> <dbl> <dbl> <dbl> <int>
1 Target_1 2554. 2664. 3028. 2810.     6
2 Target_2 2236. 2773  3466  2848.     6
3 Target_3 2732  3210. 3759. 3220      6

calculate summary for more than one column

2 Answers2