Sum in a Cross Table

Question

I am new in R. Apologies if the questions is too silly.

I have a sample input data table as follows.

Column-1 Column-2 Column-3 
 FF       FF       2
 NN       NN       5
 FF       FF       2
 NN       NN       1
 FN       FF       3

Output should be

       FF   FN  NF  NN
    FF  4   0   0   0
    FN  3   0   0   0
    NF  0   0   0   0
    NN  0   0   0   6

I have used table() function, but it only gives "count", and not "sum". Appreciate any help!

Answer 1

With the little known xtabs function. Your data:

l <- c("FF", "FN", "NF", "NN")

data <- data.frame(
   Column1 = factor(c("FF", "NN", "FF", "NN", "FN"), levels = l),
   Column2 = factor(c("FF", "NN", "FF", "NN", "FF"), levels = l),
   Column3 = c(2, 5, 2, 1, 3))

---

xtabs(Column3 ~ ., data)
#        Column2
# Column1 FF FN NF NN
#      FF  4  0  0  0
#      FN  3  0  0  0
#      NF  0  0  0  0
#      NN  0  0  0  6

The output is a table but you can wrap it into as.matrix to get a matrix.

Answer 2

You can use the daply function from plyr :

R> daply(mydf, .(Column.1, Column.2), summarize, sum(Column.3))
        Column.2
Column.1 FF   NN  
      FF 4    NULL
      FN 3    NULL
      NN NULL 6 

And here is a solution with base R :

res <- with(mydf,
     by(Column.3, list(Column.1, Column.2), FUN=sum))
res <- as.table(res)
res[is.na(res)] <- 0

Which gives :

   FF NN
FF  4  0
FN  3  0
NN  0  6