I am creating a file for running in a model with species counts at a number of different sites that we visited multiple times. However, not all the sites were visited every visit.
I need a column to remain all NA's if it was not visited but should have a 0 if it was from a plot that was visited but the species was not detected. Below is a mini sample of my data in the format I currently have it in. So I essentially need a way to convert all the NA's in column p1 and p2 to 0 while keep p3 for the 2nd visit all NA's. I have hundreds of columns in my real data so I would prefer to not have to manually assign whether NA's should convert to 0 or remain NA if possible column by column.
## creates example dataframe
library(dplyr)
library(tidyr)
Species<-c("a","b","c","a","d","d","c","a","b","c") # four species
visit<-c(1,1,2,2,1,2,2,1,2,1) #two visits
Site<-c("p1","p1","p1","p1","p2","p2","p2","p3","p2","p2") #three sites
TotalCount<-c(1,3,4,3,5,2,1,3,1,1) $number of individuals captured
df<-data.frame(Species, visit, Site, TotalCount)
df2 <- df %>%
dplyr::select("Species", "Site","TotalCount","visit") %>%
pivot_wider(names_from = Site, values_from = TotalCount,
values_fill = NA)%>%
arrange(visit, Species)
final df2 data frame should look like this:
Species visit p1 p2 p3
1 a 1 1 0 3
2 b 1 3 0 0
3 c 1 0 1 0
4 d 1 0 5 0
5 a 2 3 0 NA
6 b 2 0 1 NA
7 c 2 4 1 NA
8 d 2 0 2 NA
I believe this accomplishes the task using package hutilscpp
library(hutilscpp)
df2 %>%
group_by(visit) %>%
mutate_all(.funs = \(x) if (allNA(x)) x else coalesce0(x))