Transforming matrix of presence/absence to Data.frame of vertice connection. (Removing duplicated rows with eeuqal unordered values)

user_7479014 0
Viewed 174

I have a matrix table where rows indicate a site, and columns indicate the presence/absence of a particular rock.

>Mat
       A   B   C   D   E   F   G
Aiz    1   0   0   0   0   0   0
Aren   0   1   1   0   1   0   0
Atx    0   0   1   0   1   0   0
Berr   1   1   0   0   0   1   0
Bra    0   0   0   0   0   1   0
Bur    0   1   0   0   1   0   0
Cab    1   0   1   1   1   0   0

As you can see, there are some rows that have the same elements in certain columns, eg. row Aiz and row Berr, share a presence in column A, which indicates me that they share one same rock in my example.

From this matrix I want to construct and undirected graph where the nodes are the sites (the rownames) and the links are the share of an element of a column.

So, basically, I need to transform this matrix into a data.frame of the style:

>DF
   siteA    siteB weight
1    Aiz    Berr     1
2    Aiz     Cab     1
3   Aren   Atxos     2
4   Aren    Berr     1
5   Aren     Bur     2       
6   Aren     Cab     1
7    Atx     Bur     1
...

Where each row identifies two sites that share a same rock (presence in a same column of original Mat) and with column weight, representing the number of rocks that both sites have in common.

So, through a series of nested for loops, and if conditions, I manage to arrive close to the DF matrix, although my DF has rows with repeated results, eg:

> df_links
   siteA    siteB weight
1    Aiz    Berr     1
2    Aiz     Cab     1
3   Aren   Atxos     2
4   Berr     Aiz     1
5  Atxos    Aren     2

where you see, eg: row 1 and row 4 (same for row 3 and row 5) share the same elements for the site columns. And since this is for an undirected graph, having Aiz- Berr or Ber - Aiz would mean the same, therefore I would only need one of the rows.

Q 1: So, I tried to solve the repetition problem with tidyverse, but nothing seemed to work. At best I would get rid of only one of the repeating rows and not all. So, my question is, is there a way where I can do this? To retain only one of the rows that have the same elements [i,j], regardless of order?

Q 2: this might be a bit more cumbersome, so it comes in second. Even though my code works (until the problem pointed above in Q1), its not the prettiest to look at. Is a sequence of matrix to data.frames to data.frames with for loops, and conditions trown in. Is there a tiddier version to go from the original Mat to the desired DF?? I´m not that well versed in sapply and the whole family, so I used the loops. Any quicker and better looking solution?

dataframe repeat r matrix
2 Answers

To remove duplicated entries of 2 interchangeable columns, first reorder then remove duplicated rows of your dataframe.

df_links <- transform(df_links, siteA = pmin(siteA, siteB), 
                  siteB = pmax(siteA, siteB))
unique(df_links)
#  siteA siteB weight
#1   Aiz  Berr      1
#2   Aiz   Cab      1
#3  Aren Atxos      2

To avoid having to remove duplicated pairs of vertices in an undirected graph, you may use the dist function, which is very helpful when dealing with similarity between pairs of vectors. As you define the similarity (or weight) to be the number of common rocks between sites (or vectors), you need to pass a custom function, which can be done using dist from the package proxy.

#The similarity is the number of matching '1'
similarityMatrix <- as.matrix(proxy::dist(Mat, method = function(x,y){
  length(which(x+y==2))
}))
similarityMatrix
#     Aiz Aren Atx Berr Bra Bur Cab
#Aiz    0    0   0    1   0   0   1
#Aren   0    0   2    1   0   2   2
#Atx    0    2   0    0   0   1   2
#Berr   1    1   0    0   1   1   1
#Bra    0    0   0    1   0   0   0
#Bur    0    2   1    1   0   0   1
#Cab    1    2   2    1   0   1   0

There, you have a similarity matrix between all pairs of sites. Since the graph you want to build is undirected, you need to select from this matrix each pair only once.

#Unique pairwise combinations of different vectors
combinations <- t(combn(colnames(similarityMatrix), 2))
pairwiseSites <- data.frame(combinations, similarityMatrix[combinations])

colnames(pairwiseSites) <- c("siteA", "siteB", "weight")

pairwiseSites
#   siteA siteB weight
#1    Aiz  Aren      0
#2    Aiz   Atx      0
#3    Aiz  Berr      1
#4    Aiz   Bra      0
#5    Aiz   Bur      0
#6    Aiz   Cab      1
#7   Aren   Atx      2
#8   Aren  Berr      1
#9   Aren   Bra      0
#10  Aren   Bur      2
#11  Aren   Cab      2
#12   Atx  Berr      0
#13   Atx   Bra      0
#14   Atx   Bur      1
#15   Atx   Cab      2
#16  Berr   Bra      1
#17  Berr   Bur      1
#18  Berr   Cab      1
#19   Bra   Bur      0
#20   Bra   Cab      0
#21   Bur   Cab      1

Raw data

Mat <- read.table(header=TRUE, text="
A   B   C   D   E   F   G
Aiz    1   0   0   0   0   0   0
Aren   0   1   1   0   1   0   0
Atx    0   0   1   0   1   0   0
Berr   1   1   0   0   0   1   0
Bra    0   0   0   0   0   1   0
Bur    0   1   0   0   1   0   0
Cab    1   0   1   1   1   0   0")

df_links <- read.table(header=TRUE, text="
siteA    siteB weight
1    Aiz    Berr     1
2    Aiz     Cab     1
3   Aren   Atxos     2
4   Berr     Aiz     1
5  Atxos    Aren     2")
user_140277750

The problem can be solved using the purrr package.

# reproduce input
mat <- matrix(
  data = c(1,0,0,0,0,0,0,
           0,1,1,0,1,0,0,
           0,0,1,0,1,0,0,
           1,1,0,0,0,1,0,
           0,0,0,0,0,1,0,
           0,1,0,0,1,0,0,
           1,0,1,1,1,0,0), nrow = 7, ncol = 7)
colnames(mat) <- LETTERS[1:7]
rownames(mat) <- c("Aiz", "Aren", "Atx", "Berr", "Bra", "Bur", "Cab")

# convert to dataframe
df <- mat %>% 
  dplyr::as_tibble() %>% 
  dplyr::bind_cols(
    tibble::tibble(Names = rownames(mat)))

# calculate the connections
purrr::map_df(df$Names, function(x){
  output <-purrr::map_df(df$Names, function(y){
    if(x >= y) return(tibble::tibble()) # avoid double counting
    tibble::tibble(
      siteA = x,
      siteB = y,
      weight = sum(as.integer(df[df$Names==x,1:7]) & as.integer(df[df$Names==y,1:7])))
    })
  })

good luck

user_90257150
Related