0

I have an xts with OHLC and a logical column: isSwingBottom. I want a new column that gives the index of first occurrence of isSwingBottom by searching only in dates prior to the current row. I need the closest found row. For example, in this data for isSwingBottom, 1995-10-04/1995-10-10 should be NA, 1995-10-11/1995-10-31 should be 1995-10-10 and so on. I want to avoid using a loop.

myXTS <- structure(c(408.75, 417.2937, 423.3188, 423.775, 419.3375, 415.55, 
419.325, 417.25, 415.8375, 411.7812, 408.975, 406.2625, 396.6062, 
385.6125, 374.375, 368.0375, 369.8625, 356.2688, 360.45, 357.45, 
354.35, 363.6313, 356.25, 361.0813, 364.375, 365.9, 351.15, 342.0375, 
333.5938, 315.4812, 418.75, 431.25, 431.25, 429.375, 421.875, 
422.5, 419.375, 418.125, 417.5, 415.625, 411.875, 406.25, 396.875, 
378.75, 393.8188, 373.5, 371.5, 366.25, 366.25, 358.4937, 364.625, 
372.25, 363.75, 368.75, 369.25, 362.5, 350, 343.75, 337.5, 322.5, 
408.75, 417.625, 420.625, 415.625, 412.5, 417.625, 417, 415.125, 
406.25, 406.25, 405, 387.5, 382.5, 368.75, 361.875, 368.125, 
353.75, 350, 356.25, 350.875, 355, 360.625, 355.125, 358.875, 
362.5, 344, 339.5, 332.5, 313.375, 309.5, 417.2937, 423.3188, 
423.775, 419.3375, 415.55, 419.325, 417.25, 415.8375, 411.7812, 
408.975, 406.2625, 396.6062, 385.6125, 372.0562, 368.0375, 369.8625, 
356.2688, 360.45, 357.45, 354.35, 363.6313, 361.5, 361.0813, 
364.375, 365.9, 351.15, 342.0375, 333.5938, 315.4812, 315.8188, 
4560, 11120, 30840, 8640, 7400, 3040, 6360, 1720, 6080, 6840, 
9200, 38440, 53000, 32000, 46240, 17680, 57240, 57840, 28680, 
28880, 66760, 56640, 23240, 33480, 39120, 62480, 64320, 79920, 
66320, 60360, NA, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, NA, 0, 0, 0, 0, 0, 1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 
0, 0, 0), .Dim = c(30L, 7L), .Dimnames = list(NULL, c("Open", 
"High", "Low", "Close", "Volume", "isSwingBottom", "isSwingTop"
)), index = structure(c(812764800, 812851200, 812937600, 813196800, 
813283200, 813369600, 813456000, 813542400, 813801600, 813888000, 
813974400, 814060800, 814147200, 814406400, 814492800, 814579200, 
814665600, 814752000, 815011200, 815097600, 815184000, 815270400, 
815356800, 815616000, 815702400, 815788800, 815875200, 815961600, 
816220800, 816307200), tzone = "UTC", tclass = "Date"), class = c("xts", 
"zoo"))
Anupam
  • 81
  • 5

1 Answers1

1

If you want a new column with dates you might need to convert myXTS to dataframe. Try this approach :

library(dplyr)
library(zoo)
library(tidyr)

myXTS %>%
  fortify.zoo() %>%
  mutate(new_col = if_else(isSwingBottom == 1, Index, as.Date(NA))) %>%
  fill(new_col)


#        Index     Open     High     Low    Close Volume isSwingBottom isSwingTop    new_col
#1  1995-10-04 408.7500 418.7500 408.750 417.2937   4560            NA          0       <NA>
#2  1995-10-05 417.2937 431.2500 417.625 423.3188  11120             0          0       <NA>
#3  1995-10-06 423.3188 431.2500 420.625 423.7750  30840             0          0       <NA>
#4  1995-10-09 423.7750 429.3750 415.625 419.3375   8640             0          0       <NA>
#5  1995-10-10 419.3375 421.8750 412.500 415.5500   7400             1          0 1995-10-10
#6  1995-10-11 415.5500 422.5000 417.625 419.3250   3040             0          1 1995-10-10
#7  1995-10-12 419.3250 419.3750 417.000 417.2500   6360             0          0 1995-10-10
#8  1995-10-13 417.2500 418.1250 415.125 415.8375   1720             0          0 1995-10-10
#9  1995-10-16 415.8375 417.5000 406.250 411.7812   6080             0          0 1995-10-10
#10 1995-10-17 411.7812 415.6250 406.250 408.9750   6840             0          0 1995-10-10
#11 1995-10-18 408.9750 411.8750 405.000 406.2625   9200             0          0 1995-10-10
#12 1995-10-19 406.2625 406.2500 387.500 396.6062  38440             0          0 1995-10-10
#13 1995-10-20 396.6062 396.8750 382.500 385.6125  53000             0          0 1995-10-10
#14 1995-10-23 385.6125 378.7500 368.750 372.0562  32000             0          0 1995-10-10
#15 1995-10-24 374.3750 393.8188 361.875 368.0375  46240             0          0 1995-10-10
#16 1995-10-25 368.0375 373.5000 368.125 369.8625  17680             0          0 1995-10-10
#17 1995-10-26 369.8625 371.5000 353.750 356.2688  57240             0          0 1995-10-10
#18 1995-10-27 356.2688 366.2500 350.000 360.4500  57840             0          0 1995-10-10
#19 1995-10-30 360.4500 366.2500 356.250 357.4500  28680             0          0 1995-10-10
#20 1995-10-31 357.4500 358.4937 350.875 354.3500  28880             1          0 1995-10-31
#21 1995-11-01 354.3500 364.6250 355.000 363.6313  66760             0          0 1995-10-31
#22 1995-11-02 363.6313 372.2500 360.625 361.5000  56640             0          1 1995-10-31
#23 1995-11-03 356.2500 363.7500 355.125 361.0813  23240             1          0 1995-11-03
#24 1995-11-06 361.0813 368.7500 358.875 364.3750  33480             0          0 1995-11-03
#25 1995-11-07 364.3750 369.2500 362.500 365.9000  39120             0          1 1995-11-03
#26 1995-11-08 365.9000 362.5000 344.000 351.1500  62480             0          0 1995-11-03
#27 1995-11-09 351.1500 350.0000 339.500 342.0375  64320             0          0 1995-11-03
#28 1995-11-10 342.0375 343.7500 332.500 333.5938  79920             0          0 1995-11-03
#29 1995-11-13 333.5938 337.5000 313.375 315.4812  66320             0          0 1995-11-03
#30 1995-11-14 315.4812 322.5000 309.500 315.8188  60360            NA          0 1995-11-03
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • can u pls suggest how to add a lag in this approach. In case isSwingBottom =1 (e.g 1995-10-10 and 1995-10-31) - I need index of previous true not the same row – Anupam Mar 19 '21 at 06:22
  • @Anupam To the above answer you can add `%>% mutate(new_col = lag(new_col))`. Is that what you want? – Ronak Shah Mar 19 '21 at 06:29
  • Using the same idea and without using any additional packages we could compute the row number of the last swing bottom using `n <- nrow(myXTS); ix <- na.locf0(ifelse(myXTS$isSwingBottom == 1, 1:n, NA))` and the corresponding times are `time(myXTS)[ix]` . – G. Grothendieck Mar 19 '21 at 11:38