The inspiration for this post came from my structural engineering work, specifically using the ASCE wind codes.
For laypersons, ASCE is the American Society of Civil Engineers. ASCE publishes ASCE 7 which establishes the minimum loads (like dead, live, snow, wind, seismic, flood, etc) that structures must be designed to withstand, thereby ensuring safety and reliability.
In the ASCE table, notice that the left side lookup values are in descending order whereas the top side lookup values are in ascending order. In reference data, we commonly see every sort of arrangement in tables.
A canonical representation for the table would be ascending order for both lookups; however, it would be an unnecessary burden to require the user to presort the table; better to put that sorting in the proposed function.
Notice also, in this example, that the left side lookup starts with a range ≤0.16.
Similarly, the top side starts with a range ≤0.05 and ends with a range ≥45. A structural engineer would understand the implications of these ranges: both lookup lower bounds are zero, the top side lookup upper bound is theoretically infinity. When a range is specified in a lookup, no interpolation is needed; the answer is constant across the range.
Overview
In this post, only linear interpolation is being addressed.
The function was written using straight Python. And I easily made an Excel function using PyXLL. PyXLL allows me to efficiently develop and prove a new design table.
One might imagine this as a trivial calculation, and for a human, it is. To cast the calculation into a general purpose function is not so trivial.
Published tables are written for humans. Though the data is explicitly written, the lookups are generally not. On the left, right, top, and the bottom sides often there is an implied limit: maybe a fixed value, maybe not. Maybe only nonzero values, maybe only nonnegative values, maybe only positive values, maybe only values from zero to one. The tables are published in context. Knowing, and presenting this context, leads to the calculation of ‘legitimate’ interpolations.
I’ve worked to build that context into my proposed function.
tab_interp(row_lookup, column_lookup, entire_table)
col_lookup: (float)
entire_table: (range)
A sequence of self-explanatory screenshots follow</em
The Excel function call (note: no table reference):
=ASCE_wind_interpolate($B$14,E5)
The Jupyter cell simply reads:
my_array = %xl_get --cell d4:r12 --type numpy_array my_array
Do you like great support from a company? I do.
