Efficient solution to (recursively) replace NAs with the mean of lags, by group

Question

I need to replace NAs with the mean of previous three values, by group. Once an NA is replaced, it will serve as input for computing the mean corresponding to the next NA (if next NA is within the next three months).

Here it is an example:

id   date   value
1 2017-04-01 40
1 2017-05-01 40
1 2017-06-01 10
1 2017-07-01 NA
1 2017-08-01 NA
2 2014-01-01 27
2 2014-02-01 13

Data:

dt <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 2L, 2L), date = structure(c(17257, 17287, 17318, 17348, 17379, 16071, 16102), class = "Date"), value = c(40, 40, 10, NA, NA, 27, 13)), row.names = c(1L, 2L, 3L, 4L, 5L, 8L, 9L), class = "data.frame")

The output should look like:

id   date   value
1 2017-04-01 40.00
1 2017-05-01 40.00
1 2017-06-01 10.00
1 2017-07-01 30.00
1 2017-08-01 26.66
2 2014-01-01 27.00
2 2014-02-01 13.00

where 26.66 = (30 + 10 + 40)/3

What is an efficient way to do this (i.e. to avoid for loops)?

See [this question](https://stackoverflow.com/questions/52023709/what-can-r-do-about-a-messy-data-format). — Rui Barradas, Nov 22 '18 at 16:05

score 2 · Answer 1 · answered Nov 22 '18 at 16:16

The following uses base R only and does what you need.

sp <- split(dt, dt$id)
sp <- lapply(sp, function(DF){
  for(i in which(is.na(DF$value))){
    tmp <- DF[seq_len(i - 1), ]
    DF$value[i] <- mean(tail(tmp$value, 3))
  }
  DF
})

result <- do.call(rbind, sp)
row.names(result) <- NULL

result
#  id       date    value
#1  1 2017-01-04 40.00000
#2  1 2017-01-05 40.00000
#3  1 2017-01-06 10.00000
#4  1 2017-01-07 30.00000
#5  1 2017-01-08 26.66667
#6  2 2014-01-01 27.00000
#7  2 2014-01-02 13.00000

G. Grothendieck · Accepted Answer · 2018-11-22T22:09:23.393

Define a roll function which takes 3 or less previous values as a list and the current value and returns as a list the previous 2 values with the current value if the current value is not NA and the prevous 2 values with the mean if the current value is NA. Use that with Reduce and pick off the last value of each list in the result. Then apply all that to each group using ave.

roll <- function(prev, cur) {
  prev <- unlist(prev)
  list(tail(prev, 2), if (is.na(cur)) mean(prev) else cur)
}

reduce_roll <- function(x) {
  sapply(Reduce(roll, init = x[1], x[-1], acc = TRUE), tail, 1)
}

transform(dt, value = ave(value, id, FUN = reduce_roll))

giving:

  id       date    value
1  1 2017-04-01       40
2  1 2017-05-01       40
3  1 2017-06-01       10
4  1 2017-07-01       30
5  1 2017-08-01 26.66667
8  2 2014-01-01       27
9  2 2014-02-01       13

Neat answer, thanks. Also, I corrected the cut off data (it was a copy-paste mistake which I didn't notice). — NRLP, Nov 22 '18 at 19:04

Efficient solution to (recursively) replace NAs with the mean of lags, by group

2 Answers2

Linked