Summarizing using dplyr with a for loop

Question

I would like to summarise each of my independant variables (columns) with my target variable using dplyr over a for loop. This is my main dataframe:

  contract_ID    Asurion         Variable_1     Variable_2  Variable_3
         1          Y                a               c          f
         2          Y                a               d          g
         3          N                b               c          g
         4          N                a               d          f
         5          Y                b               c          f
         6          Y                a               d          f

After the group by I get

a1 <- a %>% 
  group_by(Asurion,BhvrBnk_Donates_to_Env_Causes) %>%       
  summarise(counT=n_distinct(CONTRACT_ID)) %>%                                        
  mutate(perc=paste0(round(counT/sum(counT)*100,2),"%"))

 Asurion Variable_1 CounT   perc
    Y         a        3     75%
    Y         b        1     25%
    N         a        1     50%
    N         b        1     50%

I would like to have this summairsation for each of my variable present in my dataframe and I would like to do this using a for loop. How do i get to my desired result

This is what I have tried using but it doesnt seem to work. it is for a school project and I need to use a for loop for this. Please help me out here

categorical <- colnames(a)###where categroical is the names of all columns in a  
###I would like to have a for loop for every column in a and summarise in the following way. I would like to store each of the summarisations in a separate dataframe 

for (i in categorical) {
  a[[i]] <- a %>% 
     group_by(Asurion,get(i)) %>% 
    summarise(counT=n_distinct(CONTRACT_ID)) %>% 
    mutate(perc=paste0(round(counT/sum(counT)*100,2),"%"))
  }

Answer 1

You may not really need for loop to get what you wanted.

df<-data.frame(contract_ID = 1:6, 
               Asurion = c("Y", "Y", "N", "N", "Y", "Y"), 
               Variable_1 = c("a", "a", "b", "a", "b","a"), 
               Variable_2 = c("c", "d", "c", "d", "c", "d"), 
               Variable_3 = c("f", "g", "g", "f", "f", "f"))

pct <- function(x) {
  df %>% 
  group_by(Asurion, {{x}}) %>% 
  summarise(counT=n_distinct(contract_ID)) %>% 
  mutate(perc = paste0(round(counT/sum(counT)*100,2),"%"))
}

pct(Variable_1)
pct(Variable_2)
pct(Variable_3)

If you do have many variables, you could use something like for loop or apply to iterate the last bit. Here is one option:

categorical<- df[3:5]
a <- list()
j = 1
for (i in categorical) {
  a[[j]] <- df %>% 
    group_by(Asurion, {{i}}) %>% 
    summarise(counT=n_distinct(contract_ID)) %>% 
    mutate(perc = paste0(round(counT/sum(counT)*100,2),"%"))
  j = j + 1
}
a

[[1]]
# A tibble: 4 x 4
# Groups:   Asurion [2]
  Asurion `<fct>` counT perc 
  <fct>   <fct>   <int> <chr>
1 N       a           1 50%  
2 N       b           1 50%  
3 Y       a           3 75%  
4 Y       b           1 25%  

[[2]]
# A tibble: 4 x 4
# Groups:   Asurion [2]
  Asurion `<fct>` counT perc 
  <fct>   <fct>   <int> <chr>
1 N       c           1 50%  
2 N       d           1 50%  
3 Y       c           2 50%  
4 Y       d           2 50%  

[[3]]
# A tibble: 4 x 4
# Groups:   Asurion [2]
  Asurion `<fct>` counT perc 
  <fct>   <fct>   <int> <chr>
1 N       f           1 50%  
2 N       g           1 50%  
3 Y       f           3 75%  
4 Y       g           1 25%  

EDIT Add variable names as new variable values in response to your question to identify group_by variables.

categorical<- df[3:5]
vnames <- colnames(categorical)
a <- list()
j = 1
for (i in categorical) {
  a[[j]] <- df %>% 
    group_by(Asurion, {{i}}) %>% 
    summarise(counT=n_distinct(contract_ID)) %>% 
    mutate(perc = paste0(round(counT/sum(counT)*100,2),"%"))
    a[[j]]$vnames = vnames[j]
  j = j + 1
}
a

Answer 2

Here's a tidyr and dplyr way that produces a list of results per your question:

library(tidyr)
library(dplyr)

DF%>%
  add_count(Asurion, name = 'all_n')%>%
  pivot_longer(cols = starts_with('Variable'))%>%
  group_by(Asurion, name, value)%>%
  summarize(CounT = n(), 
            perc = n() / first(all_n))%>%
  ungroup()%>%
  group_split(name, keep = F)

[[1]]
# A tibble: 4 x 4
  Asurion value CounT  perc
  <fct>   <fct> <int> <dbl>
1 N       a         1  0.5 
2 N       b         1  0.5 
3 Y       a         3  0.75
4 Y       b         1  0.25

[[2]]
# A tibble: 4 x 4
  Asurion value CounT  perc
  <fct>   <fct> <int> <dbl>
1 N       c         1   0.5
2 N       d         1   0.5
3 Y       c         2   0.5
4 Y       d         2   0.5

[[3]]
# A tibble: 4 x 4
  Asurion value CounT  perc
  <fct>   <fct> <int> <dbl>
1 N       f         1  0.5 
2 N       g         1  0.5 
3 Y       f         3  0.75
4 Y       g         1  0.25

And a base solution as well that better matches the intended output:

## base
lapply(grep('Variable', names(DF), value = T), # get vars starting with "Variable"
       function(col_name){
         t = table(DF[, c('Asurion', col_name)]) 
         data.frame(prop.table(t, 1), CounT = c(t))
       }
)

[[1]]
  Asurion Variable_1 Freq CounT
1       N          a 0.50     1
2       Y          a 0.75     3
3       N          b 0.50     1
4       Y          b 0.25     1

[[2]]
  Asurion Variable_2 Freq CounT
1       N          c  0.5     1
2       Y          c  0.5     2
3       N          d  0.5     1
4       Y          d  0.5     2

[[3]]
  Asurion Variable_3 Freq CounT
1       N          f 0.50     1
2       Y          f 0.75     3
3       N          g 0.50     1
4       Y          g 0.25     1

Data per @Zhiqiang Wang:

DF<-data.frame(contract_ID = 1:6, 
               Asurion = c("Y", "Y", "N", "N", "Y", "Y"), 
               Variable_1 = c("a", "a", "b", "a", "b","a"), 
               Variable_2 = c("c", "d", "c", "d", "c", "d"), 
               Variable_3 = c("f", "g", "g", "f", "f", "f"))

Answer 3

Base R Solution:

 df2 <- data.frame(

  reshape(df,

          direction = "long",

          varying = names(df)[!(names(df) %in% c("contract_ID", "Asurion"))],

          v.names = "Val",

          timevar = "Variable",

          times = names(df)[!(names(df) %in% c("contract_ID", "Asurion"))]

  ),

  row.names = NULL,

  stringsAsFactors = F

)

# Count the unique contract ids within the specified group: 

df2$CounT <- as.numeric(ave(df2$contract_ID,

                            paste(df2$Asurion, df2$Variable, df2$Val, sep = "_"),

                            FUN = function(x){length(unique(x))}))

# Create the percentage of total counts: 

df2$perc <- paste0(round((df2$CounT/as.numeric(ave(df2$Variable, 

                                                   paste(df2$Variable, df2$Val, sep = "_"),

                                                   FUN = length))) * 100,2),"%")

# Allocate some memory for list of dataframes: 

df_list <- vector("list", length(unique(df2$Variable)))

# Store the summary dataframes in the list: 

df_list <- lapply(split(df2, df2$Variable),

                  function(x){x <- unique(x[,c(!(names(x) %in% c("id", "contract_ID")))])})

# Push the dataframes from the list into the global environment: 

list2env(df_list, .GlobalEnv)

Tidyverse Solution:

require(tidyverse)

# Allocate some memory for list of dataframes: 

df_list <- vector("list", length(unique(names(df)[grepl("Variable_", names(df))])))

# Tidyverse summary: 

df_list <- 

  df %>% 

  gather(Variable, Value, -contract_ID, -Asurion) %>% 

  group_by(Asurion, Variable, Value) %>% 

  mutate(CounT = length(unique(contract_ID))) %>%

  ungroup() %>% 

  group_by(Variable, Value) %>% 

  mutate(perc = paste0(round((CounT/n()) * 100, 2), "%")) %>% 

  ungroup() %>% 

  select(-contract_ID) %>% 

  distinct() %>% 

  split(., .$Variable)

# Push the dataframes from the list into the global environment: 

list2env(df_list, .GlobalEnv)

Data:

structure(list(contract_ID = 1:6, Asurion = c("Y", "Y", "N", 
"N", "Y", "Y"), Variable_1 = c("a", "a", "b", "a", "b", "a"), 
    Variable_2 = c("c", "d", "c", "d", "c", "d"), Variable_3 = c("f", 
    "g", "g", "f", "f", "f")), class = "data.frame", row.names = c(NA, 
-6L))