[R-sig-Finance] Moving zoo objects to a db and back again

[Gabor Grothendieck]

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
>