I want to transfer the negative values at the current row to the previous row by adding them to the previous row within each group. Following is the sample raw data I have:
raw_data <- data.frame(GROUP = rep(c('A','B','C'),each = 6),
YEARMO = rep(c(201801:201806),3),
VALUE = c(100,-10,20,70,-50,30,20,60,40,-20,-10,50,0,10,-30,50,100,-100))
> raw_data
GROUP YEARMO VALUE
1 A 201801 100
2 A 201802 -10
3 A 201803 20
4 A 201804 70
5 A 201805 -50
6 A 201806 30
7 B 201801 20
8 B 201802 60
9 B 201803 40
10 B 201804 -20
11 B 201805 -10
12 B 201806 50
13 C 201801 0
14 C 201802 10
15 C 201803 -30
16 C 201804 50
17 C 201805 100
18 C 201806 -100
Following is the output that I want:
final_data <- data.frame(GROUP = rep(c('A','B','C'),each = 6),
YEARMO = rep(c(201801:201806),3),
VALUE = c(90,0,20,20,0,30,20,60,10,0,0,50,-20,0,0,50,0,0))
> final_data
GROUP YEARMO VALUE
1 A 201801 90
2 A 201802 0
3 A 201803 20
4 A 201804 20
5 A 201805 0
6 A 201806 30
7 B 201801 20
8 B 201802 60
9 B 201803 10
10 B 201804 0
11 B 201805 0
12 B 201806 50
13 C 201801 -20
14 C 201802 0
15 C 201803 0
16 C 201804 50
17 C 201805 0
18 C 201806 0
Following data frames will show how the transformation can be made in each group:
Trans_GRP_A <- data.frame(GROUP = rep('A',each = 6),
YEARMO = c(201801:201806),
VALUE = c(100,-10,20,70,-50,30),
ITER_1 = c(100,-10,20,20,0,30),
ITER_2 = c(90,0,20,20,0,30))
> Trans_GRP_A
GROUP YEARMO VALUE ITER_1 ITER_2
1 A 201801 100 100 90
2 A 201802 -10 -10 0
3 A 201803 20 20 20
4 A 201804 70 20 20
5 A 201805 -50 0 0
6 A 201806 30 30 30
> Trans_GRP_B <- data.frame(GROUP = rep('B',each = 6),
+ YEARMO = c(201801:201806),
+ VALUE = c(20,60,40,-20,-10,50),
+ ITER_1 = c(20,60,40,-30,0,50),
+ ITER_2 = c(20,60,10,0,0,50))
> Trans_GRP_B
GROUP YEARMO VALUE ITER_1 ITER_2
1 B 201801 20 20 20
2 B 201802 60 60 60
3 B 201803 40 40 10
4 B 201804 -20 -30 0
5 B 201805 -10 0 0
6 B 201806 50 50 50
> Trans_GRP_C <- data.frame(GROUP = rep('C',each = 6),
+ YEARMO = c(201801:201806),
+ VALUE = c(0,10,-30,50,100,-100),
+ ITER_1 = c(0,10,-30,50,0,0),
+ ITER_2 = c(0,-20,0,50,0,0),
+ ITER_3 = c(-20,0,0,50,0,0))
> Trans_GRP_C
GROUP YEARMO VALUE ITER_1 ITER_2 ITER_3
1 C 201801 0 0 0 -20
2 C 201802 10 10 -20 0
3 C 201803 -30 -30 0 0
4 C 201804 50 50 50 50
5 C 201805 100 0 0 0
6 C 201806 -100 0 0 0
The logic for transfer is as follows:
Any solution is welcome. I think a solution which is vectorized might perform faster.
Here is another option to sum the positive part of the vector with the shifted negative part of the vector recursively until there are no more negative values left or it has been executed .N times (where .N is the number of row for each GROUP)
setDT(raw_data)[, OUTPUT := {
posVal <- replace(VALUE, VALUE<0, 0)
negVal <- replace(VALUE, VALUE>0, 0)
n <- 1L
while (any(negVal < 0) && n < .N) {
posVal <- replace(posVal, posVal<0, 0) +
shift(negVal, 1L, type="lead", fill=0) +
c(negVal[1L], rep(0, .N-1L))
negVal <- replace(posVal, posVal>0, 0)
n <- n + 1L
}
posVal
}, by=.(GROUP)]
output:
GROUP YEARMO VALUE OUTPUT
1: A 201801 100 90
2: A 201802 -10 0
3: A 201803 20 20
4: A 201804 70 20
5: A 201805 -50 0
6: A 201806 30 30
7: B 201801 20 20
8: B 201802 60 60
9: B 201803 40 10
10: B 201804 -20 0
11: B 201805 -10 0
12: B 201806 50 50
13: C 201801 0 -20
14: C 201802 10 0
15: C 201803 -30 0
16: C 201804 50 50
17: C 201805 100 0
18: C 201806 -100 0