CODEWITHSUNDEEP
rdataframematrixgraphadjacency-matrix
Emil
Emil

Reputation: 41

Convert a dataframe with 3 columns to a weighted adjacency matrix

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.

Upvotes: 4

Views: 880

Answers (2)

Karolis Koncevičius
Karolis Koncevičius

Reputation: 9656

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

Upvotes: 1

ThomasIsCoding
ThomasIsCoding

Reputation: 101403

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"))

Upvotes: 2

Related Questions