R - mutate getting the first smaller value from the top rows

Question

So, the problem is, I'm trying to get the first smaller value within the top rows of the transformed variable. My df looks something like:

count
24
33
33
34
35
33
34
35
...

It only contains a column. The output that I'm looking for:

count	close_prev
24	NA
33	24
33	24
34	33
35	34
33	24
33	24
34	33
35	34

So, I'm looking for the first smaller number from the top rows.

The code I have so far:

table %>%
   mutate(close_prev = map_dbl(row_number(), ~closest(count[seq_len(max(.x - 1, 1))], count[.x])))

It's not working :c Can someone help me? Thank you in advance

Answer 1

You can match() the value against itself and then index the result against count padded with an NA.

library(dplyr)

dat %>%
  mutate(x = c(NA, count)[match(count, count)])

# A tibble: 9 × 2
  count close_prev
  <dbl>      <dbl>
1    24         NA
2    33         24
3    33         24
4    34         33
5    35         34
6    33         24
7    33         24
8    34         33
9    35         34

Answer 2

You are looking for a rolling join. This is available in dplyr 1.1.0 and above:

left_join(a, a, join_by(closest(count > count)), multiple = "first")

  count.x count.y
1      24      NA
2      33      24
3      33      24
4      34      33
5      35      34
6      33      24
7      33      24
8      34      33
9      35      34

---

In data.table, you can use roll = "nearest":

library(data.table)
setDT(a)
a[a, on = "count", roll = "nearest", mult = "first"][]