2

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

8
  • It's the same as before, just change the field names 'QUANTITY_SOLID' to 'SEGMENT_LENGTH' and 'QUANTITY_SOLID_SUM' to 'SEGMENT_LENGTH_SUM' (add new field for that perhaps). Cursors support definition queries which is covered by the documentation (google ArcGis Search Cursor) which can be used to refine the results. A word of caution: if you are running the model in ArcMap it will only use what is selected in ArcMap, so be sure to either have nothing selected or be sure your selection is what you want. Aug 11, 2014 at 22:20
  • @ Michael - I understand, but what I'm trying to do is have the 'SUM_Quantity_Solid' field be populated with the different SUMs of 'QUANTITY_SOLID' based on the distinct 'SEGMENT_LENGTH' Aug 11, 2014 at 22:22
  • Agreed Ian. JumpInTheFire, you are aware of the summary statistics tool aren't you? Essentially you want to sum the QUANTITY_SOLD for each unique SEGMENT_LENGTH is that right? Aug 11, 2014 at 22:24
  • 1
    I can't help feeling that you're re-inventing the wheel, but I can see you're using this as a learning experience. You want to go through first and find each unique SEGMENT_LENGTH and put into a list then iterate over the list (for Value in list:) and create a search/update cursor for each value in the list and sum that. Have a go and see what you come up with. Aug 11, 2014 at 22:27
  • 1
    We're here to be bothered by questions JumpInTheFire. I mentioned the geoprocessing tool Summary Statistics as I (and quite a few others) will always strive for the simplest solution, being a single tool to do the work. Python is a fairly easy language but in some cases it's counter-intuitive; a little help can go a long way in understanding how python does things. Good luck with your learning and if you get stuck don't hesitate to post, we're here to help! Aug 14, 2014 at 21:15

1 Answer 1

3

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.

5
  • 1
    Why not just use Slist = set(row[0] for row in arcpy.da.SearchCursor(fc, 'SEGMENT_LENGTH'))? There shouldn't be a need to check each time if the value is already in the list. Dump everything into the list and remove duplicates afterwards.
    – Paul
    Aug 11, 2014 at 23:49
  • Six of one, half dozen of the other @paul. I do it that way which may be less efficient than your method but is (in my opinion) less cryptic for a beginner. I would like to see that method expanded, perhaps you could put that in as an answer. Aug 11, 2014 at 23:57
  • I appreciate the help from both of you. I definitely agree with Michael that writing something a little less opaque is good for me, for the time being. I do certainly though (eventually), want to understand how to do so in the most efficient way. Aug 12, 2014 at 19:20
  • @ Michael - I just had the chance to try the code and received this error: SyntaxError: Generator expression must be parenthesized if not sole argument and once (I believe) I have fixed the parenthesis placement, I receive TypeError: value #0 - unsupported type: tuple any ideas? Aug 12, 2014 at 21:05
  • Nevermind, I was able to figure it out. Thanks again for the help! Aug 13, 2014 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.