I have a dataframe that looks like this
Country <- rep(c("Austria", "Austria","Belgium", "Belgium", "Spain", "Slovenia", "France"), times=3)
Institute <- rep(c("Inst 1","Inst 2","Inst 3","Inst 4","Inst 5","Inst 6","Inst 7"), times=3)
Ans <- rep(c(1,2,3,1,NA,2,2),times=3)
Category.1 <- rep(c("Cat 1", "Cat 2", "Cat 2", "Cat 2","Cat 2", "Cat 1", "Cat 1"),times=3)
Category.2 <- rep(c("P", "L", "M", "P", "P", "L", "M"),times=3)
qs <- c(rep("Q1.a-Some Text", times=7),rep("Q1.b-Some Text", times=7), rep("Q1.c-Some Text", times=7))
df <- data.frame(Country=Country,Institute=Institute, Category.1=Category.1, Category.2=Category.2, qs=qs, Ans=Ans)
df<-df %>% spread(qs,Ans)
head(df)
Country Institute Category.1 Category.2 Q1.a-Some Text Q1.b-Some Text Q1.c-Some Text
1 Austria Inst 1 Cat 1 P 1 1 1
2 Austria Inst 2 Cat 2 L 2 2 2
3 Belgium Inst 3 Cat 2 M 3 3 3
4 Belgium Inst 4 Cat 2 P 1 1 1
5 France Inst 7 Cat 1 M 2 2 2
6 Slovenia Inst 6 Cat 1 L 2 2 2
Short explanation of the dataframe: There is some question, say Q1, and for this question there are multiple "Sub-questions", say a,b,c, where for each of these "sub-question/options" respondents were asked to answer using some scale, in this example from 1 to 3. My scope is to calculate the relative frequencies for each subquestion, of each response. So, I use this function:
multichoice<-function(data, question.prefix){
index<-grep(question.prefix, names(data)) # identifies the index for the available options in Q.12
cases<-length(index) # The number of possible options / columns
# Identify the range of possible answers for each question
# Step 1. Search for the min in each col and across each col choose the min
# step 2. Search for the max in each col and across each col choose the max
mn<-min(data[,index[1:cases]], na.rm=T)
mx<-max(data[,index[1:cases]], na.rm=T)
d = colSums(data[, index] != 0, na.rm = TRUE) # The number of elements across column vector, that are different from zero.
vec<-matrix(,nrow=length(mn:mx),ncol=cases)
for(j in 1:cases){
for(i in mn:mx){
vec[i,j]=sum(data[, index[j]] == i, na.rm = TRUE)/d[j] # This stores the relative responses for option j for the answer that is i
}
}
vec1<-as.data.frame(vec)
names(vec1)<-names(data[index])
vec1<-t(vec1)
return(vec1)
}
Calling, the function I get the desired dataframe.
q1 <- as.data.frame(multichoiceq4(df,"^Q1"))
head(q1)
V1 V2 V3
Q1.a-Some Text 0.3333333 0.5 0.1666667
Q1.b-Some Text 0.3333333 0.5 0.1666667
Q1.c-Some Text 0.3333333 0.5 0.1666667
Which shows that for option "a", 33% of participants answered with 1, 50% with 2 etc ...
My QUESTION
I want to calculate, the same but conditional on the categories. So, I want to see how the relative frequencies will look like based on category1, category2. Can someone suggest me something on how I can do this ?
I think you could make your code more flexible by keeping your data in a long format (that is, don't do df<-df %>% spread(qs,Ans)) and using dplyr, e.g.:
This part essentially reproduces functionality of your multichoice function:
df %>%
group_by(qs,Ans) %>%
summarize(total=n()) %>%
filter(!is.na(Ans)) %>%
mutate(frac=total/sum(total)) %>%
dcast(qs~Ans,value.var='frac')
# qs 1 2 3
# 1 Q1.a-Some Text 0.3333333 0.5 0.1666667
# 2 Q1.b-Some Text 0.3333333 0.5 0.1666667
# 3 Q1.c-Some Text 0.3333333 0.5 0.1666667
And this one gives an example how it can be modified to take into account categories.
df %>%
group_by(qs,Category.1,Ans) %>%
summarize(total=n()) %>%
filter(!is.na(Ans)) %>%
mutate(frac=total/sum(total)) %>%
dcast(qs~Ans+Category.1,value.var='frac')
# qs 1_Cat 1 1_Cat 2 2_Cat 1 2_Cat 2 3_Cat 2
# 1 Q1.a-Some Text 0.3333333 0.3333333 0.6666667 0.3333333 0.3333333
# 2 Q1.b-Some Text 0.3333333 0.3333333 0.6666667 0.3333333 0.3333333
# 3 Q1.c-Some Text 0.3333333 0.3333333 0.6666667 0.3333333 0.3333333