Converting a matrix into a data frame in a long format (R)

Question

I have a matrix in this format:

Example_matrix<- structure(list(X1 = c("Species 1", "Species 2", "Species 3", 
"Species 4", "Species 5", "Species 6", "Species 7", "Species 8"
), `Site 1` = c(0, 0, 0, 2, 2, 0, 0, 5), `Site 2` = c(0, 0, 0, 
4, 0, 0, 0, 0), `Site 3` = c(0, 1, 0, 0, 0, 1, 0, 0), `Site 4` = c(0, 
0, 0, 0, 0, 1, 0, 0)), class = c("spec_tbl_df", "tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -8L), spec = structure(list(
    cols = list(X1 = structure(list(), class = c("collector_character", 
    "collector")), `Site 1` = structure(list(), class = c("collector_double", 
    "collector")), `Site 2` = structure(list(), class = c("collector_double", 
    "collector")), `Site 3` = structure(list(), class = c("collector_double", 
    "collector")), `Site 4` = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1), class = "col_spec"))

(This is a simplified version of it)

Is there way of quickly converting a matrix like that into a long format df? I had a look at some of the similar questions but the options didn't work for me. All I'm wanting to do is to end up with a df that will have 3 columns: Site, Species and the Abundance (which is currently the value within each cell in the matrix).

Thanks!

Answer 1

Maybe you can try the following base R method using reshape

dflong <- `rownames<-`(setNames(reshape(Example_matrix,
                                        direction = "long",
                                        v.names = "Site",
                                        idvar = "X1",
                                        times = names(Example_matrix[-1]),
                                        varying = list(names(Example_matrix[-1]))), 
                                c("X1","Site","Abundance")),
                       NULL)

such that

> dflong
          X1   Site Abundance
1  Species 1 Site 1         0
2  Species 2 Site 1         0
3  Species 3 Site 1         0
4  Species 4 Site 1         2
5  Species 5 Site 1         2
6  Species 6 Site 1         0
7  Species 7 Site 1         0
8  Species 8 Site 1         5
9  Species 1 Site 2         0
10 Species 2 Site 2         0
11 Species 3 Site 2         0
12 Species 4 Site 2         4
13 Species 5 Site 2         0
14 Species 6 Site 2         0
15 Species 7 Site 2         0
16 Species 8 Site 2         0
17 Species 1 Site 3         0
18 Species 2 Site 3         1
19 Species 3 Site 3         0
20 Species 4 Site 3         0
21 Species 5 Site 3         0
22 Species 6 Site 3         1
23 Species 7 Site 3         0
24 Species 8 Site 3         0
25 Species 1 Site 4         0
26 Species 2 Site 4         0
27 Species 3 Site 4         0
28 Species 4 Site 4         0
29 Species 5 Site 4         0
30 Species 6 Site 4         1
31 Species 7 Site 4         0
32 Species 8 Site 4         0

Answer 2

We could use pivot_longer from tidyr

library(dplyr)
library(tidyr)
newdat <- Example_matrix %>% 
    pivot_longer(cols = starts_with('Site'),
          names_to = 'Site', values_to = 'Abundance')

newdat
# A tibble: 32 x 3
#   X1        Site   Abundance
#   <chr>     <chr>      <dbl>
# 1 Species 1 Site 1         0
# 2 Species 1 Site 2         0
# 3 Species 1 Site 3         0
# 4 Species 1 Site 4         0
# 5 Species 2 Site 1         0
# 6 Species 2 Site 2         0
# 7 Species 2 Site 3         1
# 8 Species 2 Site 4         0
# 9 Species 3 Site 1         0
#10 Species 3 Site 2         0
# … with 22 more rows

---

In base R, we can do

as.data.frame.table(`row.names<-`(as.matrix(Example_matrix)[,-1],
       Example_matrix$X1))