CODEWITHSUNDEEP
rxtszoo
Pierre Lapointe
Pierre Lapointe

Reputation: 16277

Convert times series to.quarterly with NAs

I have a multivariate zoo time series that contains NAs and I want to convert that to a quarterly series. 

df1 <-1:12
df1[df1%%4==0] <- NA
zoo.object  <- zoo(matrix(df1, ncol=2),as.Date("2013-01-01")+(0:5)*35)
colnames(zoo.object) <-c("stock1","stock2")


> zoo.object
           stock1 stock2
2013-01-01      1      7
2013-02-05      2     NA
2013-03-12      3      9
2013-04-16     NA     10
2013-05-21      5     11
2013-06-25      6     NA

Ideally, I would like to keep the earlier data in the quarter for each stocks. I have tried to.quarterly from the xts package, but the problem is that it removes lines with NAs. 

> to.quarterly(zoo.object,OHLC = FALSE)
        stock1 stock2
2013 Q1      3      9
2013 Q2      5     11
Warning message:
In to.period(x, "quarters", indexAt = indexAt, name = name, ...) :
  missing values removed from data

I have also thought about using na.locf, but this carries data from one quarter to the next. (in the example stock 1 on 2013-04-16 (quarter 1) should not be equal to 3 (as this is a quarter 2 value). A reverse na.locf would not work either because it could carry numbers from a later quarter.

Using my example, here’s what I would like: 

        stock1 stock2
2013 Q1      1      7
2013 Q2      5     10

Upvotes: 2

Views: 1375

Answers (1)

GSee
GSee

Reputation: 49810

Try this

library(xts)
(z <- period.apply(zoo.object, endpoints(zoo.object, "quarters"), 
                    function(x) first(na.locf(x, fromLast=TRUE))))
#           stock1 stock2
#2013-03-12      1      7
#2013-06-25      5     10

Per the comments, you can convert the index to yearqtr:

index(z) <- as.yearqtr(index(z))
z
#         stock1 stock2
#2013 Q1       1      7
#2013 Q2       5     10

Or maybe you prefer

index(z) <- as.Date(as.yearqtr(index(z)))
z
#           stock1 stock2
#2013-01-01      1      7
#2013-04-01      5     10

Upvotes: 4

Related Questions