logo
Splunk Team

quickest way to look up nearest index value

  • Last Update :
  • Techknowledgy :

Try the following:

min(range(len(a)), key = lambda i: abs(a[i] - 11.5))

For example:

>>> a = [25.75443, 26.7803, 25.79099, 24.17642, 24.3526, 22.79056, 20.84866, 19.49222, 18.38086, 18.0358, 16.57819, 15.71255, 14.79059, 13.64154, 13.09409, 12.18347, 11.33447, 10.32184, 9.544922, 8.813385, 8.181152, 6.983734, 6.048035, 5.505096, 4.65799] >>>
   min(range(len(a)), key = lambda i: abs(a[i] - 11.5))
16

Or to get the index and the value:

>>> min(enumerate(a), key = lambda x: abs(x[1] - 11.5))
   (16, 11.33447)
import numpy as np

a = [25.75443, 26.7803, 25.79099, 24.17642, 24.3526, 22.79056, 20.84866, 19.49222, 18.38086, 18.0358, 16.57819, 15.71255, 14.79059, 13.64154, 13.09409, 12.18347, 11.33447, 10.32184, 9.544922, 8.813385, 8.181152, 6.983734, 6.048035, 5.505096, 4.65799]

index = np.argmin(np.abs(np.array(a) - 11.5))
a[index] # here is your result

[EDIT] Using zip(), this is pretty simple to achieve:

 array_to_sort = zip(original_array, range(len(original_array)))
 array_to_sort.sort(key = i: i[0])

Bisect is your friend. Here's my solution. It scales O(n*log(n)):

class Closest:
   ""
"Assumes *no* redundant entries - all inputs must be unique"
""
def __init__(self, numlist = None, firstdistance = 0):
   if numlist == None:
   numlist = []
self.numindexes = dict((val, n) for n, val in enumerate(numlist))
self.nums = sorted(self.numindexes)
self.firstdistance = firstdistance

def append(self, num):
   if num in self.numindexes:
   raise ValueError("Cannot append '%s' it is already used" % str(num))
self.numindexes[num] = len(self.nums)
bisect.insort(self.nums, num)

def rank(self, target):
   rank = bisect.bisect(self.nums, target)
if rank == 0:
   pass
elif len(self.nums) == rank:
   rank -= 1
else:
   dist1 = target - self.nums[rank - 1]
dist2 = self.nums[rank] - target
if dist1 < dist2:
   rank -= 1
return rank

def closest(self, target):
   try:
   return self.numindexes[self.nums[self.rank(target)]]
except IndexError:
   return 0

def distance(self, target):
   rank = self.rank(target)
try:
dist = abs(self.nums[rank] - target)
except IndexError:
   dist = self.firstdistance
return dist

Use it like this:

a = [25.75443, 26.7803, 25.79099, 24.17642, 24.3526, 22.79056, 20.84866,
   19.49222, 18.38086, 18.0358, 16.57819, 15.71255, 14.79059, 13.64154,
   13.09409, 12.18347, 1.33447, 10.32184, 9.544922, 8.813385, 8.181152,
   6.983734, 6.048035, 5.505096, 4.65799
]
targets = [1.0, 100.0, 15.0, 15.6, 8.0]
cl = Closest(a)
for x in targets:
   rank = cl.rank(x)
print("Closest to %5.1f : rank=%2i num=%8.5f index=%2i " % (x, rank,
   cl.nums[rank], cl.closest(x)))

Will output:

Closest to 1.0: rank = 0 num = 1.33447 index = 16
Closest to 100.0: rank = 25 num = 26.78030 index = 1
Closest to 15.0: rank = 12 num = 14.79059 index = 12
Closest to 15.6: rank = 13 num = 15.71255 index = 11
Closest to 8.0: rank = 5 num = 8.18115 index = 20

Output:

Closest to 100.0: rank = 25 num = 99.90000 index = 25

Suggestion : 2

At the core, this is an INDEX and MATCH formula: MATCH locates the position of the closest match, feeds the position to INDEX, and INDEX returns the value at that position in the Trip column. The hard work is done with the MATCH function, which is carefully configured to match the "minimum difference" like this:,We now have what we need to find the position of the closest match (smallest difference), and we can rewrite the MATCH portion of the formula like this:,To find the closest match in numeric data, you can use INDEX and MATCH, with help from the ABS and MIN functions. In the example shown, the formula in F5, copied down, is:,With 101 as the lookup value, MATCH returns 8, since 101 is in the 8th position in the array. Finally, this position is fed into INDEX as the row argument, with the named range trip as the array:

{
   = INDEX(data, MATCH(MIN(ABS(data - value)), ABS(data - value), 0))
}

To find the closest match in numeric data, you can use INDEX and MATCH, with help from the ABS and MIN functions. In the example shown, the formula in F5, copied down, is:

= INDEX(trip, MATCH(MIN(ABS(cost - E5)), ABS(cost - E5), 0))
2._
= INDEX(trip, MATCH(MIN(ABS(cost - E5)), ABS(cost - E5), 0))
= INDEX(trip, MATCH(MIN(ABS(cost - E5)), ABS(cost - E5), 0))

At the core, this is an INDEX and MATCH formula: MATCH locates the position of the closest match, feeds the position to INDEX, and INDEX returns the value at that position in the Trip column. The hard work is done with the MATCH function, which is carefully configured to match the "minimum difference" like this:

MATCH(MIN(ABS(cost - E5)), ABS(cost - E5), 0)
2._
MATCH(MIN(ABS(cost - E5)), ABS(cost - E5), 0)

Taking things step-by-step, the lookup value is calculated with MIN and ABS like this:

MIN(ABS(cost - E5)

First, the value in E5 is subtracted from the named range cost (C5:C14). This is an array operation, and since there are 10 values in the range, the result is an array with 10 values like this:

{
   899;
   199;
   250; - 201;
   495;
   1000;
   450; - 101;
   500;
   795
}
6._
{
   899;
   199;
   250; - 201;
   495;
   1000;
   450; - 101;
   500;
   795
}
MATCH(MIN(ABS(cost - E5)), ABS(cost - E5), 0)
MIN(ABS(cost - E5)

Suggestion : 3

Using INDEX-MATCH Formula to Find the Closest Match in Excel,How to Find the Closest Match in ExcelUsing INDEX-MATCH Formula to Find the Closest Match in Excel Using XLOOKUP (for Excel 365 and Later Versions),In this tutorial we will show you two ways to find the closest match (nearest value) in Excel:,The formula to find the price closest to the value in cell E2 of the above sample dataset is as follows:

Together, the formula to find the product corresponding to the price closest to the value in E2 is:

{
   = INDEX(A2: A10, MATCH(MIN(ABS(B2: B10 - E2)), ABS(B2: B10 - E2), 0))
}

The formula to find the price closest to the value in cell E2 of the above sample dataset is as follows:

= XLOOKUP(E2, B2: B10, A2: A10, , 1)

Another advantage over the traditional lookup functions is that it does not require the data to be sorted in order to perform a search.

Note: The XLOOKUP
function is only available in Excel 365 and later versions

We used the XLOOKUP function as follows to find the price closest to the value in cell E2 is as follows:

= XLOOKUP(E2, B2: B10, A2: A10, , 1)

Suggestion : 4

This part gives you the minimum difference between the given experience (which is 2.5 years) and all the other experiences. In this example, it returns 0.3,The lookup value in the MATCH formula is MIN(ABS(D2-B2:B15)).,The trick in this formula is to change the lookup array and the lookup value to find the minimum experience difference in required and actual values.,There can be many different scenarios where you need to look for the closest match (or the nearest matching value).

The below formula would do this:

= VLOOKUP(B2, $E$2: $F$6, 2, 1)

Below is the formula that will give us the result:

= INDEX($A$2: $A$15, MATCH(MIN(ABS(D2 - B2: B15)), ABS(D2 - $B$2: $B$15), 0))

Below is the formula that will give the upcoming event name:

= INDEX($A$2: $A$11, MATCH(E1, $B$2: $B$11, 1) + 1)


Trending Technology


Most popular in python