How to perform the equivalent of Excel countifs with multiple conditions in dplyr?-CodePudding

In the below reproducible code, I would like to add a column for CountIfs as detailed in the below image, whereby the Excel countifs() formula in the image has multiple conditions with the tops of the specified ranges "anchored". Any recommendations for how to do the same in dplyr? I'm sure it requires grouping but unsure of how to handle the multiple conditions. The blue below shows the current reproducible code output, the yellow shows what I would like to add, and the non-highlighted shows the underlying countifs() formula.

Reproducible code:

library(dplyr)

myData <- 
  data.frame(
    Name = c("R","R","T","R","N","N","T"),
    Group = c(0,0,0,0,1,1,0),
    Code = c(0,2,2,0,3,0,4)
  )

myDataRender <- myData %>% 
  group_by(Group) %>% 
  mutate(CodeGrp = if_else(Group == 0, 0, max(Code)))
print.data.frame(myDataRender)

CodePudding user response：

Something like this:

library(dplyr)

myData %>% 
  group_by(Group) %>% 
  mutate(CodeGrp = if_else(Group == 0, 0, max(Code)),
         CountIfs = if_else(CodeGrp > 0 & Code == Code, 1, 0))

  Name  Group  Code CodeGrp CountIfs
  <chr> <dbl> <dbl>   <dbl>    <dbl>
1 R         0     0       0        0
2 R         0     2       0        0
3 T         0     2       0        0
4 R         0     0       0        0
5 N         1     3       3        1
6 N         1     0       3        1
7 T         0     4       0        0

CodePudding user response：

Try the following. The second part of the mutate simply checks if CodeGrp is greater than zero, returning 0/1, the numbers corresponding to FALSE/TRUE.

suppressPackageStartupMessages(
  library(dplyr)
)

myData <- 
  data.frame(
    Name = c("R","R","T","R","N","N","T"),
    Group = c(0,0,0,0,1,1,0),
    Code = c(0,2,2,0,3,0,4)
  )

myDataRender <- myData %>% 
  group_by(Group) %>% 
  mutate(CodeGrp = if_else(Group == 0, 0, max(Code)),
         CountIfs = as.integer(CodeGrp > 0))

print.data.frame(myDataRender)
#>   Name Group Code CodeGrp CountIfs
#> 1    R     0    0       0        0
#> 2    R     0    2       0        0
#> 3    T     0    2       0        0
#> 4    R     0    0       0        0
#> 5    N     1    3       3        1
#> 6    N     1    0       3        1
#> 7    T     0    4       0        0

^{Created on 2022-08-21 by the reprex package (v2.0.1)}