How to find the maximum value for given range in a big data set?

I have a big dataset I named Jon that looks like this:

and i have another file that i named "test" that has the start and end coordinates that I want to look at in the Jon file.

Based on this information, can you help me write a code that will give me the biggest lgmeandiff information for each of the coordinates that lies in the test file for each row.
For example: test[c(3),], the coordinates are between 358-366. SO I want to look at these coordinates values in the Jon file and choose the row that has the biggest lgmeandiff for that range and write that information to an output file for each of coordinates.

for(i in 1:nrow(test)){
  listofcats <- seq.int(test$from[i], test$to[i])
}

tempframe <- c()
outputframe <- as.data.frame(matrix(nrow = 0, ncol = ncol(Jon)))

for(i in 1:nrow(test)){
  for(k in 1:nrow(Jon)){
      tempframe <- seq.int(test$from[i], test$to[i])
      tempMax <- Jon[c(max(abs(Jon$lgmeandiff[k] %in% tempframe))),]
      outputframe <- rbind(tempframe,outputframe)
    }
  }
}

Edit:

Data are too large, so I am including the dput(head()) for each data set:

for Jon dataset;

    structure(list(TTS = c(291L, 322L, 358L, 360L, 361L, 363L), lgmeandiff = c(-922337203685478, 
    -922337203685478, -0.0954, -0.9885, -1.4739, -3.7959), lgtotdiff = c(-922337203685478, 
    -922337203685478, 3.2265, 2.3334, 1.848, -0.4739), meandiff = c(0, 
    0, 0.936, 0.504, 0.36, 0.072), totdiff = c(0, 0, 9.36, 5.04, 
    3.6, 0.72), premean = c(0, 0.720000028610229, 1.15200004577637, 
    1.44000005722046, 1.58400001525879, 1.87200012207031), precnt = c(0, 
    7.20000028610229, 11.5200004577637, 14.4000005722046, 15.840000629425, 
    18.720000743866), sufmean = c(0, 0.720000028610229, 2.08800010681152, 
    1.94400005340576, 1.94400005340576, 1.94400005340576), sufcnt = c(0, 
    7.20000028610229, 20.8800008296967, 19.4400007724762, 19.4400007724762, 
    19.4400007724762)), row.names = c(NA, 6L), class = "data.frame")

for test dataset

structure(list(from = c(291, 322, 358, 371, 392, 395), to = c(291L, 
322L, 366L, 382L, 392L, 401L)), row.names = c(1L, 2L, 3L, 7L, 
10L, 11L), class = "data.frame")

join_by was added in dplyr 1.1.0 to create a join specification. In your case you want to merge if TSS falls between from and to (inclusive):

library(dplyr)

full_join(jon, test, by = join_by(between(TTS, from, to))) |>
  slice_max(lgmeandiff, by = c(from, to)) |>
  select(from, to, lgmeandiff)

Output

  from  to    lgmeandiff
1  291 291 -9.223372e+14
2  322 322 -9.223372e+14
3  358 366 -9.540000e-02
4  371 382            NA
5  392 392            NA
6  395 401            NA

Here is one option to write the output to an Excel file, where each coordinate pair will be a sheet in the workbook:

library(dplyr)

full_join(jon, test, by = join_by(between(TTS, from, to))) |>
  slice_max(lgmeandiff, by = c(from, to)) |>
  mutate(coord = paste(from, "-", to),
         lgmeandiff = lgmeandiff, .keep = "none") |>
  split(~ coord) |>
  writexl::write_xlsx("output.xlsx")

Note: Excel has a limit on the number of sheets. It is 255, so if you have more than 255 unique pairs (which is sounds like you might) then you might consider an alternate output type.

Use seq.int in Map.

with(test, Map(seq.int, from, to)) |>
{\(.) sapply(., \(i) max(jon[jon$TTS %in% i, 'lgmeandiff']))}() |>
setNames(apply(test, 1, toString))
#      291, 291      322, 322      358, 366      371, 382      392, 392      395, 401 
# -9.223372e+14 -9.223372e+14 -9.540000e-02          -Inf          -Inf          -Inf 
# Warning messages:
# 1: In max(jon[jon$TTS %in% i, "lgmeandiff"]) :
#   no non-missing arguments to max; returning -Inf
# 2: In max(jon[jon$TTS %in% i, "lgmeandiff"]) :
#   no non-missing arguments to max; returning -Inf
# 3: In max(jon[jon$TTS %in% i, "lgmeandiff"]) :
#   no non-missing arguments to max; returning -Inf

More verbose version without warnings:

with(test, Map(seq.int, from, to)) |>
{\(.) sapply(., \(i) {
v <- jon[jon$TTS %in% i, 'lgmeandiff']
if (all(is.na(v))) {
NA_real_
} else {
max(jon[jon$TTS %in% i, 'lgmeandiff'])
}
})}() |>
setNames(apply(test, 1, toString))
#      291, 291      322, 322      358, 366      371, 382      392, 392      395, 401 
# -9.223372e+14 -9.223372e+14 -9.540000e-02            NA            NA            NA 

You could also consider max(., na.rm=TRUE).

Data:

jon <- structure(list(TTS = c(291L, 322L, 358L, 360L, 361L, 363L), lgmeandiff = c(-922337203685478, 
-922337203685478, -0.0954, -0.9885, -1.4739, -3.7959), lgtotdiff = c(-922337203685478, 
-922337203685478, 3.2265, 2.3334, 1.848, -0.4739), meandiff = c(0, 
0, 0.936, 0.504, 0.36, 0.072), totdiff = c(0, 0, 9.36, 5.04, 
3.6, 0.72), premean = c(0, 0.720000028610229, 1.15200004577637, 
1.44000005722046, 1.58400001525879, 1.87200012207031), precnt = c(0, 
7.20000028610229, 11.5200004577637, 14.4000005722046, 15.840000629425, 
18.720000743866), sufmean = c(0, 0.720000028610229, 2.08800010681152, 
1.94400005340576, 1.94400005340576, 1.94400005340576), sufcnt = c(0, 
7.20000028610229, 20.8800008296967, 19.4400007724762, 19.4400007724762, 
19.4400007724762)), row.names = c(NA, 6L), class = "data.frame")
test <- structure(list(from = c(291, 322, 358, 371, 392, 395), to = c(291L, 
322L, 366L, 382L, 392L, 401L)), row.names = c(1L, 2L, 3L, 7L, 
10L, 11L), class = "data.frame")