How to run the equivalent of Excel's cumulative countifs in R using dplyr?-CodePudding

I am trying to run the equivalent of Excel's cumulative countifs formulas in R, using dplyr, but am having problems. I can run countifs in dplyr, but not accumulating the countifs row by row, as illustrated below in the Excel excerpt. I am trying to replicate the Class Count column in R; the formulas for that column are shown in the Count Formula column in yellow. Any recommendations for how to do this?

In the illustration below, note how countifs is anchored at the first row of column B for "Element" in order to enable the cumulative counts.

Here's the code for setting up the dataframe:

myData = data.frame(Element = c("A","A","C","A","B","B","A"), 
                    Class = c(0,0,0,0,1,1,2)
                    )

CodePudding user response：

Using group_by and cumsum you could do:

library(dplyr)

Class_to_Count <- 1

myData |> 
  group_by(Element) |> 
  mutate(Class_Count = cumsum(Class == Class_to_Count))
#> # A tibble: 7 × 3
#> # Groups:   Element [3]
#>   Element Class Class_Count
#>   <chr>   <dbl>       <int>
#> 1 A           0           0
#> 2 A           0           0
#> 3 C           0           0
#> 4 A           0           0
#> 5 B           1           1
#> 6 B           1           2
#> 7 A           2           0

CodePudding user response：

Another possible solution:

library(tidyverse)

d2 <- 1

myData %>% 
  mutate(count = map(1:nrow(.), \(x) sum(.$Element[1:x] == .$Element[x] &
     .$Class[1:x] == d2)))

#>   Element Class count
#> 1       A     0     0
#> 2       A     0     0
#> 3       C     0     0
#> 4       A     0     0
#> 5       B     1     1
#> 6       B     1     2
#> 7       A     2     0