Finding sum of values in field

StephenFricke · ‎10-11-2011

I have a cities feature class with a field for the Population in the attribute table. I want to find the sum of all the values in the Population field which will give me the total population of all the cities. I think I might have to use the arcpy.CalculateField_management function but I am confused on the syntax I should be using, or maybe there is a different way I should be writing the script. Any help would be much appreciated!

MarcNakleh · ‎10-14-2011

import arcpy
arcpy.env.workspace = r"C:\temp\python\test.gdb"
arcpy.env.overwriteOutput = True

cities = "Cities"
counties = "Counties"
city_layer = "cities_feat"
county_layer = "counties_feat"

arcpy.MakeFeatureLayer_management(cities, city_layer)
for row in arcpy.SearchCursor(counties):
    county_OID = row.OBJECTID
    arcpy.MakeFeatureLayer_management(counties, county_layer,
                                      'OBJECTID = ' + str(county_OID))
    arcpy.SelectLayerByLocation_management(city_layer, 'WITHIN', county_layer)

    list2 = [r.POPULATION for r in arcpy.SearchCursor(city_layer)]
    sumlist = sum(list2)

    county_rows = arcpy.UpdateCursor("counties_feat")
    for layer_row in county_rows:
        layer_row.setValue('URBPOP', sumlist)
        county_rows.updateRow(row2)

del county_rows, row, layer_row

MarcNakleh · ‎10-14-2011

Hey Jake,
Right on! Spatial Join would've been what I'd recommend, though the Python code to do the same work isn't too long and exposes people to a lot of the ideas behind cursors and layers.

As a point of information, you could probably speed up this code by using a couple of nifty tricks:

In general, list comprehensions are much faster than iterated item appends. There's always the risk of things getting messy, but I think your code is a great example of a case where it would be easy to slide it in.

You seem to take the max OID and then just iterate for all numbers between it. You could make that code faster by only iterating through the items of your first list (for valid_OID in list:) or just iterating through the rows of the full feature class directly.

import arcpy
arcpy.env.workspace = r"C:\temp\python\test.gdb"
arcpy.env.overwriteOutput = True

cities = "Cities"
counties = "Counties"
city_layer = "cities_feat"
county_layer = "counties_feat"

arcpy.MakeFeatureLayer_management(cities, city_layer)

for row in arcpy.SearchCursor(counties):
    county_OID = row.OBJECTID
    arcpy.MakeFeatureLayer_management(counties, county_layer, 'OBJECTID = ' + str(county_OID))
    arcpy.SelectLayerByLocation_management(city_layer, 'WITHIN', county_layer)
    
    list2 = [r.POPULATION for r in arcpy.SearchCursor(city_layer)]    
    sumlist = sum(list2)
    
    county_rows = arcpy.UpdateCursor(county_layer )
    for layer_row in county_rows:
        layer_row.URBPOP = sumlist
        county_rows.updateRow(row2)

del county_rows, row, layer_row

I hope this is of use!

StephenFricke · ‎10-14-2011

Hey thank you all very much for your help! I have written the script like JSkinn suggested and it worked great. The only problem is at the end when I say to delete row and row2 there is an error because they aren't defined. The exact error message is: <type 'exceptions.NameError'>: name 'row2' is not defined
Failed to execute ().
Anyone know how I can fix this?

import arcpy
from arcpy import env
cities = "Cities"
counties = "Counties"
destField= "URBPOP"
env.workspace = "C:\TEMP\Tooldata\Florida.gdb"
env.overwriteOutput = True
fldList = arcpy.ListFields(counties)
msg = "\nFields in " + counties + ":"
arcpy.AddMessage(msg)
for aFld in fldList:
    msg = " - " + aFld.name
    arcpy.AddMessage(msg)
fieldPresent = False
for aFld in fldList:
    if aFld.name == destField:
        fieldPresent = True
if not fieldPresent:
    arcpy.AddField_management(counties,destField,"DOUBLE")
    msg = "\nNew Field " + destField + " created in " + counties
    arcpy.AddMessage(msg)

arcpy.MakeFeatureLayer_management(cities, "cities_feat")

list = []

rows = arcpy.SearchCursor("Counties")

for row in rows:
    OID = row.getValue("NAME_1")
    list.append(OID)

maxOID = list[-1]

del row, rows

x = 1
while x <= maxOID:
    list2 = []
    arcpy.MakeFeatureLayer_management(counties, "counties_feat", "OBJECTID = " + str(x))
    arcpy.SelectLayerByLocation_management("cities_feat", "WITHIN", "counties_feat")
    rows = arcpy.SearchCursor("cities_feat")
    for row in rows:
        pop = row.getValue("POP_98")
        list2.append(pop)
    sumlist = sum(list2)
    rows2 = arcpy.UpdateCursor("counties_feat")
    for row2 in rows2:
        row2.URBPOP = sumlist
        rows2.updateRow(row2)
    
    msg = " - " + row2.NAME_1 + " - " + "Urban Population: " + str(sumlist)
    arcpy.AddMessage(msg)
    x += 1
    del row, rows, row2, rows2
arcpy.Delete_management("counties_feat")
arcpy.Delete_management("cities_feat")

JakeSkinner · ‎10-14-2011

Try placing the syntax to delete the cursors outside of the while loop:

while x <= maxOID:
    list2 = []
    arcpy.MakeFeatureLayer_management(counties, "counties_feat", "OBJECTID = " + str(x))
    arcpy.SelectLayerByLocation_management("cities_feat", "WITHIN", "counties_feat")
    rows = arcpy.SearchCursor("cities_feat")
    for row in rows:
        pop = row.getValue("POP_98")
        list2.append(pop)
    sumlist = sum(list2)
    rows2 = arcpy.UpdateCursor("counties_feat")
    for row2 in rows2:
        row2.URBPOP = sumlist
        rows2.updateRow(row2)
    
    msg = " - " + row2.NAME_1 + " - " + "Urban Population: " + str(sumlist)
    arcpy.AddMessage(msg)
    x += 1
del row, rows, row2, rows2
arcpy.Delete_management("counties_feat")
arcpy.Delete_management("cities_feat")

StephenFricke · ‎10-14-2011

Thanks for the suggestion JSkinn, I have tried as you suggested also and when I do this the loop doesn't stop, and continually gives the last county an Urban Pop value or zero over and over....

Try placing the syntax to delete the cursors outside of the while loop:

while x <= maxOID:
    list2 = []
    arcpy.MakeFeatureLayer_management(counties, "counties_feat", "OBJECTID = " + str(x))
    arcpy.SelectLayerByLocation_management("cities_feat", "WITHIN", "counties_feat")
    rows = arcpy.SearchCursor("cities_feat")
    for row in rows:
        pop = row.getValue("POP_98")
        list2.append(pop)
    sumlist = sum(list2)
    rows2 = arcpy.UpdateCursor("counties_feat")
    for row2 in rows2:
        row2.URBPOP = sumlist
        rows2.updateRow(row2)
    
    msg = " - " + row2.NAME_1 + " - " + "Urban Population: " + str(sumlist)
    arcpy.AddMessage(msg)
    x += 1
del row, rows, row2, rows2
arcpy.Delete_management("counties_feat")
arcpy.Delete_management("cities_feat")

StephenFricke · ‎10-15-2011

Does anyone have any suggestions on how I should correct the script? Thank you.

JakeSkinner · ‎10-17-2011

I could not reproduce the behavior you are experiencing. Placing this syntax outside the loop created no errors, and the script executed successfully. I'm not sure why the script is running in a continuous loop for you.

If I placed the 'del row, rows....' inside the while loop I would receive "NameError: name 'row' is not defined" error message.

If the script is executing successfully with this error message, you can run an except to pass this error within the while loop:

try:
  del row, rows, row2, rows2
except NameError:
   pass

Though, this may cause the script to skip over some counties and not update the 'URBPOP' field correctly.

AlanKilgore · ‎01-28-2016

I ran into a situation where the results of an UpdateCursor was empty. After a "for row in rows:" loop, the "del row" generated and error and prevents the script from finishing. I tried using the None object without sucess, i.e. "if row == None: del row". I'm pretty basic when it comes to python, so this try statement really helped me out. Thanks!

OLANIYANOLAKUNLE · ‎04-13-2016

I need to automatically calculate a field based on values from previous rows?

I need to automatically calculate a field based on values from previous rows such that once the total of all the previous rows is greater than 450 and less than 500, the next row starts

counting from 0 or the balance of what made the previous row greater than 500.

The script below just calculates the sum of the cities point that falls within the county, I want something that would make the value of a row (i.e. row2) the addition of the value of row1 + value of row2 and so on till when it gets to 500, where it starts from 1 again. Kindly help me please.

cities = "Cities"  
        counties = "Counties"  
        destField= "URBPOP"  
        env.workspace = "C:\Users\KUNLE\Documents\ArcGIS\Default.gdb"  
        env.overwriteOutput = True  
        fldList = arcpy.ListFields(counties)  
        msg = "\nFields in " + counties + ":"  
        arcpy.AddMessage(msg)  
        for aFld in fldList:  
            msg = " - " + aFld.name  
            arcpy.AddMessage(msg)  
        fieldPresent = False  
        for aFld in fldList:  
            if aFld.name == destField:  
                fieldPresent = True  
        if not fieldPresent:  
            arcpy.AddField_management(counties,destField,"DOUBLE")  
            msg = "\nNew Field " + destField + " created in " + counties  
            arcpy.AddMessage(msg)  

        arcpy.MakeFeatureLayer_management(cities, "cities_feat")  
  
        list = []  
  
        rows = arcpy.SearchCursor("Counties")  
  
        for row in rows:  
            OID = row.getValue("POP")  
            list.append(OID)  
  
        maxOID = list[-1]  
  
        del row, rows  

        x = 1  
        while x <= maxOID:  
            list2 = []  
            arcpy.MakeFeatureLayer_management(counties, "counties_feat", "OBJECTID = " + str(x))  
            arcpy.SelectLayerByLocation_management("cities_feat", "WITHIN", "counties_feat")  
            rows = arcpy.SearchCursor("cities_feat")  
            for row in rows:  
                pop = row.getValue("POPULATION")  
                list2.append(pop)  
            sumlist = sum(list2)  
            rows2 = arcpy.UpdateCursor("counties_feat")  
            for row2 in rows2:  
                row2.URBPOP = sumlist  
                rows2.updateRow(row2)  
      
            #msg = " - " + row2.POP + " - " + "Urban Population: " + str(sumlist)  
            #arcpy.AddMessage(msg)  
            x += 1  
        del row, rows, row2, rows2  
        arcpy.Delete_management("counties_feat")  
        arcpy.Delete_management("cities_feat")