merge by date not time in R

Question

I have a large time-series file that I imported from my working directory and then turn them into log returns by:

read.csv("/Volumes/3TB/ALLsince1996.csv",header=T)-> ALL
all <- xts(ALL[,2:dim(ALL)[2]], order.by= as.POSIXct(ALL[,1], format="%m/%d/%y"))
RETS <- CalculateReturns(all, method= c("log"))
RETS<- na.locf(RETS)
RETS[is.na(RETS)] <- 0

I then download the 3-Month Treaury via FRED by:

# 3-Mo Treasury
data <- new.env()
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
getSymbols( FEDs
        , src = "FRED"  
        , env = data
)
data$DGS3MO -> TB3
TB3/100/365 -> TB3
na.locf(TB3["1996-01-01::"])-> TB3

I then try to combine the log returns series with the 3-month treasury using cbind() and get the following:

both <- cbind(RETS[,1], TB3)

both:

    row.names      ZX.Adjusted  DGS3MO
1   1995-12-31 16:00:00 NA  NA
2   1996-01-01 00:00:00 0   NA
3   1996-01-01 16:00:00 NA  0.0001424658
4   1996-01-02 00:00:00 0   NA
5   1996-01-02 16:00:00 NA  0.0001424658
6   1996-01-03 00:00:00 0   NA
7   1996-01-03 16:00:00 NA  0.0001421918
8   1996-01-04 00:00:00 0   NA
9   1996-01-04 16:00:00 NA  0.0001421918

But this returns a vector with two-times per day; such as 1996-01-01 00:00:00 and 1996-01-01 16:00:00 . What I would like is to combine the two by date not by time.

REPRODUCIBLE DATA:

#Pull Data from getSymbols() 
library(quantmod)

dataset<- xts()
symbols <- c( "GLD", "IWM", "SPY", "GS")

system.time(
for(i in 1:length(symbols)) {
symbols[i]-> symbol
tryit <- try(getSymbols(symbol, from="1995-12-31", src='yahoo'))
if(inherits(tryit, "try-error")){
i <- i+1
} else {
data <- getSymbols(symbol, from="1995-12-31", src='yahoo')
dataset <- merge(dataset, Ad(get(symbols[i])))
rm(symbol)
}
}
)

Because it was a large file I saved dataset and index(dataset) in two separate files as I could not save the index with the dataset

write.csv(dataset, "dataset.csv")
write.csv(index(dataset), "index.csv")

I later opened the index.csv file in Excel & manually pasted the index to dataset.csv & saved the file.I later tried to reopen the .csv unto my workspace & calculate log returns

read.csv("dataset.csv",header=T)-> ALL
all <- xts(ALL[,2:dim(ALL)[2]], order.by= as.POSIXct(ALL[,1], format="%m/%d/%y"))
RETS <- CalculateReturns(all, method= c("log"))
RETS<- na.locf(RETS)
RETS[is.na(RETS)] <- 0

Next Download the 3-Month T-Bill, same code as above...

# 3-Mo Treasury
data <- new.env()
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
getSymbols( FEDs
        , src = "FRED"  
        , env = data
)
data$DGS3MO -> TB3
TB3/100/365 -> TB3
na.locf(TB3["1996-01-01::"])-> TB3

Now try to combine the RETS1 with TB3...

both <- cbind(RETS1, TB3)

Would you mind making your example reproducible? That'd help us understand your problem: http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example. Basically, give us a sample so we can copy from here and paste into our R workspaces and get the same result you are. — Will Beason, Aug 17 '14 at 03:41
Have you tried the merge function? You can specify the merge column in both data.frames. — mgriebe, Aug 17 '14 at 05:35
@mgriebe I have also tried the `merge(x,y, join='left')` but doesn't work, — Rime, Aug 17 '14 at 06:15

score 1 · Accepted Answer · answered Aug 17 '14 at 09:04

@Rime, to reformat the index without the time information use the strptime function and later merge the series as suggested above.

index(dataset) <- strptime(index(dataset),"%Y-%m-%d")

A much easier and more elegant way to accomplish what you are trying to do is to use the makeReturnFrame function using the fantastic qmao-package (https://r-forge.r-project.org/R/?group_id=1113) with a lot of utility and helper function for this kind of stuff.

library(quantmod)
library(qmao)

symbols <- c( "GLD", "IWM", "SPY", "GS")

getSymbols(symbols, from="1995-12-31", src='yahoo')
rets <- makeReturnFrame(symbols,silent = TRUE)
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
data <- new.env()
getSymbols( FEDs
            , src = "FRED"  
            , env = data
)
data$DGS3MO -> TB3
TB3/100/365 -> TB3
na.locf(TB3["1996-01-01::"])-> TB3
series.merged <- merge(rets,TB3,join = "inner") 

> tail(series.merged)
                     GLD          IWM          SPY            GS       DGS3MO
2014-08-07  4.050035e-03 -0.004844797 -0.005429405 -0.0037775986 8.219178e-07
2014-08-08  7.924872e-05  0.009666235  0.011502456  0.0185147075 8.219178e-07
2014-08-11 -1.824311e-03  0.009485466  0.002893760  0.0011603622 1.095890e-06
2014-08-12  2.381425e-04 -0.006905738 -0.001394160 -0.0007540822 8.219178e-07
2014-08-13  1.665411e-03  0.007787650  0.006746170  0.0002320859 1.095890e-06
2014-08-14  8.712527e-04  0.001497468  0.004710710  0.0020863525 1.095890e-06

score 0 · Answer 2 · edited Oct 10 '14 at 10:35

The time component has to removed from the indexes of both datasets for proper merge across date index

#Read stock returns data
require(quantmod)

data_agg<- xts()
symbols <- c( "GLD", "IWM", "SPY", "GS")


for(i in 1:length(symbols)) {
symbols[i]->symbol #assign
tryit <- try(getSymbols(symbol, from="1995-12-31", src='yahoo'))
if(inherits(tryit, "try-error")){
i <- i+1
} else {
data <- getSymbols(symbol, from="1995-12-31", src='yahoo')
data_agg <- merge(data_agg, Ad(get(symbols[i])))
rm(symbol)
}
}

head(data_agg)
#                    GLD.Adjusted IWM.Adjusted SPY.Adjusted GS.Adjusted
#1996-01-02 05:30:00           NA           NA        44.91          NA
#1996-01-03 05:30:00           NA           NA        45.03          NA
#1996-01-04 05:30:00           NA           NA        44.60          NA
#1996-01-05 05:30:00           NA           NA        44.51          NA
#1996-01-08 05:30:00           NA           NA        44.68          NA
#1996-01-09 05:30:00           NA           NA        43.91          NA


#Read interest rate data

data <- new.env()
FEDs <- c( "DGS3MO") # DGS3MO : 3-Mo Treasury Constant maturity
getSymbols( FEDs
            , src = "FRED"  
            , env = data
)
TB3 <- data$DGS3MO
TB3 <- TB3/100/365 
TB3 <- na.locf(TB3["1996-01-01::"])

head(TB3)
#                 DGS3MO
#1996-01-01           NA
#1996-01-02 0.0001424658
#1996-01-03 0.0001424658
#1996-01-04 0.0001421918
#1996-01-05 0.0001421918
#1996-01-08 0.0001419178

cbind is not advisable for merging purpose as it places the inputs side-by-side by coercing to same format. merge.xts is the method for merging across xts objects

#The index of the datasets contain time component hence the doubled rows here

head(merge.xts(data_agg,TB3))
#           GLD.Adjusted IWM.Adjusted SPY.Adjusted GS.Adjusted       DGS3MO
#1996-01-01           NA           NA           NA          NA           NA
#1996-01-01           NA           NA        44.91          NA           NA
#1996-01-02           NA           NA           NA          NA 0.0001424658
#1996-01-02           NA           NA        45.03          NA           NA
#1996-01-03           NA           NA           NA          NA 0.0001424658
#1996-01-03           NA           NA        44.60          NA           NA


head(as.Date(index(data_agg)))
#[1] "1996-01-02" "1996-01-03" "1996-01-04" "1996-01-05" "1996-01-08" "1996-01-09"

#Only one observation per day, since there are no duplicate dates
#We can safely strip the time component from dates
any(duplicated(as.Date(index(data_agg))))
#[1] FALSE

any(duplicated(as.Date(index(TB3))))
#[1] FALSE


#Keep only the date component across both datasets
index(data_agg)<-as.Date(index(data_agg))
index(TB3)<-as.Date(index(TB3))


#Properly merged datasets across date index
head(merge.xts(data_agg,TB3))
#           GLD.Adjusted IWM.Adjusted SPY.Adjusted GS.Adjusted       DGS3MO
#1995-12-31           NA           NA        44.91          NA           NA
#1996-01-01           NA           NA        45.03          NA 0.0001424658
#1996-01-02           NA           NA        44.60          NA 0.0001424658
#1996-01-03           NA           NA        44.51          NA 0.0001421918
#1996-01-04           NA           NA           NA          NA 0.0001421918
#1996-01-06           NA           NA        44.68          NA           NA

You can use complete.cases to keep non-missing returns only.

merge by date not time in R

2 Answers2