Occurrence table

[Julie19]

Hello,
I have a data frame and I want to transform it into an occurrence table. If you have tips , let me know pls

Here is the table
Placette Espèce Fleur Tige Feuille Racine
EP01 especeA 1 1 0 0
ED02 especeA 1 0 0 0
ED03 especeA 0 1 1 1
ED05 especeA 0 0 0 1
EP01 especeB 0 1 1 0
EI03 especeB 0 0 0 1

And I want to transform the table like this :
Placette Espece Substrat
EP01 especeA Fleur
EP01 especeA Tige
ED02 especeA Fleur
ED03 especeA Tige
ED03 especeA Feuille
ED03 especeA Racine
...

When the species is present in 3 differents substrates , then there are 3 lines of the species with the substrate each line.
Have you any advices ?
Thank you !

[siddharthprabhu]

Hi @Julie19. This operation is known as pivoting and the tidyr package has functions specifically for performing these kinds of transformations. Read more about it here: https://tidyr.tidyverse.org/articles/pivot.html

library(tidyverse)

df <- tribble(~ Placette, ~ Espèce, ~ Fleur, ~ Tige, ~ Feuille, ~ Racine,
              "EP01", "especeA", 1, 1, 0, 0,
              "ED02", "especeA", 1, 0, 0, 0,
              "ED03", "especeA", 0, 1, 1, 1,
              "ED05", "especeA", 0, 0, 0, 1,
              "EP01", "especeB", 0, 1, 1, 0,
              "EI03", "especeB", 0, 0, 0, 1)

df %>% 
  pivot_longer(cols = -c(Placette, Espèce), names_to = "Substrat") %>% 
  filter(value == 1) %>% 
  select(-value)
#> # A tibble: 10 x 3
#>    Placette Espèce  Substrat
#>    <chr>    <chr>   <chr>   
#>  1 EP01     especeA Fleur   
#>  2 EP01     especeA Tige    
#>  3 ED02     especeA Fleur   
#>  4 ED03     especeA Tige    
#>  5 ED03     especeA Feuille 
#>  6 ED03     especeA Racine  
#>  7 ED05     especeA Racine  
#>  8 EP01     especeB Tige    
#>  9 EP01     especeB Feuille 
#> 10 EI03     especeB Racine

^{Created on 2020-03-30 by the reprex package (v0.3.0)}

[Julie19]

Hi ! Thank you !
But how to do the same thing and no writing the data frame ? because I have many rows.

[TomasG]

@Julie19 If you have the table in a file, then import it into R and save it as an object

[Julie19]

Okay thank you very much for the answer !