Convert a dataframe with 3 columns to a weighted adjacency matrix

Question

I am trying to make a matrix in R, based on a dataframe with 3 columns: 1st column is names of people in a group, 2nd column is also names of people in a group, 3rd column is a value that indicates how well the people match:

 Person1 Person2 Match
1     Amber Tiffany     5
2     Amber   James     1
3     Amber Kenneth     7
4     Amber  Gordon     9
5   Tiffany   James     4
6   Tiffany Kenneth     6
7   Tiffany  Gordon     6
8     James Kenneth     3
9     James  Gordon     7
10 Kenneth   Gordon     2

etc

(in fact it's many more names and values)

I want the names of the people on both axes of the matrix, like this:

	Amber	Tiffany	James	Kenneth	Gordon
Amber	0	5	1	7	9
Tiffany	5	0	4	6	6
James	1	4	0	3	7
Kenneth	7	6	3	0	2
Gordon	9	6	7	2	0

I can't find an easy way to do this. I've read and tried several things, such as:

But I didn't succeed yet.

use `xtabs(Match ~ Person1 + Person2, df1)` – akrun Jan 14 '21 at 20:17 — akrun, Jan 14 '21 at 20:17

score 2 · Answer 1 · answered Jan 14 '21 at 20:59

An option with igraph

get.adjacency(
  graph_from_data_frame(df, directed = FALSE),
  attr = "Match",
  sparse = FALSE
)

gives

        Amber Tiffany James Kenneth Gordon
Amber       0       5     1       7      9
Tiffany     5       0     4       6      6
James       1       4     0       3      7
Kenneth     7       6     3       0      2
Gordon      9       6     7       2      0

Data

> dput(df)
structure(list(Person1 = c("Amber", "Amber", "Amber", "Amber", 
"Tiffany", "Tiffany", "Tiffany", "James", "James", "Kenneth"),
    Person2 = c("Tiffany", "James", "Kenneth", "Gordon", "James",
    "Kenneth", "Gordon", "Kenneth", "Gordon", "Gordon"), Match = c(5L,
    1L, 7L, 9L, 4L, 6L, 6L, 3L, 7L, 2L)), class = "data.frame", row.names = c("1",
"2", "3", "4", "5", "6", "7", "8", "9", "10"))

score 1 · Answer 2 · answered Jan 14 '21 at 21:44

I would first create an empty matrix:

allpersons <- union(df$Person1, df$Person2)
mat <- matrix(0, nrow=length(allpersons), ncol=length(allpersons),
              dimnames=list(allpersons, allpersons))

And then add all the values from the third column of df by row and column name pairs:

mat[as.matrix(df[,c(1,2)])] <- df[,3]
mat[as.matrix(df[,c(2,1)])] <- df[,3]

Note - because the matrix has to be symmetrical the values are added two times: once for row-column pairs and then again for column-row pairs.

Result:

        Amber Tiffany James Kenneth Gordon
Amber       0       5     1       7      9
Tiffany     5       0     4       6      6
James       1       4     0       3      7
Kenneth     7       6     3       0      2
Gordon      9       6     7       2      0

Convert a dataframe with 3 columns to a weighted adjacency matrix

2 Answers2