# Fill missing value based on ID

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  ## 3 Answers 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))  • Thanks, I was indeed almost certain that there is a way to do it within the tidyverse, but just didn't find it. :) – reima Mar 21 '19 at 7:31 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.

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")