topic Re: Finding sum of values in field in Python Questions

Finding sum of values in field

StephenFricke — Tue, 11 Oct 2011 19:46:48 GMT

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!

Re: Finding sum of values in field

RichardFairhurst — Tue, 11 Oct 2011 21:56:03 GMT

You could use the Summarize tool without a case field and sum the population. You could add a temporary field to both tables with a dummy value like 1 and join the result to your original layer on that field. Then you can calculate over the resulting summed value to all of the records or get percentages. Or else add the dummy field to your original layer first and use that as the Case field for the summary.

A cursor in python could also do the sum and write the result, but not the field calculator.

Re: Finding sum of values in field

StephenFricke — Tue, 11 Oct 2011 22:36:30 GMT

Could you tell me what the syntax would be for finding the sum of a field with a cursor?

Re: Finding sum of values in field

JakeSkinner — Sat, 11 Dec 2021 15:31:02 GMT

Hi Peter,

Here is an example on how to do this. The below code will append all the rows from the population field to a list using a search cursor, then it will sum the list.

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

fc = "Cities"

list = []

rows = arcpy.SearchCursor(fc)
for row in rows:
    pop = row.getValue("POPULATION")
    list.append(pop)

print sum(list)

Re: Finding sum of values in field

StephenFricke — Sat, 11 Dec 2021 15:31:04 GMT

Hey Jake, thanks! That was a lot of help. I am still getting an error when I run my script saying <type 'exceptions.TypeError'>: coercing to Unicode: need string or buffer, int found. Could you please take a look at the script and let me know what the error might be?

inWorkspace = "C:/TEMP/Tooldata/Florida.gdb"
arcpy.env.workspace = inWorkspace
inPoly = "C:/TEMP/Tooldata/Florida.gdb/Counties"
inPoint = "C:/TEMP/Tooldata/Florida.gdb/Cities"
destField = "URBPOP"
msg = "Workspace is " + arcpy.env.workspace
arcpy.AddMessage(msg)
fcList = arcpy.ListFeatureClasses()
msg = "\nFeature classes in " + inWorkspace + ":"
arcpy.AddMessage(msg)
for anFC in fcList:
    msg = " - " + anFC
    arcpy.AddMessage(msg)
fldList = arcpy.ListFields(inPoly)
msg = "\nFields in " + inPoly + ":"
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(inPoly,destField,"DOUBLE")
    msg = "\nNew Field " + destField + " created in " + inPoly
    arcpy.AddMessage(msg)
arcpy.MakeFeatureLayer_management(inPoly,"allPolys")
arcpy.MakeFeatureLayer_management(inPoint,"allPoints")
polyRows = None
polyRow = None
polyRows = arcpy.UpdateCursor(inPoly)
msg = '\nUpdate cursor created for ' + inPoly + '. Records are:'
arcpy.AddMessage(msg)
for polyRow in polyRows:
    firstPart = '"NAME_1" ='
    lastPart = "'" + '' + "'"
    where_clause = firstPart + lastPart
    arcpy.MakeFeatureLayer_management(inPoly,"oneCounty",where_clause)
    arcpy.SelectLayerByLocation_management("allPoints", "WITHIN", "oneCounty")
    list= []
    fc = "allPoints"
    rows = arcpy.SearchCursor(fc)
    for row in rows:
        pop = row.getValue("POP_98")
        list.append(pop)
    msg = " - " + polyRow.NAME_1 + " - " + "Urban Population: " + sum(list)
    UrbanPop= sum(list)
    arcpy.AddMessage(msg)
    polyRow.URBPOP = int(UrbanPop)
    arcpy.Delete_management("oneCounty")

if polyRow:
    del polyRow
if polyRows:
    del polyRows
arcpy.Delete_management("allPolys")
arcpy.Delete_management("allPoints")

The first part of the script is not important, the last part where I am trying to sum the population of cities for each county is the part that I am struggling with. Let me know if you see what I'm doing wrong. Thanks!

Re: Finding sum of values in field

MathewCoyle — Wed, 12 Oct 2011 17:10:49 GMT

This line is probably the problem.

msg = " - " + polyRow.NAME_1 + " - " + "Urban Population: " + sum(list)

You can't concatenate strings and ints. The blow line should fix it.

msg = " - " + polyRow.NAME_1 + " - " + "Urban Population: " + str(sum(list))

I didn't see anywhere else where that error is made, but you'd need to change it if you are putting together strings and numeric types anywhere else as well.

Re: Finding sum of values in field

JakeSkinner — Sat, 11 Dec 2021 15:31:07 GMT

I believe you will also need to add another line for the update to occur.

for polyRow in polyRows:
    firstPart = '"NAME_1" ='
    lastPart = "'" + '' + "'"
    where_clause = firstPart + lastPart
    arcpy.MakeFeatureLayer_management(inPoly,"oneCounty",where_clause)
    arcpy.SelectLayerByLocation_management("allPoints", "WITHIN", "oneCounty")
    list= []
    fc = "allPoints"
    rows = arcpy.SearchCursor(fc)
    for row in rows:
        pop = row.getValue("POP_98")
        list.append(pop)
    msg = " - " + polyRow.NAME_1 + " - " + "Urban Population: " + sum(list)
    UrbanPop= sum(list)
    arcpy.AddMessage(msg)
    polyRow.URBPOP = int(UrbanPop)
    polyRows.updateRow(polyRow)
    arcpy.Delete_management("oneCounty")

Re: Finding sum of values in field

DarrenWiens2 — Sat, 11 Dec 2021 15:31:09 GMT

Along different lines, you can do this in the field calculator.

expression:

func()

codeblock:

def func():
    sum = 0
    fc = "H:/GIS_Data/TEMP.gdb/points" # the path to your feature class

    rows = arcpy.SearchCursor(fc)

    for row in rows:
        sum = row.THEFIELDTOSUM + sum # enter the fieldname in this line
    
    return sum

Re: Finding sum of values in field

StephenFricke — Sat, 11 Dec 2021 15:31:12 GMT

Hey thanks for the help everyone. I am getting my script to run but when it runs the urban population for every county is being printed as 0. Can anyone tell me why this would be with the way the script currently is? I also tried the way dkwiens suggested and I am also getting zero for the Urban population in each county.

inWorkspace = "C:/TEMP/Tooldata/Florida.gdb"
arcpy.env.workspace = inWorkspace
inPoly = "C:/TEMP/Tooldata/Florida.gdb/Counties"
inPoint = "C:/TEMP/Tooldata/Florida.gdb/Cities"
destField = "URBPOP"

fieldPresent = False
for aFld in fldList:
    if aFld.name == destField:
        fieldPresent = True
if not fieldPresent:
    arcpy.AddField_management(inPoly,destField,"DOUBLE")
    msg = "\nNew Field " + destField + " created in " + inPoly
    arcpy.AddMessage(msg)
arcpy.MakeFeatureLayer_management(inPoly,"allPolys")
arcpy.MakeFeatureLayer_management(inPoint,"allPoints")
polyRows = None
polyRow = None
polyRows = arcpy.UpdateCursor(inPoly)
msg = '\nUpdate cursor created for ' + inPoly + '. Records are:'
arcpy.AddMessage(msg)
for polyRow in polyRows:
    firstPart = '"NAME_1" ='
    lastPart = "'" + '' + "'"
    where_clause = firstPart + lastPart
    arcpy.MakeFeatureLayer_management(inPoly,"oneCounty",where_clause)
    targetCities = arcpy.SelectLayerByLocation_management("allPoints", "WITHIN", "oneCounty")
    list= []
    rows = arcpy.SearchCursor(targetCities)
    for row in rows:
        pop = row.getValue("POP_98")
        list.append(pop)
    msg = " - " + polyRow.NAME_1 + " - " + "Urban Population: " + str(sum(list))
    arcpy.AddMessage(msg)
    UrbanPop= sum(list)
    polyRow.URBPOP = int(UrbanPop)
    polyRows.updateRow(polyRow)
    arcpy.Delete_management("oneCounty")

if polyRow:
    del polyRow
if polyRows:
    del polyRows
arcpy.Delete_management("allPolys")
arcpy.Delete_management("allPoints")

Re: Finding sum of values in field

JakeSkinner — Sat, 11 Dec 2021 15:31:15 GMT

From the looks of your code I believe you are trying to select all cities within a county, sum the population of the cities and update a field in the counties feature class with this value. You can easily do this by creating a spatial join. You can right-click on the counties feature class in ArcMap's table of contents > Joins and Relates > Joins. Specify to join attributes spatially and choose to sum the fields.

This will produce a new output feature class with the summed population (and summed outputs of all other numeric fields) of all cities within each county. If you do not want a new feature class, and the other summed numeric fields, you can use python. Here is the code that I was able to get to work:

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

cities = "Cities"
counties = "Counties"

arcpy.MakeFeatureLayer_management(cities, "cities_feat")

list = []

# Get max OBJECTID for loop
rows = arcpy.SearchCursor(counties)
for row in rows:
    OID = row.getValue("OBJECTID")
    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)
    x += 1

del row, rows, row2, rows2

Re: Finding sum of values in field

MarcNakleh — Fri, 14 Oct 2011 13:30:15 GMT

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

Re: Finding sum of values in field

MarcNakleh — Sat, 11 Dec 2021 15:31:17 GMT

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!

Re: Finding sum of values in field

StephenFricke — Sat, 11 Dec 2021 15:31:20 GMT

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

Re: Finding sum of values in field

JakeSkinner — Sat, 11 Dec 2021 15:31:22 GMT

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

Re: Finding sum of values in field

StephenFricke — Sat, 11 Dec 2021 15:31:25 GMT

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

Re: Finding sum of values in field

StephenFricke — Sun, 16 Oct 2011 03:59:51 GMT

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

Re: Finding sum of values in field

JakeSkinner — Sat, 11 Dec 2021 15:31:27 GMT

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.

Re: Finding sum of values in field

AlanKilgore — Fri, 29 Jan 2016 01:09:57 GMT

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!

Re: Finding sum of values in field

OLANIYANOLAKUNLE — Sat, 11 Dec 2021 15:31:30 GMT

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