Subset xts object using vector of unique index days

Question

I'm trying to subset an xts object using a vector of xts timestamps that have been processed into a vector of unique timestamps. This follows on from this previous question that was only partially answered.

Some sample data:

dput(sample.data.merge, control="all")

structure(c(11.65, 11.13, 11.13, 11.5, 11.8, 11.45, 11.45, 11.08, 
11.08, 11.25, 9.8, 10.45, 10.9, 10.9, 10.9, 10.9, 10.9, 10.9, 
10.45, 10.5, 10.5, 10.08, 10.08, 10.65, 10.08, 10.65, 10.6, 10.65, 
10.65, 10.085, 10.145, 11.9, 11.085, 9.35, 9.15, 9.15, 9.9, 9.0875, 
9.3, 9.3, 9.3, 9.35, 9.35, 9.35, 9.25, 9.5, 9.45, 9.3, 11.15, 
11.15, 11.15, 11.15, 11.8, 8, 10.05, 10.05, 10.25, 10.4, 10.15, 
10.15, 10.3, 10.15, 10.1, 11.08, 11.08, 11.08, 11.65, 11.85, 
11.9, 11.9, 11.9, 12.65, 13.35, 13.35, 15.95, 15.9, 15.4, 15.4, 
15.4, 15.4, 15.13, 12.13, 12.35, 11.082, 11.082, 11.08, 12.1, 
12.3, 12.3, 12.4, 12.6, 12.6, 12.13, 12.45, 12.9, 12.9, 12.9, 
14, 12.6, 12.6, 12.45, 15.25, 12.085, 12.95, 12.95, 12.35, 12.13, 
12.8, 14, 14, 12.45, 12.45, 12.45, 12.45, 12.25, 12.6, 12.085, 
15.1, 15.15, 15.35, 15.3, 12.5, 12.5, 12.15, 12.2, 11.085, 11.35, 
11.45, 11.13, 11.13, 11.35, 11.2, 12.5, 12.6, 12.95, 12.95, 12.5, 
12.45, 12.3, 12.3, 12.3, 12.45, 12.45, 12.45, 12.5, 12.45, 12.45, 
12.13, 12.13, 12.65, 190, 190, 190, 190, 130, 190, 190, 190, 
190, 190, 130, 190, 130, 130, 445, 445, 445, 445, 130, 445, 190, 
445, 445, 190, 190, 190, 190, 130, 190, 190, 190, 190, 190, 190, 
190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 
190, 275, 190, 190, 190, 190, 190, 190, 190, 190, 190, 130, 130, 
190, 190, 190, 130, 130, 130, 190, 130, 190, 190, 190, 130, 190, 
190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 
1190, 190, 190, 130, 130, 130, 190, 1130, 190, 190, 130, 190, 
190, 190, 190, 190, 190, 130, 130, 190, 190, 375, 190, 190, 190, 
130, 190, 130, 190, 190, 190, 190, 130, 190, 190, 190, 190, 190, 
190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 190, 
130, 130, 130, 190, 130, 190, 190, 190, 130, 130, 445, 445, 130, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, 0, 0, NA, NA, NA, NA, NA, 0.21, 0.21, 0.26, 0.0250000000000004, 
0, 0.0250000000000004, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, 0.0249999999999995, 0.0250000000000004, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.0250000000000004, 
0.100000000000001, 0.39, NA, NA, NA, NA, NA, 0.0250000000000004, 
NA, NA, NA, NA, NA, 0.524999999999999, 0.25, 0, 0, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 0.149999999999999, 0.135000000000001, 
0.149999999999999, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, 0.409999999999999, 0.375, 0.3, 0.635, 0.385, 0.335, 0.175000000000001, 
0, NA, NA, NA, NA, NA, 1.4, 0.2, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, 0.109999999999999, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, 0.0749999999999993, 0.0749999999999993, 0.0749999999999993, 
0.0250000000000004, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0, 0, NA, NA, NA, 
NA, NA, 127.5, 0, 0, 0, 0, 0, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, 0, 0, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, 30, 30, 30, NA, NA, NA, NA, 
NA, 0, NA, NA, NA, NA, NA, 0, 0, 0, 0, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 30, 30, 30, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, 0, 30, 30, 0, 0, 0, 0, 0, NA, NA, NA, NA, NA, 0, 
0, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, 0, 0, 30, 0, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 10.9, 
10.9, NA, NA, NA, NA, NA, 10.29, 10.29, 10.34, 10.625, 10.65, 
10.625, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 9.325, 
9.325, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 10.15, 10.225, 10.69, NA, NA, NA, NA, NA, 11.9, 
NA, NA, NA, NA, NA, 15.4, 15.4, 15.4, 15.4, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, 12.35, 12.35, 12.425, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 12.65, 12.575, 12.875, 12.875, 12.625, 
12.625, 12.625, 12.45, NA, NA, NA, NA, NA, 13.85, 15.125, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, 11.275, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 12.375, 12.375, 12.375, 12.45, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 445, 445, NA, NA, NA, NA, NA, 317.5, 190, 190, 190, 190, 
190, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 190, 
190, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 160, 160, 160, NA, NA, NA, NA, NA, 190, NA, NA, 
NA, NA, NA, 190, 190, 190, 190, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 160, 160, 160, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 190, 190, 190, 190, 190, 190, 190, 190, NA, NA, NA, NA, 
NA, 190, 190, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 190, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 130, 130, 160, 190, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NaN, Inf, NA, NA, NA, NA, NA, 0.999999999999996, 
1.71428571428572, 1, 1, NaN, 21.5999999999997, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, 1.00000000000004, 2.99999999999993, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 37.1999999999995, 8.54999999999987, 0.999999999999998, 
NA, NA, NA, NA, NA, 29.9999999999996, NA, NA, NA, NA, NA, 0, 
0, NaN, Inf, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1.66666666666666, 
1.62962962962963, 0.166666666666658, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, 1.26829268292683, 0.600000000000004, 
3.75, 1.77165354330709, 0.454545454545457, 0.522388059701495, 
1, NaN, NA, NA, NA, NA, NA, 1.07142857142857, 0.875000000000003, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.681818181818179, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 1, 1, 1, 2, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NaN, Inf, NA, NA, NA, NA, NA, 1, NaN, NaN, Inf, NaN, NaN, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NaN, NaN, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 1, 1, 1, NA, NA, NA, NA, NA, Inf, NA, NA, NA, NA, NA, 
NaN, NaN, NaN, NaN, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1, 
1, 32.3333333333333, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NaN, 6.16666666666667, 0, NaN, NaN, Inf, NaN, Inf, NA, 
NA, NA, NA, NA, NaN, NaN, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NaN, NA, NA, NA, NA, NA, NA, NA, NA, NA, NaN, Inf, 1, NaN, 
NA, NA, NA, NA, NA), .Dim = c(150L, 8L), .Dimnames = list(NULL, 
    c("price", "volume", "madprice", "madvolume", "medianprice", 
    "medianvolume", "absdevmadprice", "absdevmadvolume")), index = structure(c(1325584080, 
1325594940, 1325594940, 1325604600, 1325759100, 1325762520, 1325762520, 
1325769300, 1325769300, 1325848080, 1325864880, 1326128220, 1326196500, 
1326196500, 1326196500, 1326196500, 1326196500, 1326196500, 1326209700, 
1326279480, 1326283620, 1326288300, 1326288300, 1326289680, 1326289680, 
1326289680, 1326292320, 1326294060, 1326294600, 1326297600, 1326387000, 
1326456720, 1326467160, 1326711600, 1326723000, 1326724260, 1326809940, 
1326814860, 1326885960, 1326885960, 1326889980, 1326894000, 1326895200, 
1326895200, 1326898080, 1326986700, 1326987240, 1326992100, 1327072140, 
1327328040, 1327328040, 1327328040, 1327417920, 1327423140, 1327424820, 
1327425240, 1327483200, 1327496520, 1327570320, 1327570320, 1327575420, 
1327588680, 1327588980, 1327595880, 1327595880, 1327595880, 1327664820, 
1327674720, 1327680660, 1327680780, 1327680780, 1327683960, 1327914300, 
1327914300, 1327915260, 1327918140, 1327924860, 1327924920, 1327924980, 
1327924980, 1327927680, 1328013360, 1328014200, 1328025000, 1328025000, 
1328026740, 1328089440, 1328091360, 1328091360, 1328110620, 1328111340, 
1328111340, 1328112420, 1328113800, 1328193540, 1328194080, 1328194140, 
1328196720, 1328274360, 1328274420, 1328278320, 1328519280, 1328520120, 
1328520600, 1328520600, 1328524140, 1328527980, 1328531580, 1328540880, 
1328540880, 1328547600, 1328547660, 1328547720, 1328547780, 1328607060, 
1328608080, 1328618760, 1328623380, 1328623380, 1328625720, 1328631480, 
1328717760, 1328717880, 1328793000, 1328797980, 1329132840, 1329210480, 
1329215400, 1329215820, 1329215820, 1329219480, 1329223140, 1329300900, 
1329301620, 1329315240, 1329315240, 1329388740, 1329389700, 1329390000, 
1329390000, 1329390180, 1329391860, 1329391860, 1329391860, 1329402120, 
1329467700, 1329467700, 1329469080, 1329469080, 1329471300), tzone = "", tclass = c("POSIXlt", 
"POSIXt")), .indexCLASS = c("POSIXlt", "POSIXt"), .indexTZ = "", tclass = c("POSIXlt", 
"POSIXt"), tzone = "", class = c("xts", "zoo"))

The code:

sample.data.mergesub <- sample.data.merge['T10:30/T17:30']
sample.data.mergeout <- sample.data.mergesub[ which((sample.data.mergesub$absdevmadprice >=5 & sample.data.mergesub$absdevmadprice < Inf) | (sample.data.mergesub$absdevmadvol>=10 & sample.data.mergesub$absdevmadvol<Inf)),]
sample.data.unique <- unique(.indexday(sample.data.mergeout))

This sample.data.unique is therefore a vector of index days. Question: I'd like to use this to extract the full day of data from the original dataset sample.data in order to later graph the full day of trades, rather than the subset of data. For instance, if Jan 03 2012 10:53:00 meets the conditions of having absdevmadprice >= 5, and less than infinite, then I'd like to return the day (Jan 03 2012) into a vector and use this to subset the original dataset. This would select all observations in that day (so over the whole trading period) and I could then graph this day.

I've tried this code (based on Joshua's answer here) but it doesn't work:

> sample.data.uniquePOS<-sample.data.merge[paste(as.Date(as.POSIXct(sample.data.unique, origin = "1970-01-01 00:00.00 UTC", tz="GMT")))]

It returns simply the column names:

> sample.data.uniquePOS
price volume madprice madvolume medianprice medianvolume absdevmadprice
     absdevmadvolume

For info, the structure of the variables:

> str(sample.data.merge)
An ‘xts’ object on 2012-01-03 09:48:00/2012-02-17 09:35:00 containing:
  Data: num [1:150, 1:8] 11.6 11.1 11.1 11.5 11.8 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:8] "price" "volume" "madprice" "madvolume" ...
  Indexed by objects of class: [POSIXlt,POSIXt] TZ: 
  xts Attributes:  
 NULL

> str(sample.data.uniquePOS)
An 'xts' object of zero-width

> str(sample.data.unique)
 num 15371

Thanks for the help (and if anyone can explain why the code doesn't work!).

No need to duplicate your question. I suggest that you delete your previous one. — agstudy, Mar 25 '13 at 17:24
I managed to solve it now with this code. Will post it as an answer when SO allows me to. sample.data.uniquePOS <- sample.data.merge[paste(as.Date.numeric(sample.data.unique, origin= "1970-01-01 00:00.00 UTC", tz="GMT")),] — Rothsom, Mar 25 '13 at 20:06
Can you add more meaningful sample data. The sample data you have added has NA for `absdevmadprice ` and `absdevmadvol` for ` sample.data.mergesub` ? — CHP, Mar 26 '13 at 03:25
@geektrader There are many NAs because of the rolling nature of the runMAD and runMedian, but 'sample.data.mergesub' does have some non-NA data in it. Can you check that the 'sample.data.mergesub' subset uses a 10:30-17:30 window? When I first posted it the window was just a few minutes by mistake, and I changed it immediately - but it's still possible you copied it before the changes were made. — Rothsom, Mar 26 '13 at 10:27

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

answer to own question:

Using these posts (Ananda's answer to this, Joshua's answer to this, and the as.Date.numeric function I found out about here) I was able to solve my own problem. This line of code seems to do it:

sample.data.uniquePOS <- sample.data.merge[paste(as.Date.numeric(sample.data.unique, origin= "1970-01-01 00:00.00 UTC", tz="GMT")),]

Can't give a great explanation as to why it works compared to the below, but perhaps as.POSIXct can't take the same format that as.Date.numeric can?

sample.data.uniquePOS <- sample.data.merge[paste(as.Date(as.POSIXct(sample.data.unique, origin = "1970-01-01 00:00.00 UTC", tz="GMT")))]

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 26 '13 at 14:35

Rothsom

53
7

2

You don't need `as.Date.numeric`. `as.Date` works just fine. `as.POSIXct` doesn't work because `sample.data.unique` is a numeric vector of days since the origin (`Date`), not seconds since the origin (`POSIXt`). – Joshua Ulrich Mar 26 '13 at 14:46

Subset xts object using vector of unique index days

1 Answers1

answer to own question: