R igraph edgelist to adjacency matrix How to trim 0 chunks?

Question

I have an edge list with 3 columns, akin to From, To, Weight, but it has no row/column names.

A1    B1    0.3
A1    B2    0.2
A1    B3    0.1
A2    B1    0.3
A2    B2    0.2
A2    B3    0.3
...   ...

Further detail: Col 1 ONLY has "Ax", and col 2 only has "Bx", they are meant to be different types. There will never be a true cross within Ax nor within Bx, e.g. there will never be a row like

A1    A3    0.2

Also, the resulting adjacency matrix is expected to be perfectly square, there are exactly as many A as there are B, and every A maps exactly once to every B, and vice versa.

Following other stackoverflow threads, I have applied

library(igraph)    
gdf <- graph.data.frame(edgelist)
adj_mat <- get.adjacency(gdf, sparse = FALSE, attr='X3')

However, this output is full of Ax-Ax and Bx-Bx pairs, i.e.

     A1    A2    A3
A1   0     0     0
A2   0     0     0
A3   0     0     0

before moving on to (also this is my desired result, without the leading and trailing chunks of 0s)

...  A1    A2    A3
B1   0.3   0.3   ...
B2   0.2   0.2   ...
B3   0.1   0.3   ...

Followed by a big square chunk of 0s with Bx-Bx crosses.

So this matrix is about 3x as big as it needs to be.

My ACTUAL adj_mat output in R, in case this helps:

                  query_0 query_1 query_2 query_3 query_4 query_5 query_6 query_7 reference_B reference_CD4_T
query_0                 0       0       0       0       0       0       0       0   0.9211076       0.9357135
query_1                 0       0       0       0       0       0       0       0   0.5982098       0.6531529
query_2                 0       0       0       0       0       0       0       0   0.9414440       0.9163261
query_3                 0       0       0       0       0       0       0       0   0.7776452       0.7595893
query_4                 0       0       0       0       0       0       0       0   0.1742785       0.4535070
query_5                 0       0       0       0       0       0       0       0   0.8929904       0.8692392
query_6                 0       0       0       0       0       0       0       0   0.8727388       0.9143452
query_7                 0       0       0       0       0       0       0       0   0.6672841       0.7709745
reference_B             0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_CD4_T         0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_CD8_T         0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_DC            0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_Mono          0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_NK            0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_other         0       0       0       0       0       0       0       0   0.0000000       0.0000000
reference_other_T       0       0       0       0       0       0       0       0   0.0000000       0.0000000
                  reference_CD8_T reference_DC reference_Mono reference_NK reference_other reference_other_T
query_0                 0.9795670    0.9616339      0.2036382    0.9735171       0.9868448         0.9683900
query_1                 0.9581395    0.9263749      0.8985018    0.8132070       0.7481512         0.9580464
query_2                 0.4993608    0.3923397      0.9837582    0.9125077       0.9859413         0.9322740
query_3                 0.9069418    0.8334141      0.9652273    0.1926747       0.9856065         0.9556887
query_4                 0.9415359    0.8987213      0.9075660    0.7965302       0.9804007         0.9542930
query_5                 0.3971887    0.5269301      0.9410492    0.9178430       0.9854246         0.6326137
query_6                 0.9752365    0.9528309      0.9084781    0.9701409       0.9274201         0.9644051
query_7                 0.9712323    0.9535511      0.9000940    0.9376305       0.8200538         0.9356329
reference_B             0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_CD4_T         0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_CD8_T         0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_DC            0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_Mono          0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_NK            0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_other         0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000
reference_other_T       0.0000000    0.0000000      0.0000000    0.0000000       0.0000000         0.0000000

In this particular case, I can get to the result I want with

adj_mat<- adj_mat[, -c(1:8)]
adj_mat<- adj_mat[-c(9:16),]

But I'm hoping to find something more generalized.

I don't strictly have to use igraph package, I'm open to using any other packages to convert from the edge list to the adjacency matrix, or even just magic data wrangling.

Do you want to reshape the data? tidyr::pivot_wider(df, names_from = From, values_from = Weight). Also see various options here stackoverflow.com/questions/5890584/… — Ronak Shah

ThomasIsCoding ThomasIsCoding · Accepted Answer · 2021-05-05T14:04:39

You probably need xtabs

> xtabs(Weight ~ ., df)
    To
From  B1  B2  B3
  A1 0.3 0.2 0.1
  A2 0.3 0.2 0.3

or

> as.data.frame.matrix(xtabs(Weight ~ ., df))
    B1  B2  B3
A1 0.3 0.2 0.1
A2 0.3 0.2 0.3

Data

> dput(df)
structure(list(From = c("A1", "A1", "A1", "A2", "A2", "A2"), 
    To = c("B1", "B2", "B3", "B1", "B2", "B3"), Weight = c(0.3,
    0.2, 0.1, 0.3, 0.2, 0.3)), class = "data.frame", row.names = c(NA,
-6L))

R igraph edgelist to adjacency matrix How to trim 0 chunks?

1 Answers