Convert tab delimited to XTS within R?

Question

I have tab delimited file in format of:

Date    Time    Last    LastSize    TotVol  Bid Ask TickID  BidSize AskSize
8/23/2012   0:00:00 0.95711 1   20670   0.95711 0.95742 0   0   0
8/23/2012   0:00:04 0.9571  1   20671   0.9571  0.9574  0   0   0

I am using the function to create an XTS within R.

> EURUSD <- as.xts(read.zoo("C:\\Users\\caustic\\Documents\\DTN\\IQFeed\\EURUSD.FXCM_1.txt",
+                           sep='\t', 
+                           tz='',   
+                           header=T,
+                           format='%d/%m/%Y %H:%M:%S'))

I get an error of: Error in as.POSIXlt.character(x, tz, ...) : character string is not in a standard unambiguous format

The question is: How do I combine the data and time into needed POSIX format? Thanks

`?read.zoo` has tons of examples and may include an example of how to do this. — Joshua Ulrich, Aug 24 '12 at 19:54
There are additional examples in the `zoo-read` vignette, as well. — G. Grothendieck, Aug 24 '12 at 19:55
Your `format` arg won't work because the date and time are in two different columns. The manual and vignette give examples how to combine dates/times from two different columns. — Joshua Ulrich, Aug 24 '12 at 20:37
It was example 4 that works. http://cran.r-project.org/web/packages/zoo/vignettes/zoo-read.pdf — heavy rocker dude, Aug 24 '12 at 21:08
Can you add the solution as an answer, in order to help others who find your question via searching? — Joshua Ulrich, Aug 24 '12 at 21:39

score 2 · Accepted Answer · answered Apr 03 '13 at 08:21

Since OP hasn't really updated his answer, I will add one way of doing this. To convert tab delimited (or CSV ) content to xts , I prefer to first read data as normal data frame and then use .xts function to convert the data.frame to xts

.xts is currently undocumented function though.

require(xts)
filecontent <- 'Date    Time    Last    LastSize    TotVol  Bid Ask TickID  BidSize AskSize
8/23/2012   0:00:00 0.95711 1   20670   0.95711 0.95742 0   0   0
8/23/2012   0:00:04 0.9571  1   20671   0.9571  0.9574  0   0   0'

DF <- read.table(text = filecontent, header = TRUE, stringsAsFactors = FALSE)

DFINDEX <- paste(DF$Date, DF$Time, sep = " ")

DF.XTS <- .xts(x = DF[, 3:10], index = as.POSIXct(DFINDEX, format = "%m/%d/%Y %H:%M:%S", tzone = "GMT"))

DF.XTS
##                        Last LastSize TotVol     Bid     Ask TickID BidSize AskSize
## 2012-08-23 08:00:00 0.95711        1  20670 0.95711 0.95742      0       0       0
## 2012-08-23 08:00:04 0.95710        1  20671 0.95710 0.95740      0       0       0

score -1 · Answer 2 · answered Aug 24 '12 at 21:43

-1

It was example 4 that works in cran.r-project.org/web/packages/zoo/vignettes/zoo-read.pdf Thanks to Joshua Ulrich for help

answered Aug 24 '12 at 21:43

heavy rocker dude

2,271
8
33
47

Give me a break. Show us how to get the example data you provided in the question into an `xts` object. – GSee Aug 24 '12 at 21:47

Convert tab delimited to XTS within R?

2 Answers2

Linked