This is my data:
ID<-rep(1:5, length.out=15 )
b<-cumsum(1:10)
c<-cumsum(1:10)*2
bc<-c(b,c)
e<-cbind(ID,bc)
e<-as.data.frame(e)
e$ID<-as.factor(e$ID)
I would like a third column that contains values that cumulative sums were derived from. This is the expected outcome. I know I need to group by ID, but no idea which function to use to do what I want.
f<-e %>% group_by(ID) %>% mutate(num=
ID bc num
1 1 1 1
2 2 3 2
3 3 6 3
4 4 10 4
5 5 15 5
6 1 2 2
I think you're looking for diff, but you are not grouping correctly. You seem to want to perform the operation for each cycle of ID 1 to 5, so you need to label each cycle and group by that:
e %>%
mutate(group = cumsum(c(-1, diff(as.numeric(as.character(ID)))) < 0)) %>%
group_by(group) %>%
mutate(num = diff(c(0, bc))) %>%
select(-group)
#> # A tibble: 20 x 4
#> # Groups: group [4]
#> group ID bc num
#> <int> <fct> <dbl> <dbl>
#> 1 1 1 1 1
#> 2 1 2 3 2
#> 3 1 3 6 3
#> 4 1 4 10 4
#> 5 1 5 15 5
#> 6 2 1 21 21
#> 7 2 2 28 7
#> 8 2 3 36 8
#> 9 2 4 45 9
#> 10 2 5 55 10
#> 11 3 1 2 2
#> 12 3 2 6 4
#> 13 3 3 12 6
#> 14 3 4 20 8
#> 15 3 5 30 10
#> 16 4 1 42 42
#> 17 4 2 56 14
#> 18 4 3 72 16
#> 19 4 4 90 18
#> 20 4 5 110 20
We could also do
library(dplyr)
e %>%
group_by(grp = cumsum(ID == 1)) %>%
mutate(num = coalesce(bc - lag(bc), bc)) %>%
ungroup %>%
select(-grp)
-output
# A tibble: 20 x 3
# ID bc num
# <dbl> <dbl> <dbl>
# 1 1 1 1
# 2 2 3 2
# 3 3 6 3
# 4 4 10 4
# 5 5 15 5
# 6 1 21 21
# 7 2 28 7
# 8 3 36 8
# 9 4 45 9
#10 5 55 10
#11 1 2 2
#12 2 6 4
#13 3 12 6
#14 4 20 8
#15 5 30 10
#16 1 42 42
#17 2 56 14
#18 3 72 16
#19 4 90 18
#20 5 110 20
