R Function looping over new row using previous row data

Question

I am trying to categorize incoming temperature data for early detection of an increasing trend (event =1) or a decreasing trend (event =2). The start of an uptrend is characterized by a 1% increase from the lowest point (Pl). The start of a downtrend is characterized by a 1% decrease from the highest point (Ph)

Dataset is initialized with trend=1, Ph and Pl = Temperature and I would like to loop over each new row, updating Pl/Ph and categorize the event type.

Dataset used

data <- data.frame (Temperature=c(93.37, 93.44, 93.22, 93.28, 93.32, 93.48, 93.32, 92.49, 92.21, 92.16, 91.31, 91.30, 91.37, 91.30, 91.21, 91.37, 91.59, 91.45, 92.07, 92.16, 92.35, 92.52, 92.48, 92.13, 92.46), 
                    event=c(1, rep(NA, 24)), Ph=c(93.37, rep(NA, 24)), Pl=c(93.37, rep(NA, 24)))

Expected result

data <- data.frame (Temperature=c(93.37, 93.44, 93.22, 93.28, 93.32, 93.48, 93.32, 92.49, 92.21, 92.16, 91.31, 91.30, 91.37, 91.30, 91.21, 91.37, 91.59, 91.45, 92.07, 92.16, 92.35, 92.52, 92.48, 92.13, 92.46), 
event=c(1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,1,1,1,1,1,1), 
Ph=c(93.37,NA,NA,NA,NA,NA,NA,92.49,92.21,92.16,91.31,91.3,91.3,91.3,91.21,91.21,91.21,91.21,91.21,NA,NA,NA,NA,NA,NA),
Pl=c(93.37,93.44,93.44,93.44,93.44,93.48,93.48,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,92.16,92.35,92.52,92.52,92.52,92.52))

I fail to convert the following code into a loop that can perform these commands on an historical dataset. I have tried a function as well as the apply family without success.

data$Ph_lag <- lag(data$Ph, 1)
data$Pl_lag <- lag(data$Pl, 1)

for(i in 2:nrow(data)) {

  if (data$event[i-1] == 1) {

    if (data$Temperature[i]  <= data$Ph_lag[i] * 0.99) { # missing value where TRUE/FALSE needed (=all NA ROWS)
      data$event[i]  <- 2
      data$Pl[i] <- data$Close[i]

    } else if (data$Temperature[i] > data$Ph_lag[i]) { 
      data$Ph[i] <- data$Temperature[i]
      data$event[i] <- 1

    } else {  
      data$Ph[i] <- data$Ph_lag[i]
      data$event[i] <- 1
    }  

  } else if (data$event[i-1] == 2) { 

    if (data$Temperature[i]  >= data$Pl_lag[i]  * 1.01) { 
      data$event[i]  <- 1
      data$Ph[i] <- data$Temperature[i]

    } else if (data$Temperature[i] < data$Pl_lag[i]) { 
      data$Pl[i] <- data$Temperature[i]
      data$event[i]  <- 2

    } else {  
      data$Pl[i] <- data$Pl_lag[i]
      data$event[i]  <- 2
    }}}

In its current state, this code is successful when applied to a single row, but can hardly be used to fill up historical data containing thousands of observations.

Comments appreciated, would be very grateful

The pasted function has no bug, it just does not loop over the newly updated rows, which is the objective. — Frédéric K., Jul 02 '19 at 12:21
Have a look on `data$Ph_lag` and `data$Pl_lag` - there are many NAs. A comparision with NA (e.g. in `if (data$Close[i] <= data$Ph_lag[i] * 0.99)`) results in NA. — jogo, Jul 03 '19 at 13:36
https://stackoverflow.com/questions/37247515/error-debug-missing-value-where-true-false-needed — jogo, Jul 03 '19 at 13:38
But how to direct the loop so that it uses row2 to compute row3, row3 to compute row4, etc by taking information computed from the previous output. The NAs originate from the fact that the loop does not update data row by row in sequence. Thanks a lot jogo — Frédéric K., Jul 03 '19 at 13:44
`if (data$Close[i] <= data$Ph[i-1] * 0.99)` ... But `data$Close` is not defined. — jogo, Jul 03 '19 at 13:48
Error in if (data$Temperature[i] <= data$Ph[i - 1] * 0.99) { : argument is of length zero — Frédéric K., Jul 03 '19 at 13:59

score 0 · Accepted Answer · answered Jul 03 '19 at 14:18

The following code runs without an error:

data <- data.frame (Temperature=c(93.37, 93.44, 93.22, 93.28, 93.32, 93.48, 93.32, 92.49, 92.21, 92.16, 91.31, 91.30, 91.37, 91.30, 91.21, 91.37, 91.59, 91.45, 92.07, 92.16, 92.35, 92.52, 92.48, 92.13, 92.46), 
                    event=c(1, rep(NA, 24)), Ph=c(93.37, rep(NA, 24)), Pl=c(93.37, rep(NA, 24)))

result <- data.frame (Temperature=c(93.37, 93.44, 93.22, 93.28, 93.32, 93.48, 93.32, 92.49, 92.21, 92.16, 91.31, 91.30, 91.37, 91.30, 91.21, 91.37, 91.59, 91.45, 92.07, 92.16, 92.35, 92.52, 92.48, 92.13, 92.46), 
                    event=c(1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,1,1,1,1,1,1), 
                    Ph=c(93.37,NA,NA,NA,NA,NA,NA,92.49,92.21,92.16,91.31,91.3,91.3,91.3,91.21,91.21,91.21,91.21,91.21,NA,NA,NA,NA,NA,NA),
                    Pl=c(93.37,93.44,93.44,93.44,93.44,93.48,93.48,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,92.16,92.35,92.52,92.52,92.52,92.52))

#data$Ph_lag <- lag(data$Ph, 1)
#data$Pl_lag <- lag(data$Pl, 1)

for(i in 2:nrow(data)) {

  if (data$event[i-1] == 1) {

    if (data$Temperature[i]  <= data$Ph[i-1] * 0.99) { # missing value where TRUE/FALSE needed (=all NA ROWS)
      data$event[i]  <- 2
      data$Pl[i] <- data$Temperature[i]

    } else if (data$Temperature[i] > data$Ph[i-1]) { 
      data$Ph[i] <- data$Temperature[i]
      data$event[i] <- 1

    } else {  
      data$Ph[i] <- data$Ph[i-1]
      data$event[i] <- 1
    }  

  } else if (data$event[i-1] == 2) { 

    if (data$Temperature[i]  >= data$Pl[i-1]  * 1.01) { 
      data$event[i]  <- 1
      data$Ph[i] <- data$Temperature[i]

    } else if (data$Temperature[i] < data$Pl[i-1]) { 
      data$Pl[i] <- data$Temperature[i]
      data$event[i]  <- 2

    } else {  
      data$Pl[i] <- data$Pl[i-1]
      data$event[i]  <- 2
    }}}

But the result is not exactly what you attended.

Many many thanks jogo! The error was coming from the fact that I used an xts object, and coredata() was not enough to convert it into a dataframe (I missed that). Now it works perfectly, thanks for your wonders — Frédéric K., Jul 04 '19 at 05:43

R Function looping over new row using previous row data

1 Answers1