Skip to content
Prev 391165 / 398506 Next

Inserting missing seq number

Bert Gunter 
Several perfectly fine "off the shelf" solutions have been offered for
this post, so what follows here should be considered only for
amusement.

This seemed like a simple problem to me, so I wondered if one could
write simple code in plain old base R to solve it. Of course, one can.
What follow are two such approaches.

First, as several have already noted, one can use the merge() trick to
obtain the full 'seq' vector and the corresponding 'count' vector with
NA's for the missing seq values. Here is a slightly more complicated
example than the OP posted that allows for more than one missing 'seq'
value in a row and starts at something other than 1(one could use diff
if NA's only occur one at a time I think).

dat <- data.frame(seq = c(3,4,6,9,10), count = c(4,7,3,5,2))
seq count
1   3     4
2   4     7
3   6     3
4   9     5
5  10     2

The merge() trick than gives:
dat <- merge(data.frame(
   seq = seq.int(dat[1,'seq'], tail(dat,1)[1,'seq'])),
   dat, all.x = TRUE)
seq count
1   3     4
2   4     7
3   5    NA
4   6     3
5   7    NA
6   8    NA
7   9     5
8  10     2

So focusing on the 'count' vector, one needs a 'fill in' function to
appropriately fill in the missing values. Here are two. fillin1() does
this in the obvious way, moving sequentially from the beginning to the
end, filling in the previous value whenever NA is encountered:

fillin1 <- function(x){
   for(i in seq_along(x))
      if(is.na(x[i]))x[i] <- x[i-1]
   x
}

fillin2() is a bit trickier, working recursively from end to
beginning. Still, it's only a few lines of code, and might be improved
in some way I didn't think of:

fillin2 <- function(x){
   if(length(x) > 1){
      z <- Recall(head(x, -1))
      if(is.na(tail(x,1))){
         x <-c(z,tail(z,1))
      } else x[-length(x)] <- z
   }
   x
}

It might be interesting to compare the performance of all the
suggestions, but I'm too lazy to do that and will compare it only to
Bill's suggestion of approx(). To make the comparison fairer, I'll
remove unnecessary overhead and put 'seq' and 'count' in the global
environment.


seq <- c(3,4,6, 9, 10)
count <- c(4,7,3,5,2)

I'll also use the post merged 'count' from the above merge()
merged_count <- dat$count
[1]  4  7 NA  3 NA NA  5  2

First, check that the fillinx functions actually work:
[1] 4 7 7 3 3 3 5 2
[1] 4 7 7 3 3 3 5 2

## and of course!
$x
[1]  3  4  5  6  7  8  9 10

$y
[1] 4 7 7 3 3 3 5 2

Timing the execution of each 5000 times:
user  system elapsed
  0.062   0.001   0.063
user  system elapsed
  0.008   0.000   0.007
user  system elapsed
  0.222   0.001   0.223

I was not surprised that the recursive solution was the slowest, but
was a little surprised that approx() was considerably slower than the
iterative fillin1() . Of course, one shouldn't invest too much faith
in this little exercise. Results might change drastically for very
long vectors with different proportions/patterns of missings, for
example. And approx is much more general and the above comparison may
be a cheat of sorts anyway since I've omitted the overhead of the
merge(), which I assumed would be small for reasonably sized examples.
That might be wrong.

Anyway, as I said, for amusement only.

Cheers,
Bert


Bert Gunter

"The trouble with having an open mind is that people keep coming along
and sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )
On Wed, Mar 30, 2022 at 8:41 AM Bill Dunlap <williamwdunlap at gmail.com> wrote:

Thread (9 messages)

Jeff Reichman Inserting missing seq number Mar 29 Eric Berger Inserting missing seq number Mar 29 Avi Gross Inserting missing seq number Mar 29 PIKAL Petr Inserting missing seq number Mar 29 Jeff Reichman Inserting missing seq number Mar 30 Bill Dunlap Inserting missing seq number Mar 30 Jim Lemon Inserting missing seq number Mar 30 Bert Gunter Inserting missing seq number Mar 30 Jeff Reichman Inserting missing seq number Mar 30