Andrew Bannerman - 2 years ago 95
R Question

# Loop until future values are higher than previous fixed value (max nrow)

I have data within a data frame at which I would wish to create a loop in order to print a

`+1`
until a future close value is higher than my current signal value with a maximum nrow loop reset of 10 rows. If the signal column value is
`+1`
, the loop is to use the
`corresponding Close value`
as a reference point of comparison. The loop would then compare the next
`row 3`
(Close) with the previous
`row 2`
(Close) until it was higher, and print
`+1`
leading up to the higher close, in this case the condition was higher 1 row later, so after the higher close was met, we proceed to print
`0`
until next
`signal = 1`
.

As another example on
`line 5`
, we see a
`+1`
in the
`signal column`
. We then begin the loop to compare the
`next day Close`
,
`row 6`
, with the previous
`row 5 Close`
, if its not true we print a
`1`
. It then loops to the
`next row 7`
and compares this close value with the
`close on row 5`
.If its false it prints
`1`
. Then it goes to the next row at
`row 7`
and compares
`row 7 close`
to see if higher than
`row 5 close`
, if false =
`1`
. On
`row 8`
we have a final
`+1`
because row
`8 close`
was higher than
`row 5 close`
.
`After we have met a higher future price per row 8 close, we then print 0's until we find the next +1 signal column`
, on match... it compares each next row with the close on the matching signal day.... Its important that on a new +1 signal that this is the line for close comparison so we can print +1's until we meet a future close value which is higher than the +1 signal close. Again after the higher value is found within a 10 row window, we proceed to print 0 until next signal +1.

Note desired output and example data below:

``````    Close             signal      Loop until Close is higher than +1 signal close
1   1455.17004           0
2   1399.42004           1
3   1402.10999           0                        1
4   1403.44995           0                        0
5   1400.11              1                        0
6   1345.56              0                        1
7   1300.2               0                        1
8   1432.04004           0                        1
9   1449.68005           0                        0
10  1465.15002           0                        0
11  1455.14001           1                        0
12  1450.89              0                        1
13  1445.56995           0                        1
14  1441.35999           0                        1
15  1401.68005           0                        1
16  1410.03003           0                        1
17  1404.08997           0                        1
18  1500.23              0                        1
19  1360.15002           0                        0
20  1394.45996           0                        0
21  1409.28003           0                        0
22  1409.12              0                        0
23  1424.96997           0                        0
24  1424.37              0                        0
``````

One caveat to all of this is that I only want a maximum loop to travel +10 lines after +1 signal. If the 'nlines' or nrow has exceeded 10 lines the loop would print 0's until it sees a +1 signal where it would begin the loop all over again with max nrow travel of 10 rows. I guess we are adding a maximum nrow loop condition in and above to the actual loop printing +1 until a future Close value is above the corresponding signal +1 close value.

Phew - let me know if im making sense! This one seems challenging in many respects

Edit

Here is an example output of shians code:

``````65  3351.45      1        0
66  3313.01      0        0
67  3305.11      0        0
68  3243.03      0        0
69  3231.64      0        0
70  3275.34      0        0
71  3369.39      0        1
72  2571.09      1        0
73  2562.65      0        0
74  2625.55      0        0
75  2710.54      1        1
76  2768.17      0        0
77  2821.65      0        0
78  2822.49      0        0
79  2908.72      0        0
80  2909.46      1        0
``````

We just need to print 1 when price is less than or equal to comparison price. I also noted line 75. We have a signal +1 at the same time as a higher close. The next higher close after line 75 should place a +1 on line 76. Maybe we can take another look at it?

Here's a skeleton of a solution

``````prices <- round(cumsum(c(1400, rnorm(99, 20, 40))), 2)
signal <- sample(c(0, 1), 100, replace = TRUE, prob = c(0.9, 0.1))
df <- data.frame(
price = prices,
signal = signal,
response = 0
)

state <- "off"
for (i in 1:nrow(df)) { # loop through data
if (state == "off") { # off state, loop does nothing until signal = 1
if (df\$signal[i] == 0) {
next
} else { # signal = 1 encountered
comparison_price <- df\$price[i] # save current price for comparing
n_comparisons <- 0              # keep track of how many comparisons
state <- "on"                   # change state to "on"
}
} else if (state == "on") {
df\$response[i] <- 1
if (df\$price[i] > comparison_price) { # found higher price
state <- "off"
if (df\$signal[i] == 1) {
comparison_price <- df\$price[i]
n_comparisons <- 0
state <- "on"
}
} else if (n_comparisons == 10) { # hit comparison limit
state <- "off"
n_comparisons <- 0
if (df\$signal[i] == 1) {
comparison_price <- df\$price[i]
n_comparisons <- 0
state <- "on"
}
} else { # price less or equal to comparison price
n_comparisons <- n_comparisons + 1
}
}
}

df
``````

The key is to maintain some kind of state as you traverse your data, here I've used a simple `"on"/"off"` state. The search for the a future higher value only turns `"on"` after hitting a 1 signal. It also turns itself off after 10 failed comparisons. As is it does not handle the case where it finds a higher value on the same row as a 1 signal, it will shut itself off on such a row but you can add code to turn it back on.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download