How would I convert a species x site dataframe into a matrix?

Question

I have a dataframe (in the form of an excel file) with rows of sampled sites and columns of each species (sp). A very standard community ecology species by sites matrix but in dataframe format.

example data (note i added a column for site names as that's how it is in my excel file):

sites<-c("SiteA", "SiteB", "SiteC")
sp1<-c(0, 5, 2)
sp2<-c(0, 1, 2)
sp3<-c(1, 1, 4)
comm<-data.frame(sites,sp1,sp2,sp3)

In my situation I only have one of these dataframes or one "plot". I need to convert this dataframe into a matrix formatted like below:

    sp   site     plot  Abundance
1   sp1     A    1        0
2   sp2     A    1        0
3   sp3     A    1        1
4   sp1     B    1        5
5   sp2     B    1        5
6   sp3     B    1        1
7   sp1     C    1        2
8   sp2     C    1        2
9   sp3     C    1        4

I have looked into using techniques described in this previous post (Collapse species matrix to site by species matrix) but the end result is different from mines where I need my matrix to ultimately look like what I showed above.

Any help would be greatly appreciated.

true, i was merely using the list to convey the formatting of the matrix i was in need of. — Leo Ohyama, Oct 25 '17 at 01:08

score 1 · Accepted Answer · answered Oct 25 '17 at 00:35

1

Using the reshape package:

library(reshape2)
comm.l <- melt(comm)
comm.l$plot <- 1

answered Oct 25 '17 at 00:35

user3640617

1,546
13
21

Nice thanks, any way I can rename those headers? – Leo Ohyama Oct 25 '17 at 00:41

score 0 · Answer 2 · answered Oct 25 '17 at 00:50

A solution using dplyr and tidyr.

library(dplyr)
library(tidyr)

comm2 <- comm %>%
  gather(sp, Abundance, starts_with("sp")) %>%
  mutate(site = sub("Site", "", sites), plot = 1) %>%
  select(sp, site, plot, Abundance) %>%
  arrange(site)
comm2
   sp site plot Abundance
1 sp1    A    1         0
2 sp2    A    1         0
3 sp3    A    1         1
4 sp1    B    1         5
5 sp2    B    1         1
6 sp3    B    1         1
7 sp1    C    1         2
8 sp2    C    1         2
9 sp3    C    1         4

How would I convert a species x site dataframe into a matrix?

2 Answers2