xts - last obervation in a period

Question

I know that this must be an easy task but I got stucked and cannot move forward.

I have the following xts:

                     timestamp    id         type  price     size          api   
2014-05-02 00:02:07 "1398981727" "36833484" "BID" "449.247" "9.00000e-02" "TRUE"
2014-05-02 00:02:07 "1398981727" "36833485" "BID" "449.248" "5.42886e-02" "TRUE"
2014-05-02 00:03:04 "1398981784" "36833488" "BID" "449.246" "4.66000e+00" "TRUE"
2014-05-02 00:03:12 "1398981792" "36833489" "BID" "449.246" "5.90000e-01" "TRUE"
2014-05-02 00:03:12 "1398981792" "36833490" "BID" "449.246" "4.00000e-02" "TRUE"
2014-05-02 00:03:14 "1398981794" "36833491" "BID" "449.246" "2.00000e-02" "TRUE"
2014-05-02 00:03:23 "1398981803" "36833498" "BID" "449.240" "1.00000e-02" "TRUE"
2014-05-02 00:04:55 "1398981895" "36833504" "BID" "448.790" "9.89321e-02" "TRUE"
2014-05-02 00:04:55 "1398981895" "36833505" "BID" "449.108" "1.75000e+00" "TRUE"
2014-05-02 00:04:55 "1398981895" "36833506" "BID" "449.110" "5.21441e-01" "TRUE"
2014-05-02 00:05:26 "1398981926" "36833508" "ASK" "447.000" "5.16985e-01" "TRUE"
2014-05-02 00:05:26 "1398981926" "36833509" "ASK" "446.473" "1.20000e-02" "TRUE"
2014-05-02 00:05:26 "1398981926" "36833510" "ASK" "446.205" "1.04000e-02" "TRUE"

from which I want to build another xts containing last observation in every second for a given type of order (BID or ASK, column type).

For instance for ASK orders, I want to build xts like this:

2014-05-02 00:02:07      NA
2014-05-02 00:03:04      NA
2014-05-02 00:03:12      NA
2014-05-02 00:03:14      NA
2014-05-02 00:03:23      NA
2014-05-02 00:04:55      NA
2014-05-02 00:05:26 446.205

New xts has to contain same index as the old one. Timestamps with no ASK orders should be NA.

I tried this

xts(period.apply(temp, INDEX = endpoints(temp, on = "secs", k = 1), FUN = function(x) tail(as.numeric(x$price[x$type == "ASK"]), 1)))

where temp = the xts input above. But it always ands up with following error:

Error in coredata.xts(x) : currently unsupported data type.

Any ideas?

score 2 · Accepted Answer · answered Aug 15 '14 at 01:36

The problem is that period.apply is meant to aggregate objects, but you're removing rows. That means the result of the first 6 function evaluates are numeric(0), which aren't a valid value for an observation.

If there are no observations, you need to return NA. That means you need to modify your function a bit to return NA if there are no ask observations.

f <- function(x) {
  y <- tail(as.numeric(x$price[x$type == "ASK"]), 1)
  if(!length(y)) NA else y
}
period.apply(temp, endpoints(temp, "secs"), f)

There's probably a better way to accomplish your objective, but you would need to provide more details on what you're ultimately trying to do.

xts - last obervation in a period

1 Answers1