How to calculate year to date and 12 months variations in r

user_19852122 0
Viewed 51

I have data of multiple years (in this example only 3). Need to calculate year to date and 12 months variations in r. Year-to-date variations are as follows:

  1. Year to date variation January 2021= Value January 2021/ Value January 2020.
  2. Year to date variation February 2021= Sum(Value January 2021: February 2021)/Sum(Value January 2020: February 2020)
  3. Year to date variation March 2021= Sum(Value January 2021: March 2021)/Sum(Value January 2020: March 2020)

12 months variations are as follows:

  1. 12 months variation January 2021= Sum (Value February 2020:Value January 2021)/ Sum(Value February 2019:Value January 2020).

  2. 12 months variation February 2021= Sum (Value March 2020:Value February 2021)/ Sum(Value March 2019:Value February 2020).

    Year Month Value Year to date variations R 12 mothns
    2019 1 182
    2019 2 160
    2019 3 170
    2019 4 123
    2019 5 165
    2019 6 153
    2019 7 152
    2019 8 182
    2019 9 156
    2019 10 141
    2019 11 161
    2019 12 193
    2020 1 143 0,785714285714286
    2020 2 138 0,821637426900585
    2020 3 113 0,76953125
    2020 4 127 0,820472440944882
    2020 5 107 0,785
    2020 6 185 0,853095487932844
    2020 7 125 0,848868778280543
    2020 8 109 0,813519813519814
    2020 9 167 0,841302841302841
    2020 10 128 0,847222222222222
    2020 11 139 0,848710601719198
    2020 12 173 0,853457172342621
    2021 1 111 0,776223776223776 0,854133754607688
    2021 2 169 0,99644128113879 0,88066062866276
    2021 3 125 1,02791878172589 0,914835164835165
    2021 4 134 1,03454894433781 0,916666666666667
    2021 5 112 1,03662420382166 0,94960362400906
    2021 6 114 0,940959409594096 0,89321468298109
    2021 7 171 0,997867803837953 0,932806324110672
    2021 8 161 1,04775549188157 1,00353356890459
    2021 9 195 1,06425041186161 1,01345816266823
    2021 10 150 1,07451564828614 1,03419811320755
    2021 11 181 1,09588116137745 1,07287933094385
    2021 12 163 1,07980652962515 1,07980652962515
r
1 Answers

You can do this by leveraging the power of data.table::shift().

library(data.table)

setDT(df)

df <- df[order(Month,Year),p:=shift(Value),Month] %>% 
  .[,ytd_var:=cumsum(Value)/cumsum(p),Year] %>% 
  .[,`:=`(mon_var=Reduce(`+`, shift(Value,0:11))/Reduce(`+`, shift(Value,12:23)), p=NULL)]

Explanation:

  1. First, get the 12-month prior value (p) for each month, by ordering by Month and Year, and using shift(Value), by Month.

  2. Then, create the year-to-date value (ytd_var) by dividing the cumulative sum of Value by the cumulative sum of p, by Year.

  3. Finally, create the 12-month variation value (mon_var) by dividing the sum of Value in the current and prior 11 months by the sum of Value in months 12 thru 23 prior to the current month. Notice that I use Reduce() to sum over all these prior values.

Output:

    Year Month Value   ytd_var   mon_var
 1: 2019     1   182        NA        NA
 2: 2019     2   160        NA        NA
 3: 2019     3   170        NA        NA
 4: 2019     4   123        NA        NA
 5: 2019     5   165        NA        NA
 6: 2019     6   153        NA        NA
 7: 2019     7   152        NA        NA
 8: 2019     8   182        NA        NA
 9: 2019     9   156        NA        NA
10: 2019    10   141        NA        NA
11: 2019    11   161        NA        NA
12: 2019    12   193        NA        NA
13: 2020     1   143 0.7857143        NA
14: 2020     2   138 0.8216374        NA
15: 2020     3   113 0.7695312        NA
16: 2020     4   127 0.8204724        NA
17: 2020     5   107 0.7850000        NA
18: 2020     6   185 0.8530955        NA
19: 2020     7   125 0.8488688        NA
20: 2020     8   109 0.8135198        NA
21: 2020     9   167 0.8413028        NA
22: 2020    10   128 0.8472222        NA
23: 2020    11   139 0.8487106        NA
24: 2020    12   173 0.8534572 0.8534572
25: 2021     1   111 0.7762238 0.8541338
26: 2021     2   169 0.9964413 0.8806606
27: 2021     3   125 1.0279188 0.9148352
28: 2021     4   134 1.0345489 0.9166667
29: 2021     5   112 1.0366242 0.9496036
30: 2021     6   114 0.9409594 0.8932147
31: 2021     7   171 0.9978678 0.9328063
32: 2021     8   161 1.0477555 1.0035336
33: 2021     9   195 1.0642504 1.0134582
34: 2021    10   150 1.0745156 1.0341981
35: 2021    11   181 1.0958812 1.0728793
36: 2021    12   163 1.0798065 1.0798065

Input:

df = structure(list(Year = c(2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 
2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2020L, 2020L, 2020L, 
2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 
2021L, 2021L, 2021L, 2021L, 2021L, 2021L, 2021L, 2021L, 2021L, 
2021L, 2021L, 2021L), Month = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 
9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 
12L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L), Value = c(182L, 
160L, 170L, 123L, 165L, 153L, 152L, 182L, 156L, 141L, 161L, 193L, 
143L, 138L, 113L, 127L, 107L, 185L, 125L, 109L, 167L, 128L, 139L, 
173L, 111L, 169L, 125L, 134L, 112L, 114L, 171L, 161L, 195L, 150L, 
181L, 163L)), row.names = c(NA, -36L), class = "data.frame")
user_44475400
Related