R cumsum with if condition

user_14504991 0
Viewed 107

lets say that I have this dataframe

df <-
  data.frame(
    id = seq(1, 8),
    type = c("NEW", "OLD", "OLD", "NEW", "OLD", "NEW", "NEW", "OLD")
  )

I'd like to created "segment" for each TYPE OLD group, thus result would be like this - each segment is marked by order number, please note that first two old types have 1 as segment, second segment is marked as 2.

df <-
  data.frame(
    id = seq(1, 8),
    type = c("NEW", "OLD", "OLD", "NEW", "OLD", "OLD", "NEW", "OLD"),
    segment = c(0, 1, 1, 0, 2, 2, 0, 3)
  )

But I'm having problems with achieving this in R. I can create if else for type segment, i guess I need to do it via cumsum function, but I havent found way how.

mutate(
    segment = if_else(type == "NEW", 0, 1)
    )
cumsum if-statement r
3 Answers

Here is an approach which uses the difference in row numbers method. This approach is often used for gaps and islands problems in databases, which is basically what this R problem also is.

df$segment <- ifelse(df$type == "OLD", df$id - cumsum(df$type == "OLD"), 0)
df

  id type y segment
1  1  NEW 1       0
2  2  OLD 1       1
3  3  OLD 1       1
4  4  NEW 2       0
5  5  OLD 2       2
6  6  OLD 2       2
7  7  NEW 3       0
8  8  OLD 3       3

The approach here is to take the difference between a sequence over the entire data frame and a sequence over only entries which are OLD. Consider the following difference:

1 2 3 4 5 6 7 8
0 1 2 2 3 4 4 5
---------------
1 1 1 2 2 2 3 3

Then, we only retain the values of the above difference which belong to OLD.

user_18632290

Another approach with cumsum:

df %>% mutate(
    segment = cumsum(lag(type, 1, 'NEW') == 'NEW' & type == 'OLD') * (type == 'OLD'))

#   id type segment_expected segment
# 1  1  NEW                0       0
# 2  2  OLD                1       1
# 3  3  OLD                1       1
# 4  4  NEW                0       0
# 5  5  OLD                2       2
# 6  6  OLD                2       2
# 7  7  NEW                0       0
# 8  8  OLD                3       3

The df used here is:

df <-data.frame(
        id = seq(1, 8),
        type = c("NEW", "OLD", "OLD", "NEW", "OLD", "OLD", "NEW", "OLD"),
        segment_expected = c(0, 1, 1, 0, 2, 2, 0, 3))
user_39265430

Base R using rle -

transform(df, segment = with(rle(type == 'NEW'), 
              rep(cumsum(values) * as.integer(!values), lengths)))

#  id type segment
#1  1  NEW       0
#2  2  OLD       1
#3  3  OLD       1
#4  4  NEW       0
#5  5  OLD       2
#6  6  NEW       0
#7  7  NEW       0
#8  8  OLD       3
user_39629140
Related