[GIS] How to SUM field, insert SUM into new field based on FIELD

arcgis-10.2arcgis-desktoparcpypython-2.7

This is a continuation of my previous question in an effort to build upon it.

How to SUM field and then insert into NULL column?

Given the examples of my data below, I would like to be able to SUM a column (QUANTITY_SOLID) based upon a field, such as 'SEGMENT_LENGTH', and have the cursor write the SUM into a new field pertaining only to that field that it belongs to. So, in my example, this would be useful because the amount of 'QUANTITY_SOLID' material being disseminated onto a road is broken up into 'SEGMENT_LENGTH' which is a road segment. My previous question allowed for me to calculate the total of 'QUANTITY_SOLID' but it was for all roads (or all roads that were in my specific query). In order to do some GIS analysis and show which roads received heavier application and overall larger quantity total, I want there to be a separate SUM for each 'SEGMENT_LENGTH'.

In general, I am not looking for someone to write the code for me or to necessarily solve this specific issue. My larger question, so I can learn for future coding, is how do I go about coding some sort of 'WHERE' clause in Python where I could be more flexible with my current script?

Thanks in advance for any help!

This is my previous code (Thanks to Aaron):

import arcpy

# Define the feature class
fc = r'C:\path\to\your\fc'

# Use a generator expression to populate a list from the 'QUANTITY_SOLID' field
b = sum(row[0] for row in arcpy.da.SearchCursor(fc, 'QUANTITY_SOLID'))

with arcpy.da.UpdateCursor(fc, ['QUANTITY_SOLID_SUM']) as cursor:
    for row in cursor:
        row[0] = b
        cursor.updateRow(row)

QUANTITY_SOLID
SEGMENT_LENGTH
SUM_Quantity_Solid

Best Answer

Building on what you've got already:

import arcpy

# Define the feature class
fc = r'C:\path\to\your\fc'

# find the unique 'SEGMENT_LENGTH' values
Slist = list()
for row in arcpy.da.SearchCursor(fc, 'SEGMENT_LENGTH'):
    # if the value isn't in the list then add it to the list
    if not row[0] in Slist:
        Slist.append(row[0])

for Value in Slist:
    # definition query to limit the rows in the cursor
    DefQ = 'SEGMENT_LENGTH = ' + str(Value)

    # Use a generator expression to populate a list from the 'QUANTITY_SOLID' field
    b = sum(row[0] for row in arcpy.da.SearchCursor(fc, 'QUANTITY_SOLID'),DefQ)

    with arcpy.da.UpdateCursor(fc, ['QUANTITY_SOLID_SUM'],DefQ) as cursor:
        for row in cursor:
            row[0] = b
            cursor.updateRow(row)

I am first getting a list with each unique value present in the SEGMENT_LENGTH field, note it works better if they are rounded to only a few decimal places or even better as an integer/string field as differences in the infinitesimal decimal place will make two values that are very similar unequal. To do this I'm using the not Value in list operator (very handy!) which returns true if the value is found in the list and false if the value is not present in the list, then append to the list which builds it up as you go - I start with an empty list then add each value in turn.

Then iterating over each value in the list using for Value in Slist: to build a definition query which I can use as the where_clause property of the arcpy.da.SearchCursor to limit the returned rows to just the ones that satisfy the query.

Related Question