How to summarize pairwise counts based on multiple columns in R?-CodePudding

In my dataset, I have plant counts for several months time across few sites sites. I identified plant species who and measured flowers vals. Notice is some sites only few species flower (May Site 1 and 2) and all species in others (June) for a given month. I am first trying to subset this data for only three species of inters "A","B" and "C"

time <- c("May","May","May","May","May","May","May","May","Jun","Jun","Jun","Jun")
site <- c(1,1,1,1,2,2,2,2,1,1,1,1)
who <- c("A","B","C","D","A","B","C","D","A","B","C","D")
val <- c(12,0,1,2,4,6,0,8,10,2,10,2)

df.test <- data.frame(time, site, who, val)

#First Need to subset rows containing `who` A, B and C
df.test <- df.test[df.test$who == c("A","B","C"), ]
 Error: I am not sure why its only picking up only from site 1. I am looking for 9 rows not 3
time site who val
 1   May 1   A  12
 2   May 1   B   0
 3   May 1   C   1

Then, based on this correct subset of data I want to find counts of how many unique sites (unique in time, and site) have only positive, non-zero values of A and B; A and C; B and C; A,B,C?

A and B only = 1
A and C only = 1
B and C only = 0
A, B and C only = 1

CodePudding user response：

I think this is pretty close to what you want:

df.test2 <- df.test[df.test$who %in% c("A","B","C"), ]
tbl <- xtabs(~who site time, df.test2)
(tbl2 <- ftable(tbl, row.vars=1, col.vars=2:3))
#     site   1       2    
#     time Jun May Jun May
# who                     
# A          1   1   0   1
# B          1   1   0   1
# C          1   1   0   1

CodePudding user response：

Something like this:

library(dplyr)

df.test %>% 
  filter(who %in% c("A", "B", "C")) %>% 
  group_by(site, time) %>% 
  mutate(x = ifelse(val > 0, who, FALSE)) %>% 
  do(data.frame(t(combn(.$x, 3)))) %>% 
  count(site, time, X1, X2, X3)

   site time  X1    X2    X3        n
  <dbl> <chr> <chr> <chr> <chr> <int>
1     1 Jun   A     B     C         1
2     1 May   A     FALSE C         1
3     2 May   A     B     FALSE     1