Filter a Pandas Dataframe using PyXLL (with video) Jul 2023

Filter a Pandas Dataframe
I coded a PyXLL function that allows filtering a pandas dataframe using Excel.

The dataframe is my AISC table of structural steel shapes. This was built from a fundamental set of properties used by all structural engineers. There are approximately 2000 shapes with about 90 properties each.

In day-to-day work, a structural engineer is selecting beams, columns, etc from the AISC shapes and, often, restricting the selection to set dimensions or to set properties — use of multiple criteria would be the norm.

The function uses only three parameters: the criteria range, the reverse sort toggle, and the upper limit on rows of results.

Note that conditional formatting is applied using the PyXLL formatter option.

Database Filter in Action — Video


Full Screenshot

Active Filters

The shape area (‘A’) is restrained:

9 ≤ A ≤ 10

A = 9.13 does match

The shape depth (‘DDET’) is restrained:

DDET = 8

DDET = 8 does match

The shape name (‘AISC_MANUAL_LABEL’) is restrained:


This restricts the shape name to this pattern:

Starts with ‘W’

Then one or more digits

Then an ‘X’

Then zero or more of any characters

‘W8X31’ does match



I offer a code snippet
from pyxll import async_call
from pyxll import xl_app
from pyxll import xl_func
from pyxll import XLCell
from pyxll import xl_macro
from pyxll import xl_menu
from pyxll import xlcAlert
from pyxll import xlfCaller
from pyxll import schedule_call

from pyxll import get_type_converter

from pyxll import DataFrameFormatter, ConditionalFormatter, Formatter
blue_formatter = Formatter(text_color=Formatter.rgb(0, 0, 0xff))
bf = Formatter.rgb(0, 0, 0xff)
light_yellow_formatter = Formatter(text_color=bf,
                                   interior_color=Formatter.rgb(255, 242, 204))..
#this next line is the PyXLL decorator
@xl_func("range critstrinp, 
         bool reverseboolinp, 
         int ibxintinp: var",
def AISC_mult_filt_sort(critstrinp,
    """FUNCTION to place filtered sorted
    (multiple criteria) steel shape list
       "criteria for AISC members"
       "Reverse order (True or False)"
       "Upper limit on output rows count
       (leave blank for default limit of 40)"
    crit_string = ""
	# CT is for 'contains'
	#    as in search_string in str(getattr(db[x], field_name))
	# SW is for 'starts with'
	#    as in getattr(db[x], field_name).startswith(search_string)
	# RE is for 'regular expression'
	#    as in, str(getattr(db[x], field_name)))
	crits = ["==", ">", "<", ">=", "<=", "!=", "<>",
             "EE", "EQ", "E", "GT", "LT", "GE", "LE",
             "NE", "CT", "SW", "RE"]
    critsbetween = ["<<", "<<=", "<=<", "<=<=", 
                    "LTLT", "LTLE", "LELT", "LELE"]	

Leave a Reply

Your email address will not be published. Required fields are marked *