arielle arielle - 1 month ago 6
R Question

R: Extract values that have the same row and header names

I am trying to extract the values (from a table) that have the same row and column headers. For example, I have a table like this:

A B B C D E E
A 1 2 3 4 5 6 7
B 8 9 10 11 12 13 14
C 15 16 17 18 19 20 21
D 22 23 24 25 26 27 28
E 29 30 31 32 33 34 35


And I want to extract values to get something like this:

A 1
B 9
B 10
C 18
D 26
E 34
E 35


Any ideas on how I could do this? It has to work for any number of rows and columns, and there could be more than two columns with same headers.

Thanks!

Answer

You can sapply across the row/column names to test equality, and then use that index to assemble the data.frame:

i <- sapply(colnames(mat), `==`, rownames(mat))    # or i <- outer(rownames(mat), colnames(mat), `==`)

i
##          A     B     B     C     D     E     E
## [1,]  TRUE FALSE FALSE FALSE FALSE FALSE FALSE
## [2,] FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE
## [3,] FALSE FALSE FALSE  TRUE FALSE FALSE FALSE
## [4,] FALSE FALSE FALSE FALSE  TRUE FALSE FALSE
## [5,] FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE

df <- data.frame(name = colnames(mat)[colSums(i) > 0], value = mat[i])

df
##   name value
## 1    A     1
## 2    B     9
## 3    B    10
## 4    C    18
## 5    D    26
## 6    E    34
## 7    E    35

Data

mat <- structure(c(1L, 8L, 15L, 22L, 29L, 2L, 9L, 16L, 23L, 30L, 3L, 
    10L, 17L, 24L, 31L, 4L, 11L, 18L, 25L, 32L, 5L, 12L, 19L, 26L, 
    33L, 6L, 13L, 20L, 27L, 34L, 7L, 14L, 21L, 28L, 35L), .Dim = c(5L, 
    7L), .Dimnames = list(c("A", "B", "C", "D", "E"), c("A", "B", 
    "B", "C", "D", "E", "E")))
Comments