CODEWITHSUNDEEP
rxts
Joel
Joel

Reputation: 65

Rolling count of observations in XTS objective by columns

I'm trying to create an XTS object that contains the rolling count of observations contained within another XTS object, where the observations are not equal to NA or 0.

XTS object looks like this:

Date         Col1    Col2    Col3
2015-06-30   1.25    1.58    NA
2015-07-31   58.7    1.87    0
2015-08-31   NA      71.8    0 
2015-09-30   15.4    78.1    0
2015-10-31   7.25    62.4    15
2015-11-30   1.0     11.5    78

Desired output looks like this:

Date         Col1    Col2    Col3
2015-06-30   1       1       NA
2015-07-31   2       2       NA
2015-08-31   NA      3       NA 
2015-09-30   1       4       NA
2015-10-31   2       5       1
2015-11-30   3       6       2

Upvotes: 0

Views: 166

Answers (1)

Zygmunt Zawadzki
Zygmunt Zawadzki

Reputation: 251

This solves the problem. I'm using plain old for loop to accomplish this task. Some people might say that it shouldn't be used in R because it is slow, however, sometimes it's tough to find an elegant and straightforward solution without it.

library(xts)
x <- read.table(text = "Date         Col1    Col2    Col3
2015-06-30   1.25    1.58    NA
2015-07-31   58.7    1.87    0
2015-08-31   NA      71.8    0 
2015-09-30   15.4    78.1    0
2015-10-31   7.25    62.4    15
2015-11-30   1.0     11.5    78", header = TRUE)


xtbl <- xts(x[,-1], order.by = as.Date(x[[1]]))

roll_count <- function(y) {
  y <- is.na(y) | y == 0
  y <- as.numeric(!y)
  for(i in seq_along(y)[-1]) {
    if(y[i] != 0) {
      y[i] <- y[i-1] + y[i]  
    }
  }
  y[y == 0] <- NA
  y
}

res <- apply(xtbl, 2, roll_count)
as.xts(res, order.by = index(xtbl))

Upvotes: 1

Related Questions