How to Build a Table that Summarizes Multiple Values at Once in R

user_9540604 0
Viewed 137

I have a dataframe (titled df1) with multiple columns that contain Yes, No or NA answers.

     A    B   C    D
1  Yes   No  No  Yes
2  Yes   No  No   No
3 <NA>  Yes Yes <NA>
4   No <NA>  No  Yes

My goal is to create a table that counts the frequency of each answer and output a table with the original column name as follows:

  Answer A B C D
1    Yes 2 1 1 2
2     No 1 2 3 1
3   <NA> 1 1 0 1

My approach so far has been to build a function then loop over that function, but the output doesn't product a table with all of the categories (A through D).

my_function <- function(table_name,col_name) {
  table_name %>% 
    group_by_(Answer = col_name) %>%
    summarise(!!paste0(col_name):= n())}

my_categories <- c("A","B","C","D")

for(i in 1:length(my_categories)){
  df2 <- myfunction(df1,CSAT_Cols[i])
}

I'm also open to different approaches if there's an easier method, but TL:DR, trying to loop over grouping by multiple categories, summarizing by n(), then creating single table with all of the data.

dplyr dataframe r function loops
2 Answers

We can reshape to 'long' format with pivot_longer and then back to 'wide' format with pivot_wider while specifying the values_fn as length on the different column

library(dplyr)
library(tidyr)
df1 %>% 
    pivot_longer(cols = everything(), values_to = 'Answer') %>% 
    pivot_wider(names_from = name, values_from = name, 
       values_fn = length, values_fill = 0)

-ouput

# A tibble: 3 x 5
  Answer     A     B     C     D
  <chr>  <int> <int> <int> <int>
1 Yes        2     1     1     2
2 No         1     2     3     1
3 <NA>       1     1     0     1

data

df1 <- structure(list(A = c("Yes", "Yes", NA, "No"), B = c("No", "No", 
"Yes", NA), C = c("No", "No", "Yes", "No"), D = c("Yes", "No", 
NA, "Yes")), class = "data.frame", row.names = c("1", "2", "3", 
"4"))
user_37322710

Maybe a base R option using table can help you

> table(unlist(df), names(df)[col(df)], useNA = "ifany")

       A B C D
  No   1 2 3 1
  Yes  2 1 1 2
  <NA> 1 1 0 1
user_121587570
Related