How can I generate a column that reflects the grouping variable name within a for loop?

user_9731173 0
Viewed 58

I would like to be able create a list of variables, and then perform a count of a target variable for each level of each variable in the list. For clear presentation of the results, I'd like my end result to take the form of four columns: Variable, Level, Result, and Count.

Consider this partially-there example, borrowing heavily from Brad Cannel's answer at dplyr- group by in a for loop r:

df <- tibble(
  var1 = c(rep("a", 5), rep("b", 5)),
  var2 = c(rep("c", 3), rep("d", 7)),
  var3 = rnorm(10),
  result=c("good","bad","good","bad","good","bad","good","bad","good","bad")
)

groups <- c(quo(var1), quo(var2))  # Create a quoture

results<-list()

for (i in seq_along(groups)) {
  results[[i]]<-df %>% 
  group_by(!!groups[[i]]) %>% # Unquote with !!
  count(result)
}

all_results<-bind_rows(results)

enter image description here

At this point, the column n has the counts that I'd like. Rather than having columns named var1 and var2, I'm hoping to produce a result that looks like:

desired_results<-tibble(
variable=c("var1","var1","var1","var1","var2","var2","var2","var2"),
level=c("a","a","b","b","c","c","d","d"),
result=c("bad","good","bad","good","bad","good","bad","good"),
n=c(2,3,3,2,1,2,4,3)

)

enter image description here

I have tried using mutate in the loop to produce my result, but can't get the formatting correct:

for (i in seq_along(groups)) {
  results[[i]]<-df %>% 
  group_by(!!groups[[i]]) %>% # Unquote with !!
  count(result)%>%
  mutate(level=!!groups[[i]])%>%
  mutate(variable=groups[i])%>%
  ungroup()%>%
  select(variable,level,result,n)
  }

I figured out how to "get there" using pivot_longer, like so (albeit just needing to rename columsn afterwards):

all_results2<-all_results%>%
  pivot_longer(cols=c(-result,-n))%>%
  filter(!(is.na(value)))

I'd really like to know how I could avoid this and just produce a column that houses the variable name right there in the for loop, and I'm guessing I'm missing some key bit of syntax. Any help in finding and explaining the solution would be greatly appreciated!

dplyr r
2 Answers

This could be done with pivot_longer, without looping, then binding the rows etc

library(dplyr)
library(tidyr)
df %>%
    pivot_longer(cols = var1:var2, names_to = 'variable', 
           values_to ='level') %>% 
    count(variable, level, result)

-output

# A tibble: 8 x 4
  variable level result     n
  <chr>    <chr> <chr>  <int>
1 var1     a     bad        2
2 var1     a     good       3
3 var1     b     bad        3
4 var1     b     good       2
5 var2     c     bad        1
6 var2     c     good       2
7 var2     d     bad        4
8 var2     d     good       3
user_37322710

Another option in the larger tidyverse, instead of the for loop, would be a call to purrr::map_dfr.

library(tidyverse)

groups <- c("var1", "var2")

map_dfr(groups,
        ~ tibble(variable = .x,
                 count(df, level = !!sym(.x), result)))

#> # A tibble: 8 x 4
#>   variable level result     n
#>   <chr>    <chr> <chr>  <int>
#> 1 var1     a     bad        2
#> 2 var1     a     good       3
#> 3 var1     b     bad        3
#> 4 var1     b     good       2
#> 5 var2     c     bad        1
#> 6 var2     c     good       2
#> 7 var2     d     bad        4
#> 8 var2     d     good       3

Created on 2021-07-21 by the reprex package (v0.3.0)

user_93493020
Related