I have a basic question about spreadsheets but it is slowing down my work.
I have a table of presence and absence of species with 714 sites and 4406 species. I need to turn this spreadsheet into a spreadsheet with 3 columns, 1 with the names of the sites, 1 with the name of all species per site (repeating the 4406 species in each site) and the other with 0 (absence of the species in the site ) and 1 (presence of the species in the site).
How can I do this?
See if that's what you need. Since you did not post a sample of your data, here is a fictitious presence and absence matrix to use for example:
matriz.pa <- data.frame(
Sitio = LETTERS[1:4],
A_arturica = sample(c(0,1), 4, replace=TRUE),
A_beliniae = sample(c(0,1), 4, replace=TRUE),
B_carmensis = sample(c(0,1), 4, replace=TRUE) )
> matriz.pa
Sitio A_arturica A_beliniae B_carmensis
1 A 1 1 1
2 B 0 1 1
3 C 1 1 0
4 D 0 1 0
Using the function melt (package reshape or reshape2):
> reshape::melt(matriz.pa, variable_name = 'Especie')
Using Sitio as id variables
Sitio Especie value
1 A A_arturica 1
2 B A_arturica 0
3 C A_arturica 1
4 D A_arturica 0
5 A A_beliniae 1
6 B A_beliniae 1
7 C A_beliniae 1
8 D A_beliniae 1
9 A B_carmensis 1
10 B B_carmensis 1
11 C B_carmensis 0
12 D B_carmensis 0
The reshape2 package contains improved versions of the reshape package functions, but what you need is simple. You can also use the gather function of the tidyr package, which does the same thing (but with fewer options, although again, what you need is simple).