AnsweredAssumed Answered

Help with ListFields and CalculateField_management syntax

Question asked by jplay on Aug 29, 2012
Latest reply on Aug 29, 2012 by jplay

Like • Show 0 Likes0
Comment • 9

I need some assistance with the syntax for a CalculateField_management calculation as I have still yet to master all the python syntax rules.

What I am trying to do is find the max value in a variable number of fields in order to populate another field. I am using the ListFields function to discover the desired fields to choose from, but getting that list into the formula is giving me some difficulty.

import arcpy, os, string LAYERS = arcpy.GetParameterAsText(0) SLOSHFILEDS = [f.name for f in arcpy.ListFields(LAYERS,"","DOUBLE") arcpy.CalculateField_management (LAYERS, "MAXSURGE", max(SLOSHFILEDS))

I have tried any number of different string combinations for the max() calc to no avail (not that this particular variation shows that).

Adding/Changing the following to the script doesn't give me the syntax error that I would recieve with the above, but it does give me a "The calculate value is invalid for the row with ObjectID = 0..." x 18,526 (or however many rows are in my table) and then does nothing to the table except populate my MAXSURGE field with 0's.

SLOSHFILEDS = arcpy.ListFields(LAYERS,"","DOUBLE") fieldNameList = [] for field in SLOSHFILEDS:         if not field.required:             fieldNameList.append(field.name) arcpy.CalculateField_management (LAYERS, "MAXSURGE", max(fieldNameList))

Hard coding the field names into the formula works great, but of course, I will not always have the same number of fields or same field names to work with.

Any help would be appreciated.

-John

mzcoyle Aug 29, 2012 7:54 AM

Correct Answer

I had forgot to put in the row object in this line.

curs.updateRow(row)

That is most likely the issue. Cursors really are the best way to go about searching or updating data on a row by row basis.

Passing field objects to the calculate field tool as variables would require you to change them to strings from a list and add the ! to mark them as field variables to the field calculator. You'd have to do something funky like this I think.

field_str = "!" + "!, !".join(fieldNameList) + "!"

See the reply in context

No one else had this question

Outcomes

mzcoyle Aug 29, 2012 5:30 AM

There may be a way to do this is a field calculator, but I would use this method. I think you may also want to modify your SLOSHFIELDS creation since you are extracting the name there and then trying to reference the required parameter and name parameter in your loop.

import arcpy
import os
import string

LAYERS = arcpy.GetParameterAsText(0)
SLOSHFIELDS = [f for f in arcpy.ListFields(LAYERS,"","DOUBLE")]
fieldNameList = []

for field in SLOSHFIELDS :
        if not field.required:
            fieldNameList.append(field.name)

max_field = "MAXSURGE"
curs = arcpy.UpdateCursor(LAYERS)
for row in curs:
    max_val = 0
    for field in fieldNameList:
        val = row.getValue(field)
        if val > max_val:
            max_val = val
    row.setValue(max_field, max_val)
    curs.updateRow(row)

Actions

clthompson Aug 29, 2012 5:38 AM

This is a tricky problem, one thing i'm not quite sure of is if this is being done on a row by row basis.. so each row or feature in your table has the maxsurge value calculated. If so I think this sort of thing probably wants to be done in the context of an update cursor rather than using the field calculator. One way to handle this would be to get your list of fields, create an update cursor that has all the fields referenced, then construct a list that contains all the values you are interested in comparing,then use max() to get the largest values from the list you created. Conceptually this might look something like this:

LAYERS = arcpy.GetParameterAsText(0)
SLOSHFILEDS = [f.name for f in arcpy.ListFields(LAYERS,"","DOUBLE") #presuming this filters your fields to the ones you want to compare
rows = arcpy.UpdateCursor(LAYERS) #leaving field names out of the cursor tool returns all fields
for row in rows:
    comp_values = []
    for fname in SLOSHFILEDS:
        comp_values.append(row.fname)
    max_val = max(comp_values)
    row.MAXSURGE = max_val
    rows.updateRow(row)

Actions

jplay Aug 29, 2012 7:40 AM

mzcoyle and clthompson, Thanks for your suggestions. Not to discourage you from making additional cursor suggestions (I'll given them a go if you provide them), but I've never had much luck with search cursors and usually try to avoid them--I just can't seem to wrap my head around them. I always end up with an infinite loop or receive some kind of error.

I tried both of your suggestions and neither worked. With mzcoyle's I receive "exceptions.AttributeError: Cursor: Error in parsing arguments." With clthompson's I receive "exceptions.RuntimeError: Row: Field fname does not exist."

The bottom code I provided will run and populate the empty MAXSURGE field, however it populates the field with 0's after giving me "The calculate value is invalid for the row with ObjectID = 0..." error. If I try to concatenate the list object into a string value to add to my max() formula, e.g.:

SLOSHSTRING = str(fieldNameList)
calculation = "\"max(" + SLOSHSTRING + ")\""
arcpy.CalculateField_management (LAYERS, "MAXSURGE", calculation, "PYTHON", "")

I receive "The calculate value is invalid for the row" error. However if I hard code the max() calculation, e.g.:

arcpy.CalculateField_management (LAYERS, "MAXSURGE", "max( !SURGE90!, !SURGE90_1!, !SURGE90_12!, !SURGE90_13!, !SURGE90_14!, !MAXSURGE!)", "PYTHON", "")

it works just fine and fairly quickly on 18,000+ rows.

I guess I'm to figure out how to get python to think that it's getting

arcpy.CalculateField_management (LAYERS, "MAXSURGE", "max( !SURGE90!, !SURGE90_1!, !SURGE90_12!, !SURGE90_13!, !SURGE90_14!, !MAXSURGE!)", "PYTHON", "")

when it receives this

arcpy.CalculateField_management (LAYERS, "MAXSURGE", max({ListObject}), "PYTHON", "")

Suggestions? With this or with cursors.

Thanks.

Actions

mzcoyle Aug 29, 2012 7:54 AM

I had forgot to put in the row object in this line.

curs.updateRow(row)

field_str = "!" + "!, !".join(fieldNameList) + "!"

Actions

jplay Aug 29, 2012 8:15 AM

THANK YOU mzcoyle! Both worked beautifully.

Actions

clthompson Aug 29, 2012 8:38 AM

"exceptions.RuntimeError: Row: Field fname does not exist."

Like i said "conceptually" :) - without testing I wasn't sure how the value held by fname needs to be handled. Ultimately that may need to be converted to a string... it may not even be possible in that context. But its a good case to play with. Cursors are really fairly simple - they allow you to step row by row through a table of data, performing some action on each row and then moving on to the next, and then when the get to the end, they stop. Sounds like you found a solution though!

Actions

jplay Aug 29, 2012 8:43 AM

Thank you clthompson. I appreciate your help. I'll give cursors another look-see, may this time they'll click.

Actions

clthompson Aug 29, 2012 10:42 AM

Ok, this has got under my skin a little so I think here's a solution:

LAYERS = arcpy.GetParameterAsText(0)
SLOSHFILEDS = [f.name for f in arcpy.ListFields(LAYERS,"","DOUBLE") #presuming this filters your fields to the ones you want to compare
rows = arcpy.UpdateCursor(LAYERS) #leaving field names out of the cursor tool returns all fields
for row in rows:
    comp_values = []
    for fname in SLOSHFILEDS:
        nom = fname.name
        comp_values.append(row.getValue(nom))
    max_val = max(comp_values)
    row.MAXSURGE = max_val
    rows.updateRow(row)

Basically, the list of created as SLOSHFILEDS is a list of field objects, which have a variety of attributes including 'name' so using that as a value to pass into the getValue method you can populate your list of values for the row. The rest of the code works as described earlier. I like this approach because its simple and I'm lazy and don't like to write lots of lines of code. For loops and cursors FTW!

Actions

jplay Aug 29, 2012 10:47 AM

Ok, this has got under my skin a little

Don't you hate when that happens? ;)

I'll give this a go as well.

Actions

9 Replies

clthompson Aug 29, 2012 5:38 AM
Mark Correct
Correct Answer
This is a tricky problem, one thing i'm not quite sure of is if this is being done on a row by row basis.. so each row or feature in your table has the maxsurge value calculated. If so I think this sort of thing probably wants to be done in the context of an update cursor rather than using the field calculator. One way to handle this would be to get your list of fields, create an update cursor that has all the fields referenced, then construct a list that contains all the values you are interested in comparing,then use max() to get the largest values from the list you created. Conceptually this might look something like this:
```
LAYERS = arcpy.GetParameterAsText(0)
SLOSHFILEDS = [f.name for f in arcpy.ListFields(LAYERS,"","DOUBLE") #presuming this filters your fields to the ones you want to compare
rows = arcpy.UpdateCursor(LAYERS) #leaving field names out of the cursor tool returns all fields
for row in rows:
    comp_values = []
    for fname in SLOSHFILEDS:
        comp_values.append(row.fname)
    max_val = max(comp_values)
    row.MAXSURGE = max_val
    rows.updateRow(row)
```
Like • Show 0 Likes0
Actions
jplay Aug 29, 2012 7:40 AM
Mark Correct
Correct Answer
mzcoyle and clthompson, Thanks for your suggestions. Not to discourage you from making additional cursor suggestions (I'll given them a go if you provide them), but I've never had much luck with search cursors and usually try to avoid them--I just can't seem to wrap my head around them. I always end up with an infinite loop or receive some kind of error.

I tried both of your suggestions and neither worked. With mzcoyle's I receive "exceptions.AttributeError: Cursor: Error in parsing arguments." With clthompson's I receive "exceptions.RuntimeError: Row: Field fname does not exist."

The bottom code I provided will run and populate the empty MAXSURGE field, however it populates the field with 0's after giving me "The calculate value is invalid for the row with ObjectID = 0..." error. If I try to concatenate the list object into a string value to add to my max() formula, e.g.:
```
SLOSHSTRING = str(fieldNameList)
calculation = "\"max(" + SLOSHSTRING + ")\""
arcpy.CalculateField_management (LAYERS, "MAXSURGE", calculation, "PYTHON", "")
```
I receive "The calculate value is invalid for the row" error. However if I hard code the max() calculation, e.g.:
```
arcpy.CalculateField_management (LAYERS, "MAXSURGE", "max( !SURGE90!, !SURGE90_1!, !SURGE90_12!, !SURGE90_13!, !SURGE90_14!, !MAXSURGE!)", "PYTHON", "")
```
it works just fine and fairly quickly on 18,000+ rows.

I guess I'm to figure out how to get python to think that it's getting
```
arcpy.CalculateField_management (LAYERS, "MAXSURGE", "max( !SURGE90!, !SURGE90_1!, !SURGE90_12!, !SURGE90_13!, !SURGE90_14!, !MAXSURGE!)", "PYTHON", "")
```
when it receives this
```
arcpy.CalculateField_management (LAYERS, "MAXSURGE", max({ListObject}), "PYTHON", "")
```
Suggestions? With this or with cursors.

Thanks.
Like • Show 0 Likes0
Actions
mzcoyle Aug 29, 2012 7:54 AM
Unmark Correct
Correct Answer
I had forgot to put in the row object in this line.
```
curs.updateRow(row)
```
That is most likely the issue. Cursors really are the best way to go about searching or updating data on a row by row basis.

Passing field objects to the calculate field tool as variables would require you to change them to strings from a list and add the ! to mark them as field variables to the field calculator. You'd have to do something funky like this I think.
```
field_str = "!" + "!, !".join(fieldNameList) + "!"
```
Like • Show 0 Likes0
Actions
jplay Aug 29, 2012 8:15 AM
Mark Correct
Correct Answer
THANK YOU mzcoyle! Both worked beautifully.
Like • Show 0 Likes0
Actions
clthompson Aug 29, 2012 8:38 AM
Mark Correct
Correct Answer
"exceptions.RuntimeError: Row: Field fname does not exist."

Like i said "conceptually" :) - without testing I wasn't sure how the value held by fname needs to be handled. Ultimately that may need to be converted to a string... it may not even be possible in that context. But its a good case to play with. Cursors are really fairly simple - they allow you to step row by row through a table of data, performing some action on each row and then moving on to the next, and then when the get to the end, they stop. Sounds like you found a solution though!
Like • Show 0 Likes0
Actions
jplay Aug 29, 2012 8:43 AM
Mark Correct
Correct Answer
Thank you clthompson. I appreciate your help. I'll give cursors another look-see, may this time they'll click.
Like • Show 0 Likes0
Actions
clthompson Aug 29, 2012 10:42 AM
Mark Correct
Correct Answer
Ok, this has got under my skin a little so I think here's a solution:
```
LAYERS = arcpy.GetParameterAsText(0)
SLOSHFILEDS = [f.name for f in arcpy.ListFields(LAYERS,"","DOUBLE") #presuming this filters your fields to the ones you want to compare
rows = arcpy.UpdateCursor(LAYERS) #leaving field names out of the cursor tool returns all fields
for row in rows:
    comp_values = []
    for fname in SLOSHFILEDS:
        nom = fname.name
        comp_values.append(row.getValue(nom))
    max_val = max(comp_values)
    row.MAXSURGE = max_val
    rows.updateRow(row)
```
Basically, the list of created as SLOSHFILEDS is a list of field objects, which have a variety of attributes including 'name' so using that as a value to pass into the getValue method you can populate your list of values for the row. The rest of the code works as described earlier. I like this approach because its simple and I'm lazy and don't like to write lots of lines of code. For loops and cursors FTW!
Like • Show 0 Likes0
Actions
jplay Aug 29, 2012 10:47 AM
Mark Correct
Correct Answer
Ok, this has got under my skin a little

Don't you hate when that happens? ;)

I'll give this a go as well.
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Help with ListFields and CalculateField_management syntax

Outcomes

Related Content

Recommended Content