Home > OS >  How to create a group based on pattern from another column?
How to create a group based on pattern from another column?

Time:03-10

I have a data frame as below,

dt <- data.frame(id = c("a","b","c","d","e","f","g","h","i","j"),
                 value = c(1,2,1,2,1,1,1,2,1,2))

> dt
   id value
1   a     1
2   b     2
3   c     1
4   d     2
5   e     1
6   f     1
7   g     1
8   h     2
9   i     1
10  j     2

I hope to create a column based on column value so that whenever it runs into a 2 in column value it will assign a new group number. The output will look like,

dtgroup <- data.frame(id = c("a","b","c","d","e","f","g","h","i","j"),
                      value = c(1,2,1,2,1,1,1,2,1,2),
                      group = c(1,1,2,2,3,3,3,3,4,4))

> dtgroup
   id value group
1   a     1     1
2   b     2     1
3   c     1     2
4   d     2     2
5   e     1     3
6   f     1     3
7   g     1     3
8   h     2     3
9   i     1     4
10  j     2     4

Any ideas? Thanks!

CodePudding user response:

We can use findInterval like below

> transform(dt, group = 1   findInterval(seq_along(value), which(value == 2), left.open = TRUE))
   id value group
1   a     1     1
2   b     2     1
3   c     1     2
4   d     2     2
5   e     1     3
6   f     1     3
7   g     1     3
8   h     2     3
9   i     1     4
10  j     2     4

or cut

> transform(dt, group = as.integer(cut(seq_along(value), c(-Inf, which(value == 2)))))
   id value group
1   a     1     1
2   b     2     1
3   c     1     2
4   d     2     2
5   e     1     3
6   f     1     3
7   g     1     3
8   h     2     3
9   i     1     4
10  j     2     4

CodePudding user response:

Another possibility. Increment by one when value is 1 and the previous value (dplyr::lag) is not 1.

dt$group <- with(dt, cumsum(value == 1 & dplyr::lag(value != 1, default = 1)))

   id value group
1   a     1     1
2   b     2     1
3   c     1     2
4   d     2     2
5   e     1     3
6   f     1     3
7   g     1     3
8   h     2     3
9   i     1     4
10  j     2     4

CodePudding user response:

With cumsum, if value doesn't have NAs:

dt$group <- head(c(0,cumsum(dt$value==2)) 1,-1)

dt

   id value group
1   a     1     1
2   b     2     1
3   c     1     2
4   d     2     2
5   e     1     3
6   f     1     3
7   g     1     3
8   h     2     3
9   i     1     4
10  j     2     4
  • Related