Convert Matrix to table in R

Question

I have a network graph as a matrix, loaded through a csv in R, and it counts the number of SMS messages between people. e.g:

NULL   john    mary    jim    bob    sue
john   0       5       2      0      1
mary   5       0       2      0      5
jim    2       2       0      8      10
bob    0       0       8      0      0
sue    1       5       10     0      0

I want to transpose this for gephi into a table that works like this:

SOURCE    TARGET    WEIGHT
john      mary      5
john      jim       2
john      bob       0
john      sue       1
mary      jim       2
mary      bob       0
mary      sue       5
jim       bob       8
jim       sue       10
bob       sue       0

Then I can visualise and play with it as a network graph in gephi.

Is there a quick R operation to use on the matrix ('m') to make it into the table and export it as another csv file?

Thanks :)

score 3 · Accepted Answer · answered Nov 08 '16 at 15:37

3

library(reshape2)

df <- melt(m) ##Assuming your data is a matrix. i.e. the people's names are the row and col names.
colnames(df) <- c("Source","Target","Weight")

answered Nov 08 '16 at 15:37

emilliman5

5,816
3
27
37

1

you can make that a one liner with `melt(mat, varnames = c("Source", "Target"), value.name = "Weight")` – Nate Nov 08 '16 at 15:40
How would you take only the top half of the matrix so that we don't end up with duplicate relationships? – Bruno Nascimento Feb 28 '17 at 14:44
You want something along these lines: `df[as.integer(as.factor(df$Source)) < as.integer(as.factor(df$Target)), ]` – emilliman5 Feb 28 '17 at 16:18

Convert Matrix to table in R

1 Answers1

Linked