Expand value to sequence

user_3283824 0
Viewed 36

I have such a data frame consisting of different groups of differing lengths. In each group, all but one value are NA but this non-NA value can be at any position. How can I expand the val column to create a sequence of ascending values per group?

dat <- data.frame(var = c(rep("A", 3), rep("B", 4), rep("C",5)),
                  val = c(4, NA, NA, NA, NA, 9, NA, NA, NA, NA, NA, 20))
> dat
   var val
1    A   4
2    A  NA
3    A  NA
4    B  NA
5    B  NA
6    B   9
7    B  NA
8    C  NA
9    C  NA
10   C  NA
11   C  NA
12   C  20

Desired output:

> dat 
   var val
1    A   4
2    A   5
3    A   6
4    B   7
5    B   8
6    B   9
7    B  10
8    C  16
9    C  17
10   C  18
11   C  19
12   C  20
missing-data r
2 Answers

Make a seq_along, subtract the index where the non NA is and add the non NA value.

cbind(dat, fill = ave(dat$val, dat$var, FUN = function(x) {
  i <- which.max(!is.na(x))
  seq_along(x) - i + x[i]
}))
#   var val fill
#1    A   4    4
#2    A  NA    5
#3    A  NA    6
#4    B  NA    7
#5    B  NA    8
#6    B   9    9
#7    B  NA   10
#8    C  NA   16
#9    C  NA   17
#10   C  NA   18
#11   C  NA   19
#12   C  20   20

or

cbind(dat, fill = ave(dat$val, dat$var, FUN = function(x) {
  i <- which.max(!is.na(x))
  seq(x[i] - i + 1, length.out = length(x))
}))
user_104885040

A possible alternative, although I like @GKi's(+1) function.

library(tidyverse)

dat <- data.frame(
  var = c(rep("A", 3), rep("B", 4), rep("C", 5)),
  val = c(4, NA, NA, NA, NA, 9, NA, NA, NA, NA, NA, 20)
)

dat |> 
  group_by(var) |> 
  mutate(fill = val) |> 
  fill(fill, .direction = "downup") |> 
  mutate(
    index = which(!is.na(val)),
    val2 = fill + row_number() - index
  )
#> # A tibble: 12 × 5
#> # Groups:   var [3]
#>    var     val  fill index  val2
#>    <chr> <dbl> <dbl> <int> <dbl>
#>  1 A         4     4     1     4
#>  2 A        NA     4     1     5
#>  3 A        NA     4     1     6
#>  4 B        NA     9     3     7
#>  5 B        NA     9     3     8
#>  6 B         9     9     3     9
#>  7 B        NA     9     3    10
#>  8 C        NA    20     5    16
#>  9 C        NA    20     5    17
#> 10 C        NA    20     5    18
#> 11 C        NA    20     5    19
#> 12 C        20    20     5    20

Created on 2022-06-14 by the reprex package (v2.0.1)

user_73270580
Related