Continual summation of a column in R until condition is met

Question

I am doing my best to learn R, and this is my first post on this forum.

I currently have a data frame with a populated vector "x" and an unpopulated vector "counter" as follows:

x <- c(NA,1,0,0,0,0,1,1,1,1,0,1)

df <- data.frame("x" = x, "counter" = 0)

    x counter
1  NA       0
2   1       0
3   0       0
4   0       0
5   0       0
6   0       0
7   1       0
8   1       0
9   1       0
10  1       0
11  0       0
12  1       0

I am having a surprisingly difficult time trying to write code that will simply populate counter so that counter sums the cumulative, sequential 1s in x, but reverts back to zero when x is zero. Accordingly, I would like counter to calculate as follows per the above example:

    x counter
1  NA       NA
2   1       1
3   0       0
4   0       0
5   0       0
6   0       0
7   1       1
8   1       2
9   1       3
10  1       4
11  0       0
12  1       1

I have tried using lag() and ifelse(), both with and without for loops, but seem to be getting further and further away from a workable solution (while lag got me close, the figures were not calculating as expected....my ifelse and for loops eventually ended up with length 1 vectors of NA_real_, NA or 1). I have also considered cumsum - but not sure how to frame the range to just the 1s - and have searched and reviewed similar posts, for example How to add value to previous row if condition is met; however, I still cannot figure out what I would expect to be a very simple task.

Admittedly, I am at a low point in my early R learning curve and greatly appreciate any help and constructive feedback anyone from the community can provide. Thank you.

Ronak Shah · Accepted Answer · 2021-04-14T02:48:41.523

1

You can use :

library(dplyr)

df %>%
  group_by(x1 = cumsum(replace(x, is.na(x), 0) == 0)) %>%
  mutate(counter = (row_number() - 1) * x) %>%
  ungroup %>%
  select(-x1)

#       x counter
#   <dbl>   <dbl>
# 1    NA      NA
# 2     1       1
# 3     0       0
# 4     0       0
# 5     0       0
# 6     0       0
# 7     1       1
# 8     1       2
# 9     1       3
#10     1       4
#11     0       0
#12     1       1

Explaining the steps -

Create a new column (x1), replace NA in x with 0 and increment the group value by 1 (using cumsum) whenever x = 0.
For each group subtract the row number with 0 and multiply it by x. This multiplication is necessary because it will help to keep counter as 0 where x = 0 and counter as NA where x is NA.

edited Apr 14 '21 at 02:48

answered Apr 13 '21 at 12:58

Ronak Shah

377,200
20
156
213

Thanks @Ronak Shah. I have spent considerable time trying to understand how your solution works (and it does work), but just cannot figure out how/why mutate(counter = (row_number() - 1) * x) ultimately "mutates" the counter column to reflect the correct number of consecutive 1s from the x column. I am stumped as to how counter = row_number() - 1) * x actually gets us there. Any enlightenment is greatly appreciated. – cpanagakos Apr 14 '21 at 02:40
I added some explanation in the answer. I would suggest you run the code line by line to get better understanding of the answer. – Ronak Shah Apr 14 '21 at 02:49

crestor · Answer 2 · 2021-04-13T14:30:16.537

Welcome @cpanagakos.

In dplyr::lag it's not posibble to use a column that still doesn't exist. (It can't refer to itself.) https://www.reddit.com/r/rstats/comments/a34n6b/dplyr_use_previous_row_from_a_column_thats_being/

For example:

library(tidyverse)


df <- tibble("x" = c(NA, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 1))

# error: lag cannot refer to a column that still doesn't exist
df %>%
  mutate(counter = case_when(is.na(x) ~ coalesce(lag(counter), 0),
                             x == 0   ~ 0,
                             x == 1   ~ lag(counter) + 1))
#> Error: Problem with `mutate()` input `counter`.
#> x object 'counter' not found
#> i Input `counter` is `case_when(...)`.

So, if you have a criteria that "resets" the counter, you would need to write a formula that changes the group when you need a reset an then refer to the row_number, that will be restarted at 1 inside the group (like @Ronald Shah and others suggest):

Create sequential counter that restarts on a condition within panel data groups

df %>%
  group_by(x1 = cumsum(!coalesce(x, 0))) %>%
  mutate(counter = row_number() - 1) %>%
  ungroup()
#> # A tibble: 12 x 3
#>        x    x1 counter
#>    <dbl> <int>   <dbl>
#>  1    NA     1      NA
#>  2     1     1       1
#>  3     0     2       0
#>  4     0     3       0
#>  5     0     4       0
#>  6     0     5       0
#>  7     1     5       1
#>  8     1     5       2
#>  9     1     5       3
#> 10     1     5       4
#> 11     0     6       0
#> 12     1     6       1

This would be one of the few cases where using a for loop in R could be justified: because the alternatives are conceptually harder to understand.

Thank you both for the response. I will research and digest each of the functions you used to better understand the solution. In the meantime, @crestor commented that this would be one of the few cases where using a loop in R could be justified - is there a more straightforward solution you could offer using a loop, as suggested? Thanks again for the help. — cpanagakos, Apr 13 '21 at 15:41
@cpanagakos you can choose one as answer and vote positive both. — crestor, Apr 13 '21 at 21:45

Continual summation of a column in R until condition is met

2 Answers2