Create a count consecutive variable based on the value in another column

Question

Hi I have something like:

df<-data.frame(group=c(1, 1, 1, 1, 2, 2, 2,3,3,3,4,4,4), 
               number=c(0,1,1,1,1,1,0,1,0,1,0,1,1))

I want my 'want' to count the times 'number'=1 repeats per 'group'. Then I want the max number of times it repeats (want2). I want the 'want' to reset to 1 when 'number'=0:

dfwant<-data.frame(group=c(1, 1, 1, 1, 2, 2, 2,3,3,3,4,4,4), 
               number=c(0,1,1,1,1,1,0,1,0,1,0,1,1),
               want=c(1,1,2,3,1,2,1,1,1,1,1,1,2),
               want2=c(3,3,3,3,2,2,2,1,1,1,2,2,2))

Thank you!

Ronak Shah · Accepted Answer · 2019-10-10 01:10:49Z

3

Since we want to reset when number = 0, we group_by group and create a new group whenever the number is 0. We then calculate want by taking cumsum of occurrence of 1 within each group and want2 by taking max in group.

library(dplyr)

df %>%
  group_by(group, group1 = cumsum(number == 0)) %>%
  mutate(want = cumsum(number == 1), 
         want = replace(want, number == 0, 1)) %>%
  group_by(group) %>%
  mutate(want1 = max(want)) %>%
  select(-group1)

#   group number  want want1
#   <dbl>  <dbl> <dbl> <dbl>
# 1     1      0     1     3
# 2     1      1     1     3
# 3     1      1     2     3
# 4     1      1     3     3
# 5     2      1     1     2
# 6     2      1     2     2
# 7     2      0     1     2
# 8     3      1     1     1
# 9     3      0     1     1
#10     3      1     1     1
#11     4      0     1     2
#12     4      1     1     2
#13     4      1     2     2

answered Oct 10, 2019 at 1:10

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

akrun Over a year ago

You could create an column number ==0 and reuse it

akrun · Accepted Answer · 2019-10-10 16:54:27Z

2

We can do this easily with rleid and rowid from data.table

library(dplyr)
library(data.table)
df %>%
   group_by(group) %>%
   mutate(want = rowid(rleid(number)), want1 = max(want))
# A tibble: 13 x 4
# Groups:   group [4]
#   group number  want want1
#   <dbl>  <dbl> <int> <int>
# 1     1      0     1     3
# 2     1      1     1     3
# 3     1      1     2     3
# 4     1      1     3     3
# 5     2      1     1     2
# 6     2      1     2     2
# 7     2      0     1     2
# 8     3      1     1     1
# 9     3      0     1     1
#10     3      1     1     1
#11     4      0     1     2
#12     4      1     1     2
#13     4      1     2     2

Or using the data.table syntax

library(data.table)
setDT(df)[, want := rowid(rleid(number)),.(group)][, want1 := max(want), group][]

edited Oct 10, 2019 at 16:54

answered Oct 10, 2019 at 16:44

akrun

891k38 gold badges590 silver badges700 bronze badges

Comments

GKi · Accepted Answer · 2019-10-10 08:11:47Z

1

In base you can use ave for getting the cumsum per group like:

df$want  <- pmax(1, ave(df$number, df$group, cumsum(df$number==0), FUN=cumsum))
df$want2 <- ave(df$want, df$group, FUN=max)
identical(df, dfwant)
#[1] TRUE

To reset when df$number==0 you can use cumsum(df$number==0) as an additional grouping, as already shown by @Ronak-Shah, for ave.

In case df$number holds also other values than 0 and 1 you have to use:

df$want  <- pmax(1, ave(df$number==1, df$group, cumsum(df$number==0)
    , FUN=cumsum))

answered Oct 10, 2019 at 8:11

GKi

40.1k3 gold badges36 silver badges56 bronze badges

Collectives™ on Stack Overflow

Create a count consecutive variable based on the value in another column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related