Apply dplyr function over several columns

Question

I have a data frame with ~150K rows and 77 categorical variables in a form such as the below. How do I found the Score and count for each category

One numeric variable and 77 grouping variables

students<-data.frame(ID = c("A","B","C","D"), Gender = c("M","F","F","F"), Socioeconomic = c("Low","Low","Medium","High"), Subject = c("Maths","Maths","Science", "Science"),
                    Scores = c(45,98, 50,38))

That is I do not want to have to go through each categorical column individually 77 times but want a tibble that contains a list of the outputs for each of the below

students %>% group_by(Gender) %>% summarise(Mean.score = mean(Scores), Count = length(ID))

students %>% group_by(Socioeconomic) %>% summarise(Mean.score = mean(Scores), Count = length(ID))

students %>% group_by(Subject) %>% summarise(Mean.score = mean(Scores), Count = length(ID))```

I'm not sure the linked question is a duplicate (although I think this question has probabily been asked before). The linked question focuses on how to summarize multiple numeric columns grouped by a single set of categorical columns. This question asks how to summarize a single numeric column grouped successively by each categorical column. — eipi10, May 06 '19 at 06:53
Yes that is correct. I would like to apply two functions to a single numeric column grouped by multiple catergorical columns — monkeyshines, May 06 '19 at 06:56

eipi10 · Accepted Answer · 2019-05-06T07:07:34.670

Here are two options:

library(tidyverse)

# map successively over each categorical column
map(students %>% select(-Scores, -ID) %>% names() %>% set_names(),
    ~ students %>% 
      group_by_at(.x) %>% 
      summarise(Mean.score = mean(Scores), 
                Count = n())
)

$Gender
# A tibble: 2 x 3
  Gender Mean.score Count
  <fct>       <dbl> <int>
1 F              62     3
2 M              45     1

$Socioeconomic
# A tibble: 3 x 3
  Socioeconomic Mean.score Count
  <fct>              <dbl> <int>
1 High                38       1
2 Low                 71.5     2
3 Medium              50       1

$Subject
# A tibble: 2 x 3
  Subject Mean.score Count
  <fct>        <dbl> <int>
1 Maths         71.5     2
2 Science       44       2

# Convert to long format, group, then summarize
students %>% 
  gather(key, value, -ID, -Scores) %>% 
  group_by(key, value) %>% 
  summarise(Count=n(),
            Mean.score=mean(Scores))

  key           value   Count Mean.score
  <chr>         <chr>   <int>      <dbl>
1 Gender        F           3       62  
2 Gender        M           1       45  
3 Socioeconomic High        1       38  
4 Socioeconomic Low         2       71.5
5 Socioeconomic Medium      1       50  
6 Subject       Maths       2       71.5
7 Subject       Science     2       44

Apply dplyr function over several columns

1 Answers1