CODEWITHSUNDEEP
rdplyrcumsum
procerus
procerus

Reputation: 294

reverse cumulative sum by group

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

Upvotes: 0

Views: 735

Answers (2)

akrun
akrun

Reputation: 887163

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

Upvotes: 1

Allan Cameron
Allan Cameron

Reputation: 173858

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

Upvotes: 1

Related Questions