topic Re: Using field lists in arcpy.da update cursor to calculate percentage in Python Questions

Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Tue, 23 Feb 2021 21:21:19 GMT

I'm trying to make some calculations using lists of fields. I need to make a list of the fields in a table, and then add them together as part of a percentage calculation. Here's my script, but I think my syntax is wrong. Help is appreciated!

# too many cropscape value fields. Use table field list to calculate percentages (hopefully). crop_table_all = r'E:\FATHOM\2020FATHOM\Analysis\MR_cropscape_2016_FPstats_02062021.gdb\Cropscape_MR_floodplain_statsALL' # List of all fields in the table cropfieldlist = [f.name for f in arcpy.ListFields(crop_table_all,"VALUE_*","")] print(cropfieldlist) #list of only the fields representing developed landcover. developedfieldlist =['Value_121','Value_122','Value_123!', '!Value_124!'] # add result field fieldName4 = "Pct_Disturbed_FATHOM" arcpy.AddField_management(crop_table_all, fieldName4, "DOUBLE") #Calculate percent developed with arcpy.da.UpdateCursor(crop_table_all, developedfieldlist, cropfieldlist,fieldName4) as cursor: for row in cursor: # Update the Pct_Area field to be the (Area_Sqm field / Shape_Area) *100 # ROAD_TYPE field. if row[1] != 0: row[3] = (sum[row [1]] / sum[row[2]]) * 100 cursor.updateRow(row) else: print (0)

Re: Using field lists in arcpy.da update cursor to calculate percentage

Anonymous User — Tue, 23 Feb 2021 21:31:06 GMT

You are passing three lists into the cursor without concatenating them into one, and its being assigned into wrong parameters

UpdateCursor (in_table, field_names, {where_clause}, {spatial_reference}, {explode_to_points}, {sql_clause}, {datum_transformation)

Create a single list of the fields and try that.

Re: Using field lists in arcpy.da update cursor to calculate percentage

BlakeTerhune — Tue, 23 Feb 2021 21:33:53 GMT

It would be helpful to mention any error messages you are receiving. If there are no errors, then what is the incorrect behavior you're seeing?

Re: Using field lists in arcpy.da update cursor to calculate percentage

DavidPike — Tue, 23 Feb 2021 21:36:27 GMT

you have a field name called

'!Value_124!'
? Might be right but thought it wasn't a valid 1st character.

I may be wrong but the syntax of the cursor seems way off. You can only specify one list of fields, then you're using cropfieldslist as an argument where the WHERE clause would be, then fieldname4 where the spatial reference would go. UpdateCursor—ArcGIS Pro | Documentation
can you elaborate more in the code comments or a as a reply on what you're expecting from the 2 input fields and fieldname4?

Also your else block is indented 1 indent to far (should be same indent as if block)

Also what is sum doing? there is a sum() method but you seem to be trying to index slice it, is sum a tuple or list you havent detailed?

Re: Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Wed, 24 Feb 2021 01:06:22 GMT

Thank you for the repoly! Whoops, yes, that is a stray exclamation point... I got confused because it isnt needed in the update cursor (but it is for calculate field which is why I was confused! Thank you for pointing that out).

I want to use an update cursor (or maybe calculate field is better? To essentially update fieldname4 with the percentage created by the sum of the fields in the developedfield list over the sum of the fields in the cropfieldlist, like this: (sum(developedfieldlist) / sum(cropfieldlist)) * 100....

Re: Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Wed, 24 Feb 2021 03:09:24 GMT

Thank you for this. How can I concatenate them into one, when I need to divide one list by the other?

Re: Using field lists in arcpy.da update cursor to calculate percentage

Anonymous User — Wed, 24 Feb 2021 03:54:05 GMT

There are several ways, and using what you have posted in the original message and substituting some pseudo values for the ListFields comprehension:

cropfieldlist = [f.name for f in arcpy.ListFields(crop_table_all,"VALUE_*","")] developedfieldlist =['Value_121', 'Value_122', 'Value_123', 'Value_124'] fieldName4 = ["Pct_Disturbed_FATHOM"] listForCursor = cropfieldlist + developedfieldlist + fieldName4

produces the concatenated list:

['croplist_1', 'croplist_2', 'croplist_3', 'croplist_4', 'Value_121', 'Value_122', 'Value_123', 'Value_124', 'Pct_Disturbed_FATHOM']

For your calculation, you can use the index from their position in the concatenated list used for your cursor. Step through with your debugger and adjust your calculation variables to use the field's index that you need to use.

row[6] = row[0]+row[1]+row[2] ... / row[3]+row[4]+row[5]...

Re: Using field lists in arcpy.da update cursor to calculate percentage

DavidPike — Wed, 24 Feb 2021 14:44:55 GMT

If this is just one feature class and you know all the fields, I would just say do it in field calculator

Re: Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Wed, 24 Feb 2021 15:54:04 GMT

When I attempt it in field calculator, here's the error I get....

crop_table_all = r'E:\FATHOM\2020FATHOM\Analysis\MR_cropscape_2016_FPstats_02062021.gdb\Cropscape_MR_floodplain_statsALL' cropfieldlist = [f.name for f in arcpy.ListFields(crop_table_all,"VALUE_*","")] print(cropfieldlist) developedfieldlist =['VALUE_121','VALUE_122','VALUE_123', 'VALUE_124'] fieldName4 = "Pct_Disturbed_FATHOM" arcpy.AddField_management(crop_table_all, fieldName4, "DOUBLE") expression4 = """sum(developedfieldlist) / sum(cropfieldlist) *100""" arcpy.CalculateField_management(crop_table_all, fieldName4, expression4, "PYTHON_9.3")

Traceback (most recent call last):
File "<string>", line 1, in <module>
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 5209, in CalculateField
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 5206, in CalculateField
retval = convertArcObjectToPythonObject(gp.CalculateField_management(*gp_fixargs((in_table, field, expression, expression_type, code_block, field_type), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 511, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000539: Traceback (most recent call last):
File "<expression>", line 1, in <module>
TypeError: unsupported operand type(s) for +: 'int' and 'str'

Failed to execute (CalculateField).

Re: Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Wed, 24 Feb 2021 23:16:29 GMT

Thank you, however, since there are over 100 fields, I want to avoid typing row[1] - row[100], is it possible to do that? That was why I wanted to make a list to use in the first place.

Re: Using field lists in arcpy.da update cursor to calculate percentage

Anonymous User — Wed, 24 Feb 2021 23:30:39 GMT

The error message is saying there is a TypeError and that it cannot add ints and strings. You may have some fields that are strings so you will have to figure a way to convert them to ints in order to perform the sum operation.

Re: Using field lists in arcpy.da update cursor to calculate percentage

Anonymous User — Thu, 25 Feb 2021 00:08:22 GMT

At this point, I’d say calculate field would be a better option. For the sake of dynamically creating rows[], you can concatenate the fieldlist so that the target field is in the first index. Then you can set up an iterative range user the for row in cursor: line.

for i in range(1, len(your fieldlist):

to create the index starting at 1 and ending at the length of how many fields you have. Then you append that int(row[i]) *from your type error I think you have strings in your fields so convert to int* to a list of

fieldvalueslist.append(int(row[i]))

which you can then use for your calculations. row[0] = sum(fieldvalueslist)

there are better methods to achieve what you need though. See the comment on your type error that you posted and see if you can find where the strings are.

Re: Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Thu, 25 Feb 2021 02:35:10 GMT

Thank you, I think I fixed that by removing the one text field from the list. I now get the error:

Re: Using field lists in arcpy.da update cursor to calculate percentage

Anonymous User — Thu, 25 Feb 2021 03:01:03 GMT

Reading through the documentation for the calculatefield, it says that if you are using python as the expression type you need to denote the fields as '!fieldname!'. I also think the 'Python_9.3' should just be 'PYTHON3'.

To get your fields formatted correctly, you can add the ! to the list of fields that you typed out, and you can do

[f'!{f.name}!' for f in arcpy.ListFields(crop_table_all,"VALUE_*","")]

for the dynamically created list.

Re: Using field lists in arcpy.da update cursor to calculate percentage

KathleenHoenke — Mon, 01 Mar 2021 15:25:31 GMT

Thank you! I ended up doing something a little different because even when I changed my fieldnames to be the appropriate format, it was throwing an error.

Here's what I came up with:

# Calculate sum field for sum of all values crop_table_all = r'E:\FATHOM\2020FATHOM\Analysis\MR_cropscape_2016_FPstats_02062021.gdb\Cropscape_MR_floodplain_statsALL' cropfieldlist = [f.name for f in arcpy.ListFields(crop_table_all,"VALUE_*","")] print (cropfieldlist) fieldName1 = "VALUE_SUM" arcpy.AddField_management(crop_table_all, fieldName1, "DOUBLE","", "", "", "", "") with arcpy.da.UpdateCursor(crop_table_all, cropfieldlist) as cursor: for row in cursor: row[-1] = sum(row[:-2]) cursor.updateRow(row) fieldName5 = "Pct_Ag_Cropscape_FATHOM" fieldName2 = "Pct_AgPlus_FATHOM" fieldName3 = "Pct_Natural_FATHOM" fieldName4 = "Pct_Developed_FATHOM" fieldName6 = "Pct_Disturbed_FATHOM" arcpy.AddField_management(crop_table_all, fieldName5, "DOUBLE","", "", "", "", "") arcpy.AddField_management(crop_table_all, fieldName2, "DOUBLE","", "", "", "", "") arcpy.AddField_management(crop_table_all, fieldName3, "DOUBLE","", "", "", "", "") arcpy.AddField_management(crop_table_all, fieldName4, "DOUBLE","", "", "", "", "") arcpy.AddField_management(crop_table_all, fieldName6, "DOUBLE","", "", "", "", "") expression2 = "((!Value_1! + !Value_2! + !Value_3! + !Value_5! + !Value_12! + !Value_13! + !Value_42! + !Value_26! + !Value_52! + !Value_225! + !Value_226! + !Value_228! + !Value_232! + !Value_237! + !Value_238! + !Value_239! + !Value_240! +!Value_241! + !Value_254!) / (!VALUE_sum!)) *100" expression3 = "(( !Value_111!+ !Value_131!+ !Value_141!+ !Value_142!+ !Value_143!+ !Value_152!+ !Value_190!+ !Value_195!) / (!VALUE_sum!)) *100" expression4 = "((!VALUE_121!+!VALUE_122!+!VALUE_123!+ !VALUE_124!) / (!VALUE_sum!)) *100" expression5 = "100 - (!Pct_Natural_FATHOM! + !Pct_Disturbed_FATHOM!)" expression6 = "(!Pct_Ag_Cropscape_FATHOM! + !Pct_Developed_FATHOM!)" arcpy.CalculateField_management(crop_table_all, fieldName5, expression5, "PYTHON_9.3") arcpy.CalculateField_management(crop_table_all, fieldName2, expression2, "PYTHON_9.3") arcpy.CalculateField_management(crop_table_all, fieldName3, expression3, "PYTHON_9.3") arcpy.CalculateField_management(crop_table_all, fieldName4, expression4, "PYTHON_9.3") arcpy.CalculateField_management(crop_table_all, fieldName6, expression6, "PYTHON_9.3")