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

1424
14
Jump to solution
02-23-2021 01:21 PM
KathleenHoenke
Occasional Contributor

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)
    

 

0 Kudos
1 Solution

Accepted Solutions
KathleenHoenke
Occasional Contributor

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

 

 

View solution in original post

0 Kudos
14 Replies
JeffK
by MVP Regular Contributor
MVP Regular Contributor

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.

 

KathleenHoenke
Occasional Contributor

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

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

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

 

0 Kudos
KathleenHoenke
Occasional Contributor

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.

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor
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.
0 Kudos
BlakeTerhune
MVP Regular Contributor

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?

DavidPike
MVP Frequent Contributor

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?

KathleenHoenke
Occasional Contributor

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

0 Kudos
DavidPike
MVP Frequent Contributor

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

0 Kudos