Summarize the rows of rows with R?

Question

My input

df1 <- data.frame(Row=c("row1", "row2", "row3", "row4", "row5"), A=c(1,2,3,5.5,5), B=c(2,2,2,2,0.5), C= c(1.5,0,0,2.1,3)) 

It looks like this:

 # Row1 1 2 1.5 # Row2 2 2 0 # Row3 3 2 0 # Row4 5.5 2 2.1 # Row5 5 0.5 3 

I want to get the sum of all these pairs of rows with the following equation. Suppose for the pairs Row1 and Row2: I want to multiply each column entry and sum them into one final answer, for example

• Answer Row1-Row2 (1*2) + (2*2)+ (1.5 *0) = 6
• Answer Row1-Row3 (1*3) + (2*2) + (1.5*0) = 7

I want to do all the analysis for each pair of rows and get a frame of the result data as follows:

 row1 row2 6 row1 row3 7 row1 row4 12.65 row1 row5 10.5 row2 row3 10 row2 row4 15 row2 row5 11 row3 row4 20.5 row3 row5 16 row4 row5 34.8 

How can I do this with R? Thanks so much for the comments.

Answer 1

• Create all the combinations you need with combn . t used to transpose the matrix, as you expect, to format it.
• Use apply to iterate over the indexes created in step 1. Note that we use negative indexing, so we are not trying to summarize the Row columns.
• Link the two results together.

`

 ind <- t(combn(nrow(df1),2)) out <- apply(ind, 1, function(x) sum(df1[x[1], -1] * df1[x[2], -1])) cbind(ind, out) out [1,] 1 2 6.00 [2,] 1 3 7.00 [3,] 1 4 12.65 ..... 

Answer 2

Yes! This is matrix multiplication !: -))

First, just to prepare the matrix:

 m = as.matrix(df1[,2:4]) row.names(m) = df1$Row 

and this operation is how easy!

 m %*% t(m) 

What is it!

One tip is that you can define data.frame this way and it will save the row.names command:

 df1 <- data.frame(row.names=c("row1", "row2", "row3", "row4", "row5"),A=c(1,2,3,5.5,5), B=c(2,2,2,2,0.5), C= c(1.5,0,0,2.1,3))