calculate difference between rows, but keep the raw value by group

Question

I have a dataframe with cumulative values by groups that I need to recalculate back to raw values. The function lag works pretty well here, but instead of the first number in a sequence, I get back either NA, either the lag between two groups.

How to instead of NA values or difference between groups get the first number in group?

My dummy data:

# make example
df <- data.frame(id = rep(1:3, each = 5),
                 hour = rep(1:5, 3),
                 value = sample(1:15))

First calculate cumulative values, than convert it back to row values. I.e value should equal to valBack. The suggestion mutate(valBack = c(cumsum[1], (cumsum - lag(cumsum))[-1])) just replace the first (NA) value to the correct value, but does not work for first numbers for each group?

df %>%
  group_by(id) %>%
  dplyr::mutate(cumsum = cumsum(value)) %>% 
  mutate(valBack = c(cumsum[1], (cumsum - lag(cumsum))[-1]))  # skip the first value in a lag vector

Which results:

   # A tibble: 15 x 5
# Groups:   id [3]
      id  hour value cumsum valBack
   <int> <int> <int>  <int>   <int>
 1     1     1    10     10      10   # this works
 2     1     2    13     23      13
 3     1     3     8     31       8
 4     1     4     4     35       4
 5     1     5     9     44       9
 6     2     1    12     12     -32    # here the new group start. The number should be 12, instead it is -32??
 7     2     2    14     26      14
 8     2     3     5     31       5
 9     2     4    15     46      15
10     2     5     1     47       1
11     3     1     2      2     -45      # here should be 2 istead of -45
12     3     2     3      5       3
13     3     3     6     11       6
14     3     4    11     22      11
15     3     5     7     29       7

I want to a safe calculation to make my valBack equal to value. (Of course, in real data I don't have value column, just cumsum column)

arg0naut91 · Accepted Answer · 2019-07-20T14:15:19.060

1

Try:

library(dplyr)

df %>%
  group_by(id) %>%
  mutate(
    cumsum = cumsum(value),
    valBack = c(cumsum[1], (cumsum - lag(cumsum))[-1])
  )

Giving:

# A tibble: 15 x 5
# Groups:   id [3]
      id  hour value cumsum valBack
   <int> <int> <int>  <int>   <int>
 1     1     1    10     10      10
 2     1     2    13     23      13
 3     1     3     8     31       8
 4     1     4     4     35       4
 5     1     5     9     44       9
 6     2     1    12     12      12
 7     2     2    14     26      14
 8     2     3     5     31       5
 9     2     4    15     46      15
10     2     5     1     47       1
11     3     1     2      2       2
12     3     2     3      5       3
13     3     3     6     11       6
14     3     4    11     22      11
15     3     5     7     29       7

edited Jul 20 '19 at 14:15

answered Jul 20 '19 at 08:57

arg0naut91

14,574
2
17
38

how to adjust it to work for individual group as well? now it only replaces the first value in a column, not for each group. Thank you again, – maycca Jul 20 '19 at 09:08
It works for each group? Not sure what you mean then. – arg0naut91 Jul 20 '19 at 09:13
hi, I have updated my example to show that it does not produce the desired output. Hope it is clearer now? – maycca Jul 20 '19 at 09:25
It's not clearer as it does not produce the output you're describing. It matches exactly what you would like to get. Can you maybe restart R and load only `dplyr` and then execute the code? Somehow it seems your code is not grouping by `id` though this is exactly what it should do. – arg0naut91 Jul 20 '19 at 09:28
god, unbelievable!! Thank you, now it works. I have added `dplyr::mutate(valBack = c(cumsum[1], (cumsum - lag(cumsum))[-1]))` again and it works :) thank you :) – maycca Jul 20 '19 at 09:36

score 1 · Answer 2 · answered Jul 20 '19 at 17:19

While the accepted answer works, it is more complicated than it needs to be. If you look at lag function you would see that it has different arguments

dplyr::lag(x, n = 1L, default = NA, order_by = NULL, ...)

which here we can use default and set it to 0 to get the desired output. Look below:

library(dplyr)

df %>%
  group_by(id) %>%
  mutate(cumsum  = cumsum(value), 
         rawdata = cumsum - lag(cumsum, default = 0))

#> # A tibble: 15 x 5
#> # Groups:   id [3]
#>       id  hour value cumsum rawdata
#>    <int> <int> <int>  <int>   <dbl>
#>  1     1     1     2      2       2
#>  2     1     2     1      3       1
#>  3     1     3    13     16      13
#>  4     1     4    15     31      15
#>  5     1     5    10     41      10
#>  6     2     1     3      3       3
#>  7     2     2     8     11       8
#>  8     2     3     4     15       4
#>  9     2     4    12     27      12
#> 10     2     5    11     38      11
#> 11     3     1    14     14      14
#> 12     3     2     6     20       6
#> 13     3     3     5     25       5
#> 14     3     4     7     32       7
#> 15     3     5     9     41       9

calculate difference between rows, but keep the raw value by group

2 Answers2

Linked