2
\$\begingroup\$

I wrote a little function that does, I think, what the Excel function does:

Given a value and a table (matrix), search for the row that has value closest to but not greater than the given value. Returns the value of a column in the matrix.

Is there something I'm missing here? It works but seems too easy.

def vlookup(self, key, table, column):
    value = table[0][column]
    for row in table:
        if row[0] >= key:
            break
        else:
            value = row[column]
    return value
\$\endgroup\$
  • \$\begingroup\$ it seems that your function works for sorted columns is this what you wanted? \$\endgroup\$ – Yannis P. Apr 6 '16 at 19:51
  • \$\begingroup\$ yeah, that's a requirement of the excel function, too. \$\endgroup\$ – Matt O'Neill Apr 6 '16 at 20:37
  • \$\begingroup\$ I was using it quite often never with sorted columns though. re your question it is correct if the column is sorted, otherwise use binary search \$\endgroup\$ – Yannis P. Apr 6 '16 at 20:39
2
\$\begingroup\$

I had to look up the definition of VLOOKUP to exactly understand what it meant, because I didn't get it quite right when first reading at your code:

The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.

(In your function index_number is called column and I think it is clearer.)

Doing that, I found the exact signature of the excel function: VLOOKUP( value, table, index_number, [approximate_match] ) which you don't quite match here. Also, as stated in the comments, there is no mention of sorted columns in the documentation, so we’ll try to get rid of that.

And last thing to mention: you seem to define this function as a method of a class but never use the self parameter. You’d be better turning that into a simple function or turning that into a staticmethod.

Handling unsorted columns

So we want to extract a column out of a table (the first one, actually), filter out values that are too high and taking the maximum of what is left. Simple enough in Python:

def demo_function(key, table):
    extracted_column = (row[0] for row in table)
    interesting_values = filter(lambda x: x < key, extracted_column)
    return max(interesting_values)

We can do even better by removing the lambda because we know that any numerical object has a bunch off dunder dedicated to comparison:

def demo_function(key, table):
    extracted_column = (row[0] for row in table)
    interesting_values = filter(key.__gt__, extracted_column)
    return max(interesting_values)

or, as a one-liner:

def demo_function(key, table):
    return max(filter(key.__gt__, (row[0] for row in table)))

Handling the return value from an other column

Since you are not actually interested in the values contained in the first column, we need to work on row items. The max function will happily use the first value of the tuple/list to determine which item is the bigger. But the filter function will need to be aware that we are working with tuples/lists:

def vlookup_approximate(key, table, column):
    return max(filter(lambda x: x[0] < key, table))

This function returns the whole row of interest. To actually achieve the desired effect, you only need to return the column column:

def vlookup_approximate(key, table, column):
    return max(filter(lambda x: x[0] < key, table))[column]

Handling exact matches

The original function has an optional fourth argument to toggle between exact and approximate matches. This means that we have to adapt our filter rule to look at exactly the key value or less than the key value in our first column. This also means that we need to use key.__ge__ (x <= key) instead of key.__gt__ (x < key) for the comparison function:

def vlookup(key, table, column, approximate_match=True):
    compare = key.__ge__ if approximate_match else key.__eq__
    return max(filter(lambda row: compare(row[0]), table))[column]

Handling lack of results

Looking at the example usages of VLOOKUP, we can see that in case of no match found in the first column, the #N/A value is returned. In our case, the use of the max function raise an exception if the selection is empty:

>>> max([])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: max() arg is an empty sequence
>>> max(filter((12).__eq__, range(10)))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: max() arg is an empty sequence

So we need to take that into account and return None instead of the exception in case no match is found to match this behaviour:

def vlookup(key, table, column, approximate_match=True):
    compare = key.__ge__ if approximate_match else key.__eq__
    try:
        return max(filter(lambda row: compare(row[0]), table))[column]
    except ValueError:
        return None

Visual clutter

The filter function, especially using a lambda can also be written as a comprehension to improve the comprehension at first glance. I am not sure of which way is faster, though. If speed matters to you, time both and keep the best one:

def vlookup(key, table, column, approximate_match=True):
    compare = key.__ge__ if approximate_match else key.__eq__
    try:
        return max(row for row in table if compare(row[0]))[column]
    except ValueError:
        return None

Python 2

As stated in the comments, ints or floats in Python 2 does not provide the __ge__ method. You can still get the same behaviour using the operator module, but you’ll need to explicitly provide key as the first parameter:

from operator import __ge__, __eq__

def vlookup(key, table, column, approximate_match=True):
    compare = __ge__ if approximate_match else __eq__
    try:
        return max(row for row in table if compare(key, row[0]))[column]
    except ValueError:
        return None

This excerpt works the same in both Python 2 and Python 3

\$\endgroup\$
  • \$\begingroup\$ Great answer! By the way, vlookup DOES require you to sort the lookup column if you want non-buggy results: VLOOKUP can provide false results if the table is not sorted in ascending order! This is an issue when you use the approximate match feature, which is TRUE by default. Basically, VLOOKUP starts at the top of the table and goes down row by row until to gets to a valie less than or equal to the lookup value. If your table is not sorted in ascending order, this can give false results, as the formula stops processing rows immediately after finding a “match.” \$\endgroup\$ – Matt O'Neill Apr 7 '16 at 13:42
  • \$\begingroup\$ One other note, using the ge doesn't work with non-strings (I'm using this to lookup base values in a rate card so need floats and ints). \$\endgroup\$ – Matt O'Neill Apr 7 '16 at 13:50
  • \$\begingroup\$ @MattO'Neill I don't understand your second comment. ints and floats do provide a __ge__ method. Just type help(0) or help(0.0) in an interactive shell… \$\endgroup\$ – 301_Moved_Permanently Apr 7 '16 at 13:58
  • 1
    \$\begingroup\$ @MathiasEttinger According to Microsoft, you should itertools.takewhile if range_lookup is true, filter if not. And you should use __le__ not __ge__. Also in Python2 (1).__ge__ raises AttributeError. (It's a nice answer none the less +1) \$\endgroup\$ – Peilonrayz Apr 7 '16 at 14:40
  • \$\begingroup\$ strange I get an error when I try to use the .__ge__ as below: >>> x = 5 >>> compare = x.__ge__ Traceback (most recent call last): File "<input>", line 1, in <module> AttributeError: 'int' object has no attribute 'ge' Also the docs say: object.__str__(self) Called by the str() built-in function and by the print statement to compute the “informal” string representation of an object. The return value must be a string object. object.__lt__(self, other) object.__ge__(self, other) Yes @JoeWallis I had the same thought re: le and ge \$\endgroup\$ – Matt O'Neill Apr 7 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.