Collapsing multi-row output of one-to-many spatial join to single-row in ArcPy?

Question

I'm working with TIGER data, looking at the relationships among incorporated places and counties. Not all places are completely contained by counties; some do span multiple counties.

In the final output I'm preparing, I need to have a "location reference memo" for each place; for a city like Cleveland that is completely contained, this might say something like

"Incorporated Place in Cuyahoga County"

for a city like Birmingham, AL, this would instead need to say something like

"Incorporated Place in Jefferson County, Shelby County"

or

"Incorporated Place in Jefferson, Shelby Counties"

-- or something like that.

I know that I can do a spatial join to get a multirow output like

Birmingham | Jefferson
Birmingham | Shelby
[city in AL] | [County in AL]

Is there any facility, perhaps after the fact, for generating a single row equivalent of this? E.g.,

Birmingham | Jefferson; Shelby

score 4 · Accepted Answer · edited Apr 19 '13 at 06:08

Here is a bit of totally untested code that might work for you:

import arcpy

feat1 = 'finished_feature_class'
feat2 = 'spatially_joined_feature_class'

with arcpy.SearchCursor(feat2,['name_of_place_field','name_of_county_field']) as cursor:
    # create a dictionary to hold our place/county data
    places = {}
    for row in cursor:
        place = row.name_of_place_field
        county = row.name_of_county_field
        if place not in places:
            # if the place is not already in the dictionary, add it along with the county associated
            # with it in this row
            places[place] = list(county)
        else:
            # if the place is already in the dictionary, check to see if this is a different county; if
            # so, append it to the list
            if county not in places[place]:
                places[place] = places[place].append(county)

with arcpy.UpdateCursor(feat1,['name_of_place_field','name_of_memo_field']) as cursor:
    for row in cursor:
        place = row.name_of_place_field
        # use the "x for x in z" iterator with the "join" function to join multiple counties together
        row.name_of_memo_field = 'Incorporated Place in {0}'.format( ','.join([county for county in places[place]]) )
        cursor.updateRow(row)

The basic steps are:

Step 0. Do a spatial join on your finished feature class, feat1.

Step 1. Go through the rows of that new feature class, feat2, and compile a list of counties each place is associated with using a dictionary, places.

Step 2. Go into feat1 and pull the list of counties associated with each row/place from the dictionary created in step 1.

Step 3. Write a string to the memo field and for each county found in the counties list, join them with a "," (or other symbol of your choice).

Step 4. Update the row.

Collapsing multi-row output of one-to-many spatial join to single-row in ArcPy?

1 Answers1

Linked