rolling percentile rank of values from column B compared to column C

Question

I'm trying to calculate the percentile rank for each row value in column #2 compared to the 4 lagged values in column #3.

I've tried using zoo::rollapply and slider::slide_dbl but I can't get the offsets to work.

My data looks as follows:

df <- data.frame(
  type = rep(c("A","B"),times = 10),
  values = c(16,16,16,16,16,17,18,18,20,20,45,44,45,44,45,47,45,46,47,50),
  points = c(20,14,34,44,40,50,44,34,43,23,60,61,62,63,64,65,66,67,68,69)
)

I wish to mutate a new column called percentile_rank by comparing each individual 'point' value compared to the lagged 4 values in the 'values' column.

I wish to roll/slide across each points value and calculate it's percentile rank compared to the same row and the preceding 3 rows (4 total) from the values column.

Any pointers on how this can be achieved would be greatly appreciated.

Answer 1

Try this:

library(zoo)

calculate_percentile_rank <- function(x) {
  rank_value <- rank(x[length(x)], x[-length(x)], ties.method = "min")
  percentile_rank <- (rank_value - 1) / (length(x) - 1)
  return(percentile_rank)
}

df$percentile_rank <- rollapplyr(df$values, width = 5, FUN = calculate_percentile_rank, fill = NA)