Select to view content in your preferred language

How do I use the minimum and maximum values from a single column in python or VBscript?

8119
18
Jump to solution
09-23-2014 07:33 AM
DanielAmrine
Occasional Contributor

GEONET,

 

    Essentially i want to normalize the Value field (VAL) in a series of point layers using an expression in python or VBscript. I have spent all morning browsing "The Web" for examples and all i can find are long expressions using the code block and that involves building a list of the values and selecting the highest or lowest.

 

The equation is (x-min(x))/(max(x)-min(x))....

 

In the python expression I tried FieldB = (!VAL! - min([!VAL!]))/(max([!VAL!])-min([!VAL!])) this returned

 

ERROR 000539: Error Running expression:(38.891808-min([38.891808]))/(max([38.891808]) etc...

 

It's reading the value from the field rather than the actual minimum which is 3.285462

 

I also tried FieldB = min([!VAL!]) and it returned the values for each record rather than actual minimum value in each record.

 

I would really love to have the ability to run a single expression on each of these layers rather than summarizing each individual table and then plugging the values in by hand. This requires going through each layer individually and this will be extremely time consuming and it seems like there should be a smarter way.  

 

Any help on this is much appreciated!

Dan

0 Kudos
18 Replies
XanderBakker
Esri Esteemed Contributor

The data access module (da) was introduced at 10.1...

0 Kudos
XanderBakker
Esri Esteemed Contributor

Haven't tested it, but this might work in 10.0 SP5:

def normalizeField(in_table, in_field, out_field):

    lst = [r.getValue(in_field) for r in arcpy.SearchCursor(in_table)]

    minimum = min(lst)

    maximum = max(lst)

    with arcpy.UpdateCursor(in_table) as cur:

        for row in cur:

            row.setValue(out_field, (float(row.getValue(in_field)) - minimum)/(maximum - minimum))

            cur.updateRow(row)

        row.setValue

JoshuaBixby
MVP Esteemed Contributor

I don't believe the original cursors (non da cursors) supported the Python with statement.

Original code example modified for original cursors:

def normalizeField_10(in_table, in_field, out_field):

    cur = arcpy.SearchCursor(in_table)

    row = next(iter(cur))

    minimum = maximum = row.getValue(in_field)

    for row in cur:

        x = row.getValue(in_field)

        if x < minimum:

            minimum = x

        if x > maximum:

            maximum = x

    del cur

  

    cur = arcpy.UpdateCursor(in_table)

    for row in cur:

        row.setValue(out_field, (float(row.getValue(in_field)) - minimum)/(maximum - minimum))

        cur.updateRow(row)

    del cur

XanderBakker
Esri Esteemed Contributor

True! The with statement does not work for the old cursors, but the list comprehension can be used to determine the min and max...

0 Kudos
DanielAmrine
Occasional Contributor

Xander and Joshua,

I really appreciate the code, and it kind of worked without the ".da."

However, I'm having difficulty setting the variable for in_table.

i'm trying to access the table of a feature class in a geodatabase. 

in_table = "C:/Users/damrine/Desktop/DATA/EARTHFIELD/rockymountian/Wattenburg/DATA/Wattenburg_Earthfield_Database.gdb/LSBC_PHID" i also tried setting the active workspace and using arcpy to select the table but it gave the same error below.

After I execute the function I gives me this:

Runtime error <type 'exceptions.AttributeError'>: 'Cursor' object has no attribute '__exit__'

It does this for both the list function and Joshua's function.

I'm at a loss and this thread really highlights how tedious python can be because once you spend the time to write the code like you guys did, you have to spend an equal or greater amount of time troubleshooting it until it works on the current data set.

I mean if you put everything into a nice little geodatabase on the c: drive and keep it tiny it works fine, but when you're working on a company network with lots of different layers and data types the troubleshooting becomes too cumbersome and it ends up taking too much time to be useful.

Forgive me for my little rant, I really do appreciate your work on this.

Dan

0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Dan,

Could you post the code you are using? It seems something is not right with the import statement(s). The def does not include any import of arcpy, if arcpy is not imported globally it will not be available inside the def...

Kind regards, Xander

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Are you using my second code snippet?  The error message you are getting is likely caused by using a Python "with" statement and the older cursors.  The second code snippet I posted most recently does not use the with statement and should work.

Your issue with working over the network has nothing to do with Python.  I would argue ArcGIS Desktop has historically handled network-based data, like UNC paths, very poorly.

I believe the second code snippet I posted will work, and with any data set.  To defend scripting with Python, it is way more powerful than most of the GUI tools, which one would expect.  And, GUI tools don't always work as well, and I find troubleshooting them much more frustrating than Python.

0 Kudos
DanielAmrine
Occasional Contributor

Joshua,

I used your second snippet, entered into the python window. Below is the pasted snippet, however i can't seem to find an option to "code wrap it" in the geonet editor.

def normalizeField_10(in_table, in_field, out_field): 

... cur = arcpy.SearchCursor(in_table)

... row = next(iter(cur)) 

... minimum = maximum = row.getValue(in_field) 

... for row in cur: 

...        x = row.getValue(in_field) 

...        if x < minimum: 

...            minimum = x 

...        if x > maximum: 

...            maximum = x 

... del cur 

...

... cur = arcpy.UpdateCursor(in_table)

... for row in cur: 

...        row.setValue(out_field, (float(row.getValue(in_field)) - minimum)/(maximum - minimum)) 

...        cur.updateRow(row) 

... del cur

This is how I defined the variables and imported the arcpy.

import arcpy

>>> in_table = "C:\Users\damrine\Desktop\DATA\EARTHFIELD\rockymountian\Wattenburg\DATA\Wattenburg_Earthfield_Database.gdb\LSBC_PHID"

>>> in_field = "VAL"

>>> out_field = "NORM"

I am trying to run this on the feature class point table located in the in_table string.I also tried setting the environment to current MXD and data frame but I couldn't get that to work either.

I didn't want to start a debate, it's a personal frustration of mine because I don't have the time to devote to learning Python. It's like learning a new language you have to practice a lot to really use it well. If you can't devote a few hours a week at least you will never learn it. The GUI and model builder are not as powerful but the learning curve is not nearly as steep. I know the problems I'm having stems from the fact that I don't know what a "Cursor" means or how that applies to the map document, It's probably really simple but not knowing this first step makes it difficult to apply these code snippets.

You helped me with the code and now it is my challenge to find out how to set it up in the correct way to run. I'm definitely not discouraging you from helping but you provided the code and just seeing it really helps me to understand how python can be applied to calculate a normalized data set.

Thank You,

Dan 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Daniel, to code wrap you need to use the 'advanced editor' option in the upper right corner.  Once in the advanced editor, you can do syntax highlighting.

Just to clarify, the code you just posted is generating the cursor object error you mention earlier?  Can you try running the command and pasting the exact error messages that are returned, all of them?

One potential problem I see, although it wouldn't generate the cursor object error, is your string representing the feature class.  If you are going to use Windows-style paths (single backslashes for directories), you should put in 'r' in front of the first quote to signify a raw string.  For example, r"C:\Users\damrine" instead of "C:\Users\damrine".  In Python, a backslash is an escape character, which can create problems with Windows-style paths if people don't realize that.

0 Kudos