4
\$\begingroup\$

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
\$\endgroup\$
3
\$\begingroup\$

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

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.

\$\endgroup\$
2
\$\begingroup\$

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")
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.