CODEWITHSUNDEEP
rdplyr
Alexander
Alexander

Reputation: 4655

create a counter based on condition in row value

I would like to create a counter that counts the occurrence of numbers. For instance, lets say we have data like this

df <- data.frame(ID = c(rep(rep(seq(1:2),each=2),times=2)),gr=rep(c("A"),each=8))

  ID gr
1  1  A
2  1  A
3  2  A
4  2  A
5  1  A
6  1  A
7  2  A
8  2  A

Every time the data ends with 2 I would like to set counter. So the expected output

  ID gr  counter
1  1  A  1
2  1  A  1
3  2  A  1 
4  2  A  1  ~~data ends with 2
5  1  A  2
6  1  A  2
7  2  A  2
8  2  A  2  ~~data ends with 2
   1  A  3
   1  A  3
   2  A  3
   2  A  3  @@just for an example

I have tried this

library(dplyr)

df%>%
  group_by(gr)%>%
  mutate(counter=cumsum(ID==2))

but it seems to not even close to the desired output!

How can we do that ?

Upvotes: 2

Views: 277

Answers (2)

Florian
Florian

Reputation: 25435

An option with dplyr:

# added one more row to your example
df <- data.frame(ID = c(rep(rep(seq(1:2),each=2),times=2),1),gr=rep(c("A"),each=9))

library(dplyr)
df %>% 
  group_by(gr) %>%
  mutate(id=cumsum(c(0,diff(df$ID)) & lag(df$ID==2))+1)

Output:

# A tibble: 9 x 3
# Groups:   gr [1]
     ID     gr    id
  <dbl> <fctr> <dbl>
1     1      A     1
2     1      A     1
3     2      A     1
4     2      A     1
5     1      A     2
6     1      A     2
7     2      A     2
8     2      A     2
9     1      A     3

Upvotes: 2

Roman Luštrik
Roman Luštrik

Reputation: 70653

There are many ways of doing this.

df <- data.frame(ID = c(rep(rep(seq(1:2),each=2),times=4)), gr= "A")

You can take an ID and see if it increased or decreased. In case of increase, say that this belongs to the same group. If not, reset group (by increasing by 1).

group <- rep(NA, length(df$ID))
group[1] <- 1

for (i in 2:length(df$ID)) {
  if (df$ID[i] >= df$ID[i-1]) {
    group[i] <- group[i-1]
  } else {
    group[i] <- group[i-1] + 1
  }
}

df$group <- group
df

   ID gr group
1   1  A     1
2   1  A     1
3   2  A     1
4   2  A     1
5   1  A     2
6   1  A     2
7   2  A     2
8   2  A     2
9   1  A     3
10  1  A     3
11  2  A     3
12  2  A     3
13  1  A     4
14  1  A     4
15  2  A     4
16  2  A     4

If you are hell-bent on dplyr, you can turn this into a function.

library(dplyr)

findIncrSeq <- function(x) {
  group <- rep(NA, length(x))
  group[1] <- 1

  for (i in 2:length(x)) {
    if (x[i] >= x[i-1]) {
      group[i] <- group[i-1]
    } else {
      group[i] <- group[i-1] + 1
    }
  }
  group
}

df %>%
  group_by(gr) %>%
  mutate(counter = findIncrSeq(ID))

# A tibble: 16 x 4
# Groups:   gr [1]
      ID     gr group counter
   <int> <fctr> <dbl>   <dbl>
 1     1      A     1       1
 2     1      A     1       1
 3     2      A     1       1
 4     2      A     1       1
 5     1      A     2       2
 6     1      A     2       2
 7     2      A     2       2
 8     2      A     2       2
 9     1      A     3       3
10     1      A     3       3
11     2      A     3       3
12     2      A     3       3
13     1      A     4       4
14     1      A     4       4
15     2      A     4       4
16     2      A     4       4

Upvotes: 2

Related Questions