By Ryogi


2011-10-12 05:27:21 8 Comments

In a data.frame (or data.table), I would like to "fill forward" NAs with the closest previous non-NA value. A simple example, using vectors (instead of a data.frame) is the following:

> y <- c(NA, 2, 2, NA, NA, 3, NA, 4, NA, NA)

I would like a function fill.NAs() that allows me to construct yy such that:

> yy
[1] NA NA NA  2  2  2  2  3  3  3  4  4

I need to repeat this operation for many (total ~1 Tb) small sized data.frames (~30-50 Mb), where a row is NA is all its entries are. What is a good way to approach the problem?

The ugly solution I cooked up uses this function:

last <- function (x){
    x[length(x)]
}    

fill.NAs <- function(isNA){
if (isNA[1] == 1) {
    isNA[1:max({which(isNA==0)[1]-1},1)] <- 0 # first is NAs 
                                              # can't be forward filled
}
isNA.neg <- isNA.pos <- isNA.diff <- diff(isNA)
isNA.pos[isNA.diff < 0] <- 0
isNA.neg[isNA.diff > 0] <- 0
which.isNA.neg <- which(as.logical(isNA.neg))
if (length(which.isNA.neg)==0) return(NULL) # generates warnings later, but works
which.isNA.pos <- which(as.logical(isNA.pos))
which.isNA <- which(as.logical(isNA))
if (length(which.isNA.neg)==length(which.isNA.pos)){
    replacement <- rep(which.isNA.pos[2:length(which.isNA.neg)], 
                                which.isNA.neg[2:max(length(which.isNA.neg)-1,2)] - 
                                which.isNA.pos[1:max(length(which.isNA.neg)-1,1)])      
    replacement <- c(replacement, rep(last(which.isNA.pos), last(which.isNA) - last(which.isNA.pos)))
} else {
    replacement <- rep(which.isNA.pos[1:length(which.isNA.neg)], which.isNA.neg - which.isNA.pos[1:length(which.isNA.neg)])     
    replacement <- c(replacement, rep(last(which.isNA.pos), last(which.isNA) - last(which.isNA.pos)))
}
replacement
}

The function fill.NAs is used as follows:

y <- c(NA, 2, 2, NA, NA, 3, NA, 4, NA, NA)
isNA <- as.numeric(is.na(y))
replacement <- fill.NAs(isNA)
if (length(replacement)){
which.isNA <- which(as.logical(isNA))
to.replace <- which.isNA[which(isNA==0)[1]:length(which.isNA)]
y[to.replace] <- y[replacement]
} 

Output

> y
[1] NA  2  2  2  2  3  3  3  4  4  4

... which seems to work. But, man, is it ugly! Any suggestions?

14 comments

@d.b 2019-03-17 05:49:38

Using ave, it should be possible to split the vector into different groups with cumsum(!is.na(x)). Each group will be made of preceding non-NA values and following consecutive NA values. Then, for each group, we can take the last non-NA value.

If there is a leading NA, that can be dealt with pmax

fill = function(x){
    ave(x, cumsum(!is.na(x)), FUN = function(y) y[pmax(1, cumsum(!is.na(y)))])
}
fill(y)
# [1] NA  2  2  2  2  3  3  4  4  4

@stats0007 2016-11-11 05:51:43

There are a bunch of packages offering na.locf (NA Last Observation Carried Forward) functions:

  • xts - xts::na.locf
  • zoo - zoo::na.locf
  • imputeTS - imputeTS::na.locf
  • spacetime - spacetime::na.locf

And also other packages where this function is named differently.

@Montgomery Clift 2018-03-22 00:16:02

Here is a modification of @AdamO's solution. This one runs faster, because it bypasses the na.omit function. This will overwrite the NA values in vector y (except for leading NAs).

   z  <- !is.na(y)                  # indicates the positions of y whose values we do not want to overwrite
   z  <- z | !cumsum(z)             # for leading NA's in y, z will be TRUE, otherwise it will be FALSE where y has a NA and TRUE where y does not have a NA
   y  <- y[z][cumsum(z)]

@AdamO 2018-01-29 23:00:56

Having a leading NA is a bit of a wrinkle, but I find a very readable (and vectorized) way of doing LOCF when the leading term is not missing is:

na.omit(y)[cumsum(!is.na(y))]

A slightly less readable modification works in general:

c(NA, na.omit(y))[cumsum(!is.na(y))+1]

gives the desired output:

c(NA, 2, 2, 2, 2, 3, 3, 4, 4, 4)

@ABT 2018-02-05 07:31:57

this is rather elegant. Not sure if it works in all cases but it sure worked for me!

@dmca 2017-01-13 19:16:30

This worked for me, although I'm not sure whether it is more efficient than other suggestions.

rollForward <- function(x){
  curr <- 0
  for (i in 1:length(x)){
    if (is.na(x[i])){
      x[i] <- curr
    }
    else{
      curr <- x[i]
    }
  }
  return(x)
}

@Tony DiFranco 2017-08-09 16:02:39

a data.table solution:

> dt <- data.table(y = c(NA, 2, 2, NA, NA, 3, NA, 4, NA, NA))
> dt[, y_forward_fill := y[1], .(cumsum(!is.na(y)))]
> dt
     y y_forward_fill
 1: NA             NA
 2:  2              2
 3:  2              2
 4: NA              2
 5: NA              2
 6:  3              3
 7: NA              3
 8:  4              4
 9: NA              4
10: NA              4

this approach could work with forward filling zeros as well:

> dt <- data.table(y = c(0, 2, -2, 0, 0, 3, 0, -4, 0, 0))
> dt[, y_forward_fill := y[1], .(cumsum(y != 0))]
> dt
     y y_forward_fill
 1:  0              0
 2:  2              2
 3: -2             -2
 4:  0             -2
 5:  0             -2
 6:  3              3
 7:  0              3
 8: -4             -4
 9:  0             -4
10:  0             -4

this method becomes very useful on data at scale and where you would want to perform a forward fill by group(s), which is trivial with data.table. just add the group(s) to the by clause prior to the cumsum logic.

@Evan Cortens 2017-02-17 22:08:32

Following up on Brandon Bertelsen's Rcpp contributions. For me, the NumericVector version didn't work: it only replaced the first NA. This is because the ina vector is only evaluated once, at the beginning of the function.

Instead, one can take the exact same approach as for the IntegerVector function. The following worked for me:

library(Rcpp)
cppFunction('NumericVector na_locf_numeric(NumericVector x) {
  R_xlen_t n = x.size();
  for(R_xlen_t i = 0; i<n; i++) {
    if(i > 0 && !R_finite(x[i]) && R_finite(x[i-1])) {
      x[i] = x[i-1];
    }
  }
  return x;
}')

In case you need a CharacterVector version, the same basic approach also works:

cppFunction('CharacterVector na_locf_character(CharacterVector x) {
  R_xlen_t n = x.size();
  for(R_xlen_t i = 0; i<n; i++) {
    if(i > 0 && x[i] == NA_STRING && x[i-1] != NA_STRING) {
      x[i] = x[i-1];
    }
  }
  return x;
}')

@stats0007 2017-03-18 21:56:31

int n = x.size() and for(int i = 0; i<n; i++) should be replaced by double. In R an vector can be larger than c++ int size.

@Evan Cortens 2017-03-19 22:38:30

It looks like this function returns "R_xlen_t". If R is compiled with long vector support, this is defined as ptrdiff_t; if it isn't, it's an int. Thanks for the correction!

@Ruben 2012-12-10 22:45:39

Sorry for digging up an old question. I couldn't look up the function to do this job on the train, so I wrote one myself.

I was proud to find out that it's a tiny bit faster.
It's less flexible though.

But it plays nice with ave, which is what I needed.

repeat.before = function(x) {   # repeats the last non NA value. Keeps leading NA
    ind = which(!is.na(x))      # get positions of nonmissing values
    if(is.na(x[1]))             # if it begins with a missing, add the 
          ind = c(1,ind)        # first position to the indices
    rep(x[ind], times = diff(   # repeat the values at these indices
       c(ind, length(x) + 1) )) # diffing the indices + length yields how often 
}                               # they need to be repeated

x = c(NA,NA,'a',NA,NA,NA,NA,NA,NA,NA,NA,'b','c','d',NA,NA,NA,NA,NA,'e')  
xx = rep(x, 1000000)  
system.time({ yzoo = na.locf(xx,na.rm=F)})  
## user  system elapsed   
## 2.754   0.667   3.406   
system.time({ yrep = repeat.before(xx)})  
## user  system elapsed   
## 0.597   0.199   0.793   

Edit

As this became my most upvoted answer, I was reminded often that I don't use my own function, because I often need zoo's maxgap argument. Because zoo has some weird problems in edge cases when I use dplyr + dates that I couldn't debug, I came back to this today to improve my old function.

I benchmarked my improved function and all the other entries here. For the basic set of features, tidyr::fill is fastest while also not failing the edge cases. The Rcpp entry by @BrandonBertelsen is faster still, but it's inflexible regarding the input's type (he tested edge cases incorrectly due to a misunderstanding of all.equal).

If you need maxgap, my function below is faster than zoo (and doesn't have the weird problems with dates).

I put up the documentation of my tests.

new function

repeat_last = function(x, forward = TRUE, maxgap = Inf, na.rm = FALSE) {
    if (!forward) x = rev(x)           # reverse x twice if carrying backward
    ind = which(!is.na(x))             # get positions of nonmissing values
    if (is.na(x[1]) && !na.rm)         # if it begins with NA
        ind = c(1,ind)                 # add first pos
    rep_times = diff(                  # diffing the indices + length yields how often
        c(ind, length(x) + 1) )          # they need to be repeated
    if (maxgap < Inf) {
        exceed = rep_times - 1 > maxgap  # exceeding maxgap
        if (any(exceed)) {               # any exceed?
            ind = sort(c(ind[exceed] + 1, ind))      # add NA in gaps
            rep_times = diff(c(ind, length(x) + 1) ) # diff again
        }
    }
    x = rep(x[ind], times = rep_times) # repeat the values at these indices
    if (!forward) x = rev(x)           # second reversion
    x
}

I've also put the function in my formr package (Github only).

@Rhubarb 2014-10-06 16:11:16

+1, but I am guessing this needs to be looped per column if you want to apply this to a df with multiple columns?

@Achim Zeileis 2017-03-02 15:28:44

@Ruben Thanks again for your report. By now the bug is fixed on R-Forge. Also I have tweaked and exported the workhorse function na.locf0 which is now similar in scope and performance to your repeat_last function. The clue was to use diff rather than cumsum and avoid ifelse. The main na.locf.default function is still somewhat slower because it does some more checks and handles multiple columns etc.

@Brandon Bertelsen 2016-09-03 08:38:38

Throwing my hat in:

library(Rcpp)
cppFunction('IntegerVector na_locf(IntegerVector x) {
  int n = x.size();

  for(int i = 0; i<n; i++) {
    if((i > 0) && (x[i] == NA_INTEGER) & (x[i-1] != NA_INTEGER)) {
      x[i] = x[i-1];
    }
  }
  return x;
}')

Setup a basic sample and a benchmark:

x <- sample(c(1,2,3,4,NA))

bench_em <- function(x,count = 10) {
  x <- sample(x,count,replace = TRUE)
  print(microbenchmark(
    na_locf(x),
    replace_na_with_last(x),
    na.lomf(x),
    na.locf(x),
    repeat.before(x)
  ), order = "mean", digits = 1)
}

And run some benchmarks:

bench_em(x,1e6)

Unit: microseconds
                    expr   min    lq  mean median    uq   max neval
              na_locf(x)   697   798   821    814   821 1e+03   100
              na.lomf(x)  3511  4137  5002   4214  4330 1e+04   100
 replace_na_with_last(x)  4482  5224  6473   5342  5801 2e+04   100
        repeat.before(x)  4793  5044  6622   5097  5520 1e+04   100
              na.locf(x) 12017 12658 17076  13545 19193 2e+05   100

Just in case:

all.equal(
     na_locf(x),
     replace_na_with_last(x),
     na.lomf(x),
     na.locf(x),
     repeat.before(x)
)
[1] TRUE

Update

For a numeric vector, the function is a bit different:

NumericVector na_locf_numeric(NumericVector x) {
  int n = x.size();
  LogicalVector ina = is_na(x);

  for(int i = 1; i<n; i++) {
    if((ina[i] == TRUE) & (ina[i-1] != TRUE)) {
      x[i] = x[i-1];
    }
  }
  return x;
}

@Abhishek Lahiri 2016-10-27 16:03:10

I tried the below:

nullIdx <- as.array(which(is.na(masterData$RequiredColumn)))
masterData$RequiredColumn[nullIdx] = masterData$RequiredColumn[nullIdx-1]

nullIdx gets the idx number where ever masterData$RequiredColumn has a Null/ NA value. In the next line we replace it with the corresponding Idx-1 value, i.e. the last good value before each NULL/ NA

@Gregor 2017-03-19 23:12:17

This doesn't work if there are multiple consecutive missing values - 1 NA NA turns into 1 1 NA. Also, I think the as.array() is unnecessary.

@Nick Nassuphis 2016-05-25 21:02:27

This has worked for me:

  replace_na_with_last<-function(x,a=!is.na(x)){
     x[which(a)[c(1,1:sum(a))][cumsum(a)+1]]
  }


> replace_na_with_last(c(1,NA,NA,NA,3,4,5,NA,5,5,5,NA,NA,NA))

[1] 1 1 1 1 3 4 5 5 5 5 5 5 5 5

> replace_na_with_last(c(NA,"aa",NA,"ccc",NA))

[1] "aa"  "aa"  "aa"  "ccc" "ccc"

speed is reasonable too:

> system.time(replace_na_with_last(sample(c(1,2,3,NA),1e6,replace=TRUE)))


 user  system elapsed 

 0.072   0.000   0.071 

@Ruben 2017-01-12 18:32:21

This function doesn't do what you expect when there are leading NAs. replace_na_with_last(c(NA,1:4,NA)) (i.e. they're filled with the following value). This is also the default behaviour of imputeTS::na.locf(x, na.remaining = "rev").

@Eldar Agalarov 2014-05-11 12:01:31

Try this function. It does not require the ZOO package:

# last observation moved forward
# replaces all NA values with last non-NA values
na.lomf <- function(x) {

    na.lomf.0 <- function(x) {
        non.na.idx <- which(!is.na(x))
        if (is.na(x[1L])) {
            non.na.idx <- c(1L, non.na.idx)
        }
        rep.int(x[non.na.idx], diff(c(non.na.idx, length(x) + 1L)))
    }

    dim.len <- length(dim(x))

    if (dim.len == 0L) {
        na.lomf.0(x)
    } else {
        apply(x, dim.len, na.lomf.0)
    }
}

Example:

> # vector
> na.lomf(c(1, NA,2, NA, NA))
[1] 1 1 2 2 2
> 
> # matrix
> na.lomf(matrix(c(1, NA, NA, 2, NA, NA), ncol = 2))
     [,1] [,2]
[1,]    1    2
[2,]    1    2
[3,]    1    2

@Artem Klevtsov 2018-05-27 05:15:39

To improve it you can add this: if (!anyNA(x)) return(x).

@Michele Usuelli 2015-03-03 22:07:24

Dealing with a big data volume, in order to be more efficient, we can use the data.table package.

require(data.table)
replaceNaWithLatest <- function(
  dfIn,
  nameColNa = names(dfIn)[1]
){
  dtTest <- data.table(dfIn)
  setnames(dtTest, nameColNa, "colNa")
  dtTest[, segment := cumsum(!is.na(colNa))]
  dtTest[, colNa := colNa[1], by = "segment"]
  dtTest[, segment := NULL]
  setnames(dtTest, "colNa", nameColNa)
  return(dtTest)
}

@xclotet 2017-01-10 17:28:15

An lapply can be added so it can directly apply it to multiple NA columns: replaceNaWithLatest <- function( dfIn, nameColsNa = names(dfIn)[1] ){ dtTest <- data.table(dfIn) invisible(lapply(nameColsNa, function(nameColNa){ setnames(dtTest, nameColNa, "colNa") dtTest[, segment := cumsum(!is.na(colNa))] dtTest[, colNa := colNa[1], by = "segment"] dtTest[, segment := NULL] setnames(dtTest, "colNa", nameColNa) })) return(dtTest) }

@Hack-R 2018-06-10 20:10:06

At first I was excited by this solution, but it's actually not doing the same thing at all. The question is about filling in 1 data set with another. This answer is just imputation.

@Dirk Eddelbuettel 2011-10-12 05:32:08

You probably want to use the na.locf() function from the zoo package to carry the last observation forward to replace your NA values.

Here is the beginning of its usage example from the help page:

> example(na.locf)

na.lcf> az <- zoo(1:6)

na.lcf> bz <- zoo(c(2,NA,1,4,5,2))

na.lcf> na.locf(bz)
1 2 3 4 5 6 
2 2 1 4 5 2 

na.lcf> na.locf(bz, fromLast = TRUE)
1 2 3 4 5 6 
2 1 1 4 5 2 

na.lcf> cz <- zoo(c(NA,9,3,2,3,2))

na.lcf> na.locf(cz)
2 3 4 5 6 
9 3 2 3 2 

@G. Grothendieck 2016-11-11 13:37:54

Also note that na.locf in zoo works with ordinary vectors as well as zoo objects. Its na.rm argument can be useful in some applications.

@BallpointBen 2018-05-17 16:21:34

Use na.locf(cz, na.rm=FALSE) to keep leading NA.

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] Remove rows with all or some NAs (missing values) in data.frame

16 Answered Questions

[SOLVED] How do I replace NA values with zeros in an R dataframe?

  • 2011-11-17 03:45:44
  • Renato Dinhani
  • 915356 View
  • 589 Score
  • 16 Answer
  • Tags:   r dataframe na

14 Answered Questions

[SOLVED] Counting the number of elements with the values of x in a vector

  • 2009-12-17 17:21:36
  • RQuestions
  • 899175 View
  • 346 Score
  • 14 Answer
  • Tags:   r vector count r-faq

0 Answered Questions

Data.table single column exclusion

2 Answered Questions

[SOLVED] Replace missing value with previous value

  • 2013-02-01 21:25:18
  • user2033963
  • 7709 View
  • 8 Score
  • 2 Answer
  • Tags:   r zoo

2 Answered Questions

[SOLVED] Error in if/while (condition) {: missing Value where TRUE/FALSE needed

  • 2011-09-08 22:17:56
  • Dombey
  • 327922 View
  • 125 Score
  • 2 Answer
  • Tags:   r r-faq

1 Answered Questions

Replace NA values with latest non-NA, AND recall original non-NA values?

  • 2017-01-14 00:02:58
  • Abby
  • 253 View
  • 0 Score
  • 1 Answer
  • Tags:   r dataframe zoo

2 Answered Questions

2 Answered Questions

3 Answered Questions

[SOLVED] Comparing elements within a list

  • 2015-07-12 16:07:25
  • user1945827
  • 98 View
  • 2 Score
  • 3 Answer
  • Tags:   r dynamic time

Sponsored Content