Best way to add values to a string for UpdateCursor

[JamesWilcox1970]

I am still really new to Python and ArcPy. We have a pair of feature classes, one with an index grid and the other with project locations. We are trying to create a table that shows what index grid cells each project intersects.

I have a script that performs Intersect on the two feature classes, creates a new table (indexTable) , adds the two needed fields: Label (the project number) and PageName (the index grid), and populates Label using an InsertCursor. I know that I eventually need to use an UpdateCursor with an update string to update PageName, but I am at a loss on how to properly do it, since some of the projects intersect more than one grid cell. 

Currently, I have a SearchCursor to get the data from the Intersect feature class, but it breaks with "SystemError: error return without exception set".

# UpdateCursor to add all index grid cells for each label
gridList = [r for r in arcpy.da.SearchCursor(intersectOut, ("Label", "PageName"))]  # List from intersection
arcpy.AddMessage("Grid list = {}".format(gridList))  # For debugging
with arcpy.da.UpdateCursor(indexTable, ("Label", "PageName")) as uCursor:
    for row in uCursor:
        name_field = "Label"
        expression = u'{} = {}'.format(arcpy.AddFieldDelimiters(intersectOut, name_field), row[0])
        # expression = "Label = '" + row[0] + "'"
        with arcpy.da.SearchCursor(intersectOut, ("Label", "PageName"), where_clause=expression) as sCursor:
            gridString = ""
            if gridString == "":
                gridString = gridString + sCursor[1]
            else:
                gridString = gridString + ", " + sCursor[1]

        arcpy.AddMessage(gridString)
    # Before going to the next record in the site index table write this list to the appropriate field.
        row[1] = gridString
        uCursor.updateRow(row)

I also tried using list comprehension to go through gridList, based on row[0] of the UpdateCursor, but that just returns a series of empty lists--and I was unsure where to go from there in any case.

# UpdateCursor to add all index grid cells for each label
gridList = [r for r in arcpy.da.SearchCursor(intersectOut, ("Label", "PageName"))]  # List from intersection
arcpy.AddMessage("Grid list = {}".format(gridList))  # For debugging
with arcpy.da.UpdateCursor(indexTable, ("Label", "PageName")) as uCursor:
    for row in uCursor:
        search = row[0]
        arcpy.AddMessage(search)
        filterList = [r for r in gridList if gridList[0] == search]

I know there has to be a way to do this, but I am at a loss.

[JamesWilcox1970]

I thought I had mentioned it in the original post, but I had not. 🙂 I have a feature class generated by an Intersect between Projects and Index Grids. Many of the projects intersect multiple grids, so I have multiple records in the feature class for a single project, each with a different index grid cell. I am trying to programmatically populate a field in an output table with all the index grids that a particular project intersects. The result I am aiming for is "Project: Foo, Index Grid: A1, A2, B2; Project: Bar, Index Grid: A3, B1, C5."

So I am looking for a way to loop through the projects in the output table, find each index grid that matches that project in the Intersect feature class, write it to a string, and use that string in an UpdateCursor to update the index grid field (PageName) in the output table. 

I'm sorry if I was unclear in my original post. Thanks for looking at this problem.

[BlakeTerhune]

I may be mixing up projects and grids but I think you get the idea. Just do the spatial join, put it into a dictionary with project as key and the value is a list of intersecting grids. Then you can do what you need with it, like write it back to your feature class as a comma separated list of values.

from collections import defaultdict

try:
    # Perform spatial join between grid and project features.
    grid_project_spatialjoin_inmem = r"in_memory\grid_project_spatialjoin"
    arcpy.SpatialJoin_analysis(
        target_features=projectFeatures,
        join_features=gridFeatures,
        out_feature_class=grid_project_spatialjoin_inmem,
        join_operation="JOIN_ONE_TO_MANY"
    )
    # Create dictionary keys of projects and values with list of grids
    project_grids = defaultdict(list)
    with arcpy.da.SearchCursor(grid_project_spatialjoin_inmem, ("projectPageName", "gridLabel")) as sCursor:
        for projectPageName, gridLabel in sCursor:
            project_grids[projectPageName].append(gridLabel)
finally:
    arcpy.Delete_management("in_memory")

# Write the intersecting grids for each project as comma separated list
with arcpy.da.UpdateCursor(gridFeatures, ("projectPageName", "gridLabel")) as uCursor:
    for projectPageName, gridLabel in uCursor:
        grid_list = project_grids.get(projectPageName, "")
        uCursor.updateRow((projectPageName, ", ".join(grid_list)))

EDIT: fixed a comment for creating the dictionary

[JamesWilcox1970 · Accepted solution]

Thank you @BlakeTerhune ! It is going to take me a little bit to understand it, try it out and hammer out any bugs, but I have very high hopes now. I will update you as I go. Thanks again!

[JamesWilcox1970]

Thank you, @BlakeTerhune ! I had to do some tweaking of the code to get it to work with the data, but your idea really helped. Here's the relevant portion of the working script:

def listToString(s):  # Takes input list and converts it to a string using join()
    # Empty string
    str1 = ""
    str1 = ", ".join(s)
    return str1

try:
    # Perform spatial join between grid and project features.
    grid_project_spatialjoin_inmem = r"in_memory\grid_project_spatialjoin"
    arcpy.SpatialJoin_analysis(
        target_features=culFC,
        join_features=quadFC,
        out_feature_class=grid_project_spatialjoin_inmem,
        join_operation="JOIN_ONE_TO_MANY"
    )
    arcpy.AddMessage("Join created.")
    # Create dictionary of grid keys and value with list of projects
    project_grids = defaultdict(list)
    with arcpy.da.SearchCursor(grid_project_spatialjoin_inmem, ("Label", "PageName")) as sCursor:
        for Label, PageName in sCursor:
            project_grids[Label].append(PageName)
finally:
    arcpy.Delete_management("in_memory")
    arcpy.AddMessage("Dictionary created.")
for key, value in project_grids.items():
    project_grids[key] = str(listToString(value))  # Changes the value to a string, but class is still list?

grid_list = project_grids.items()  # New dictionary from project_grids
grid_list = list(grid_list)  # Convert it to a list
# for item in grid_list:  # Checking list contents
#     arcpy.AddMessage("{}: {}.".format(item[0], item[1]))
with arcpy.da.InsertCursor(indexTable,("Label", "PageName")) as iCursor:
    for row in grid_list:
        iCursor.insertRow(row)
arcpy.AddMessage("Processing complete.")

Thank you again for all the help!

Jim