Create Presence/Absence column in a data frame

user_13292835 0
Viewed 703

I have a data frame that looks more or less like this

df <- tribble(
  ~Species, ~Symbiont, ~Location,
  "a",   "s01", "P01",
  "b",   "s02", "P01",
  "c",   "s01", "P02",
  "a",   "s03", "P03",
  "d",   "s02", "P02",
  "c",   "s04", "P04",
  "a",   "s03", "P01",
  "c",   "s02", "P02",
  "c",   "s03", "P02"
)

What I'd like to to achieve is to have a single row for each Species in each Location, and a presence-absence 0-1 info about the Symbiont.

For example, with the given data,


Species s01  s02  s03  s04 Location
   a     1    0    1    0    P01
   a     0    0    1    0    P03
   b     0    1    0    0    P02
   c     1    1    1    0    P02
   c     0    0    0    1    P04
   d     0    1    0    0    P02

I'd like to use tidyverse as I'm more comfortable with it but whatever works

I'll spare you the goofy attempt I've tried...

Thank you!

tidyverse r
2 Answers

First, add a presence/absence indicator to your tibble. As you only have "presence"s to start with, every value will be 1

df %>% add_column(Present=1)

Now use pivot_wider to untidy your data

df %>% 
  add_column(Present=1) %>% 
  pivot_wider(names_from=Symbiont, values_from=Present)

Finally, replace the NAs we've introduced, which correspond to absences, with 0s.

df %>% 
  add_column(Present=1) %>% 
  pivot_wider(names_from=Symbiont, values_from=Present) %>% 
  replace(is.na(.), 0)

Giving

# A tibble: 6 x 6
  Species Location   s01   s02   s03   s04
  <chr>   <chr>    <dbl> <dbl> <dbl> <dbl>
1 a       P01          1     0     1     0
2 b       P01          0     1     0     0
3 c       P02          1     1     1     0
4 a       P03          0     0     1     0
5 d       P02          0     1     0     0
6 c       P04          0     0     0     1
user_134348710

Here is a base R option using replace

replace(
  u <- reshape(cbind(df, Presence = 1),
    direction = "wide",
    idvar = c("Species", "Location"),
    timevar = "Symbiont"
  ),
  is.na(u),
  0
)

such that

  Species Location Presence.s01 Presence.s02 Presence.s03 Presence.s04
1       a      P01            1            0            1            0
2       b      P01            0            1            0            0
3       c      P02            1            1            1            0
4       a      P03            0            0            1            0
5       d      P02            0            1            0            0
6       c      P04            0            0            0            1
user_121587570
Related