Fill rows upwards +1 and downwards -1 by group

user_9455800 0
Viewed 29

Following example data, two groups 1 and 2:

id <- c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2)
var <- c(NA, NA, 0, NA, NA, NA, NA, NA, 20, NA, NA)
df <- data.frame(id, var)

> df
   id var
1   1  NA
2   1  NA
3   1   0
4   1  NA
5   1  NA
6   2  NA
7   2  NA
8   2  NA
9   2  20
10  2  NA
11  2  NA

How do i fill NA's upwards and downwards by group step by step with +1 and -1 for variable "var"? Creating a new variable "sol", the result should be:

   id var sol
1   1  NA  -1
2   1  NA  -2
3   1   0   0
4   1  NA   1
5   1  NA   2
6   2  NA  17
7   2  NA  18
8   2  NA  19
9   2  20  20
10  2  NA  21
11  2  NA  22

Note: The values in the variable "var" can vary, they are not for every group the same.

I am grateful for any help. Especially with dplyr.

dplyr r
1 Answers

Using max + which.max + row_number():

df %>% 
  group_by(id) %>% 
  mutate(sol = max(var, na.rm = T) - which.max(var) + row_number())

output

      id   var   sol
 1     1    NA    -2
 2     1    NA    -1
 3     1     0     0
 4     1    NA     1
 5     1    NA     2
 6     2    NA    17
 7     2    NA    18
 8     2    NA    19
 9     2    20    20
10     2    NA    21
11     2    NA    22
user_134606020
Related