Convert Adjacency Matrix to two column table in R

Question

I have following adjacency matrix. I want to covert it to a two column table in R. The reproducible example include:

            Village_A   Village_B   Village_C   Village_D   Village_E
Village_A   438          507         157         166         832
Village_B   285          887         116         758         244
Village_C   935          459         217         561         550
Village_D   151          606         202         869         169
Village_E   572          362         416         899         510

I want to covert it to a two column table as:

Village_A   Village_A   438
Village_B   Village_A   285
Village_C   Village_A   935
Village_D   Village_A   151
Village_E   Village_A   572
Village_A   Village_B   507
Village_B   Village_B   887
Village_C   Village_B   459
Village_D   Village_B   606
Village_E   Village_B   362
Village_A   Village_C   157
Village_B   Village_C   116
Village_C   Village_C   217
Village_D   Village_C   202
Village_E   Village_C   416
Village_A   Village_D   166
Village_B   Village_D   758
Village_C   Village_D   561
Village_D   Village_D   869
Village_E   Village_D   899
Village_A   Village_E   832
Village_B   Village_E   244
Village_C   Village_E   550
Village_D   Village_E   169
Village_E   Village_E   510

Could you please share dput(df), where df is your data frame? — rar, Oct 26 '18 at 05:41
If it is a `matrix`, use `reshape2::melt(df))` or convert a data.frame to `matrix` and use `melt` i.e. `melt(as.data.frame(df))` — akrun, Oct 26 '18 at 05:54
It did not produce desired table. It gave only one column of district. I need two columns of district. — NoviceStat, Oct 26 '18 at 06:04
Take a look at `gather` function from `tidyr`, You want to do something like `gather(df, "Village_A", "Village_B", "Village_C", "Village_D", "Village_E", key = "village_2", value = "value")` — Linards Kalvans, Oct 26 '18 at 06:44

SRhm · Accepted Answer · 2018-11-08T14:52:34.497

0

#temptable.txt:
##Village_A   Village_B   Village_C   Village_D   Village_E
##Village_A   438          507         157         166         832
##Village_B   285          887         116         758         244
##Village_C   935          459         217         561         550
##Village_D   151          606         202         869         169
##Village_E   572          362         416         899         510
data <- read.delim("temptable.txt",sep="")
crossdata <- lapply(rownames(data),function(x)sapply(colnames(data),function(y)list(x,y,data[x,y])))
crossdatatmp <- matrix(unlist(crossdata),nrow=3)
crossdatamat <- t(crossdatatmp)
colnames(crossdatamat) <- c("From","To","Value")
crossdatadf <- as.data.frame(crossdatamat,stringsAsFactors=F)
crossdatadf[,3] <- as.numeric(crossdatadf[,3])
crossdatadf
##        From        To Value
##1  Village_A Village_A   438
##2  Village_A Village_B   507
##3  Village_A Village_C   157
##4  Village_A Village_D   166
##5  Village_A Village_E   832
## ...

edited Nov 08 '18 at 14:52

answered Oct 26 '18 at 06:07

SRhm

459
1
5
11

I got this error: unexpected symbol in "crossdatatmp <- t(crossdatatmp)atatmp" – NoviceStat Oct 26 '18 at 06:36
@NoviceStat it's edited. – SRhm Oct 26 '18 at 06:37
Thanks for help @SRhm – NoviceStat Oct 26 '18 at 10:04
1

Thanks @SRhm for quick help – NoviceStat Nov 08 '18 at 15:02

score 0 · Answer 2 · answered Oct 26 '18 at 06:51

0

Take a look at gather function from tidyr. You want to do something like (assuming df is name of Your data frame):

gather(df, "Village_A", "Village_B", "Village_C", "Village_D", "Village_E", key = "village_2", value = "value")

answered Oct 26 '18 at 06:51

Linards Kalvans

479
2
8

Convert Adjacency Matrix to two column table in R

2 Answers2