Interpolate NA values in a data frame with na.approx

by

A small, reproducible example:

library(zoo)
set.seed(1)
m <- matrix(runif(16, 0, 100), nrow = 4)
missing_values <- sample(16, 7)
m[missing_values] <- NA
m
         [,1]     [,2]      [,3]     [,4]
[1,] 26.55087 20.16819 62.911404 68.70228
[2,] 37.21239       NA  6.178627 38.41037
[3,]       NA       NA        NA       NA
[4,] 90.82078 66.07978        NA       NA

na.approx(m)
         [,1]     [,2]      [,3]     [,4]
[1,] 26.55087 20.16819 62.911404 68.70228
[2,] 37.21239 35.47206  6.178627 38.41037
[3,] 64.01658 50.77592        NA       NA
[4,] 90.82078 66.07978        NA       NA

m[4, 4] <- 50
na.approx(m)
         [,1]     [,2]      [,3]     [,4]
[1,] 26.55087 20.16819 62.911404 68.70228
[2,] 37.21239 35.47206  6.178627 38.41037
[3,] 64.01658 50.77592        NA 44.20519
[4,] 90.82078 66.07978        NA 50.00000

Yup, looks like you do need the start/end values of columns to be known or the interpolation doesn’t work. Can you guess values for your boundaries?

ANOTHER EDIT: So by default, you need the start and end values of columns to be known. However it is possible to get na.approx to always fill in the blanks by passing rule = 2. See Felix’s answer. You can also use na.fill to provide a default value, as per Gabor’s comment. Finally, you can interpolate boundary conditions in two directions (see below) or guess boundary conditions.

EDIT: A further thought. Since na.approx is only interpolating in columns, and your data is spacial, perhaps interpolating in rows would be useful too. Then you could take the average.

na.approx fails when whole columns are NA, so we create a bigger dataset.

set.seed(1)
m <- matrix(runif(64, 0, 100), nrow = 8)
missing_values <- sample(64, 15)
m[missing_values] <- NA

Run na.approx both ways.

by_col <- na.approx(m)
by_row <- t(na.approx(t(m)))

Find out the best guess.

default <- 50
best_guess <- ifelse(is.na(by_row), 
  ifelse(
    is.na(by_col), 
    default,              #neither known
    by_col                #only by_col known
  ), 
  ifelse(
    is.na(by_col), 
    by_row,               #only by_row known
    (by_row + by_col) / 2 #both known
  )
)

More Related Contents:

  1. Repeat each row of data.frame the number of times specified in a column
  2. Filter data.frame rows by a logical condition
  3. Split column at delimiter in data frame [duplicate]
  4. Pass a data.frame column name to a function
  5. How to convert a list consisting of vector of different lengths to a usable data frame in R?
  6. Select groups based on number of unique / distinct values
  7. Assign multiple columns using := in data.table, by group
  8. Filtering a data frame on a vector [duplicate]
  9. How to replace NA values in a table for selected columns
  10. Remove an entire column from a data.frame in R
  11. Selecting multiple odd or even columns/rows for dataframe
  12. Sample n random rows per group in a dataframe
  13. Deleting reversed duplicates with R
  14. Select every nth row from dataframe
  15. Remove Rows From Data Frame where a Row matches a String
  16. duplicates in multiple columns
  17. R – add column that counts sequentially within groups but repeats for duplicates
  18. Extend contigency table with proportions (percentages)
  19. Splitting a data.frame by a variable [duplicate]
  20. Check whether values in one data frame column exist in a second data frame
  21. How do you remove columns from a data.frame?
  22. Unlist a data frame by rows, not columns
  23. Deleting columns from a data.frame where NA is more than 15% of the column length [duplicate]
  24. Using lapply to apply a function over list of data frames and saving output to files with different names
  25. DT[!(x == .)] and DT[x != .] treat NA in x inconsistently
  26. Return df with a columns values that occur more than once [duplicate]
  27. Row-wise sum of values grouped by columns with same name
  28. Replicate each row of data.frame and specify the number of replications for each row?
  29. R how can I calculate difference between rows in a data frame
  30. Color one point and add an annotation in ggplot2?

Leave a Comment