My approach - ifelse, or?

[Ollie]

Hi,

I'm not necessarily anticipating a code fix, I supect it's more likely my approach is wrong. I am wondering if I should perhaps use some kind or loop, or other function, instead of ifelse?

I have a data set of daily foreign exchange prices, and I want to compare the high and low values of previous days/rows to identify the type of day that occured. I have tried using multiple comparisons in an if else argument with the '|' or operator. I get this error "... ! operations are possible only for numeric, logical or complex types". Note, I am using dplyr.

DT
Open High Low Close
1 135.146 135.146 135.146 135.146
2 135.184 137.128 135.180 136.430
3 136.430 136.644 135.539 135.713
4 135.713 136.866 135.680 136.809
5 136.809 136.935 135.514 136.273
6 136.273 136.893 136.253 136.591

My code attempt:

day_type <- DT %>%
  mutate(d_type = ifelse(High < lag(High) & Low < lag(Low), "Up", "0") | #Up day
                           Low > lag(Low) & High > lag(High), "Down", "0" | #Down Day 
                          High > lag(High) & Low > lag(Low), "Inside", "0" | #Inside Day
                          High < lag(High) & Low > lag(Low), "Outside", "0") #Outside Day

Advise much appreciated

[Matthias]

ifelse() isn't sufficient here as you have too many comparisons to make,
this is better solved using case_when() which can be seen as a sequential if-statement. So in principal you can add all potential cases and whenever one is true, the result (indicated by the ~ ) is then written into that line.

library(tidyverse)

DT = tribble(
  ~Open, ~High, ~Low, ~Close,
135.146,135.146,135.146,135.146,
135.184,137.128,135.180,136.430,
136.430,136.644,135.539,135.713,
135.713,136.866,135.680,136.809,
136.809,136.935,135.514,136.273,
136.273,136.893,136.253,136.591)

day_type <- DT %>%
  mutate(d_type = case_when( 
           High < lag(High) & Low < lag(Low) ~ "Up",        #Up day
           High > lag(High) & Low > lag(Low) ~ "Down",      #Down Day 
           # Attention the comparison leading to down and inside are identical!
           High > lag(High) & Low > lag(Low) ~ "Inside",    #Inside Day
           High < lag(High) & Low > lag(Low) ~ "Outside"   #Outside Day
  ))

day_type
# A tibble: 6 x 5
   Open  High   Low Close d_type 
  <dbl> <dbl> <dbl> <dbl> <chr>  
1  135.  135.  135.  135. NA     
2  135.  137.  135.  136. Down   
3  136.  137.  136.  136. Outside
4  136.  137.  136.  137. Down   
5  137.  137.  136.  136. NA     
6  136.  137.  136.  137. Outside

You just need to get the comparisons correct. Also you can add a fallback-option, that is filled in when none of the others was true, to avoid NAs or make it a bit more simple.

day_type <- DT %>%
  mutate(d_type = case_when( 
           High < lag(High) & Low < lag(Low) ~ "Up",        #Up day
           High > lag(High) & Low > lag(Low) ~ "Down",      #Down Day 
           # Attention the comparison leading to down and inside are identical!
           High > lag(High) & Low > lag(Low) ~ "Inside",    #Inside Day
           High < lag(High) & Low > lag(Low) ~ "Outside",   #Outside Day
           # fallback
           TRUE ~ "unchanged" ))

[Matthias]

Also, using lag() and lead() may produce missings in the first/last row as there is no value to be used.

[Ollie]

Matthias, That's very useful, thank you very much!

I had yet to hear of case_when() - lots to learn.

I'll give this a try and fix my comparison logic too!