Create a sequence conditional on values in other columns in R

Question

I am working with a dataframe similar to the following:

 df = data.frame(ID1 = c(2,2,2,2,2,2,2), 
            ID2 = c(1,1,1,1,1,1,1),
            flagTag = c(0,0,0,0,1,0,0))

I need to create a new field "newField" such that the value increments when flagTag = 1 within group of ID1 and ID2 (thus unique records are identify by the combination of ID1 and ID2).The resulting table should look similar

    ID1 ID2 flagTag newField
  1   2   1       0     1
  2   2   1       0     1
  3   2   1       0     1
  4   2   1       0     1
  5   2   1       1     2
  6   2   1       0     2

I am trying to do this using dplyr but couldn't come up with a logic to do such manipulation. One way is to go record by record in the dataframe and update "newField" in loop which will be a slow procedure.

bouncyball · Accepted Answer · 2017-03-20 21:07:45Z

5

Let's use cumsum and mutate:

library(dplyr)

df %>%
    group_by(ID1, ID2) %>%
    mutate(newField = 1 + cumsum(flagTag))


    ID1   ID2 flagTag newField
  <dbl> <dbl>   <dbl>    <dbl>
1     2     1       0        1
2     2     1       0        1
3     2     1       0        1
4     2     1       0        1
5     2     1       1        2
6     2     1       0        2
7     2     1       0        2

answered Mar 20, 2017 at 21:07

bouncyball

10.8k23 silver badges32 bronze badges

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

1 Comment

Gaurav Over a year ago

Thanks, it does exactly what I was looking for.

akrun · Accepted Answer · 2017-03-21 02:58:39Z

1

Here is a base R option with ave

df$newField <- with(df, ave(flagTag, ID1, ID2, FUN = cumsum)+1)
df$newField
#[1] 1 1 1 1 2 2 2

Or using data.table

library(data.table)
setDT(df)[, newField := cumsum(flagTag) + 1, .(ID1, ID2)]

answered Mar 21, 2017 at 2:58

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Create a sequence conditional on values in other columns in R

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related