r - Change value of some column in xts based on other columns values with lookback -
i have following xts object (representing long/short entries (column 1 , 2) , exit (columns 3 , 4) triggers "aggregate" signal column should 1 (system long), -1 (system short) or 0 (system flat). can not make work "aggregate" signal column5...
the data:
longentrysignal shortentrysignal longexitsignal shortexitsignal signal 18.02.93 0 0 1 0 0 19.02.93 0 0 0 1 0 22.02.93 1 0 0 0 1 23.02.93 0 0 0 0 0 24.02.93 0 0 0 0 0 25.02.93 0 0 0 0 0 26.02.93 0 0 1 0 0 01.03.93 0 0 1 0 0 04.03.93 0 1 0 0 -1 05.03.93 0 0 0 0 0 11.03.93 0 0 0 1 0 12.03.93 0 0 1 0 0 i transform data in form:
longentrysignal shortentrysignal longexitsignal shortexitsignal signal 18.02.93 0 0 1 0 0 19.02.93 0 0 0 1 0 22.02.93 1 0 0 0 1 23.02.93 0 0 0 0 1 24.02.93 0 0 0 0 1 25.02.93 0 0 0 0 1 26.02.93 0 0 1 0 1 01.03.93 0 0 1 0 0 04.03.93 0 1 0 0 -1 05.03.93 0 0 0 0 -1 11.03.93 0 0 0 1 -1 12.03.93 0 0 1 0 0 i tried uprogramming function below (but id not work; commented out part not work , terribly slow - aware of using loops in r slow idea had):
padminusplusones<-function(signals, longentrycolumn=1, shortentrycolumn=2, signalscolumn=5) { ret<-signals #get indexes between long entry equals 1 long exit equals 1 , set signalscolumn these rows 1 ret[(lag(ret)[, longentrycolumn] == 1) & (ret[, signalscolumn] == 0), signalscolumn]<-1 #get indexes between short entry equals 1 short exit equals 1 , set signalscolumn these rows -1 ret[(lag(ret)[, shortentrycolumn] == -1) & (ret[, signalscolumn] == 0), signalscolumn]<--1 return(ret) # ret<-signals # (i in 2:nrow(ret)) { # if ((ret[i - 1, longentrycolumn] == 1) & (ret[, signalscolumn] == 0)) { # ret[i, signalscolumn]<-1 # } # if ((ret[i - 1, shortentrycolumn] == -1) & (ret[, signalscolumn] == 0)) { # ret[i, signalscolumn]<--1 # } # } # # return(ret) } thanks kind in how transform data.
kind regards, samo.
edit note: after receving 2 helpful , insightful answers prasad chalasani , j. winchester realized left out important information how data structured. changed data above better reflect data , copied original (based on 2 answers based) below:
the data:
longentrysignal shortentrysignal longexitsignal shortexitsignal signal 18.02.93 0 0 0 0 0 19.02.93 0 0 0 0 0 22.02.93 1 0 0 0 1 23.02.93 0 0 0 0 0 24.02.93 0 0 0 0 0 25.02.93 0 0 0 0 0 26.02.93 0 0 1 0 0 01.03.93 0 0 0 0 0 04.03.93 0 1 0 0 -1 05.03.93 0 0 0 0 0 11.03.93 0 0 0 1 0 12.03.93 0 0 0 0 0 i transform data in form:
longentrysignal shortentrysignal longexitsignal shortexitsignal signal 18.02.93 0 0 0 0 0 19.02.93 0 0 0 0 0 22.02.93 1 0 0 0 1 23.02.93 0 0 0 0 1 24.02.93 0 0 0 0 1 25.02.93 0 0 0 0 1 26.02.93 0 0 1 0 1 01.03.93 0 0 0 0 0 04.03.93 0 1 0 0 -1 05.03.93 0 0 0 0 -1 11.03.93 0 0 0 1 -1 12.03.93 0 0 0 0 0
you don't need use loops, nor need "lookback". can use vectorized function cumsum want. assuming long entry/exit , short entry/exit periods non-overlapping, can this: first make dummy signals:
n <- 15 zeros <- rep(0,n) longent <- replace(zeros, c(1, 12), 1) longex <- replace(zeros, c(4, 14), 1) shortent <- replace(zeros, 6, 1) shortex <- replace(zeros, 10, 1) now cumsum magic right "aggregate" signal column:
siglong <- cumsum(longent) - cumsum(longex) + longex sigshort <- -cumsum(shortent) + cumsum(shortex) - shortex > cbind(longent, longex, shortent, shortex, signal = siglong + sigshort) longent longex shortent shortex signal [1,] 1 0 0 0 1 [2,] 0 0 0 0 1 [3,] 0 0 0 0 1 [4,] 0 1 0 0 1 [5,] 0 0 0 0 0 [6,] 0 0 1 0 -1 [7,] 0 0 0 0 -1 [8,] 0 0 0 0 -1 [9,] 0 0 0 0 -1 [10,] 0 0 0 1 -1 [11,] 0 0 0 0 0 [12,] 1 0 0 0 1 [13,] 0 0 0 0 1 [14,] 0 1 0 0 1 [15,] 0 0 0 0 0 update. according op's modified question, need handle case of arbitrary sequence of entry/exit signals, , find periods between first entry , corresponding first exit. here's way simple arihtmetic operations (i.e. no expensive lookbacks or if/else checking). need small modification of cumsum function call cumplus -- cumsum, except after taking each sum, replaces 1 or 0 depending on whether it's positive or not:
cumplus <- function(y) reduce(function(a,b) + b > 0, y, 0, accum=true)[-1] (incidentally, reduce nice way compactly define cumulative function without explicitly writing out for loop -- see ?reduce details).
now take example of entry/exit signals:
longent <- c(1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0) longex <- c(0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 1) x <- longent - longex z <- cumplus(x) this want... need insert 1s @ end of each ones-block.
z <- z - c(0,pmin(0,diff(z))) > cbind(longent, longex, signal = z) longent longex signal [1,] 1 0 1 [2,] 0 0 1 [3,] 0 0 1 [4,] 1 0 1 [5,] 0 0 1 [6,] 0 0 1 [7,] 1 0 1 [8,] 0 0 1 [9,] 0 1 1 [10,] 0 0 0 [11,] 0 0 0 [12,] 0 1 0 [13,] 1 0 1 [14,] 0 0 1 [15,] 0 0 1 [16,] 1 0 1 [17,] 0 0 1 [18,] 0 0 1 [19,] 0 1 1 [20,] 0 0 0 [21,] 0 1 0 [22,] 1 0 1 [23,] 0 0 1 [24,] 0 1 1 dealing short entry/exits of course similar.
Comments
Post a Comment