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



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!).

Rothsom · Accepted Answer

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")))]

Subset xts object using vector of unique index days

Answers (1)

answer to own question:

Related Questions