How to replace NAs with the average of the nearest two values

user_12021156 0
Viewed 53

I have this data:

data <- structure(list(A = c(10L, 25L, NA, 17L, 8L, 3L), B = c(2L, NA, 
NA, NA, NA, 2L), C = c(NA, 4L, 6L, 4L, 3L, NA)), class = "data.frame", row.names = c(NA, 
-6L))

I want to replace NAs at each column with the average of the nearest two available values at that column. For the result should be like below:

data <- structure(list(A = c(10L, 25L, 21L, 17L, 8L, 3L), B = c(2L, 2L, 
2L, 2L, 2L, 2L), C = c(4L, 4L, 6L, 4L, 3L, 3L)), class = "data.frame", row.names = c(NA, 
-6L))
na r replace
2 Answers

We can use na.approx

library(zoo)
data[] <- lapply(data,  function(x) na.locf0(na.approx(x, na.rm = FALSE)))

Or with tidyverse

library(dplyr)
library(tidyr)
data %>%
  mutate_all(na.approx, na.rm = FALSE) %>%
  fill(everything(), .direction = 'updown')
#  A B C
#1 10 2 4
#2 25 2 4
#3 21 2 6
#4 17 2 4
#5  8 2 3
#6  3 2 3

data

data <- structure(list(A = c(10L, 25L, NA, 17L, 8L, 3L), 
   B = c(2L, NA, NA, NA, NA, 2L), 
   C = c(NA, 4L, 6L, 4L, 3L, NA)), class = "data.frame", row.names = c(NA, -6L))
user_37322710

Here is a base R solution using approx with option rule=2

data[] <- Map(function(x) approx(seq_along(x)[!is.na(x)],
                                 na.omit(x),
                                 seq_along(x),
                                 rule = 2)$y, 
              data)

such that

> data
   A B C
1 10 2 4
2 25 2 4
3 21 2 6
4 17 2 4
5  8 2 3
6  3 2 3
user_121587570
Related