Filling rows with previous row's value for subsequent NA values in R

Question

I have multiple missing values in subsequent rows in a data frame. I want to replace the first missing value with the non-missing value in its previous row multiplied by 3 and then fill in the next NA values using the new-filled values in their previous rows multiplied by 3. Here is an example of data and codes:

df <- mtcars
df[c(2,3,4,5),1] <-NA

IND <- is.na(df[,1])
df[IND,1] <- df[dplyr::lead(IND,1L, F),1] * 3

The last line of the above codes does the job but row by row (I should run it 4 times to fill in the 4 missing rows). How can I do it once for all four missing rows?

I want:

df[c(2,3,4,5),1] <- c(63,189,567,1701)

using one loop instead of using the above code four times.

few hours ago you asked a very [similar question](https://stackoverflow.com/q/52520969/1315767) show us what have you tried to solve your problem. — Jilber Urbina, Sep 26 '18 at 23:00
It is not the same question! I want to do it in a loop over the rows as I explained in the post! If there is a way other than a loop that would be even better. — Hossein, Sep 26 '18 at 23:09
I said similar not same. You should be a bit nicer since you're asking for help. It would be helpful for you to [read this](https://stackoverflow.com/help/how-to-ask) — Jilber Urbina, Sep 26 '18 at 23:16

G. Grothendieck · Accepted Answer · 2018-09-26T23:26:28.000

0

Try this base solution:

Fill <- function(x) rep(x[1], length(x)) * 3 ^ (seq_along(x) - 1)
ave(df[, 1], cumsum(!is.na(df[, 1])), FUN = Fill)

giving:

 [1]   21.0   63.0  189.0  567.0 1701.0   18.1   14.3   24.4   22.8   19.2
[11]   17.8   16.4   17.3   15.2   10.4   10.4   14.7   32.4   30.4   33.9
[21]   21.5   15.5   15.2   13.3   19.2   27.3   26.0   30.4   15.8   19.7
[31]   15.0   21.4

Alternately use this version of Fill:

Fill <- function(x) cumprod(replace(x, is.na(x), 3))

edited Sep 26 '18 at 23:26

answered Sep 26 '18 at 22:59

G. Grothendieck

254,981
17
203
341

It is not only filling NA values. The problem is I want to fill each NA using the previous NA value which is replaced by the previous non.NA values. It is mainly a loop over rows and I want to incorporate the above code in the post into a loop. – Hossein Sep 26 '18 at 23:07
No, I want the remaining NAs to be the previous ones multiplied by 3. I have just added the data that I want to the post. – Hossein Sep 26 '18 at 23:18
Have revised solution. – G. Grothendieck Sep 26 '18 at 23:26
Thanks G. Grothendieck! – Hossein Sep 26 '18 at 23:38

score 0 · Answer 2 · answered Sep 26 '18 at 23:06

0

Here is a for loop that should do the trick:

for(i in 1:nrow(df)){
  if(!is.na(df[i,1])){
    xx <- df[i,1] 
  }
   if(is.na(df[i,1]))
    df[i,1] <-xx
  }
}
 head(df)

There are probably more elegant solutions out there though!

answered Sep 26 '18 at 23:06

André.B

617
8
17

Thanks Andre! I am working with this loop, but I do not know how to incorporate a command like `IND <- is.na(df[i,1]) df[IND,1] <- df[dplyr::lead(IND,1L, F),1] * 3` into a loop. – Hossein Sep 26 '18 at 23:27
No worries mate, I saw your comment and have been trying to work out a solution but I wasn't able to figure it out before G. Grothendieck! It looks like they solved it, no? – André.B Sep 26 '18 at 23:33

Filling rows with previous row's value for subsequent NA values in R

2 Answers2