CODEWITHSUNDEEP
rdataframexts
Milktrader
Milktrader

Reputation: 9838

Add two xts objects to a single data frame column

This is the result I want to achieve:

             Trade Time     Last  Yesterday Close
GLD 2011-03-04 04:00:00 139.3500           138.09  
SLV 2011-03-04 04:00:00  34.6925            33.42

To get the first two columns, 

require("quantmod")

intra <- getQuote("GLD;SLV", what=yahooQF("Last Trade (Price Only)")) 

>intra
             Trade Time     Last
GLD 2011-03-04 04:00:00 139.3500
SLV 2011-03-04 04:00:00  34.6925

> class(intra)
[1] "data.frame"

To get yesterday's close, 

require("quantmod")

getSymbols("GLD;SLV")

GLD <- GLD[,4]    #only the close
SLV <- SLV[,4]

GLD  <- head(tail(GLD, n=2), n=1)   #only yesterday
SLV  <- head(tail(SLV, n=2), n=1)

GLD  <- as.vector(GLD)  #prepare for merge with intra data.frame
SLV  <- as.vector(SLV)

EOD <- rbind(GLD, SLV)

> EOD
      [,1]
GLD 138.09
SLV  33.42

> class(EOD)
[1] "matrix"

Then, the naive merge() approach

> super <- merge(intra, EOD)

> class(super)
[1] "data.frame"

> super
           Trade Time     Last   [,1]
1 2011-03-04 04:00:00 139.3500 138.09
2 2011-03-04 04:00:00  34.6925 138.09
3 2011-03-04 04:00:00 139.3500  33.42
4 2011-03-04 04:00:00  34.6925  33.42

Close, but something is terribly wrong. First, I lost the descriptive GLD and SLV labels that were on the intra data.frame. Second, I have four rows instead of two (mysterious duplication).

Refactoring tips are always welcome.

Upvotes: 0

Views: 2190

Answers (1)

Sacha Epskamp
Sacha Epskamp

Reputation: 47582

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))]

Upvotes: 2

Related Questions