Generate Cross-table in R with mean and SD

Question

I have a large dataset for which I need to generate multiple cross-tables. These are particularly two dimensional tables to generate frequencies along with mean and SD.

So give an example I have the below data -

City <- c("A","B","A","A","B","C","D","A","D","C")
Q1 <- c("Agree","Agree","Agree","Agree","Agree","Neither","Neither","Disagree","Agree","Agree")
df <- data.frame(City,Q1)

Keeping the data in mind, I want to generate a cross-table with mean as below -

    City            
        A   B   C   D
Agree   3   2   1   1
Neither         1   1
Disagree    1           
Total   4   2   2   2
Mean    2.5 3   2.5 2.5

When generating the mean, Agree is given a weight of 3, Neither is given a weight of 2 and Disagree is a given a weight of 1. The cross-table output should have the mean just below the Total column. It would be good to have gridlines between each column and row.

Can you please suggest how to achieve this in R?

Answer 1

Here's a possible solution using addmargins which allows you to pass predefined functions to your table result

wm <- function(x) sum(x * c(3, 1, 2)) / sum(x)
addmargins(table(df[2:1]), 1, list(list(Total = sum, Mean = wm)))

#           City
# Q1           A   B   C   D
#   Agree    3.0 2.0 1.0 1.0
#   Disagree 1.0 0.0 0.0 0.0
#   Neither  0.0 0.0 1.0 1.0
#   Total    4.0 2.0 2.0 2.0
#   Mean     2.5 3.0 2.5 2.5

If you want SD to, you can simply add , SD = sd to the functions list

Answer 2

Here's a solution:

x <- table(df$Q1, df$City) #building basic crosstab
#assigning weights to vector
weights <- c("Agree" = 3, "Disagree" = 1, "Neither" = 2)
#getting weighted mean
weightedmean <- apply(x, 2, function(x) {sum(x * weights)/sum(x)})
#building out table
x <- rbind(x,
           apply(x, 2, sum), #row sums
           weightedmean)
rownames(x)[4:5] <- c("Total", "Mean")