Expand last observed values within group in data table in R

Question

Imagine a data.table in R like this

dtable = data.table(
  id = c(1, 1, 1, 2, 2, 2),
  time = c(1, 2, 3, 2, 3, 4),
  value_a = c(NA, 'Yes', NA, 'No', NA, 'Yes'),
  value_b = c('No', 'Yes', NA, NA, NA, NA)
)
cols <- c("value_a", "value_b")

which evaluates to

   id time value_a value_b
1:  1    1    <NA>      No
2:  1    2     Yes     Yes
3:  1    3    <NA>    <NA>
4:  2    2      No    <NA>
5:  2    3    <NA>    <NA>
6:  2    4     Yes    <NA>

For each id and time I wish to expand the latest observed (<NA> corresponds to no observation) value. I.e. I am searching an efficient method to create the resulting table:

   id time value_a value_b
1:  1    1    <NA>      No
2:  1    2     Yes     Yes
3:  1    3     Yes     Yes
4:  2    2      No    <NA>
5:  2    3      No    <NA>
6:  2    4     Yes    <NA>

My dataset is very large so efficiency is important.

Possible duplicate of [Filling in missing (blanks) in a data table, per category - backwards and forwards](https://stackoverflow.com/questions/12607465/filling-in-missing-blanks-in-a-data-table-per-category-backwards-and-forwar) — chinsoon12, Jun 05 '18 at 00:18

score 2 · Accepted Answer · answered Jun 04 '18 at 15:19

This should be faster.
Using na.locf (forward filling NA) from zoo package, you can do:

dtable[, c('value_a','value_b') := lapply(.SD, na.locf, na.rm=F), .SDcols = c('value_a','value_b'), .(id)]

print(dtable)

   id time value_a value_b
1:  1    1      NA      No
2:  1    2     Yes     Yes
3:  1    3     Yes     Yes
4:  2    2      No      NA
5:  2    3      No      NA
6:  2    4     Yes      NA

mr.bjerre · Answer 2 · 2018-06-04T12:17:48.697

Inspired by @chinsoon12 I've come up with the following solution

cols <- c("value_a", "value_b")
dtable[, (cols) := lapply(.SD, function(x) {
  if (.N > 1) {
    na_idx = which(is.na(x))
    value_idx = which(!is.na(x))

    # determine if there are any non NA values
    if (length(value_idx) > 0){

      # update all NAs observed after an actual observed observation
      if (length(na_idx[na_idx > min(value_idx)]) > 0)
        na_idx[na_idx > min(value_idx)] <- sapply(na_idx[na_idx > min(value_idx)], function(i) max(value_idx[value_idx < i]))

      # build new index array to use for return
      replace_with_idx <- c(na_idx, value_idx)
      return(x[replace_with_idx[order(replace_with_idx)]])
    } else {
      NA  # if all NA
    }
  }
  x  # if only one observed value
}), 
by=id, .SDcols=cols]

Expand last observed values within group in data table in R

2 Answers2