I have a data set (ds) with the variables ``,date, values and break. values is a cumulative sum until break == "True" and after the data start the process of cumulative sum again. In my example:
# Packages
library(dplyr)
library(lubridate)
# My data set
ds <- read.csv("https://raw.githubusercontent.com/Leprechault/trash/main/accumulated_values_table")
ds$date <- ymd(ds$date)
str(ds)
# 'data.frame': 4458 obs. of 4 variables:
# $ id : int 0 1 2 2 1 0 3 2 4 1 ...
# $ date : Date, format: "2020-11-26" "2020-11-26" "2020-11-26" "2020-11-27" ...
# $ values: int 36 33 27 22 34 28 5 18 28 24 ...
# $ break.: chr "False" "False" "False" "False" ...
I'd like to find any way to remove the cumulative sum and the desired output is the daily number of values by id. I need some help for any function to make the subtraction of day+1 - day until my column break == "True" for each id. For example to my id=3:
# Actual dataset
ds.3 <- ds %>% filter(id==3)
head(ds.3)
# id date values break.
# 1 3 2020-11-27 5 True
# 2 3 2020-12-03 16 False
# 3 3 2020-12-05 18 False
# 4 3 2020-12-08 7 True
# 5 3 2020-12-09 27 False
# 6 3 2020-12-19 18 False
# Desirable dataset
# id date values break.
# 1 3 2020-11-27 5 True
# 2 3 2020-12-03 16 False
# 3 3 2020-12-05 2 False
# 4 3 2020-12-08 5 True
# 5 3 2020-12-09 27 False
# 6 3 2020-12-19 0 False * PS: if the sum is negative, the the value is equal to 0
Please any ideas?
I'm inferring that we want to group by rows between "True" (by id too) and reassign values only where break. is not "True".
library(dplyr)
out <- ds %>%
mutate(break. = break. == "True") %>%
group_by(id) %>%
mutate(grp = cumsum(break.)) %>%
group_by(id, grp, break.) %>%
mutate(values2 = if (break.[1]) values else pmax(0, c(values[1], diff(values)))) %>%
ungroup()
out
# # A tibble: 4,458 x 6
# id date values break. grp values2
# <int> <chr> <int> <lgl> <int> <dbl>
# 1 0 2020-11-26 36 FALSE 0 36
# 2 1 2020-11-26 33 FALSE 0 33
# 3 2 2020-11-26 27 FALSE 0 27
# 4 2 2020-11-27 22 FALSE 0 0
# 5 1 2020-11-27 34 TRUE 1 34
# 6 0 2020-11-27 28 FALSE 0 0
# 7 3 2020-11-27 5 TRUE 1 5
# 8 2 2020-11-28 18 FALSE 0 0
# 9 4 2020-11-28 28 FALSE 0 28
# 10 1 2020-11-28 24 FALSE 1 24
# # ... with 4,448 more rows
And we can look at just id == 3:
filter(out, id == 3)
# # A tibble: 17 x 6
# id date values break. grp values2
# <int> <chr> <int> <lgl> <int> <dbl>
# 1 3 2020-11-27 5 TRUE 1 5
# 2 3 2020-12-03 16 FALSE 1 16
# 3 3 2020-12-05 18 FALSE 1 2
# 4 3 2020-12-08 7 TRUE 2 7
# 5 3 2020-12-09 27 FALSE 2 27
# 6 3 2020-12-19 18 FALSE 2 0
# 7 3 2020-12-21 2 FALSE 2 0
# 8 3 2020-12-23 18 FALSE 2 16
# 9 3 2020-12-27 15 FALSE 2 0
# 10 3 2020-12-29 11 FALSE 2 0
# 11 3 2021-01-10 11 FALSE 2 0
# 12 3 2021-01-12 14 FALSE 2 3
# 13 3 2021-01-27 10 FALSE 2 0
# 14 3 2021-01-29 15 FALSE 2 5
# 15 3 2021-01-31 15 FALSE 2 0
# 16 3 2021-02-08 16 FALSE 2 1
# 17 3 2021-02-09 8 FALSE 2 0
ds$break. <- ds$break. == "True"
ds$grp <- ave(ds$break., ds$id, FUN = cumsum)
ds$values2 <- ifelse(
ds$break. == "True", ds$values,
ave(ds$values, ds[c("id","grp","break.")],
FUN = function(z) pmax(0, c(z[1], diff(z))))
)
subset(ds, id == 3)
# id date values break. grp values2
# 7 3 2020-11-27 5 TRUE 1 5
# 35 3 2020-12-03 16 FALSE 1 16
# 48 3 2020-12-05 18 FALSE 1 2
# 64 3 2020-12-08 7 TRUE 2 7
# 74 3 2020-12-09 27 FALSE 2 27
# 182 3 2020-12-19 18 FALSE 2 0
# 201 3 2020-12-21 2 FALSE 2 0
# 220 3 2020-12-23 18 FALSE 2 16
# 249 3 2020-12-27 15 FALSE 2 0
# 277 3 2020-12-29 11 FALSE 2 0
# 380 3 2021-01-10 11 FALSE 2 0
# 390 3 2021-01-12 14 FALSE 2 3
# 509 3 2021-01-27 10 FALSE 2 0
# 524 3 2021-01-29 15 FALSE 2 5
# 539 3 2021-01-31 15 FALSE 2 0
# 608 3 2021-02-08 16 FALSE 2 1
# 619 3 2021-02-09 8 FALSE 2 0