topic Re: Excel formula with lookup tables for field calculation in Python Questions

Excel formula with lookup tables for field calculation

SergioMoreno_Rojas — Thu, 26 Jul 2012 09:19:14 GMT

The challenge I have is to calculate a new value for one of the field columns of the attribute table of a normal polygon layer.
Precondition: use a formula from an Excel spredsheet which works utilizing lookup tables (data for the formula).

Any ideas for the easiest approach? Maybe do all the calculations outside ARcGIS in a database and then feed the field with the new value?

Thanks,

Sergio

Re: Excel formula with lookup tables for field calculation

AustinDavis — Thu, 26 Jul 2012 13:50:16 GMT

You could instantiate your excel logic in python using python dictionaries as the look-up lists...

Or pre-calculate everything in excel and join it to your spatial layers.

Re: Excel formula with lookup tables for field calculation

ChristopherThompson — Fri, 10 Dec 2021 20:38:02 GMT

there are a number of ways to do this probably. I think the easiest would be using an Update Cursor to cycle through your feature class and implement a series of if/elif/else statements to update the values in your table. that logic might look something like this:

rows = arcpy.UpdateCursor(FeatureClass,'','','updateField')
for row in rows:
 if row.updateField == 1:
  row.updateField = *some calculation*
  rows.updateRow(row)
 elif row.updateField == 2:
  row.updateField = *some calculation*
  rows.updateRow(row)
 elif ...another condition to test for..

There are otherways you could implement this, but depending on the number of conditions you have to test for this should be pretty easy. Situations where you have several values that get updated by the same formula could use an "in" operator:

if row.updateField in (1,5,10,11) :

The other trick to this is you have to be able to translate your excel formula into a calculation that can be performed in python. For simple calcs this shouldn't be hard but if the formulas contain references to other cells or worksheets then that becomes increasingly difficult though not impossible. In this situation as the other respondant suggested you might simply make those calculations in excel, save the results (values, not formulas and formats!) as a .csv and then join that back to your GIS data and calculate the the fields from the joined table.

Re: Excel formula with lookup tables for field calculation

SergioMoreno_Rojas — Fri, 27 Jul 2012 10:19:28 GMT

Another level of difficulty on this is that all the process has to be automatic, as it will be a processing service for Flex Viewer in a company webportal based on user defined parameters for the Excel formula.

Not sure if pre-calculate everything in Excel and join it to the spatial layers can be done automatic: ArcGIS (export table values) -> Excel (calc) -> ArcGIS (feed table with the new values)

I know ArcGIS is not a software designed for complex calculations like Excel but maybe writing it in Python could solve it? Do this within a model to allow publishing the service afterwards? FEASIBLE????

I will investigate about writing the excel logic in python using python dictionaries as the look-up lists...

Thanks everybody

Re: Excel formula with lookup tables for field calculation

ChristopherThompson — Fri, 27 Jul 2012 10:28:42 GMT

can you provide an example of a formula that is being used and the look up tables?

Re: Excel formula with lookup tables for field calculation

SergioMoreno_Rojas — Fri, 27 Jul 2012 11:13:47 GMT

can you provide an example of a formula that is being used and the look up tables?

Check out the formula in the 'P2O5 (kg/ha)' coloured box within the Excel example spredsheet attached.

Re: Excel formula with lookup tables for field calculation

ChristopherThompson — Sun, 12 Dec 2021 15:54:55 GMT

Wow, that is possibly the longest excel formula i have ever seen.. I nearly fell out of my chair! i have 2 monitors and could not stretch out excel wide enough to view the whole thing.. ! I am truly impressed. LOL

That said, without dissecting the whole thing I mostly see how it works and i think you can do this with using dictionaries that have multiple values per key.. .so just the first row of your removal data would look like this in a dictionary:

removal_data = {'Field Beans':[11.0,11.0,12.0,12.0]}

and so this part of your excel formula:
'$J$29*(VLOOKUP($J$28,RemovalData,3,0))'
could be implemented by getting a value from a dictionary based on the key reference. To do this, first make a function to get a value from the dictionary above, the arguments are the dictionary name, the key you're interested in and the index of the column holding the data you want. Remember in excel the column indexes are 1 based but in python indexes are 0 based, so your column references will always be 1 less than in excel

def get_val(dname,key,col_idx):
 d_cols = dname[key]
 return d_cols[col_idx]

In this example value for $J$29 is variable J29, and variable for $J$28 is J28 - then in the formula this would be implmented by:

J29 = 10 #some number provided by user input or derived from some other process in your code
J28 = 'Field Beans' #again, how this is supplied is a matter of how the overall program will work - user input or from some other process inside the program, this could be based on a list of the keys in a given dictionary for instance

result = J29 * (get_val(removal_data,J28,2))

Ultimately it looks like you need to handle the following inputs to the formula:
a variable to contain the data Column B (B2 for instance)
A dictionary with multiple key values for Removal Data
A dictionary with multiple key values for Manure Data
a variable to contain the value for PTarget

Beyond that, there are multiple nested If/elif/else type statements and some basic mathmatical operations. Complex mostly in the nesting of the If/elif/else and the use of the lookups, but very doable I think.