How to find the closest value from above the current row that meets a certain criteria?

Question

I need to find the id from the closest preceding row that has level equal to one less than the current row's level. Sample data provided below.

id <- c(42,53,82,17,33,92,6,12,66)
level <- c(2,3,3,3,3,4,3,3,4)
df <- tibble(id,level)

     id level
1    42     2
2    53     3
3    82     3
4    17     3
5    33     3
6    92     4
7     6     3
8    12     3
9    66     4

The expected results are as follows:

     id level results
1    42     2      NA
2    53     3      42
3    82     3      42
4    17     3      42
5    33     3      42
6    92     4      33
7     6     3      42
8    12     3      42
9    66     4      12

I was looking at: Find closest value above current row that meets condition and apply function for each group.

I could not edit the answers from the above question to get the desired result. Specifically I am having difficulty referencing the level from the current row to compare to preceding rows.

Answer 1

A solution using a rolling join:

library(dplyr)

df <- df |> mutate(rn = row_number())

df |> 
  left_join(df, by = join_by(closest(level > level), closest(rn > rn))) |> 
  select("id" = id.x, "level" = level.x, "results" = id.y)

# A tibble: 9 × 3
     id level results
  <dbl> <dbl>   <dbl>
1    42     2      NA
2    53     3      42
3    82     3      42
4    17     3      42
5    33     3      42
6    92     4      33
7     6     3      42
8    12     3      42
9    66     4      12

Answer 2

Here, we ensure that for each row, we find the nearest previous row with a level value one less than the current row's level, and capture the id of that row.

1. Row-wise Iteration with map_dbl(): Using map_dbl(seq_along(level), ~{...}) we check for each row in df to check for previous rows with a level one less.

2. Identification of Matching Previous Rows: With idx <- which(level[1:.x] == (level[.x] - 1)) we search for indexes of all previous rows whose level is exactly one less than that of the current row.

3. Selection of the Closest Matching id: With if(length(idx) == 0) NA_real_ else id[max(idx)] we check if matching rows exist, return the id of the closest preceding matching row. Else return NA.

library(dplyr)
library(purrr)

df %>%
  mutate(result = map_dbl(seq_along(level), ~{
    idx <- which(level[1:.x] == (level[.x] - 1))
    if(length(idx) == 0) NA_real_ else id[max(idx)] 
  }))

# A tibble: 9 × 3
     id level result
  <dbl> <dbl>  <dbl>
1    42     2     NA
2    53     3     42
3    82     3     42
4    17     3     42
5    33     3     42
6    92     4     33
7     6     3     42
8    12     3     42
9    66     4     12