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

[KathleenHoenke]

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)
    

[KathleenHoenke]

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).

[Anonymous User]

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.

[KathleenHoenke]

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

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))
RuntimeError: Object: Error in executing tool

[Anonymous User]

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.

[KathleenHoenke · Accepted solution]

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")