How to calculate differences from 12-month cumulative values by year and month in data frame?

General
1

How to calculate differences from 12-month cumulative values by year and month in data frame?

df <- data.frame(obl=rep(1:2,each=24),
year=rep(2004:2005, each=12),
month=rep(1:12,2005-2004+1),
cum_month=cumsum(1:12))

df
obl year month cum_month
1 1 2004 1 1
2 1 2004 2 3
3 1 2004 3 6
4 1 2004 4 10
5 1 2004 5 15
6 1 2004 6 21
7 1 2004 7 28
8 1 2004 8 36
9 1 2004 9 45
10 1 2004 10 55
11 1 2004 11 66
12 1 2004 12 78
13 1 2005 1 1
14 1 2005 2 3
15 1 2005 3 6
16 1 2005 4 10
17 1 2005 5 15
18 1 2005 6 21
19 1 2005 7 28
20 1 2005 8 36
21 1 2005 9 45
22 1 2005 10 55
23 1 2005 11 66
24 1 2005 12 78
25 2 2004 1 1
26 2 2004 2 3
27 2 2004 3 6
28 2 2004 4 10
29 2 2004 5 15
30 2 2004 6 21
31 2 2004 7 28
32 2 2004 8 36
33 2 2004 9 45
34 2 2004 10 55
35 2 2004 11 66
36 2 2004 12 78
37 2 2005 1 1
38 2 2005 2 3
39 2 2005 3 6
40 2 2005 4 10
41 2 2005 5 15
42 2 2005 6 21
43 2 2005 7 28
44 2 2005 8 36
45 2 2005 9 45
46 2 2005 10 55
47 2 2005 11 66
48 2 2005 12 78

out
obl year month cum_month dif_cum
1 1 2004 1 1 1
2 1 2004 2 3 2
3 1 2004 3 6 3
4 1 2004 4 10 4
5 1 2004 5 15 5
6 1 2004 6 21 6
7 1 2004 7 28 7
8 1 2004 8 36 8
9 1 2004 9 45 9
10 1 2004 10 55 10
11 1 2004 11 66 11
12 1 2004 12 78 12
13 1 2005 1 1 1
14 1 2005 2 3 2
15 1 2005 3 6 3
16 1 2005 4 10 4
17 1 2005 5 15 5
18 1 2005 6 21 6
19 1 2005 7 28 7
20 1 2005 8 36 8
21 1 2005 9 45 9
22 1 2005 10 55 10
23 1 2005 11 66 11
24 1 2005 12 78 12
25 2 2004 1 1 1
26 2 2004 2 3 2
27 2 2004 3 6 3
28 2 2004 4 10 4
29 2 2004 5 15 5
30 2 2004 6 21 6
31 2 2004 7 28 7
32 2 2004 8 36 8
33 2 2004 9 45 9
34 2 2004 10 55 10
35 2 2004 11 66 11
36 2 2004 12 78 12
37 2 2005 1 1 1
38 2 2005 2 3 2
39 2 2005 3 6 3
40 2 2005 4 10 4
41 2 2005 5 15 5
42 2 2005 6 21 6
43 2 2005 7 28 7
44 2 2005 8 36 8
45 2 2005 9 45 9
46 2 2005 10 55 10
47 2 2005 11 66 11
48 2 2005 12 78 12

2

I would do it this way

(df <- data.frame(obl=rep(1:2,each=24),
                 year=rep(2004:2005, each=12),
                 month=rep(1:12,2005-2004+1),
                 cum_month=cumsum(1:12)))

library(tidyverse)

(result_df <- group_by(df,
                      obl,
                      year) %>%
              mutate(cum_dif = case_when(is.na(lag(cum_month)) ~ cum_month,
                                          TRUE ~ cum_month - lag(cum_month))))
3

Many thanks, it works very well!

4

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.