title: Build a square adjacency matrix from data.frame or data.table
body: <p>I am trying to build a square adjacency <code>matrix</code> from a <code>data.table</code>.
  Here is a reproducible example of what I already have :</p>
  
  <pre><code>require(data.table)
  require(plyr)
  require(reshape2)
  # Build a mock data.table
  dt &lt;- data.table(Source=as.character(rep(letters[1:3],2)),Target=as.character(rep(letters[4:2],2)))
  dt
  #   Source Target
  #1:      a      d
  #2:      b      c
  #3:      c      b
  #4:      a      d
  #5:      b      c
  #6:      c      b
  sry &lt;- ddply(dt, .(Source,Target), summarize, Frequency=length(Source))
  sry
  #  Source Target Frequency
  #1      a      d         2
  #2      b      c         2
  #3      c      b         2
  mtx &lt;- as.matrix(dcast(sry, Source ~ Target, value.var="Frequency", fill=0))
  rownames(mtx) &lt;- mtx[,1]
  mtx &lt;- mtx[,2:ncol(mtx)]
  mtx
  #  b   c   d
  #a "0" "0" "2"
  #b "0" "2" "0"
  #c "2" "0" "0"
  </code></pre>
  
  <p>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, like :</p>
  
  <pre><code>  a b c d
  a 0 0 0 2
  b 0 0 2 0
  c 0 2 0 0
  d 0 0 0 0
  </code></pre>
  
  <p>Note that I am working on quite large data, so I'd like to find an efficient solution for this.</p>
  
  <p>Thank you for your help.</p>
  
  <hr>
  
  <p><strong>SOLUTIONS</strong> (EDIT) :</p>
  
  <p>Given the quality of the solutions offered and the size of my dataset, I benchmarked all the solutions.</p>
  
  <pre><code>#The bench was made with a 1-million-row sample from my original dataset
  library(data.table)
  aa &lt;- fread("small2.csv",sep="^")
  dt &lt;- aa[,c(8,9),with=F]
  colnames(dt) &lt;- c("Source","Target")
  dim(dt)
  #[1] 1000001       2
  levs &lt;- unique(unlist(dt, use.names=F))
  length(levs)
  #[1] 2222
  </code></pre>
  
  <p>Given this data, the desired output is a 2222*2222 matrix (2222*2223 solutions where the first column contains the row <em>names</em> are also obviously acceptable).</p>
  
  <pre><code># Ananda Mahto's first solution
  am1 &lt;- function() {
      table(dt[, lapply(.SD, factor, levs)])
  }
  dim(am1())
  #[1] 2222 2222
  
  # Ananda Mahto's second solution
  am2 &lt;- 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's solution
  akr &lt;- function() {
      dt %&gt;%
         mutate_each(funs(factor(., levs))) %&gt;%
         group_by(Source, Target) %&gt;%
         tally() %&gt;%
         spread(Target, n, drop=FALSE, fill=0)
  }
  dim(akr())
  #[1] 2222 2223
  
  library(igraph)
  # Carlos Cinelli's solution
  cc &lt;- function() {
      g &lt;- graph_from_data_frame(dt)
      as_adjacency_matrix(g)
  }
  dim(cc())
  #[1] 2222 2222
  </code></pre>
  
  <p>And the result of the benchmark is…</p>
  
  <pre><code>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
  </code></pre>