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

March 12, 2024

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}