I have two data sets, AAPL and AMZN, that I wish two merge but find it difficult to do so as merge cbind fail to do as I desire it to be. I believe the issues is recognizing the data sets as data.frames but not sure.
The data looks like this:
Date Time Open High Low Close Volume
1 12/14/12 9:30 514.75 515.10 512.72 512.86 2504264
2 12/14/12 9:31 512.80 513.00 510.00 510.17 574498
3 12/14/12 9:32 510.04 511.70 509.11 511.26 673126
4 12/14/12 9:33 511.26 511.54 508.82 509.25 477914
5 12/14/12 9:34 509.03 510.65 508.50 510.54 432689
Desired Outcome:
Date Time Open High Low Close Volume
12/14/12 9:30 250.11 250.64 250.07 250.37 38249
12/14/12 9:31 250.60 250.60 250.16 250.51 6954
12/14/12 9:32 250.47 250.72 250.43 250.72 3843
12/14/12 9:33 250.69 250.70 250.44 250.50 3990
12/14/12 9:34 250.46 250.64 250.21 250.31 4490
Date Time Open High Low Close Volume
12/14/12 9:31 512.80 513.00 510.00 510.17 574498
12/14/12 9:32 510.04 511.70 509.11 511.26 673126
12/14/12 9:33 511.26 511.54 508.82 509.25 477914
12/14/12 9:34 509.03 510.65 508.50 510.54 432689
Essentially, I want to merge the two data sets by Date and Time side-by-side (I could not do it on here). I have tried converting each data set to xts but not sure if it is correct:
AAPL <- read.csv("aapl1.csv",header=TRUE)
AMZN <- read.csv("amzn1.csv",header=TRUE)
aapl <- xts(AAPL[,c(3:7)], AAPL$DATETIME <-as.POSIXct(paste(AAPL$Date,AAPL$Time), format=""%m/%d/%Y %H:%M"))
amzn <- xts(AMZN[,c(3:7)], AMZN$DATETIME <-as.POSIXct(paste(AMZN$Date,AMZN$Time), format=""%m/%d/%Y %H:%M"))
It then fails to merge when I use cbind , merge , or even join.
A second alternative is join() from the plyr package. It has some advanteges over merge(), but also provides less options. Would be recommendable for very large data sets because it is faster than merge().
require(plyr)
join(AAPL, AMZN, by = c("Date", "Time"))