Recalculate grid: compare a center cell with its 8 surrounding cells and write out the value

I have a spatial dataframe with three columns, lat, lon and X. X has a set of values from 1-25 which I would like to compare with its immediate surrounding grids.

The lat, lon columns are filled with coordinates as decimals, such as:

24 24 ,
24 24.1 ,
24 24.2 ,

The third column is the value of X.

I would like to compare the value of i.e. X at 24,24 with X at 23.9,24.1 and 23.9,24 and 23.9,23.9 etc (all 8 grids surrounding the one grid)

and for all grids where X value is greater, would like to start building an indice from 1 until 8. (so if x is larger than a neighbouring grid 1, if greater than another neighbouring grid 2, etc.) for all those that are smaller I want to skip to the next grid until all 8 grids are checked against the center grid.

And I would like to loop this to check across a landscape that contains ~300,000 grids.

I cannot figure out the code in R, and if anyone of you have tried a similar thing, or would love to take a stab at it I would very much appreciate the help!

I wrote a for loop with embedded if conditional:

   lat <- seq(38, 39.5, by = 0.1)
    
    lon <- seq(30, 40, by = 0.1)
    
    #create empty df
    Per_yr_beta <- data.frame(matrix(ncol = 4, nrow = 22779))
    
    #provide column names
    colnames(Per_yr_beta) <- c('Lon', 'Lat', 'Total', 'Beta')
    
    
  for (i in lat) {
  
  if(is.na(i+0.1)==F) {ymax1 = (i+0.1)} else {next}

  if(is.na(i-0.1)==F) {ymin1 = (i-0.1)} else {next}
  
  sq_grid <- Per_yr_dummy %>% 
    filter (Lat <= ymax1 & Lat >= ymin1)
  
  for (j in lon) {
  
    if(is.na(j+0.1)==F) {xmax1 = (i+0.1)} else {next}
    
    if(is.na(j-0.1)==F) {xmin1 = (i-0.1)} else {next}  
    
 
  sq_grid <- sq_grid %>% filter (Lon <= xmax1 & Lon >= xmin1);
    
    x=0;

    if(sq_grid$Total[5] > sq_grid$Total[1]){x=x+1}
    if(sq_grid$Total[5] > sq_grid$Total[2]){x=x+1} 
    if(sq_grid$Total[5] > sq_grid$Total[3]){x=x+1} 
    if(sq_grid$Total[5] > sq_grid$Total[4]){x=x+1}
    if(sq_grid$Total[5] > sq_grid$Total[6]){x=x+1}
    if(sq_grid$Total[5] > sq_grid$Total[7]){x=x+1}
    if(sq_grid$Total[5] > sq_grid$Total[8]){x=x+1}
    if(sq_grid$Total[5] > sq_grid$Total[9]){x=x+1}

    sq_grid$Beta[5] <- x           
    sq_grid_v <- as.data.frame(sq_grid[5, 1:4])
 
    z=1;
    Per_yr_beta[z,] <- sq_grid_v
    z=z+1
    
}}

Here I'm stuck. The loop runs for the first row, but then nothing...

If I understood correctly, you want each new cell to carry the count of adjacent cells with lower values, like this:

You can do so by converting your dataframe to a raster and applying a moving window.

Let's take an easily reproducible 4 x 4 grid (16 cells), specified as a dataframe d:

    d <- data.frame(expand.grid(lon = 1:4, lat = 1:4),
                    X = as.integer(1:16)
                    )
    
    ## > head(d)
    ##   lon lat X
    ## 1   1   1 1
    ## 2   2   1 2
    ## 3   3   1 3
    ## 4   4   1 4
    ## 5   1   2 5
    ## ... 11 more rows

make it a spatial raster:

    library(terra)
    
    r <- rast(d)
    
    plot(r)
    text(r, labels = values(r))

use a moving window of edge length w = 3 (3 x 3 cells) which contains the central cell and its adjacent cells (neighbours):

    w_vals = focalValues(r, w = 3,
                         fill = Inf ## (1)
                         )

(1) use Infinite vor ficticious neighbours outside the grid, so they won't contribute to the count of cells with lower values

... this results in a 16 x 9 matrix, where each row corresponds to a cell of your initial raster, containing 9 values (central cell and neighbours), the focal central cell in column 5:

Now, count rowwise the sum of values smaller than the focal cell (column 5). These are the values for the recalculated, resulting raster:

new_vals <- apply(w_vals, 1, \(the_row) sum(the_row[5] > the_row[-5]))

create new raster and check:

    r_new <- setValues(r, new_vals)
    
    plot(r_new)
    text(r_new, labels = values(r_new))