How to run the table function in R across multiple variables and compile the results into a new dataset using a function?

Question

I have a dataset of about 100 variables and I would like to build a table with summaries of approximately 30 of these variables. To do this, I've manually run the table and other functions on these variables and rbinded the results. However, since I need to do this for 30+ vars, I would like to automate the process using a function.

Here is an example dataset:


df <- data.frame(v1=c('a','b','c','c','b'),v2=c('d','d','e','e','e'),cat=c('1low','1low','2med','3high','2med'))

The goal is to create a table like below (without the NAs). Example of final table

Below is my code:

library(formattable)

# For var1 & var2, apply the table function and convert to dataframe so that the row labels are incorporated into dataset
var1.df <- as.data.frame(table(df$v1, df$cat))

# reshape to achive wide format (goal to view the count of each var1 level across low, med, high cats)
var1.df <- reshape(var1.df, idvar = "Var1", timevar = "Var2", direction = "wide")

# add col names
names(var1.df) <- c("vcat","low","med","high"); var1.df

# repeat above steps for next variable. in true dataset, I will need to repeat for 30 vars...
var2.df <- as.data.frame(table(df$v2, df$cat))
var2.df <- reshape(var2.df, idvar = "Var1", timevar = "Var2", direction = "wide")
names(var2.df) <- c("vcat","low","med","high")

# Create variable headings
var1.heading <- data.frame("variable 1",NA,NA,NA) # ideally, the NAs are blanks
names(var1.heading) <- c("vcat","low","med","high")

var2.heading <- data.frame("variable 2","","","")
names(var2.heading) <- c("vcat","low","med","high")

# Rbind the category headings and the table result data
table01 <- do.call("rbind", list(var1.heading, var1.df, 
                                 var2.heading, var2.df))

# Format the table for presentation
heading.list <- c("variable 1", "variable 2")
x <- formattable(table01, 
                 align =c("l","c","c","c","c"),
                 list(vcat = formatter("span", style = x ~ ifelse(x %in% heading.list, 
                                                                  style(font.weight = "bold"), NA))))

My below attemps to automate the above code are either incomplete (a) or do not run properly (b)

# (a)
lapply(df, function(x) as.data.frame(table(x, df$cat)))

# (b)
myfxn <- function(x){
  y <- as.data.frame(table(x, df$cat))
  y <- reshape(y, idvar = "x", timevar = "Var2", direction = "wide")
  names(y) <- c("vcat","low","med","high")
}
lapply(df, myfxn(x))

Any suggestions as to how I can automate this process for several more variables? Also, is there another way to insert category headings in the table other than manually creating an inserting a one line dataframe? Note that I inserted NAs in var1.heading since it's the first dataframe; when I tried inserting "" instead of blanks (like var2.heading), the subsequent dataframes wouldn't bind because they were factor vars, not character. Thank you very much in advance!

user_2994949 · Answer

I would start with your b attempt since it was very close. I think the only reason you are reshaping is because data.frame(table() which you don't need to do if you drop the class "table" from tables.

I would also try to complete the entire manipulation for one variable in the function, ie, adding headers, labels, etc. This way you can test your function on a single variable to make sure it is doing exactly what you want, then start to loop over all variables.

# (b)
myfxn <- function(x, header = 'variable') {
  y <- unclass(table(x, df$cat))
  colnames(y) <- gsub('\d', '', colnames(y))
  y <- data.frame(vcat = rownames(y), y, stringsAsFactors = FALSE)
  rbind(c(header, rep('', ncol(y) - 1)), y)
}

myfxn(df$v1)
#       vcat low med high
# 1 variable             
# a        a   1   0    0
# b        b   1   1    0
# c        c   0   1    1

Next, I would use Map or mapply instead of lapply to pass multiple arguments to myfxn

l <- Map(myfxn, df[-3], heading.list)

formattable(
  do.call('rbind', l), row.names = FALSE,
  align = c('l', rep('c', nlevels(df$cat))),
  list(
    vcat = formatter('span', style = x ~ ifelse(x %in% heading.list, style(font.weight = 'bold'), NA))
  )
)

## apply for 30 variables
heading.list <- sprintf('variable %s', 1:30)
l <- Map(myfxn, df[sample(1:2, 30, TRUE)], heading.list)

formattable(
  do.call('rbind', l), row.names = FALSE,
  align = c('l', rep('c', nlevels(df$cat))),
  list(
    vcat = formatter('span', style = x ~ ifelse(x %in% heading.list, style(font.weight = 'bold'), NA))
  )
)

How to run the table function in R across multiple variables and compile the results into a new dataset using a function?

1 Answers