I need to store some larger zoo objects on a local database. I realize that this is a vast subject, but I suspect that I am not alone in needing to use a database for storing equity prices, volumes, etc. I have not been able to find much help on designing the db tables, and pointers to the literature are more than welcome. The following code works, but I would like to make it more robust or elegant. "prices" is a df with the prices of shares. # str(prices) # num [1:1872, 1:25] 9184 9311 9545 9538 9643 ... # - attr(*, "dimnames")=List of 2 # ..$ : NULL # ..$ : chr [1:25] "AdjClose" "A" "AA" "AAC" ... # - attr(*, "index")=Class 'Date' num [1:1872] 10595 10596 10597 10598 10599 .. # extract the date from the zoo object: Date <- index(prices) # extract the data from the zoo object: y <- coredata(prices) # combine into a df . Note: I can not write a zoo object directly to the db - correct? z <- as.data.frame(cbind(Date,y)) # Write to db: dbWriteTable(con, "Prices", z) # Read from db: a <- dbReadTable(con, "Prices") # extract the date index.date <- as.Date(a[[1]]) # form the zoo object b <- zoo(a[-1], index.date) Many thanks, Michael Cohen Southern Cross University
Moving zoo objects to a db and back again
3 messages · Michael Cohen, Ajay Shah, Gabor Grothendieck
On Sat, Jun 17, 2006 at 11:49:49AM +1000, Michael Cohen wrote:
I need to store some larger zoo objects on a local database.
One possibility could be like this. Use save() to make a binary file
out of R. Feed that into the database as a blob. :-) On the reverse
trip, extract the blob from the database and do load(). This is
inefficient in some ways and efficient in others.
-ans
Ajay Shah http://www.mayin.org/ajayshah ajayshah at mayin.org http://ajayshahblog.blogspot.com <*(:-? - wizard who doesn't know the answer.
Yes. Convert the zoo object to/from a data.frame:
library(zoo)
library(RSQLite)
# z is zoo object
z <- zoo(cbind(x = 1:10, y = 11:20), as.Date("2006-01-01") + 0:9)
# set up data base
m <- dbDriver("SQLite")
con <- dbConnect(m, dbname = "base.dbms")
# create data frame from zoo object and write to data base
z.df <- cbind(idx = index(z), as.data.frame(z))
dbWriteTable(con, "z", z.df, overwrite = TRUE)
# read data frame from data base and conver to zoo
z2.df <- dbReadTable(con, "z")
z2 <- zoo(data.matrix(z2.df[,-1]), as.Date(z2.df[,1]))
all.equal(coredata(z), coredata(z2)) # TRUE
all.equal(time(z), time(z2)) # TRUE
On 6/16/06, Michael Cohen <michaelbcohen at gmail.com> wrote:
I need to store some larger zoo objects on a local database. I realize that this is a vast subject, but I suspect that I am not alone in needing to use a database for storing equity prices, volumes, etc. I have not been able to find much help on designing the db tables, and pointers to the literature are more than welcome. The following code works, but I would like to make it more robust or elegant. "prices" is a df with the prices of shares. # str(prices) # num [1:1872, 1:25] 9184 9311 9545 9538 9643 ... # - attr(*, "dimnames")=List of 2 # ..$ : NULL # ..$ : chr [1:25] "AdjClose" "A" "AA" "AAC" ... # - attr(*, "index")=Class 'Date' num [1:1872] 10595 10596 10597 10598 10599 .. # extract the date from the zoo object: Date <- index(prices) # extract the data from the zoo object: y <- coredata(prices) # combine into a df . Note: I can not write a zoo object directly to the db - correct? z <- as.data.frame(cbind(Date,y)) # Write to db: dbWriteTable(con, "Prices", z) # Read from db: a <- dbReadTable(con, "Prices") # extract the date index.date <- as.Date(a[[1]]) # form the zoo object b <- zoo(a[-1], index.date) Many thanks, Michael Cohen Southern Cross University
_______________________________________________ R-SIG-Finance at stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-sig-finance