R grouped counter that copes with NAs or conditions

Question

I have an R dataframe where I need a counter which gives me a fresh new number for a new set of circumstances while also continuing this number (respecting the order of the data).

There are quite a few previous posts on this but none seems to work for my problem. I've tried using combinations of row_counter, ave and rleid and none seems to hit the spot.

id <- c("A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","C","C","C","C","D","D")
marker_new <- c(1,0,0,0,0,1,0,1,0,0,0,0,1,0,1,1,0,1,0,1,0)
counter_result <- c(1,1,1,1,1,1,1,2,2,2,2,2,3,3,4,1,1,2,2,1,1)

df <- data.frame(id,marker_new, counter_result)

df <- df %>%
  group_by(id, marker_new) %>%
  mutate(counter = 
           ifelse(marker_new != 0,
                  row_number(),
                  lag(marker_new,lag(marker_new))) %>%
  ungroup()

I can get to the point using the code above which will give me a fresh number but won't continue this set of numbers down (as in the counter_result i've included).

Any help much appreciated!

`cumsum` by `id` seems to give `counter_result`, `df %>% group_by(id) %>% mutate(counter_new = cumsum(marker_new)) ` — Ronak Shah, Sep 06 '19 at 10:59
@RonakShah that solves it! Or at least it makes me aware of a new problem in my data... but answers my question! Not sure how to mark a comment as a solution though — TimL, Sep 06 '19 at 14:12

score 2 · Accepted Answer · answered Sep 06 '19 at 14:47

Since, we have marker_new column as 1/0, we can use cumsum by group (id) to get counter.

Base R:

df$result <- with(df, ave(marker_new, id, FUN = cumsum))

dplyr:

df %>%  group_by(id) %>% mutate(result = cumsum(marker_new))

data.table

setDT(df)[, result := cumsum(marker_new), by = id]

R grouped counter that copes with NAs or conditions

1 Answers1