leeon 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
, head(which(data$low[d:nrow(data)] <= data$sl[d]), 1)+d-1
)
}; data


But loops are slow, is there any other way?

Answer

You could try:

v <- sapply(data$sl, function(x) { any(data$low < x) })
ifelse(v, which(data$low == min(data$low[v])), -1)

Which gives:

#[1] -1  4  4  4 -1
Comments