Interval Filter using PyXLL Oct 2024

I have a list of 10 random probabilities which are generated using a PyXLL function, mix_func(). The list is returned to the spreadsheet — as a single object — in a single cell.

Then the filter_int_compare function returns only those values between 0.0 and 0.5, ‘CO’.

Notice that the dynamic ranges are dynamically formatted, using PyXLL.

Here are introductory screenshots.

These would have engineering use in filtering a list of values that fit (or do not fit) within a given range.

I start with a straightforward (naive) approach to writing the function and then add a more useful (maybe more sophisticated) approach to writing it.

I offer a look at my code.

Please note that the code shown is neither succinct nor final — it is written in a ‘long-hand’ expository form, with the intent being that it can be easily followed.

The first function is a boolean PyXLL function that returns a literal (closed, closed) interval check.

The second function is a boolean PyXLL function that does the logical complement of the literal (closed, closed) interval check.

from numbers import Number
@xl_func("float bracket_number1, float bracket_number2, float number_entry: bool")
def between_compare(bracket_number1, bracket_number2, number_entry):
    """NAIVE Given an Excel cell, filter the numeric value, retaining only if within the interval

    Args:
        bracket_number1: one extreme value in the interval
        bracket_number2: the other extreme value in the interval
        number_entry: a single Excel cell to be filtered

    Returns:
        Value if it meets the comparison specification. Otherwise, the string 'empty'

    """
    if not (isinstance(bracket_number1, Number) 
            and isinstance(bracket_number2, Number) 
            and isinstance(number_entry, Number)):
        raise ValueError("Expected all numeric values")
    if (bracket_number1 < number_entry < bracket_number2 or
        bracket_number2 < number_entry < bracket_number1):
        return True
    else:
        return False
    

@xl_func("float bracket_number1, float bracket_number2, float number_entry: bool")
def not_between_compare(bracket_number1, bracket_number2, number_entry):
    return not between_compare(bracket_number1, bracket_number2, number_entry)
    """complement of NAIVE between_compare()"""

~~~

I added an input parameter to allow closed or open intervals (any combination).

Then I built a function abbreviation: ‘interval_compare’ is abbreviated to ‘int_comp’.

Then I built a function, ‘filter_int_comp’ incorporating sorted results and/or unique results.

Guide to interval types:

The interval type may be specified using either the character designation or the symbolic designation.

from numbers import Number
@xl_func("float bracket_number1, float bracket_number2, str interval_type, float number_entry: bool", volatile=False)
def interval_compare(bracket_number1, bracket_number2, interval_type, number_entry):
    """Given an Excel cell, filter the numeric value, retaining only if within the interval

    Args:
        bracket_number1: one extreme value in the interval
        bracket_number2: the other extreme value in the interval
        interval_type: type of interval closure (closed/open and combinations)
        number_entry: a single Excel cell to be filtered
        sortQ: a boolean, True indicates an ascending sort
        reverseQ: a boolean, True indicates sorted descending
        unique_onlyQ: a boolean, indicates removal of duplicates

    Returns:
        List of values meeting the comparison specification, if any. Otherwise, the string 'empty'
        
    Raises:
        ValueError("Expected 2 numeric values and string interval_type")

    """
    
    if not (isinstance(bracket_number1, Number) 
            and isinstance(bracket_number2, Number) 
            and isinstance(interval_type, str)):
        raise ValueError("Expected 2 numeric values and string interval_type")

    ivupper = interval_type.upper()

    if not(ivupper in ['[]','[)','(]','()','CC','CO','OC','OO']):
        raise ValueError("Interval_type must be \n['[]','[)','(]','()','CC','CO','OC','OO']")
                                   
    if ivupper in ['[]','CC']:
        if (bracket_number1 < number_entry < bracket_number2 or
            bracket_number2 < number_entry < bracket_number1):
            return True
    elif ivupper in ['[)','CO']:
        if (bracket_number1 < number_entry <= bracket_number2 or
            bracket_number2 < number_entry <= bracket_number1):
            return True
    elif ivupper in ['(]','OC']:
        if (bracket_number1 <= number_entry < bracket_number2 or
            bracket_number2 <= number_entry < bracket_number1):
            return True
    elif ivupper in ['()','OO']:
        if (bracket_number1 <= number_entry <= bracket_number2 or
            bracket_number2 <= number_entry <= bracket_number1):
            return True
    else:
       return False
    

# this is a boolean PyXLL function suitable for a single value as input
@xl_func("float bracket_number1, float bracket_number2, str interval_type, float number_entry: bool", volatile=False)
def int_comp(bracket_number1, bracket_number2, interval_type, number_entry):
    return interval_compare(bracket_number1, bracket_number2, interval_type, number_entry)
    """alias for interval_compare()"""

# this is a boolean PyXLL function suitable for a range of values as input
@xl_func("float bracket_number1, float bracket_number2, str interval_type, var number_entry_range[][], bool sortQ, bool reverseQ, bool unique_onlyQ: var", volatile=False)
def filter_int_comp(bracket_number1, bracket_number2, interval_type, number_entry_range, sortQ=False, reverseQ=False, unique_onlyQ=False):
    """Given an Excel range, filter the numeric values, retaining only those that are within the interval

    Args:
        bracket_number1: one extreme value in the interval
        bracket_number2: the other extreme value in the interval
        interval_type: type of interval closure (closed/open and combinations)
        number_entry_range: an Excel RANGE to be filtered
        sortQ: a boolean, True indicates sorted ascending
        reverseQ: a boolean, True indicates sorted descending
        unique_onlyQ: a boolean, indicates removal of duplicates

    Returns:
        List of values meeting the comparison specification, if any. Otherwise, the string 'empty'
        
    Raises:
        string 'Error in multiple range input': If the function fails.

    """

    ret = []
    rg_flattened = itertools.chain.from_iterable(number_entry_range)
    for e in rg_flattened:
        e_value = None
        if e == '' or e is None:
            # no conversion
            e_value = ''
        else:
            try:
                e_value = e.Value
            except:
                e_value = e
        if type(e_value) == float:
            comparison = int_comp(bracket_number1, bracket_number2, interval_type, e_value)
            if comparison:
                ret.append(e_value)
    
    if unique_onlyQ:
        ret = list(set(ret))
    if sortQ:
        ret.sort()
    if reverseQ:
        ret.reverse()

    if len(ret) == 0:
        return "empty"
    else:
        return ret


from random import random
# @xl_func("int x: object")
@xl_func("int x: object")
def mix_func(x):
    if x == 0:
        # returned as a number to Excel
        return 0

    # return a list of values as an 'object'
    array = [random() for i in range(x)]
    return array


@xl_func("object x: var")
def unmix_func(x):
    try:
        return [[e] for e in x]
    except:
        return 'empty'

~~~

Here is the video.