CODEWITHSUNDEEP
rrowinterpolationnamissing-data
user3091668
user3091668

Reputation: 2310

Substitute NA values depending of position in dataframe

I would like to substitute the NA values by a previous and posterior rows average values. Moreover, when the first or last lines are NA values I would like just the repeat next and before rows, accordingly. My real data have negative and decimals values.

My input:

1.0   NA    1.0
NA    2.0   2.0
3.0   3.0   NA

My expected output:

1.0   2.0   1.0
2.0   2.0   2.0
3.0   3.0   2.0

Cheers!

Upvotes: 4

Views: 421

Answers (3)

Steffen Moritz
Steffen Moritz

Reputation: 7730

Quite simple to solve:

library(imputeTS)
na.interpolation(x)

That's it already.

Upvotes: 0

flodel
flodel

Reputation: 89057

All vectorized after turning your data into a matrix (which will also make computation faster):

x <- matrix(c(2, NA, 3, NA, 2, 3, 1, 2, NA), 3, 3)

p <- rbind(tail(x, -1), NA) # a matrix of previous value
n <- rbind(NA, head(x, -1)) # a matrix of next value
m <- matrix(rowMeans(cbind(as.vector(p),
                           as.vector(n)), na.rm = TRUE), nrow(x)) # replacements

ifelse(is.na(x), m, x)

Upvotes: 3

shadow
shadow

Reputation: 22293

You could also use the na.approx function from the zoo package. Note that this has a slightly different behavior (than the solution by @flodel) when you have two consecutive NA values. For the first and last row you could then use na.locf. 

y <- na.approx(x)
y[nrow(y), ] <- na.locf(y[(nrow(y)-1):nrow(y), ])[2, ] 
y[1, ] <- na.locf(y[1:2,], fromLast=TRUE)[1, ]

EDIT: @Grothendieck pointed out that this was much too complicated. You can combine the entire code above into one line: 

na.approx(x, rule=2)

Upvotes: 4

Related Questions