Convert data frame into adjacency matrix format in R

My data looks like this:

Sample_A    Sample_B  Value

Rabbit       Mouse     1.2
Mouse        Tiger     7.89
Tiger        Lion      -0.9
Mouse        Rabbit     1.2
Lion         Rabbit     98.13

I would like to turn it into this format:

         Lion      Tiger      Mouse    Rabbit
Rabbit   98.13       .        .

so, I would like the rows of my 2nd column (sample2) each become a column by itself and then the assigned values be assigned accordingly. The order of the sample names does not matter. My real data has 2000 rows and the same 3 columns. Is there any way to prepare my desired matrix in R? any function to use? Later on I plan to graph it and that’s why I need it. Thanks

>Solution :

This sounds like a textbook case for using a pivot. There are many ways to do this in R. Here’s a base R solution:

#Your example data:
original <- data.frame(Sample_A = c("Rabit", "Mouse", "Tiger", "Mouse", "Lion"),
                       Sample_B = c("Mouse", "Tiger", "Lion", "Rabit", "Rabit"),
                       Value = c(1.2, 7.89, -0.9, 1.2, 98.13))
with(original, tapply(Value, list(Sample_A, Sample_B), FUN = identity))


      Lion Mouse Rabit Tiger
Lion    NA    NA 98.13    NA
Mouse   NA    NA  1.20  7.89
Rabit   NA   1.2    NA    NA
Tiger -0.9    NA    NA    NA

Leave a Reply