Create an adjacency matrix from a csv file of three variables

Question

So I have a csv file with three variables 'Team 1' 'Team 2' and 'Winner.' I want to create an adjacency matrix that has rownames=colnames. Is there any way to get this done? This is what I want:

   A B C
A  0 2 1    
B  1 0 3    
C  2 4 0

So this particular matrix would indicate that A won from B 1 time and B from A 2 times and so on. The rownames indicate the winners.

For example, if my data looks like this:

   Team A       Team B      Winner
  Germany    Argentina     Germany
  Croatia      Germany     Croatia
Argentina      Croatia   Argentina

would give the matrix

            Germany Argentina   Croatia 
Germany           0         0         1  
Argentina         1         0         0  
Croatia           0         1         0

My code

data = as.matrix(read.csv("data.csv"))

labels = unique(c(data[,1],data[,2]))
A = matrix(0, length(labels),length(labels))

rownames(A) = colnames(A) <- labels
A

This creates the adjacency matrix, but how do I fill it in?

Answer 1

You could use table to pull out the results.

First, you may want to set common levels for all teams

lvs <- sort(as.character(unique(unlist(d))))
d[] <- lapply(d, factor, levels=lvs)

Then table the data

res <- table(d[c("Team.A", "Winner")]) + table(d[c("Team.B", "Winner")])    
diag(res) <- 0
res

#            Winner
# Team.A      Argentina Croatia Germany
#   Argentina         0       0       1
#   Croatia           1       0       0
#   Germany           0       1       0

If you want a specific order, you could set the variables to factor before using table, or you can change the order after

vars <- c("Germany", "Argentina","Croatia")
res[vars, vars]

Data

d <- read.table(header=T, text="'Team A'       'Team B'      Winner
  Germany    Argentina     Germany
  Croatia      Germany     Croatia
Argentina      Croatia   Argentina")