SBS SBS - 3 years ago 63
R Question

Column addition in based on condition of another column using R

I have data frame

mydata
as follows,

ID TS TB TC
1 1.7360 -1 0
2 1.7302 -1 0.254
3 1.7244 0 0.624
4 1.7232 0 0.254
5 1.7208 0 1.25
6 1.7208 0 0
7 1.7208 0 0
8 1.7023 0 0
9 1.6814 0 0
10 1.6768 1 0
11 1.6746 0 6.25
12 1.6503 0 0.2547
13 1.6258 0 0.987
14 1.6190 0 0.3654
15 1.6154 0 0.6251
16 1.6258 0 0.369
17 1.6397 0 0
18 1.6443 0 0
19 1.6491 0 0
20 1.6503 0 0


Now I need to add/subtract values of
TS and TC
and create another new column
TSC
with a condition that if there is
-1
in column
TB
then
TSC == TS + TC
until there is another
+1 or -1
in
TB
, else if there is
1
in column
TB
then
TSC == TS - TC
until there is another
+1 or -1
in
TB
. Also there are possibilities of having
0
at the begining of the data frame in the column
B
then
Tsc == TS +TC
.

Below is my final result

ID TS TB TC TSC
1 1.73602 -1 0 1.736020823
2 1.73023 -1 0.254 1.984238239
3 1.72445 0 0.624 2.348455656
4 1.72326 0 0.254 1.977269484
5 1.72089 0 1.25 2.970897142
6 1.72089 0 0 1.720897142
7 1.72089 0 0 1.720897142
8 1.70236 0 0 1.70236322
9 1.68145 0 0 1.681456955
10 1.67686 1 0 1.676860542
11 1.67463 0 6.25 4.575363528
12 1.65031 0 0.2547 1.395619965
13 1.62585 0 0.987 0.638855188
14 1.61903 0 0.3654 1.253634704
15 1.61547 0 0.6251 0.990376191
16 1.62585 0 0.369 1.256855188
17 1.63979 0 0 1.639792697
18 1.64438 0 0 1.64438911
19 1.64913 0 0 1.649133794
20 1.65031 0 0 1.650319965

Answer Source

By constructing a vector of +1 for adding and -1 for subtracting its easy. So how do we construct that?

First constructing a vector replacing all the zeroes with the previous non-zero. By using rle we can construct a vector of how many times -1, 1, or 0 appears, and then replace the zeroes with the previous non-zero, then inverse that and get a vector of 1 and -1 only.

Stick a -1 on the start to satisfy the "if starts with zero, subtract" condition:

> rx = rle(c(-1,mydata$TB))

Now see where the zeroes are:

> wz = which(rx$values==0)

Set the zeroes to whatever the previous values were.

> rx$values[wz]=rx$values[wz-1]

Now expand to a vector of 1 and -1, chopping off the first to get rid of that -1 we started with. Also, make it +1 for adding and -1 for subtracting:

> mydata$TBop = -inverse.rle(rx)[-1]

Then do the operation:

> mydata$TSC=mydata$TS + mydata$TBop*mydata$TC
> mydata
    ID     TS TB     TC TBop     TSC
 1:  1 1.7360 -1 0.0000    1  1.7360
 2:  2 1.7302 -1 0.2540    1  1.9842
 3:  3 1.7244  0 0.6240    1  2.3484
 4:  4 1.7232  0 0.2540    1  1.9772
 5:  5 1.7208  0 1.2500    1  2.9708
 6:  6 1.7208  0 0.0000    1  1.7208
 7:  7 1.7208  0 0.0000    1  1.7208
 8:  8 1.7023  0 0.0000    1  1.7023
 9:  9 1.6814  0 0.0000    1  1.6814
10: 10 1.6768  1 0.0000   -1  1.6768
11: 11 1.6746  0 6.2500   -1 -4.5754
12: 12 1.6503  0 0.2547   -1  1.3956
13: 13 1.6258  0 0.9870   -1  0.6388
14: 14 1.6190  0 0.3654   -1  1.2536
15: 15 1.6154  0 0.6251   -1  0.9903
16: 16 1.6258  0 0.3690   -1  1.2568
17: 17 1.6397  0 0.0000   -1  1.6397
18: 18 1.6443  0 0.0000   -1  1.6443
19: 19 1.6491  0 0.0000   -1  1.6491
20: 20 1.6503  0 0.0000   -1  1.6503

Probably best to write a testable function for the operator:

Top <-
function(x){
 rx = rle(c(-1,x))
 wz = which(rx$values==0)
 rx$values[wz] = rx$values[wz-1]
 -inverse.rle(rx)[-1]
}

Then you can check simple examples:

> Top(0) # add
[1] 1
> Top(-1) # add
[1] 1
> Top(1) # subtract
[1] -1

> Top(c(0,-1,1,-1,0,0,0))
[1]  1  1 -1  1  1  1  1   # add, add, sub, add, add, add, add

> Top(mydata$TB)
[1]  1  1  1  1  1  1  1  1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1

Then your solution is a one-liner:

> mydata$TSC = mydata$TS + Top(mydata$TB) * mydata$TC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download