berrypy berrypy -4 years ago 191
R Question

Increment count over rows with conditional restarting

I would like to increment a count that restarts from 1 when a condition in an existing column is met.

For example I have the following data frame:

df <- data.frame(x1 = c(10, 100, 200, 300, 87, 90, 45, 80),
x2 = c("start", "a", "b", "c", "start", "k", "l", "o"))


I would like to create
x3
that starts counting from 1 each time that
x2 == "start"
.

The resulting data frame should look like this:

x1 x2 x3
1 10 start 1
2 100 a 2
3 200 b 3
4 300 c 4
5 87 start 1
6 90 k 2
7 45 l 3
8 80 o 4


I'm guessing there are existing functions in R that give a general solution. Can anyone point me in the right direction?

Answer Source

Using base R:

df$x3 <- with(df, ave(x1, cumsum(x2=='start'), FUN = seq_along))

gives:

> df
   x1    x2 x3
1  10 start  1
2 100     a  2
3 200     b  3
4 300     c  4
5  87 start  1
6  90     k  2
7  45     l  3
8  80     o  4

Or with the dplyr or data.table packages:

library(dplyr)
df %>% 
  group_by(grp = cumsum(x2=='start')) %>% 
  mutate(x3 = row_number())

library(data.table)
setDT(df)[, x3 := 1:.N, cumsum(x2=='start')][]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download