Increase from previous value by condition in R

Question

I was searching for an answer to my specific problem, but I didn't find a conclusion.

I have a dataframe with data

Now i want to add "b" column with number increase from previous b if a == 1

Result like this

Thanks in advance!

markus · Answer 1 · 2018-07-02T06:55:49.840

Here is one approach that uses rleid() from data.table to create a grouping variable that we use inside ave(). We then calculate the cumsum per group which will be 0 whenever a == 0.

library(data.table)
df$new_b <- with(df, ave(a, rleid(a), FUN = cumsum))
df
#   ID a b new_b
#1   1 0 0     0
#2   2 0 0     0
#3   3 1 1     1
#4   4 1 2     2
#5   5 1 3     3
#6   6 1 4     4
#7   7 0 0     0
#8   8 1 1     1
#9   9 1 2     2
#10 10 0 0     0
#11 11 1 1     1
#12 12 0 0     0
#13 13 0 0     0
#14 14 1 1     1
#15 15 1 2     2
#16 16 1 3     3
#17 17 1 4     4

Once data.table is loaded you could also do

setDT(df)[, new_b := cumsum(a), rleid(a)][]

data

df <- structure(list(ID = 1:17, a = c(0L, 0L, 1L, 1L, 1L, 1L, 0L, 1L, 
1L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 1L), b = c(0L, 0L, 1L, 2L, 3L, 
4L, 0L, 1L, 2L, 0L, 1L, 0L, 0L, 1L, 2L, 3L, 4L)), .Names = c("ID", 
"a", "b"), class = "data.frame", row.names = c(NA, -17L))

score 1 · Answer 2 · answered Jul 01 '18 at 09:13

1

How about the following using base R's rle

df$b <- unlist(mapply(
    function(len, val) if (val == 0) rep(0, len) else 1:len,
    rle(df$a)$lengths, rle(df$a)$values));
df;
#   ID a b
#1   1 0 0
#2   2 0 0
#3   3 1 1
#4   4 1 2
#5   5 1 3
#6   6 1 4
#7   7 0 0
#8   8 1 1
#9   9 1 2
#10 10 0 0
#11 11 1 1
#12 12 0 0
#13 13 0 0

Sample data

df <- read.table(text =
    "ID  a
1   0
2   0
3   1
4   1
5   1
6   1
7   0
8   1
9   1
10  0
11  1
12  0
13  0", header = T)

answered Jul 01 '18 at 09:13

Maurits Evers

49,617
4
47
68

Yeah!! Thank you very much for help – Quý Jul 01 '18 at 09:18
Our result matches which means solutions are correct. I was slightly confused by `OP's` expected data having 17 rows. – MKR Jul 01 '18 at 09:23
@MKR Yes so was I; I think OP must've used a slightly different input data for his expected output. @markus posted a a nice (and earlier) answer using `data.table::rleid`, that he took down again, perhaps also because of this confusion. A pity. – Maurits Evers Jul 01 '18 at 09:28
You're very welcome @Quý! – Maurits Evers Jul 01 '18 at 09:33
1

@MauritsEvers Undeleted my answer after reading your comment. – markus Jul 01 '18 at 10:26

score 1 · Accepted Answer · answered Jul 01 '18 at 09:18

Using dplyr an option can be to group on cumsum(a==0). This will create a group which got a previous row (if available with a=0 for all rows with a=1. Now, lag(cumsum(a==1)) will provide expected count.

library(dplyr)

df %>% group_by(grp = cumsum(a==0)) %>%
  mutate(b = ifelse(a==1, lag(cumsum(a==1))+1,0)) %>% 
  ungroup() %>% 
  select(-grp) %>%
  as.data.frame()

#    ID a b
# 1   1 0 0
# 2   2 0 0
# 3   3 1 1
# 4   4 1 2
# 5   5 1 3
# 6   6 1 4
# 7   7 0 0
# 8   8 1 1
# 9   9 1 2
# 10 10 0 0
# 11 11 1 1
# 12 12 0 0
# 13 13 0 0

Data:

df <- read.table(text="
ID  a
1   0
2   0
3   1
4   1
5   1
6   1
7   0
8   1
9   1
10  0
11  1
12  0
13  0",
header = TRUE, stringsAsFactors = FALSE)

Increase from previous value by condition in R

3 Answers3

Sample data