Extract one triangle of correlation matrix with attributes

Question

I have dataframe / matrix of equal rows and columns. I want to extract only the top or bottom triangle.

x<-data.frame(matrix(1:25,nrow=5)) colnames(x)<-LETTERS[1:5] rownames(x)<-LETTERS[1:5] x[upper.tri(x,diag=F)] 

From this result it cannot be said which combination of column and row mattered. So, I would like to have row and column attributes in the results. Something like that:

 Col Row Val BA 6 CA 11 CB 12 ... 

I need to do this for a large correlation matrix. Thanks.

Answer 1

First, to make things unambiguous, I change

 colnames(x) <- LETTERS[6:10] 

Use expand.grid to get row and column names like this

 rowCol <- expand.grid(rownames(x), colnames(x)) 

To get the correct rows from this data frame, take

 labs <- rowCol[as.vector(upper.tri(x,diag=F)),] df <- cbind(labs, x[upper.tri(x,diag=F)]) colnames(df) <- c("Row","Col","Val") df[,c(2,1,3)] ## Col Row Val ## 6 GA 6 ## 11 HA 11 ## ... 

Answer 2

I would just use which with arr.ind = TRUE as follows:

 ind <- which( upper.tri(x,diag=F) , arr.ind = TRUE ) data.frame( col = dimnames(x)[[2]][ind[,2]] , row = dimnames(x)[[1]][ind[,1]] , val = x[ ind ] ) col row val 1 BA 6 2 CA 11 3 CB 12 4 DA 16 5 DB 17 6 DC 18 7 EA 21 8 EB 22 9 EC 23 10 ED 24 

Answer 3

... it could be a solution

 nam <-apply(ind, 2, function(y, x) rownames(x)[c(y)], x=x) cbind(nam, x[upper.tri(x,diag=F)]) 

Hth

Answer 4

The bottom triangle is defined by the expression "the column index is not greater than the row index." This code gives the bottom triangle (or the top, switching operator>) a value of 0. Use "" instead of 0 to save the triangle.

 x[!(col(x) > index(x))] <- 0 

To create a data set, as in the original message, I would use the functions reshape2 :: melt and dplyr: :( filter, select).

First create an id variable to melt.

 x$id <- rownames(x) 

Then

 melt(x, id = "id") %>% filter(value > 0 ) %>% select(Col = variable, Row = id, Val = value) Col Row Val 1 BA 6 2 CA 11 3 CB 12 4 DA 16 5 DB 17 6 DC 18 7 EA 21 8 EB 22 9 EC 23 10 ED 24