topic Re: How do I use the minimum and maximum values from a single column in python or VBscript? in Python Questions

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

DanielAmrine — Tue, 23 Sep 2014 14:33:46 GMT

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

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

IanGrasshoff — Tue, 23 Sep 2014 14:51:38 GMT

You could use several methods, the easiest one would be to use Summary Statistics Tool (Analysis Tools Toolbox -> Statistics -> Summary Statistics). The other option would be to use the dissolve tool (Data Management Tools -> Generalization -> Dissolve -> Pick your summary stats). To use the dissolve, you will need a uniform field to use as a dissolve value, ex. DISSOLVE_ID, every row equals 1 or 0 or something. Use that column to dissolve on, then choose your summary stats pointed to the field you want the min/max values for.

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

JohannesBierer — Tue, 23 Sep 2014 14:55:19 GMT

If you search for a python solution, have a look here:

Calculate a mean value from a field | ArcPy Café

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

JoshuaBixby — Tue, 23 Sep 2014 18:35:44 GMT

There is definitely a way to address this without resorting to plugging values in by hand. That being said, it isn't going to be a simple expression in Field Calculator that does it. Do you have a requirement to use Field Calculator? If so, I think "The Web" has already given you the answer(s).

The behavior you are seeing, at least the parts that aren't errors, are expected. Field Calculator operates like a cursor, at least logically. Field Calculator only sees one record at a time, even if it is operating over an entire set of records. The Codeblock section allows for a little bit of kung fu but we are talking Kung Fu Panda and not Caine. This is why your min and max functions are returning VAL instead of the min and max for the field. If you were looking at minimum or maximum values across fields for a given record, then the approach might work.

As Ian Grasshoff‌ mentions, you could use the Summary Statistics tool. My rub with that tool, and why I seldom use it, is that it creates a table to place the values. The last thing I want is another table to extract values from and have to clean up afterwards.

As Johannes Bierer‌ links to, you could use arcpy.da.TableToNumPyArrary to dump the table and find the minimum, maximum, or other statistics that way. My only concern with that approach, well all approaches involving lists, is that creating the lists can consume lots of memory depending on the data sets involved. Why create a list with a million elements if you only want to know the minimum and maximum values for the field.

Here is an example of a function you could create and then call from the Python interactive window:

def normalizeField(in_table, in_field, out_field):
    with arcpy.da.SearchCursor(in_table, in_field) as cur:
        x, = next(iter(cur))
        minimum = maximum = x
        for x, in cur:
            if x < minimum:
                minimum = x
            if x > maximum:
                maximum = x
    
    with arcpy.da.UpdateCursor(in_table, [in_field, out_field]) as cur:
        for x, y in cur:
            y = (float(x) - minimum)/(maximum - minimum)
            cur.updateRow([x,y])

The float with the UpdateCursor ensures the normalization value doesn't truncate to an integer if the in_field happens to be an integer.

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

DanielAmrine — Tue, 23 Sep 2014 19:06:09 GMT

Joshua,

Thank you for this great answer, and thank you Ian and Johannes as well.

I created this model as a workaround but it is cumbersome and has to be "Reset" each time.

I also had to remove the natural neighbor because every time i tried to run the model it said the "Z value" field was missing even though i double checked the input and the field was there populated with the correct values. Not sure why this happened.

I copied your code into the Python Window, but when i call function I'm not clear on how to add the table, in field, and outfield. This is the results i'm getting. I understand the concept of the code but I'm missing the correct syntax.

>>> normalizeField('MSBNC_PHIN',!VAL!,!NORM!)

Parsing error <type 'exceptions.SyntaxError'>: invalid syntax (line 1)

>>> normalizeField('MSBNC_PHIN',[!VAL!],[!NORM!])

Parsing error <type 'exceptions.SyntaxError'>: invalid syntax (line 1)

>>> normalizeField('MSBNC_PHIN',(!VAL!),(!NORM!))

Parsing error <type 'exceptions.SyntaxError'>: invalid syntax (line 1)

>>> normalizeField

>>> normalizeField('MSBNC_PHIN',[!VAL!,!NORM!])

Parsing error <type 'exceptions.SyntaxError'>: invalid syntax (line 1)

Thanks Again!

Dan

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

XanderBakker — Tue, 23 Sep 2014 19:12:44 GMT

Nice example Joshua Bixby, but maybe to make it a little shorter you can determine the min and max as follows:

def normalizeField(in_table, in_field, out_field):
    lst = [r[0] for r in arcpy.da.SearchCursor(in_table, (in_field))]
    minimum = min(lst)
    maximum = max(lst)
    # ect

Not sure how this affects performance though.

Kind regards, Xander

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

JoshuaBixby — Tue, 23 Sep 2014 19:23:13 GMT

Xander Bakker‌, I agree the list-based example you provide is more succinct, not to mention will also work. The reason I went with a slightly more verbose iterator than a more succinct list comprehension was memory management. If one happens to be dealing with millions, tens of millions, or even more records, fully populating a list will impact memory and probably performance. That said, most users likely never deal with data sets large enough to see memory or performance differences between the two approaches..

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

JoshuaBixby — Tue, 23 Sep 2014 19:38:49 GMT

I roughed out the example to run from the interactive Python window. I don't work with Model Builder much or at all, so I can't help much in terms of incorporating it into that workflow.

The function takes strings for all arguments. If VAL is the name of the field, use "VAL" with just quotes and no brackets or parentheses, same with NORM.

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

XanderBakker — Tue, 23 Sep 2014 19:42:54 GMT

You are absolutely right. Memory considerations should be taken into account.

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

DanielAmrine — Tue, 23 Sep 2014 20:35:12 GMT

Joshua,

It recognized the table and fields, but then I got the following error:

>>> normalizeField('LSBC_PHID',"VAL","NORM")

Runtime error <type 'exceptions.AttributeError'>: 'module' object has no attribute 'da'

I imported Arcpy....forgot that as step 1....and then tried again. I then received this error:

Parsing error <type 'exceptions.SyntaxError'>: EOL while scanning string literal (line 1). I was missing a " before on of the field names...tried it again and received the error above again.

I had to set the variables so In_table = "c:\etc...." location of the table and then in_field="VAL", and out_field="NORM"

I also had to reverse the directions of all slash bars because Modelbuilder uses "/" and Windows Explorer uses "\".

After all this I was able to run the first code but I then received this error:

>>> normalizeField(in_table,in_field,out_field)

Runtime error <type 'exceptions.RuntimeError'>: ERROR 999999: Error executing function.

An invalid SQL statement was used.

An invalid SQL statement was used. [LSBC_PHID]

An invalid SQL statement was used. [SELECT * FROM LSBC_PHID WHERE ( VALTEST )]

I thought VAL may be a reserved word in arcpy or python so I changed the field name to "VALTEST" and I still got this error. LSBC_PHID is the name of the feature class with the table Ii'm using. Do I need to export it as a separate table?

I am running ArcMAP 10.0 with service pack 5 Build 4400.

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

XanderBakker — Tue, 23 Sep 2014 20:38:42 GMT

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

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

XanderBakker — Tue, 23 Sep 2014 20:53:59 GMT

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

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

JoshuaBixby — Tue, 23 Sep 2014 20:59:45 GMT

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

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

XanderBakker — Tue, 23 Sep 2014 21:17:33 GMT

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

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

DanielAmrine — Wed, 24 Sep 2014 15:32:29 GMT

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

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

XanderBakker — Wed, 24 Sep 2014 16:12:33 GMT

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

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

JoshuaBixby — Wed, 24 Sep 2014 16:13:17 GMT

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.

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

DanielAmrine — Thu, 25 Sep 2014 14:24:36 GMT

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

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

JoshuaBixby — Thu, 25 Sep 2014 14:41:00 GMT

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.