Fill missing value based on ID

Question

Say that I have running id from 1 to n, and a value column:

set.seed(1)
x <- data.frame(c(1:10),rnorm(10,10, sd = 2.5))
colnames(x) <- c("id", "value")

   id     value
1   1  8.433865
2   2 10.459108
3   3  7.910928
4   4 13.988202
5   5 10.823769
6   6  7.948829
7   7 11.218573
8   8 11.845812
9   9 11.439453
10 10  9.236529

Now let's imagine that I have for some reason lost some of that data, but I nevertheless need to fill it with some value

# Let's lose data
(x <-  x[-5,]) 

Now I am missing observation #5, but I still need to replace it with a value (e.g. 0 or NA). Note that in reality I don't necessary know what observation ID is missing.

This is what I wrote, and it works. However, I am wondering whether there is a vectorized way of doing this (or a more efficient way in general)?

f <- function(x, fill_value){
  # Get number of rows
  n <- nrow(x)
  max_id <- max(x$id)

  # Get missing data position
  no_data_position <-  which(!(1:max_id %in% x$id)) 

  # Fill missing data
  out <- data.frame()
  start <-  0
  counter <- 1

  for(i in 1:max_id){
    if(!i %in% no_data_position){
      out[start + i, "id"] <- start + i
      out[start + i, "value"] <- x$value[counter]     
      counter <- counter + 1 
    } else {
      out[start + i, "id"] <- start + i
      out[start + i, "value"] <- fill_value
    }
  } 
  return(out)
}


f(x, NA)

   id     value
1   1  8.433865
2   2 10.459108
3   3  7.910928
4   4 13.988202
5   5        NA
6   6  7.948829
7   7 11.218573
8   8 11.845812
9   9 11.439453
10 10  9.236529

Answer 1

Your code does work but there are indeed better ways of doing this.

Some details could be improved in your current function:

• You could replace which(!(1:max_id %in% x$id)) with setdiff(seq(min_id, max_id), x$id). It's more legible, and, more importantly, it does not rely on the fact that your IDs are the n first integers. (Consider for instance which(!(2:5 %in% c(2, 3, 5))): it does not return 4.)
• start is assigned to 0 but is never modified, so you could get rid of this local variable.

But the main point is that growing a dataframe in a loop is generally not a good idea, as most of the time you can find better options. Here are two possible solutions:

1) With base R

y <- data.frame(id = seq(min(x$id), max(x$id)))
x <- merge(y, x, all.x = TRUE)
x$value[is.na(x$value)] <- fill_value

2) With tidyr

library(tidyr)

complete(x, id = seq(min(id), max(id)), fill = list(value = fill_value))

Answer 2

First, you can find the missing values using set operations:

no_data_position <- setdiff(c(1:max(x$id)), x$id)

And then just build a dataframe with the missing values and merge it:

out <- merge(x, data.frame(id=no_data_position, value=fill_value), all=TRUE)

And that is literally the full function:

f <- function(x, fill_value) {
    no_data_position <- setdiff(c(1:max(x$id)), x$id)
    merge(x, data.frame(id=no_data_position, value=fill_value), all=TRUE)
}

Note that you don't need the explicit return, a function implicitly returns the last return value (not sure if that is best practice in R, though). You probably also want to give that function a more descriptive name as well.

Answer 3

An alternative: as long as the id column is an integer, I have found the padr package's pad_id function helpful for this:

padr::pad_int(x, "id")