Build a square adjacency matrix from data.frame or data.table

Question

I am trying to build a square matrix adjacency from data.table . Here is a reproducible example of what I already have:

 require(data.table) require(plyr) require(reshape2) # Build a mock data.table dt <- data.table(Source=as.character(rep(letters[1:3],2)),Target=as.character(rep(letters[4:2],2))) dt # Source Target #1: ad #2: bc #3: cb #4: ad #5: bc #6: cb sry <- ddply(dt, .(Source,Target), summarize, Frequency=length(Source)) sry # Source Target Frequency #1 ad 2 #2 bc 2 #3 cb 2 mtx <- as.matrix(dcast(sry, Source ~ Target, value.var="Frequency", fill=0)) rownames(mtx) <- mtx[,1] mtx <- mtx[,2:ncol(mtx)] mtx # bcd #a "0" "0" "2" #b "0" "2" "0" #c "2" "0" "0" 

Now this is very close to what I want to get, except that I would like to have all the nodes represented in both dimensions, for example:

  abcd a 0 0 0 2 b 0 0 2 0 c 0 2 0 0 d 0 0 0 0 

Please note that I am working on fairly large data, so I would like to find an effective solution for this.

Thank you for your help.

---

SOLUTIONS (EDIT):

Given the quality of the proposed solutions and the size of my data set, I compared all the solutions.

 #The bench was made with a 1-million-row sample from my original dataset library(data.table) aa <- fread("small2.csv",sep="^") dt <- aa[,c(8,9),with=F] colnames(dt) <- c("Source","Target") dim(dt) #[1] 1000001 2 levs <- unique(unlist(dt, use.names=F)) length(levs) #[1] 2222 

Given this data, the desired result is a 2222 * 2222 matrix (2222 * 2223 solutions in which the first column contains row names are also obviously acceptable).

 # Ananda Mahto first solution am1 <- function() { table(dt[, lapply(.SD, factor, levs)]) } dim(am1()) #[1] 2222 2222 # Ananda Mahto second solution am2 <- function() { as.matrix(dcast(dt[, lapply(.SD, factor, levs)], Source~Target, drop=F, value.var="Target", fun.aggregate=length)) } dim(am2()) #[1] 2222 2223 library(dplyr) library(tidyr) # Akrun solution akr <- function() { dt %>% mutate_each(funs(factor(., levs))) %>% group_by(Source, Target) %>% tally() %>% spread(Target, n, drop=FALSE, fill=0) } dim(akr()) #[1] 2222 2223 library(igraph) # Carlos Cinelli solution cc <- function() { g <- graph_from_data_frame(dt) as_adjacency_matrix(g) } dim(cc()) #[1] 2222 2222 

And the test result ...

 library(rbenchmark) benchmark(am1(), am2(), akr(), cc(), replications=75) # test replications elapsed relative user.self sys.self user.child sys.child # 1 am1() 75 15.939 1.000 15.636 0.280 0 0 # 2 am2() 75 111.558 6.999 109.345 1.616 0 0 # 3 akr() 75 43.786 2.747 42.463 1.134 0 0 # 4 cc() 75 46.193 2.898 45.532 0.563 0 0 

Answer 1

It sounds like you're just looking for a table , but you need to make sure that both columns have the same factor levels:

 levs <- unique(unlist(dt, use.names = FALSE)) table(lapply(dt, factor, levs)) # Target # Source abcd # a 0 0 0 2 # b 0 0 2 0 # c 0 2 0 0 # d 0 0 0 0 

---

I don’t know if it will offer any speed improvements, but you can also use dcast from "data.table":

 dcast(lapply(dt, factor, levs), Source ~ Target, drop = FALSE, value.var = "Target", fun.aggregate = length) 

Answer 2

You can also use igraph . Since you said you were dealing with big data, igraph has the advantage of using sparse matrices:

 library(igraph) g <- graph_from_data_frame(dt) as_adjacency_matrix(g) 4 x 4 sparse Matrix of class "dgCMatrix" abcd a . . . 2 b . . 2 . c . 2 . . d . . . . 

Answer 3

We can use dplyr/tidyr

 library(dplyr) library(tidyr) dt %>% mutate_each(funs(factor(., letters[1:4]))) %>% group_by(Source, Target) %>% tally() %>% spread(Target, n, drop=FALSE, fill=0) # Source abcd # (fctr) (dbl) (dbl) (dbl) (dbl) #1 a 0 0 0 2 #2 b 0 0 2 0 #3 c 0 2 0 0 #4 d 0 0 0 0