Merging irregular time series

Question

I have two time series, one being a daily time series and the other one a discrete one. In my case I have share prices and ratings I need to merge but in a way that the merged time series keeps the daily dates according to the stock prices and that the rating is fitted to the daily data by ticker and date. A simple merge command would only look for the exact date and ticker and apply NA to non-fitting cases. But I would like to look for the exact matches and fill the dates between with last rating.

 Daily time series:

         ticker       date        stock.price
          AA US Equity 2004-09-06  1
          AA US Equity 2004-09-07  2
          AA US Equity 2004-09-08  3
          AA US Equity 2004-09-09  4
          AA US Equity 2004-09-10  5
          AA US Equity 2004-09-11  6

  Discrete time series
          ticker        date        Rating Last_Rating
          AA US Equity   2004-09-08   A         A+
          AA US Equity   2004-09-11   AA        A
          AAL LN Equity  2005-09-08   BB        BB
          AAL LN Equity  2007-09-09   AA        AA-
          ABE SM Equity  2006-09-10   AA        AA-
          ABE SM Equity  2009-09-11   AA        AA-


  Required Output:

           ticker       date        stock.price  Rating
          AA US Equity 2004-09-06    1             A+
          AA US Equity 2004-09-07    2             A+
          AA US Equity 2004-09-08    3             A
          AA US Equity 2004-09-09    4             A
          AA US Equity 2004-09-10    5             A
          AA US Equity 2004-09-11    6             AA

I would be very greatful for your help.

please make your data easy to load, i.e. `dput` instead of/in addition to copy-pasting (this is simple rolling merge problem in `data.table`, but your current data presentation is too cumbersome to work with) — eddi, Oct 28 '13 at 20:56

score 1 · Accepted Answer · answered Oct 29 '13 at 00:43

Maybe this is the solution you want. The function na.locf in the time series package zoo can be used to carry values forward (or backward).

library(zoo)
library(plyr)
options(stringsAsFactors=FALSE)

daily_ts=data.frame(
    ticker=c('A','A','A','A','B','B','B','B'),
    date=c(1,2,3,4,1,2,3,4),
    stock.price=c(1.1,1.2,1.3,1.4,4.1,4.2,4.3,4.4)
    )
discrete_ts=data.frame(
    ticker=c('A','A','B','B'),
    date=c(2,4,2,4),
    Rating=c('A','AA','BB','BB-'),
    Last_Rating=c('A+','A','BB+','BB')
    )

res=ddply(
    merge(daily_ts,discrete_ts,by=c("ticker","date"),all=TRUE),
    "ticker",
    function(x) 
        data.frame(
            x[,c("ticker","date","stock.price")],
            Rating=na.locf(x$Rating,na.rm=FALSE),
            Last_Rating=na.locf(x$Last_Rating,na.rm=FALSE,fromLast=TRUE)
            )
    )

res=within(
    res,
    Rating<-ifelse(
        is.na(Rating),
        Last_Rating,Rating
        )
    )[,setdiff(colnames(res),"Last_Rating")]

res

Gives

#  ticker date stock.price Rating
#1      A    1         1.1     A+
#2      A    2         1.2      A
#3      A    3         1.3      A
#4      A    4         1.4     AA
#5      B    1         4.1    BB+
#6      B    2         4.2     BB
#7      B    3         4.3     BB
#8      B    4         4.4    BB-

Merging irregular time series

1 Answers1

Linked