Creating Contingency Tables for Multiple Choice Survey Data

Multiple choice questions, or “multi-code” questions, are very common in survey analysis and in itself adds a level of complication in analysis. A simple example of a multi-code question is this:

Q11. Which of the following best describes your experience with these brands?

SHOW BRANDS ONE BY ONE: [Brand A, Brand B…]

0. I have never heard of… [BRAND]
1. I have heard of [BRAND] but have never bought from them
2. I have bought from [BRAND]

Typically, the output of the data would look something like this:

This is a simulated data set where each column represents the scores for each brand, and each row is a respondent’s answer for that brand.

One of the key things that we want to quickly find out from this data is whether some brands perform better on penetration (Q11x = 2) and brand awareness (Q11x = 1+2). To do this we would want to look at the column percentages for each column (brand) and yet have the percentages organised in a way that will allow us to easily compare differences across brands.

In my own attempts to find a solution for this, I’ve found the table() function in base R and CrossTable() in the gmodels package are on their own insufficient for analysing multi-code questions, since they seem to only work for single-code questions. I’ve thus proceeded to try and produce my own functions.

First, I would format the data so that each variable would be a factor rather than numeric:

Q11 %<>% lapply(as.factor) %<>%
  data.frame()
glimpse(Q11)

Observations: 500
Variables: 5
$ Q11a <fctr> 1, 2, 1, 2, 1, 2, 0, 2, 0, 0, 2, 1, 2, 0, 0, 0, 0, 1, 0, 2, 1, 0, 1, 0, 0, 0, 1, 1, ...
$ Q11b <fctr> 0, 1, 2, 2, 0, 0, 1, 2, 0, 1, 2, 0, 2, 2, 2, 2, 0, 0, 0, 2, 0, 0, 0, 2, 1, 1, 1, 0, ...
$ Q11c <fctr> 0, 1, 2, 1, 1, 2, 2, 2, 2, 2, 1, 1, 2, 1, 1, 1, 1, 0, 1, 2, 1, 2, 0, 0, 0, 2, 2, 0, ...
$ Q11d <fctr> 2, 2, 1, 0, 2, 0, 0, 0, 2, 1, 1, 1, 0, 2, 2, 2, 1, 1, 1, 1, 1, 1, 2, 0, 0, 2, 2, 1, ...
$ Q11e <fctr> 2, 0, 2, 2, 2, 2, 2, 0, 0, 0, 0, 2, 2, 1, 2, 0, 0, 1, 2, 1, 0, 0, 1, 2, 0, 2, 2, 1, ...

My next step would be to write two functions: one for producing a contingency table with column percentages for each brand (similar to what prop.table(table()) does), and another for creating a “multi-code” version of the table which allows you to compare the column percentages across all the brands. Here are the two functions:

ctab <- function(vec){
  var<-list(0)
  prop<-list(0)
  tab<-data.frame(Q=0,Prop=0)
  for(i in 1:nlevels(vec)){
    var[i]<-levels(vec)[i]
    prop[i]<-mean(vec==levels(vec)[i])
  }
  var <- unlist(var)
  prop <- unlist(prop)
  tab <- cbind(var,prop) %>% as.data.frame()
  names(tab)<-c("Q","Prop")
  tab
}

ctab.mc <- function(x){
  ind.tabs <-list()
  x.codes <- ncol(x)
  x.names <- names(x)
  for(i in 1:x.codes){
    ind.tabs[[i]]<-ctab(x[,i])
  }
  all.merged <-merge(x=ind.tabs[[1]],y=ind.tabs[[2]],by="Q")
  for(i in 3:x.codes){
    all.merged<-merge(x=all.merged,y=ind.tabs[[i]],by="Q")
  }
  for(i in 1:x.codes){
    names(all.merged)[i+1]<-x.names[i]
  }
  
  all.merged
}

Let’s now have a look at what these functions output respectively. When the responses for Brand A is passed through to the ctab function, we get a contingency table showing the percentage of respondents answering responses 0, 1, or 2 for Brand A:

ctab(Q11$Q11a)

And similarly for Brand B:

ctab(Q11$Q11b)

The second function ctab.mc() saves time by merging all of these individual contingency tables into a single combined contingency table. Instead of passing a column/vector in the argument, this time instead we will pass the whole data frame (where all columns are multi-code data, no ID columns etc.). This is what it looks like in action:

ctab.mc(Q11)

This way, we can easily see that penetration is the highest for Brand C, since it has the highest % for code 2. It is also the brand scoring the highest on brand awareness, since its combined % for codes 1 and 2 is also the highest.

It’s likely that there are other solutions out there that can do this job more elegantly and efficiently, but hopefully this example would still be useful in showing how custom functions can help solve problems when a ready-made solution isn’t known.