leeon - 2 months ago 6
R Question

# R find next row with specific value

Code to recreate the example:

``````library(data.table)
data = data.table(
open = c(1.19535, 1.19619, 1.19632, 1.19620, 1.19565),
low = c(1.19534, 1.19608, 1.19617, 1.19554, 1.19556),
sl = c(1.19525, 1.19569, 1.19582, 1.19570, 1.19515)
)
``````

I have the following data:

``````      open     low      sl
1: 1.19535 1.19534 1.19525
2: 1.19619 1.19608 1.19569
3: 1.19632 1.19617 1.19582
4: 1.19620 1.19554 1.19570
5: 1.19565 1.19556 1.19515
``````

These are stock prices, each row represents one minute period. Columns show their open price (first column), lowest price in the period (low) and stop loss (sl)

What I would like to calculate is the line at which my stop loss is hit. for example:

line 1: we open position at 1.19535. We would close it if the price goes below 1.19525. The price in the observed period does not go so low (the lowest value in the column 'low' is 1.19534, so we do not close it. If we do not close the position I want value -1.

line 2: we open position at 1.19619. We would close it if the price goes below 1.19569. This happens in line 4, where 'low' is 1.19554. In this case I want value 4, since this is the number of the row where the position is close.

So the desired result is the following:

``````      open     low      sl row
1: 1.19535 1.19534 1.19525  -1
2: 1.19619 1.19608 1.19569   4
3: 1.19632 1.19617 1.19582   4
4: 1.19620 1.19554 1.19570   4
5: 1.19565 1.19556 1.19515  -1
``````

I can achieve this result with the following loop:

``````data\$row = 0
for (d in 1:nrow(data)){
data[d,4] = ifelse(length(head(which(data\$low[d:nrow(data)] <= data\$sl[d]), 1)+d-1) == 0
, -1
)
}; data
``````

But loops are slow, is there any other way?

``````v <- sapply(data\$sl, function(x) { any(data\$low < x) })
``````#[1] -1  4  4  4 -1