Generate adjacency matrix from survey output

Question

I am trying to pull data from Excel in the form:

Survey Respondents	Abby	Brandon	Jane	Mary	Pat
Jane	1	0	0	1	0
Pat	0	0	1	1	0

Where 1 represents that the survey respondent knows the person in the column and zero represents that the survey respondent does not know the person in the column.

And I want to create two matrices that would have the form (where the numbers represent people)

	1	3	4	5
1	0	1	0	0
2	0	0	0	0
3	1	0	1	0
4	0	1	0	1
5	0	1	1	0

And

ID	NAME
1	Abby
2	Brandon
3	Jane
4	Mary
5	Pat

I've done this in Matlab, but since the network analysis is done in R, I'd rather do it all in R.

I am brand new to R, so I tried to do this by creating a new matrix from the starting matrix but was having trouble with matrix dimensions. And was unsure how to include the 0 and 1 data in addition to just creating the row and column labels.

MrFlick · Accepted Answer · 2023-06-06 17:16:24Z

If you want to look at network/graph data, the igraph package can help. Here's how you could use it after transforming your data

library(dplyr)
library(tidyr)
library(igraph)

#sample data
dd <- data.frame(
  SurveyRespondents = c("Jane", "Pat"), 
    Abby = 1:0, Brandon = c(0L, 0L), Jane = 0:1, 
    Mary = c(1L, 1L), Pat = c(0L, 0L))

people <- sort(unique(c(dd$SurveyRespondents, names(dd)[-1])))
dd %>% 
  pivot_longer(-SurveyRespondents) %>% 
  filter(value==1) %>% 
  select(-value) %>% 
  graph_from_data_frame(., directed=FALSE, vertices = people) %>% 
  as_adjacency_matrix()

#         Abby Brandon Jane Mary Pat
# Abby       .       .    1    .   .
# Brandon    .       .    .    .   .
# Jane       1       .    .    1   1
# Mary       .       .    1    .   1
# Pat        .       .    1    1   .

igraph also makes it easy to plot the data

ThomasIsCoding · Accepted Answer · 2023-06-13 18:11:06Z

2

Base R

You can use the following base R option

v <- as.matrix(df[-1][match(names(df)[-1], df$SurveyRespondents), ])
u <- `rownames<-`(replace(v, is.na(v), 0), colnames(v))
pmax(u, t(u))

and you will obtain

        Abby Brandon Jane Mary Pat
Abby       0       0    1    0   0
Brandon    0       0    0    0   0
Jane       1       0    0    1   1
Mary       0       0    1    0   1
Pat        0       0    1    1   0

igraph

With igraph, we can try

`row.names<-`(as.matrix(df[-1]), df$SurveyRespondents) %>%
    as.data.frame.table() %>%
    filter(Freq > 0) %>%
    graph_from_data_frame(directed = FALSE, vertices = names(df)[-1]) %>%
    as_adjacency_matrix(sparse = FALSE)

which gives

        Abby Brandon Jane Mary Pat
Abby       0       0    1    0   0
Brandon    0       0    0    0   0
Jane       1       0    0    1   1
Mary       0       0    1    0   1
Pat        0       0    1    1   0

edited Jun 13, 2023 at 18:11

answered Jun 6, 2023 at 22:03

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

2 Comments

Szabolcs Over a year ago

Updated to non-deprecated names, hope you don't mind.

ThomasIsCoding Over a year ago

@Szabolcs haha, definitely no problem. I am glad that you keep my knowledge up-to-date :)

Collectives™ on Stack Overflow

Generate adjacency matrix from survey output

2 Answers 2

Comments

Base R

igraph

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Base R

igraph

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related