convert ordered but non-unique csv to unique xts

classic Classic list List threaded Threaded
4 messages Options
dae
Reply | Threaded
Open this post in threaded view
|

convert ordered but non-unique csv to unique xts

dae
The csv file has ordered data but does not provide microseconds.
There are entries with the same datetime  (to the seconds level) which
are in the proper sequential order.

I want to convert the file into an xts object with unique time values
(by adding microseconds), if that can be done.
I am stuck for what steps to take, starting from the csv file.

Example data from csv file (note that four entries all have same datetime):

Time,High,Low,Open,Close,Vol
2013/05/08 08:02:04,6.35,6.345,6.35,6.345,172
2013/05/08 08:02:07,6.345,6.3425,6.345,6.3425,69
2013/05/08 08:02:07,6.3425,6.34,6.3425,6.34,74
2013/05/08 08:02:07,6.3425,6.34,6.34,6.3425,80
2013/05/08 08:02:07,6.345,6.3425,6.3425,6.345,87
2013/05/08 08:02:13,6.345,6.3425,6.345,6.3425,110

Goal; an xts object something like this
(however the microseconds may appear):

                                          High,Low,Open,Close,Vol
2013-05-08 08:02:04.000,6.35,6.345,6.35,6.345,172
2013-05-08 08:02:07.001,6.345,6.3425,6.345,6.3425,69
2013-05-08 08:02:07.002,6.3425,6.34,6.3425,6.34,74
2013-05-08 08:02:07.003,6.3425,6.34,6.34,6.3425,80
2013-05-08 08:02:07.004,6.345,6.3425,6.3425,6.345,87
2013-05-08 08:02:13.000,6.345,6.3425,6.345,6.3425,110

Any help greatly appreciated. Thanks.
Reply | Threaded
Open this post in threaded view
|

Re: convert ordered but non-unique csv to unique xts

Joshua Ulrich
Use make.index.unique after you've created an xts object from the data
in the CSV.
--
Joshua Ulrich  |  about.me/joshuaulrich
FOSS Trading  |  www.fosstrading.com

R/Finance 2013: Applied Finance with R  | www.RinFinance.com


On Thu, May 9, 2013 at 4:00 PM, dae <[hidden email]> wrote:

> The csv file has ordered data but does not provide microseconds.
> There are entries with the same datetime  (to the seconds level) which
> are in the proper sequential order.
>
> I want to convert the file into an xts object with unique time values
> (by adding microseconds), if that can be done.
> I am stuck for what steps to take, starting from the csv file.
>
> Example data from csv file (note that four entries all have same datetime):
>
> Time,High,Low,Open,Close,Vol
> 2013/05/08 08:02:04,6.35,6.345,6.35,6.345,172
> 2013/05/08 08:02:07,6.345,6.3425,6.345,6.3425,69
> 2013/05/08 08:02:07,6.3425,6.34,6.3425,6.34,74
> 2013/05/08 08:02:07,6.3425,6.34,6.34,6.3425,80
> 2013/05/08 08:02:07,6.345,6.3425,6.3425,6.345,87
> 2013/05/08 08:02:13,6.345,6.3425,6.345,6.3425,110
>
> Goal; an xts object something like this
> (however the microseconds may appear):
>
>                                           High,Low,Open,Close,Vol
> 2013-05-08 08:02:04.000,6.35,6.345,6.35,6.345,172
> 2013-05-08 08:02:07.001,6.345,6.3425,6.345,6.3425,69
> 2013-05-08 08:02:07.002,6.3425,6.34,6.3425,6.34,74
> 2013-05-08 08:02:07.003,6.3425,6.34,6.34,6.3425,80
> 2013-05-08 08:02:07.004,6.345,6.3425,6.3425,6.345,87
> 2013-05-08 08:02:13.000,6.345,6.3425,6.345,6.3425,110
>
> Any help greatly appreciated. Thanks.
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/convert-ordered-but-non-unique-csv-to-unique-xts-tp4666712.html
> Sent from the Rmetrics mailing list archive at Nabble.com.
>
> _______________________________________________
> [hidden email] 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.

_______________________________________________
[hidden email] 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.
dae
Reply | Threaded
Open this post in threaded view
|

Re: convert ordered but non-unique csv to unique xts

dae
I placed the example code in a file named "zc.csv"

 Time,High,Low,Open,Close,Vol
 2013/05/08 08:02:04,6.35,6.345,6.35,6.345,172
 2013/05/08 08:02:07,6.345,6.3425,6.345,6.3425,69
 2013/05/08 08:02:07,6.3425,6.34,6.3425,6.34,74
 2013/05/08 08:02:07,6.3425,6.34,6.34,6.3425,80
 2013/05/08 08:02:07,6.345,6.3425,6.3425,6.345,87
 2013/05/08 08:02:13,6.345,6.3425,6.345,6.3425,110

I  ran these steps to convert to xts and make the index unique:
-------
library("quantmod")
ds = options(digits.secs=6)
x = "zc.csv"
zc.xts =  as.xts(read.zoo(x, header = T, sep = ",", format = "%Y/%m/%d %H:%M:%S", tz=""))
# that code works, but generates a warning:
#Warning message:
#  In zoo(rval3, ix) :
#  some methods for “zoo” objects do not work if the index entries in ‘order.by’ are not unique

zc2.xts = make.index.unique (zc.xts)
isOrdered( .index(zc2.xts) )  #evaluates to TRUE
zc2.xts

---------------
This is how zc2.xts appears:

                                                      High    Low     Open   Close   Vol
2013-05-08 08:02:04.000000 6.3500 6.3450 6.3500 6.3450 172
2013-05-08 08:02:07.000000 6.3450 6.3425 6.3450 6.3425  69
2013-05-08 08:02:07.000000 6.3425 6.3400 6.3425 6.3400  74
2013-05-08 08:02:07.000001 6.3425 6.3400 6.3400 6.3425  80
2013-05-08 08:02:07.000002 6.3450 6.3425 6.3425 6.3450  87
2013-05-08 08:02:13.000000 6.3450 6.3425 6.3450 6.3425 110

Look at the index of the second and third records.  
Although apparently the indexes are unique, the indexes look the same.
Is there some way to make them look different?


I am running this code on a 32 bit WinXP SP3 box.
I am looking at it through RStudio 0.97.449
Reply | Threaded
Open this post in threaded view
|

Re: convert ordered but non-unique csv to unique xts

Jeffrey Ryan
You are more or less stuck with the output you see.

The internal conversion to printed time in R (and the OS itself) is where
you are losing the microsecond precision.  R kindly buys us the subsecond
granularity over normal OS resolution, but the printing is deferred to the
OS IIRC, and you just can't be assured you see the full precision. This is
good in in many cases of course, e.g. 3 prints as 3 and not 3.00000000001
or something similar, but here it isn't what you want.

You can see the actual numbers if you really want to with:

> .index(make.index.unique(zc.xts,1e-6))
[1] 1368018124.000000 1368018127.000000 1368018127.000001 1368018127.000002
[5] 1368018127.000003 1368018133.000000
attr(,"tzone")
[1] "America/Chicago"
attr(,"tclass")
[1] "POSIXct" "POSIXt"

Note too that you are playing at the edge of floating point resolution:

> .index(make.index.unique(zc.xts,1e-7))
[1] 1368018124 1368018127 1368018127 1368018127 1368018127 1368018133
attr(,"tzone")
[1] "America/Chicago"
attr(,"tclass")
[1] "POSIXct" "POSIXt"

HTH
Jeff


On Thu, May 9, 2013 at 11:48 PM, dae <[hidden email]> wrote:

> I placed the example code in a file named "zc.csv"
>
>  Time,High,Low,Open,Close,Vol
>  2013/05/08 08:02:04,6.35,6.345,6.35,6.345,172
>  2013/05/08 08:02:07,6.345,6.3425,6.345,6.3425,69
>  2013/05/08 08:02:07,6.3425,6.34,6.3425,6.34,74
>  2013/05/08 08:02:07,6.3425,6.34,6.34,6.3425,80
>  2013/05/08 08:02:07,6.345,6.3425,6.3425,6.345,87
>  2013/05/08 08:02:13,6.345,6.3425,6.345,6.3425,110
>
> I  ran these steps to convert to xts and make the index unique:
> -------
> library("quantmod")
> ds = options(digits.secs=6)
> x = "zc.csv"
> zc.xts =  as.xts(read.zoo(x, header = T, sep = ",", format = "%Y/%m/%d
> %H:%M:%S", tz=""))
> # that code works, but generates a warning:
> #Warning message:
> #  In zoo(rval3, ix) :
> #  some methods for “zoo” objects do not work if the index entries in
> ‘order.by’ are not unique
>
> zc2.xts = make.index.unique (zc.xts)
> isOrdered( .index(zc2.xts) )  #evaluates to TRUE
> zc2.xts
>
> ---------------
> This is how zc2.xts appears:
>
>                                                       High    Low     Open
> Close   Vol
> 2013-05-08 08:02:04.000000 6.3500 6.3450 6.3500 6.3450 172
> 2013-05-08 08:02:07.000000 6.3450 6.3425 6.3450 6.3425  69
> 2013-05-08 08:02:07.000000 6.3425 6.3400 6.3425 6.3400  74
> 2013-05-08 08:02:07.000001 6.3425 6.3400 6.3400 6.3425  80
> 2013-05-08 08:02:07.000002 6.3450 6.3425 6.3425 6.3450  87
> 2013-05-08 08:02:13.000000 6.3450 6.3425 6.3450 6.3425 110
>
> Look at the index of the second and third records.
> Although apparently the indexes are unique, the indexes look the same.
> Is there some way to make them look different?
>
>
> I am running this code on a 32 bit WinXP SP3 box.
> I am looking at it through RStudio 0.97.449
>
>
>
>
> --
> View this message in context:
> http://r.789695.n4.nabble.com/convert-ordered-but-non-unique-csv-to-unique-xts-tp4666712p4666735.html
> Sent from the Rmetrics mailing list archive at Nabble.com.
>
> _______________________________________________
> [hidden email] 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
[hidden email]

www.lemnica.com

R/Finance 2013: Applied R in Finance
May 17, 18 Chicago, IL
www.RinFinance.com

        [[alternative HTML version deleted]]


_______________________________________________
[hidden email] 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.