Andrew Bannerman Andrew Bannerman - 1 year ago 68
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?

Answer Source

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