R; For each level in column A, replace values in column B, following condition in column C

Question

I have a list of species observations in N sites. Observations are presence, absence or unknown (1, 0, 'na'). What I need to do is, for each species, satisfy the condition:

for each SITE, if no 1 %in% SITE, replace all 0 with 'na'

I've managed a workaround using a nested loop and lists, but that seems horribly inefficient. Some questions pertaining matching values in column provided more elegant solutions, but I couldn't apply them in a more complex setting.

Here's some dummy data:

x <- c(1,2,3,4,5,6,7,8,9,10)
site <- c(1,1,1,2,2,2,3,3,3,1)
sp1 <- factor(c(1,1,'na','na',0,0,'na','na','na',0))
sp2 <- factor(c(0,0,1,1,'na','na',0,1,'na','na'))
table <- cbind.data.frame(x,site,sp1,sp2)

And what I did:

for (j in c(3:4)){
  site.present <- unique(table$site[which(table[,j]==1)])
  for (i in (1:length(table[,j]))) {
    ifelse(!(table[i,2]%in%site.present), 
           ifelse(table[i,j]==0,table[i,j]<-'na',T),T)
  }
}

In this example [5,3] and [6,3] should become 'na' instead of 0 (because for sp1 there is no presence in site 2). The code above works, but it seems silly for processing millions of entries...

Much appreciated!

Answer 1

Using dplyr and base::replace. We can replace any zero with NA where is no species equal to 1 in that site.

library(dplyr)
df <- table

df %>% mutate_all(~as.numeric(as.character(.))) %>% 
       group_by(site) %>% 
       #mutate(sp1_mod=replace(sp1,all(sp1!=1, na.rm = TRUE) & sp1==0,NA)) #for one column
       mutate_at(vars('sp1','sp2'), list(~replace(.,all(.!=1, na.rm = TRUE) & .==0,NA))) 

Also, instead of naming variables inside vars one by one we can use one of the Select helpers see ?dplyr::select, e.g. we can use matches to match any column names start with sp and with a digit or more

mutate_at(vars(matches('sp\\d+')), list(~replace(.,any(.==1, na.rm = TRUE) & .==0,NA)))

Answer 2

Is this what you are looking for?

library(dplyr)

table %>%
  group_by(site) %>%
  mutate(sp1 = if_else(
    !any(sp1 == 1) & sp1 == 0,
    "na",
    as.character(sp1)
  ))