transform SQL adjacency list to R adjacency matrix

1
votes

I have a MySQL table pedigree that stores all my interconnecting parentage data as 2 adjacency lists:

Pedigree table

org_id INT UNSIGNED NOT NULL PRIMARY KEY,
dam_id INT UNSIGNED,
sire_id INT UNSIGNED,
FOREIGN KEY (org_id) REFERENCES organisms(org_id)
FOREIGN KEY (dam_id) REFERENCES organisms(org_id),
FOREIGN KEY (sire_id) REFERENCES organisms(org_id)
  • Any org_id may or may not have children. Number of children is unlimited.
  • Each org_id in the pedigree table is required to have at least a dam_id OR a sire_id
  • If an org_id has no parents, it will not be listed in the pedigree table except as a sire or dam
  • An org_id may have dam_id==sire_id

Sample Data

Org Dam Sire
23, 42, 57
26, 25, 25
27, 43, 43
28, 44, 44
30, 25, 25
31, 45, 25
32, 45, 45
33, 31, 32
34, 28, 59
35, 27, 28
36, 28, 28
39, 38, 34
41, 27, 24

I want to use R's igraph package (unless there is something more appropriate) to display a directed DAG of my pedigrees with ancestor nodes occurring above child nodes. I am unclear about exactly what igraph needs to do this. I think I need to generate an adjacency matrix from my adjacency list, but I'm at a loss as to how to do this efficiently.

Ideas?

1
rdata-structuresadjacency-listadjacency-matrix

1 Answers

1
votes

Consider using an edge list. Two column matrix with each edge as a row in the matrix. igraph will pull the names for the nodes from the values in the edge list as well.

g <- graph.edgelist(el)

And if you need the adjacency matrix for some reason

adj.mat <- get.adjacency(g)