Converting Multiple Existing Xts Objects to Multiple Data.Frames

Converting Multiple existing xts objects to multiple data.frames

By wrapping with which, it just returns the position with the names of the objects and not the actual objects. We may need to get the value of the objects with the names from that 'xtsObjects' output (mget - returns the values of multiple objects in a list, then loop over the list with map and convert to data.frame)

library(dplyr)
library(purrr)
out <- mget(names(xtsObjects)) %>% 
      map(fortify.zoo)

-output

> str(out)
List of 4
 $ PDD :'data.frame':   793 obs. of  7 variables:
  ..$ Index       : Date[1:793], format: "2018-07-26" "2018-07-27" "2018-07-30" "2018-07-31" ...
  ..$ PDD.Open    : num [1:793] 26.5 27.5 23.3 22.2 19.4 ...
  ..$ PDD.High    : num [1:793] 27.5 27.5 23.9 22.7 21.4 ...
  ..$ PDD.Low     : num [1:793] 25 23.2 21.9 19.6 18.6 ...
  ..$ PDD.Close   : num [1:793] 26.7 24.6 22.5 22.6 20.3 ...
  ..$ PDD.Volume  : num [1:793] 43213200 19923300 13967700 13709600 19339000 ...
  ..$ PDD.Adjusted: num [1:793] 26.7 24.6 22.5 22.6 20.3 ...
 $ RMO :'data.frame':   623 obs. of  7 variables:
  ..$ Index       : Date[1:623], format: "2019-04-01" "2019-04-02" "2019-04-03" "2019-04-04" ...
  ..$ RMO.Open    : num [1:623] 9.8 9.75 9.75 9.76 9.78 9.76 9.76 9.77 9.74 9.74 ...
  ..$ RMO.High    : num [1:623] 9.8 9.75 9.75 9.76 9.78 9.76 9.76 9.77 9.74 9.74 ...
  ..$ RMO.Low     : num [1:623] 9.75 9.75 9.75 9.76 9.75 9.76 9.76 9.77 9.74 9.74 ...
  ..$ RMO.Close   : num [1:623] 9.75 9.75 9.75 9.76 9.75 9.76 9.76 9.77 9.74 9.74 ...
  ..$ RMO.Volume  : num [1:623] 161000 11000 0 1000 100600 ...
  ..$ RMO.Adjusted: num [1:623] 9.75 9.75 9.75 9.76 9.75 9.76 9.76 9.77 9.74 9.74 ...
 $ TSLA:'data.frame':   2826 obs. of  7 variables:
  ..$ Index        : Date[1:2826], format: "2010-06-29" "2010-06-30" "2010-07-01" "2010-07-02" ...
  ..$ TSLA.Open    : num [1:2826] 3.8 5.16 5 4.6 4 ...
  ..$ TSLA.High    : num [1:2826] 5 6.08 5.18 4.62 4 ...
  ..$ TSLA.Low     : num [1:2826] 3.51 4.66 4.05 3.74 3.17 ...
  ..$ TSLA.Close   : num [1:2826] 4.78 4.77 4.39 3.84 3.22 ...
  ..$ TSLA.Volume  : num [1:2826] 93831500 85935500 41094000 25699000 34334500 ...
  ..$ TSLA.Adjusted: num [1:2826] 4.78 4.77 4.39 3.84 3.22 ...
 $ TSM :'data.frame':   3704 obs. of  7 variables:
  ..$ Index       : Date[1:3704], format: "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-08" ...
  ..$ TSM.Open    : num [1:3704] 11.1 10.9 10.8 10.6 10.8 ...
  ..$ TSM.High    : num [1:3704] 11.1 11 10.8 10.8 10.8 ...
  ..$ TSM.Low     : num [1:3704] 10.8 10.7 10.5 10.6 10.5 ...
  ..$ TSM.Close   : num [1:3704] 10.9 10.9 10.6 10.7 10.6 ...
  ..$ TSM.Volume  : num [1:3704] 12438784 17678653 12691240 5477350 7396096 ...
  ..$ TSM.Adjusted: num [1:3704] 6.38 6.38 6.21 6.25 6.17 ...

---

Regarding the for loop doesn't do anything issue, 1) it is simply trying to do fortify.zoo on the object names and not on the values, 2) even if it is the value on which fortify.zoo is applied, it should be assigned back to the original object i.e. if we want to update the original object names, use assign

for (i in seq_along(xtsObjects)) {
     assign(names(xtsObjects[i]), fortify.zoo(get(names(xtsObjects[i]))))
}

Convert xts/zoo object to data.frame in R

We could use fortify.zoo

library(zoo)
df1 <- fortify.zoo(NKLA)

-output

> str(df1)
'data.frame':   6 obs. of  7 variables:
 $ Index        : POSIXct, format: "2018-06-11" "2018-06-12" "2018-06-13" "2018-06-14" ...
 $ NKLA.Open    : num  9.57 9.56 9.57 9.57 9.57 9.54
 $ NKLA.High    : num  9.58 9.56 9.58 9.57 9.57 9.58
 $ NKLA.Low     : num  9.57 9.56 9.56 9.57 9.57 9.54
 $ NKLA.Close   : num  9.58 9.56 9.57 9.57 9.57 9.58
 $ NKLA.Volume  : num  402600 300000 179100 0 0 ...
 $ NKLA.Adjusted: num  9.58 9.56 9.57 9.57 9.57 9.58

data

NKLA <- structure(c(9.57, 9.56, 9.57, 9.57, 9.57, 9.54, 9.58, 9.56, 9.58, 
9.57, 9.57, 9.58, 9.57, 9.56, 9.56, 9.57, 9.57, 9.54, 9.58, 9.56, 
9.57, 9.57, 9.57, 9.58, 402600, 3e+05, 179100, 0, 0, 300, 9.58, 
9.56, 9.57, 9.57, 9.57, 9.58), .Dim = c(6L, 6L), .Dimnames = list(
    NULL, c("NKLA.Open", "NKLA.High", "NKLA.Low", "NKLA.Close", 
    "NKLA.Volume", "NKLA.Adjusted")), index = structure(c(1528689600, 
1528776000, 1528862400, 1528948800, 1529035200, 1529294400), tzone = "", tclass = c("POSIXct", 
"POSIXt")), class = c("xts", "zoo"))

Add two xts objects to a single data frame column

Is this what you mean?

intra$YesterdayClose <- EOD
intra
             Trade.Time     Last YesterdayClose
GLD 2011-03-04 04:00:00 139.3500         138.09
SLV 2011-03-04 04:00:00  34.6925          33.42

This assumes the same order in EOD as in the dataframe, if this is not the case then you can do this:

intra$YesterdayClose <- EOD[match(rownames(EOD),rownames(intra))]

Combine multiple xts objects created by getSymbols

The simplest way to do this is to load all the data into a new environment. Then you can use eapply to extract all the adjusted close columns into a list. Finally, you can use do.call to merge all the adjusted close prices in the list into one xts object.

library(quantmod)
tickers <- c("SNC.TO", "PHII", "HBC.TO", "GTE", "MOO",
             "MND.TO", "STKL", "SXC","XIU.TO")
dataEnv <- new.env()
getSymbols(tickers, from="2010-06-30", to="2015-06-30", env=dataEnv)
plist <- eapply(dataEnv, Ad)
pframe <- do.call(merge, plist)

Converting an xts object to a useful data frame

You can create your data.frame like this for example :

 data.frame(value=coredata(d3),timestamp=index(d3))
# value           timestamp
# 1    65 2013-06-02 00:12:00
# 2    70 2013-06-02 00:15:00
# 3    70 2013-06-02 00:30:00
# 4    70 2013-06-02 00:45:00
# 5    70 2013-06-02 01:00:00

I advise you also to use read.zoo to read your data as a zoo object and avoid coercing xts by hand. For example:

dat <- read.zoo(text='timestamp,value
"2013-06-02 00:00:00",70
"2013-06-02 00:02:00",70
"2013-06-02 00:07:00",60
"2013-06-02 00:15:00",70
"2013-06-02 00:12:00",60
"2013-06-02 00:30:00",70
"2013-06-02 00:45:00",70
"2013-06-02 01:00:00",70',tz ='' , format = "%Y-%m-%d %H:%M:%S",header=TRUE,
         sep=',')
d2 <- as.xts(dat)

Convert data.frame to xts object and preserve types

No, you can't. xts/zoo objects are a matrix with an index attribute and you can't mix types in a matrix.

We've considered creating an xts-data.frame class but a primary concern of xts is speed and memory efficiency. data.frames are not speed and memory efficient, so this hasn't been a priority.

R: Convert list of xts objects to data.frame

I would try this (although the ldply solution may be faster).

library(xts)
A <- xts(read.zoo(data.frame(day=as.Date("2001-05-25") + 1:10, x=rnorm(10), y=rnorm(10))))
B <- xts(read.zoo(data.frame(day=as.Date("2001-05-25") + 1:10, x=rnorm(10), y=rnorm(10))))
C <- list(US=A, CAN=B)
D <- do.call(merge.zoo, C)
E <- data.frame(day=index(D), coredata(D))
reshape(E, direction="long", idvar="day", timevar="country", varying=2:ncol(E))

Yields:

> reshape(E, direction="long", idvar="day", timevar="country", varying=2:ncol(E))
                      day country           x           y
2001-05-26.US  2001-05-26      US -1.14792688 -0.70425857
2001-05-27.US  2001-05-27      US  0.42892010 -0.62678907
2001-05-28.US  2001-05-28      US  1.20302730 -0.88504965
2001-05-29.US  2001-05-29      US  0.14411623  0.62155740
2001-05-30.US  2001-05-30      US -0.30979083 -1.63573976
2001-05-31.US  2001-05-31      US -0.53765221 -0.94028377
2001-06-01.US  2001-06-01      US  0.21273968  0.39703515
2001-06-02.US  2001-06-02      US -0.45567642  0.28003478
2001-06-03.US  2001-06-03      US -0.52659903 -1.05184085
2001-06-04.US  2001-06-04      US  0.23540896 -1.52234888
2001-05-26.CAN 2001-05-26     CAN  0.27341723 -0.29382874
2001-05-27.CAN 2001-05-27     CAN  0.08398618  0.88950783
2001-05-28.CAN 2001-05-28     CAN  0.24333694  0.60005146
2001-05-29.CAN 2001-05-29     CAN  0.82480254 -0.77898367
2001-05-30.CAN 2001-05-30     CAN -0.18744699 -1.14777217
2001-05-31.CAN 2001-05-31     CAN  0.98918900 -0.04893292
2001-06-01.CAN 2001-06-01     CAN -0.27379800 -1.23558134
2001-06-02.CAN 2001-06-02     CAN -0.88556293  2.34522201
2001-06-03.CAN 2001-06-03     CAN -0.68985258 -0.37681843
2001-06-04.CAN 2001-06-04     CAN  0.11916878 -2.39336976

---

Related Topics