rdataframetidyrna

How to replace any NAs in dataframe in R by the last 4 values


I have a data frame in R with following structure below. I want to fill in the missing values by carrying forward the most recent non-missing value, but I want to look back 4 rows and use that value to fill the gap. I may not be clear with the question but you may look below in the example of the result.

   maker  num
1  local_1  1
2  local_2  2
3  local_3  5
4  local_4  6
5  local_1  NA
6  local_2  NA
7  local_3  NA
8  local_4  NA
9  local_1  4
10 local_2  7
11 local_3  8
12 local_4  9
13 local_1  NA
14 local_2  NA
15 local_3  NA
16 local_4  NA
17 local_1  NA
18 local_2  NA
19 local_3  NA
20 local_4  NA

to be converted into:

   maker  num
1  local_1  1
2  local_2  2
3  local_3  5
4  local_4  6
5  local_1  1
6  local_2  2
7  local_3  5
8  local_4  6
9  local_1  4
10 local_2  7
11 local_3  8
12 local_4  9
13 local_1  4
14 local_2  7
15 local_3  8
16 local_4  9
17 local_1  4
18 local_2  7
19 local_3  8
20 local_4  9

The number of NA are always divisible by 4, but doesn't have a pattern of number of consecutive NAs.


Solution

  • You could use a for-loop, e.g.

    df <- read.table(text = "maker  num
    local_1  1
    local_2  2
    local_3  5
    local_4  6
    local_1  NA
    local_2  NA
    local_3  NA
    local_4  NA
    local_1  4
    local_2  7
    local_3  8
    local_4  9
    local_1  NA
    local_2  NA
    local_3  NA
    local_4  NA
    local_1  NA
    local_2  NA
    local_3  NA
    local_4  NA", header = TRUE)
    
    for(i in 1:nrow(df)) {
        if (is.na(df$num[i])){
        df$num[i] <- df$num[i - 4]
      }
    }
    df
    #>      maker num
    #> 1  local_1   1
    #> 2  local_2   2
    #> 3  local_3   5
    #> 4  local_4   6
    #> 5  local_1   1
    #> 6  local_2   2
    #> 7  local_3   5
    #> 8  local_4   6
    #> 9  local_1   4
    #> 10 local_2   7
    #> 11 local_3   8
    #> 12 local_4   9
    #> 13 local_1   4
    #> 14 local_2   7
    #> 15 local_3   8
    #> 16 local_4   9
    #> 17 local_1   4
    #> 18 local_2   7
    #> 19 local_3   8
    #> 20 local_4   9
    

    Created on 2024-03-12 with reprex v2.1.0