[R-SIG-Finance] How do I bin data into 5 min bins and compute the medians in the bins?

R. Michael Weylandt michael.weylandt at gmail.com
Fri Jan 20 17:45:51 CET 2012 

It's remarkable how often people's R problems are actually Excel problems.

:-)

Michael Weylandt

On Fri, Jan 20, 2012 at 11:35 AM, Michael <comtech.usa at gmail.com> wrote:
> I followed the "period.apply" advice given previously and it worked...
>
> And the write.zoo problem is just an artifact of Excel displaying
> dates/times...
>
>   Index 17:00.1 49:00.1 00:00.1 03:00.1
>
> On Fri, Jan 20, 2012 at 9:51 AM, Michael <comtech.usa at gmail.com> wrote:
>
>> I finally got it work based on you guys' suggestions... Thanks so much
>> guys!
>>
>>
>> On Thu, Jan 19, 2012 at 2:38 PM, Michael <comtech.usa at gmail.com> wrote:
>>
>>> Okay I can see it displays the .082225 part...
>>>
>>> But when I used:
>>>
>>>
>>> write.zoo
>>>
>>> It doesn't write out the correct DateTimeStamp...
>>>
>>>
>>>
>>> Instead, the csv file has the 1st column:
>>>
>>>    Index 17:00.1 49:00.1 00:00.1 03:00.1
>>>
>>>
>>>
>>>
>>>   On Thu, Jan 19, 2012 at 2:14 PM, Jeffrey Ryan <jeffrey.ryan at lemnica.com
>>> > wrote:
>>>
>>>> It is preserved, just not displayed.
>>>>
>>>> options(digits.secs=6)
>>>>
>>>> HTH
>>>> Jeff
>>>>
>>>> On Thu, Jan 19, 2012 at 1:36 PM, Michael <comtech.usa at gmail.com> wrote:
>>>> > So is there a way to convert into xts object while preserving the
>>>> ".082225"
>>>> > part?
>>>> >
>>>> > Thx
>>>> >
>>>> > On Thu, Jan 19, 2012 at 1:32 PM, Michael <comtech.usa at gmail.com>
>>>> wrote:
>>>> >
>>>> >> Thank you! But it doesn't work...
>>>> >>
>>>> >> >
>>>> >> g1
>>>> >>
>>>> >> [1] 46.00 46.00 46.00 45.75 45.75
>>>> >>
>>>> >>
>>>> >> >
>>>> >> g2
>>>> >>
>>>> >> [1] "2006-06-16 12:12:00.082225" "2006-06-16 12:12:00.082225"
>>>> >>
>>>> >> [3] "2006-06-16 12:12:00.082225" "2006-06-16 12:07:00.082225"
>>>> >>
>>>> >> [5] "2006-06-16 12:12:00.082225"
>>>> >>
>>>> >> It doesn't work:
>>>> >>
>>>> >> g3=xts(g1, order.by=as.POSIXct(strptime(g2, "%Y-%m-%d %H:%M:%OS3")))
>>>> >>
>>>> >> >
>>>> >> g3
>>>> >>
>>>> >> [,1]
>>>> >>
>>>> >> <NA> 46.00
>>>> >>
>>>> >> <NA> 46.00
>>>> >>
>>>> >> <NA> 46.00
>>>> >>
>>>> >> <NA> 45.75
>>>> >>
>>>> >> <NA> 45.75
>>>> >>
>>>> >>
>>>> >>
>>>> >> But the following worked:
>>>> >>
>>>> >> >
>>>> >> g3=xts(g1, order.by=as.POSIXct(strptime(g2, "%Y-%m-%d %H:%M:%OS")))
>>>> >>
>>>> >> >
>>>> >>
>>>> >> >
>>>> >>
>>>> >> >
>>>> >> g3
>>>> >>
>>>> >> [,1]
>>>> >>
>>>> >> 2006-06-16 12:07:00 45.75
>>>> >>
>>>> >> 2006-06-16 12:12:00 46.00
>>>> >>
>>>> >> 2006-06-16 12:12:00 46.00
>>>> >>
>>>> >> 2006-06-16 12:12:00 46.00
>>>> >>
>>>> >> 2006-06-16 12:12:00 45.75
>>>> >>
>>>> >>
>>>> >>
>>>> >> On Thu, Jan 19, 2012 at 1:07 PM, G See <gsee000 at gmail.com> wrote:
>>>> >>
>>>> >>> Michael comtech,
>>>> >>>
>>>> >>> You might also consider reading one of the 2 e-mails that Brian sent
>>>> >>> you on-list in the last month that say to look at
>>>> >>> ?strftime
>>>> >>>
>>>> >>> For example ... the second example on that page
>>>> >>> format(Sys.time(), "%H:%M:%OS3")
>>>> >>>
>>>> >>>
>>>> >>> On Thu, Jan 19, 2012 at 1:01 PM, Jeffrey Ryan <
>>>> jeffrey.ryan at lemnica.com>
>>>> >>> wrote:
>>>> >>> > You should lead with what you have tried, it lets everyone have a
>>>> >>> > better idea of what you are looking for.
>>>> >>> >
>>>> >>> > That said, the .082225 is perfectly acceptable to xts, though
>>>> >>> > precision-wise you are near the limit of floating point precision
>>>> on
>>>> >>> > many platforms.  The previous post in this thread is on target to
>>>> what
>>>> >>> > you want.
>>>> >>> >
>>>> >>> > Take a good look at ?endpoints and to a lesser extend
>>>> ?period.apply.
>>>> >>> >
>>>> >>> > HTH
>>>> >>> > Jeff
>>>> >>> >
>>>> >>> > On Thu, Jan 19, 2012 at 12:54 PM, Michael <comtech.usa at gmail.com>
>>>> >>> wrote:
>>>> >>> >> I have also searched "crazily"... and read into both "xts" and
>>>> >>> "zoo"... but
>>>> >>> >> couldn't find a solution...
>>>> >>> >>
>>>> >>> >> My questions are:
>>>> >>> >>
>>>> >>> >> 1. How to handle the ".082225" part in "xts" and "zoo"?
>>>> >>> >>
>>>> >>> >> 2. The "xts/zoo" interval arithmatic only handles OHLC of an
>>>> interval,
>>>> >>> not
>>>> >>> >> median of an interval, right?
>>>> >>> >>
>>>> >>> >> Thanks a lot!
>>>> >>> >>
>>>> >>> >> On Thu, Jan 19, 2012 at 12:51 PM, Jeffrey Ryan <
>>>> >>> jeffrey.ryan at lemnica.com>wrote:
>>>> >>> >>
>>>> >>> >>> Use a time series class.  This has all been solved thousands of
>>>> times
>>>> >>> >>> before in private and on the lists.
>>>> >>> >>>
>>>> >>> >>> Jeff
>>>> >>> >>>
>>>> >>> >>> On Thu, Jan 19, 2012 at 12:43 PM, Michael <comtech.usa at gmail.com
>>>> >
>>>> >>> wrote:
>>>> >>> >>> > Hi all,
>>>> >>> >>> >
>>>> >>> >>> > I have data in the following format:
>>>> >>> >>> >
>>>> >>> >>> > How do I bucket the data into 5 min bins and then compute the
>>>> >>> median of
>>>> >>> >>> > quotes in the bins?
>>>> >>> >>> >
>>>> >>> >>> > Currently the "DateTimeStamps" are "factors" in the data frame
>>>> and I
>>>> >>> >>> > ordered the rows according to these DateTimeStamps
>>>> >>> >>> >
>>>> >>> >>> > Thanks a lot!
>>>> >>> >>> >
>>>> >>> >>> > Bid           Offer      DateTimeStamps
>>>> >>> >>> >
>>>> >>> >>> > 46.00       46.10     2006-06-16 12:12:00.082225
>>>> >>> >>> >
>>>> >>> >>> >        [[alternative HTML version deleted]]
>>>> >>> >>> >
>>>> >>> >>> > _______________________________________________
>>>> >>> >>> > R-SIG-Finance at r-project.org mailing list
>>>> >>> >>> > https://stat.ethz.ch/mailman/listinfo/r-sig-finance
>>>> >>> >>> > -- Subscriber-posting only. If you want to post, subscribe
>>>> first.
>>>> >>> >>> > -- Also note that this is not the r-help list where general R
>>>> >>> questions
>>>> >>> >>> should go.
>>>> >>> >>>
>>>> >>> >>>
>>>> >>> >>>
>>>> >>> >>> --
>>>> >>> >>> Jeffrey Ryan
>>>> >>> >>> jeffrey.ryan at lemnica.com
>>>> >>> >>>
>>>> >>> >>> www.lemnica.com
>>>> >>> >>> www.esotericR.com <http://www.esotericr.com/> <
>>>> http://www.esotericr.com/> <
>>>> >>> http://www.esotericr.com/>
>>>> >>> >>>
>>>> >>> >>
>>>> >>> >>        [[alternative HTML version deleted]]
>>>> >>> >>
>>>> >>> >> _______________________________________________
>>>> >>> >> R-SIG-Finance at r-project.org mailing list
>>>> >>> >> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
>>>> >>> >> -- Subscriber-posting only. If you want to post, subscribe first.
>>>> >>> >> -- Also note that this is not the r-help list where general R
>>>> >>> questions should go.
>>>> >>> >
>>>> >>> >
>>>> >>> >
>>>> >>> > --
>>>> >>> > Jeffrey Ryan
>>>> >>> > jeffrey.ryan at lemnica.com
>>>> >>> >
>>>> >>> > www.lemnica.com
>>>> >>> > www.esotericR.com <http://www.esotericr.com/> <
>>>> http://www.esotericr.com/>
>>>>  >>> >
>>>> >>> > _______________________________________________
>>>> >>> > R-SIG-Finance at r-project.org mailing list
>>>> >>> > https://stat.ethz.ch/mailman/listinfo/r-sig-finance
>>>> >>> > -- Subscriber-posting only. If you want to post, subscribe first.
>>>> >>> > -- Also note that this is not the r-help list where general R
>>>> questions
>>>> >>> should go.
>>>> >>>
>>>> >>
>>>> >>
>>>> >
>>>> >        [[alternative HTML version deleted]]
>>>> >
>>>> > _______________________________________________
>>>> > R-SIG-Finance at r-project.org mailing list
>>>> > https://stat.ethz.ch/mailman/listinfo/r-sig-finance
>>>> > -- Subscriber-posting only. If you want to post, subscribe first.
>>>> > -- Also note that this is not the r-help list where general R
>>>> questions should go.
>>>>
>>>>
>>>>
>>>> --
>>>> Jeffrey Ryan
>>>> jeffrey.ryan at lemnica.com
>>>>
>>>> www.lemnica.com
>>>> www.esotericR.com <http://www.esotericr.com/>
>>>>
>>>
>>>
>>
>
>        [[alternative HTML version deleted]]
>
> _______________________________________________
> R-SIG-Finance at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions should go.

More information about the R-SIG-Finance mailing list