CODEWITHSUNDEEP
rdplyrdata-manipulation
J.Q
J.Q

Reputation: 1031

data frame to affiliation matrix in R

HAVE is a data frame with this structure:

name workplace pr_happy
a     A            0.93
b     B            0.54
c     A            0.72
d     C            0.17
e     D            0.44

I WANT to build an adjacency matrix of name and workplace (exactly like this question: converting data frame into affiliation network in R), but instead of a matrix with binary values, I want the values of pr_happy to populate the cells for each affiliation. WANT should look like this:

       A    B    C    D 
a   0.93 0.00 0.00 0.00
b   0.00 0.54 0.00 0.00
c   0.72 0.00 0.00 0.00
d   0.00 0.00 0.17 0.00
e   0.00 0.00 0.00 0.44

I'm having a hard time wrapping my head around a way to do this simply. Any thoughts?

Upvotes: 0

Views: 338

Answers (2)

David Cros
David Cros

Reputation: 46

You can do it like that :

WANT=matrix(data = 0,nrow = 5,ncol = 4)
rownames(WANT)=letters[1:5]
colnames(WANT)=LETTERS[1:4]

for ( i in 1:5){
   WANT[HAVE[i,1],HAVE[i,2]]=HAVE[i,3]
}

(although I am sure there is a way without the loop)

Upvotes: 2

zack
zack

Reputation: 5405

This is essentially pivoting and replacing NA values

Using tidyverse:

library(tidyverse)

dat %>% 
  spread(workplace, pr_happy, fill = 0) %>% # thank you @Jordo82
  tibble::column_to_rownames("name")

     A    B    C    D
a 0.93 0.00 0.00 0.00
b 0.00 0.54 0.00 0.00
c 0.72 0.00 0.00 0.00
d 0.00 0.00 0.17 0.00
e 0.00 0.00 0.00 0.44

data

dat <- structure(list(name = c("a", "b", "c", "d", "e"),
                      workplace = c("A", "B", "A", "C", "D"),
                      pr_happy = c(0.93, 0.54, 0.72, 0.17, 0.44)),
                 .Names = c("name", "workplace", "pr_happy"),
                 row.names = c(NA, -5L), class = c("data.frame"))

Upvotes: 4

Related Questions