The problem looks so easy, that it should have a simple solution, yet I couldn't find any;/
I have a long table of records indexed by time. Time intervals are not fixed. There is one categorical variable and I am interested in calculating the streak for each category (for how many days in a row we had "A", than e.g. "B". Then "A" may return and begin another streak). Doing it in Excel requires simply an if function with reference to the row above. In R I can do it with a for loop, which I provide in the toy example below. I mainly wonder, how it could be done in dplyr.
library(tidyverse)
library(lubridate)
set.seed(33)
# I create a date column - 20 dates starting from "2020-01-31", then uneven intervals, from 1 to 5 weeks
date <- rep(ymd(20200131), 20)
# (btw, this, I belive, should also be possible to do without a for loop, and I also cannot come up with a solution,
# how):
for (i in 2:length(date)){
date[i] <- date[i-1]+7*sample(1:5, 1)
}
# A categorical column
user <- c(rep("A",3), "B", rep("C",4), rep("B",5), rep("A", 6), "B")
df <- data.frame(date, user)
df$desired_result <-0
for (i in 2:nrow(df)){
if (df[i, "user"] != df[i-1, "user"]) df[i, "desired_result"] <- 0
else df[i, "desired_result"] <- as.integer(df[i, "date"] - df[i-1, "date"]) + df[i-1, "desired_result"]
}
date user desired_result
1 2020-01-31 A 0
2 2020-03-06 A 35
3 2020-04-03 A 63
4 2020-04-10 B 0
5 2020-04-17 C 0
6 2020-05-08 C 21
7 2020-05-29 C 42
8 2020-06-26 C 70
9 2020-07-03 B 0
10 2020-07-10 B 7
11 2020-07-24 B 21
12 2020-08-28 B 56
13 2020-09-18 B 77
14 2020-10-02 A 0
15 2020-10-09 A 7
16 2020-10-23 A 21
17 2020-11-06 A 35
18 2020-11-13 A 42
19 2020-11-20 A 49
20 2020-12-04 B 0
And now the question: how to do it in dplyr?
# This is wrong: "object 'result' not found":
df %>%
as_tibble() %>%
mutate(result = if_else(user == lag(user),
as.integer(date - lag(date)) + lag(result),
0))
# This is wrong: if condition is fulfilled, it adds as.integer(date - lag(date)) to 0, not to the result in the row above.
# It dosen't proceed like a loop does, from the top of the column to the bottom, doesn't "update" values in the column,
# as it proceeds.
df %>%
as_tibble() %>%
mutate(result = 0) %>%
mutate(result = if_else(user == lag(user),
as.integer(date - lag(date)) + lag(result),
0))
# A tibble: 20 x 4
date user desired_result result
<date> <fct> <dbl> <dbl>
1 2020-01-31 A 0 NA
2 2020-02-14 A 14 14
3 2020-03-13 A 42 28
4 2020-03-20 B 0 0
5 2020-04-03 C 0 0
6 2020-05-01 C 28 28
7 2020-05-08 C 35 7
8 2020-06-12 C 70 35
9 2020-07-17 B 0 0
10 2020-08-21 B 35 35
11 2020-09-04 B 49 14
12 2020-09-18 B 63 14
13 2020-10-16 B 91 28
14 2020-10-23 A 0 0
15 2020-11-13 A 21 21
16 2020-11-27 A 35 14
17 2020-12-25 A 63 28
18 2021-01-08 A 77 14
19 2021-02-12 A 112 35
20 2021-03-05 B 0 0
I've tried group_by() - rather not applicable, because categories may return and start new streaks, cumsum() - also didn't help me so far. I have a strong feeling that there must be a basic solution:)