Is there a way to fill NAs in xts object, but only upto a month forward? My time series is on a weekday frequency. I want to avoid backward filling data at a particular date with a value that is more than a month old. #note: no of weekdays in the month vary and therefore, I don't have a fixed number to set as input for maxgap in na.locf. Plus, maxgap =20 did not work for me on the below sample set for column Z.
Small excerpt of the dataset:
tempY <- structure(c(1.013367062565, 1.02549265039, 1.06441613562, 1.061125834671,
1.058095154587, 1.019989589422, 1.009980314126, 1.020806729739,
1.026513004385, 1.025998357485, 1.011398006146, NA, 1.014043343859,
1.016744497778, 1.027453095813, 1.030421507552, 1.059024397083,
1.050921074032, 1.054401954388, 1.05940756622, 1.088564123549,
1.063802687358, 1.047656980939, 1.051924103152, 1.038016436332,
1.038100571345, 1.037565907689, 1.024397906272, 1.04517232233,
1.040964696607, 1.085927457557, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1.141623602982,
NA, NA, NA, NA, NA, NA, NA, NA, 1.044212, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA), class = c("xts", "zoo"), .indexCLASS = "Date", .indexTZ = "UTC", tclass = "Date", tzone = "UTC", index = structure(c(946598400,
946857600, 946944000, 947030400, 947116800, 947203200, 947462400,
947548800, 947635200, 947721600, 947808000, 948067200, 948153600,
948240000, 948326400, 948412800, 948672000, 948758400, 948844800,
948931200, 949017600, 949276800, 949363200, 949449600, 949536000,
949622400, 949881600, 949968000, 950054400, 950140800), tzone = "UTC", tclass = "Date"), .Dim = c(30L,
3L), .Dimnames = list(NULL, c("X", "Y", "Z")))
Expected output:
tempZ <- structure(c(1.013367062565, 1.02549265039, 1.06441613562, 1.061125834671,
1.058095154587, 1.019989589422, 1.009980314126, 1.020806729739,
1.026513004385, 1.025998357485, 1.011398006146, 1.011398006146,
1.014043343859, 1.016744497778, 1.027453095813, 1.030421507552,
1.059024397083, 1.050921074032, 1.054401954388, 1.05940756622,
1.088564123549, 1.063802687358, 1.047656980939, 1.051924103152,
1.038016436332, 1.038100571345, 1.037565907689, 1.024397906272,
1.04517232233, 1.040964696607, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.141623602982,
1.141623602982, 1.141623602982, 1.141623602982, 1.141623602982,
1.141623602982, 1.141623602982, 1.141623602982, 1.141623602982,
1.044212, 1.044212, 1.044212, 1.044212, 1.044212, 1.044212, 1.044212,
1.044212, 1.044212, 1.044212, 1.044212, 1.044212, 1.044212, 1.044212,
1.044212, 1.044212, 1.044212, 1.044212, 1.044212, 1.044212, 1.044212,
1.044212, NA, NA, NA, NA, NA, NA, NA, NA), class = c("xts", "zoo"
), .indexCLASS = "Date", .indexTZ = "UTC", tclass = "Date", tzone = "UTC", index = structure(c(946598400,
946857600, 946944000, 947030400, 947116800, 947203200, 947462400,
947548800, 947635200, 947721600, 947808000, 948067200, 948153600,
948240000, 948326400, 948412800, 948672000, 948758400, 948844800,
948931200, 949017600, 949276800, 949363200, 949449600, 949536000,
949622400, 949881600, 949968000, 950054400, 950140800), tzone = "UTC", tclass = "Date"), .Dim = c(30L,
3L), .Dimnames = list(NULL, c("X", "Y", "Z")))
Update: @G. Grothendieck: dput output for one variable:
structure(c(1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.085927457557, 1.085927457557, 1.085927457557,
1.085927457557, 1.141623602982, 1.141623602982, 1.141623602982,
1.141623602982, 1.141623602982, 1.141623602982, 1.141623602982,
1.141623602982, 1.141623602982, 1.141623602982, 1.141623602982,
1.141623602982, 1.141623602982, 1.141623602982, 1.141623602982,
1.141623602982, 1.141623602982, 1.141623602982, 1.141623602982,
1.141623602982, 1.141623602982, 1.181744131387, 1.181744131387,
1.181744131387, 1.181744131387, 1.181744131387, 1.181744131387,
1.181744131387, 1.181744131387, 1.181744131387, 1.181744131387,
1.181744131387, 1.181744131387, 1.181744131387, 1.181744131387,
1.181744131387, 1.181744131387, 1.181744131387, 1.181744131387,
1.181744131387, 1.181744131387, 1.181744131387, NA, NA, 1.113811336182,
1.113811336182, 1.113811336182, 1.113811336182, 1.113811336182
), class = c("xts", "zoo"), .indexCLASS = "Date", tclass = "Date", .indexTZ = "UTC", tzone = "UTC", index = structure(c(946598400,
946857600, 946944000, 947030400, 947116800, 947203200, 947462400,
947548800, 947635200, 947721600, 947808000, 948067200, 948153600,
948240000, 948326400, 948412800, 948672000, 948758400, 948844800,
948931200, 949017600, 949276800, 949363200, 949449600, 949536000,
949622400, 949881600, 949968000, 950054400, 950140800, 950227200,
950486400, 950572800, 950659200, 950745600, 950832000, 951091200,
951177600, 951264000, 951350400, 951436800, 951696000, 951782400,
951868800, 951955200, 952041600, 952300800, 952387200, 952473600,
952560000, 952646400, 952905600, 952992000, 953078400, 953164800,
953251200, 953510400, 953596800, 953683200, 953769600, 953856000,
954115200, 954201600, 954288000, 954374400, 954460800, 954720000,
954806400, 954892800, 954979200), tzone = "UTC", tclass = "Date"), .Dim = c(70L,
1L), .Dimnames = list(NULL, "X990100"))```
rollapplyr in zoo can take a vector width. We can set each of its elements to be the number of points from the current point to a month ago (i.e. the current position minus the position a month ago) and then rollapplyr can apply na.locf0 to only the last month.
library(lubridate)
library(xts)
tt <- time(tempY)
w <- seq_along(tt) - findInterval(tt %m-% months(1), tt)
Fill <- function(x) {
last <- tail(x, 1)
if (is.na(last)) tail(na.locf0(x), 1) else last
}
rollapplyr(as.zoo(tempY), w, Fill)