r convert summary data to presence/absence data

user_3054331 0
Viewed 156

I conducted 5 presence/absence measures at multiple sites and summed them together and ended up with a dataframe that looked something like this:

df <- data.frame("site" = c("a", "b", "c"), 
                 "species1" = c(0, 2, 1), 
                 "species2" = c(5, 2, 4))

ie. at site "a" species1 was recorded 0/5 times and species2 was recorded 5/5 times.

What I would like to do is convert this back into presence/absence data. Something like this:

data.frame("site" = ("a", "b", "c"), 
           "species1" = c(0,0,0,0,0, 1,1,0,0,0, 1,0,0,0,0),
           "species2" = c(1,1,1,1,1, 1,1,0,0,0, 1,1,1,1,0))

I can duplicate each row 5 times with:

df %>% slice(rep(1:n(), each = 5))

but I can't figure out how to change "2" into "1,1,0,0,0". Ideally the order of the 1s and 0s (within each site) would also be randomised (ie. "0,0,1,0,1"), but that might be too difficult.

Any help would be appreciated.

dplyr dataframe r
2 Answers

After repeating the rows you can compare the row number with any value of the respective column and assign 1 if the current row number is less than the value.

library(dplyr)

df %>% 
  slice(rep(seq_len(n()), each = 5)) %>%
  group_by(site) %>%
  mutate(across(starts_with('species'), ~+(row_number() <= first(.))))
  #Use mutate_at with old dplyr
  #mutate_at(vars(starts_with('species')), ~+(row_number() <= first(.)))


#   site  species1 species2
#   <chr>    <int>    <int>
# 1 a            0        1
# 2 a            0        1
# 3 a            0        1
# 4 a            0        1
# 5 a            0        1
# 6 b            1        1
# 7 b            1        1
# 8 b            0        0
# 9 b            0        0
#10 b            0        0
#11 c            1        1
#12 c            0        1
#13 c            0        1
#14 c            0        1
#15 c            0        0
user_39629140

We can also use uncount

library(dplyr)
library(tidyr)
df %>% 
   uncount(max(species2), .remove = FALSE) %>% 
   group_by(site) %>%
   mutate(across(starts_with('species'), ~ as.integer(row_number() <= first(.))))
# A tibble: 15 x 3
# Groups:   site [3]
#   site  species1 species2
#   <chr>    <int>    <int>
# 1 a            0        1
# 2 a            0        1
# 3 a            0        1
# 4 a            0        1
# 5 a            0        1
# 6 b            1        1
# 7 b            1        1
# 8 b            0        0
# 9 b            0        0
#10 b            0        0
#11 c            1        1
#12 c            0        1
#13 c            0        1
#14 c            0        1
#15 c            0        0
user_37322710
Related