Field Calculate a List using Arcade/Python after Spatially Joining

[CiaraMarleneKeenan]

Problem: I want to get multiple records from one field(FacilityID) into one APN record in a list format that is also in numerical order.

My workflow: Spatially Joined 2 layers(one to many) and now I have duplicate APN records. I would like to have the individual APNs with the multiple Facility IDs listed in one record. I know I could always copy and paste data into excel and do what I need to do there, but I really want to learn arcade 🙂 

My experience with using arcade has only been for formatting labels and simple field calculations. So I am still fairly new to the scripting game.

[JakeSkinner]

Hi @CiaraMarleneKeenan,

You could do this via python.  Below is an example.  It first gets a dictionary of all APNs with their FacilityID.  Then delete's all identical rows based on the APN field.  Finally, it updates the FacilityID field for each APN based off the dictionary.  You will want to make sure the field, FacilityID, you are updating is a TEXT field.

import arcpy

table = r'C:\TEMP\Python\Test.gdb\Areas'

# Create dictionary of APNs and FacilityIDs
apnDict = {}
with arcpy.da.SearchCursor(table, ['APN', 'FacilityID']) as cursor:
    for row in cursor:
        apnDict.setdefault(row[0], [])
        apnDict[row[0]].append(str(row[1]))
del cursor

# Delete Identical APNs
arcpy.DeleteIdentical_management(table, 'APN')

# Update APNs with Facility IDs
with arcpy.da.UpdateCursor(table, ['APN', 'FacilityID']) as cursor:
    for row in cursor:
        facilities = apnDict[row[0]]
        facilityIDs = ', '.join(facilities)
        row[1] = facilityIDs
        cursor.updateRow(row)
del cursor

Before:

After:

[RhettZufelt]

I know you said python/arcade, but this is built right into the spatial join tool.  This post shows the 'trick', even though for ArcMap, works in Pro as well.

R_

[AdamAraza]

Hi Ciara,

I also have a similar workflow that you may find useful. This is the python script I use. It spatial joins your parcels layer to the facilities layer and will output a new layer with just the APN and the facility ID with a single APN and a list of all the facility IDs. I run this same function to against multiple layers and then join them all together using the APN field so I can have a master parcel layer that has the APN, a list of addresses on the parcel, a list of facility IDs on the parcel, any related tract map information on the parcel, treeIDs on the parcel, etc.

You would have to update line 3 so that the value after apnField is the actual name of your APN field and value after apn_Layer is the name of your parcel layer. then in line 55, you update the values of your actual facility layer, the name of the facility ID field, the name you want the facility ID field to have in your new layer, the path of the new layer, and then set the length of the field. Some of the layers I join to my parcel layer have a lot of intersecting records, so the field length can vary. Hope this helps!

import arcpy

def comma_join(inputLayer, inputField, outputLayer, fieldName, fieldLength, apnField="APN_SPACE", apnLayer = "Your_PARCEL_Layer", mergeRule="Join"):
    """
    Performs a spatial join and keeps a target field with values joined by commas.

    Parameters:
    - inputLayer: Layer on which the spatial join is performed.
    - inputField: Field in the input layer whose values need to be comma joined.
    - outputLayer: The resulting layer after spatial join.
    - fieldName: The name of the new field where the joined values will be stored.
    - fieldLength: The length of the new field.
    - apnField: Name of the APN field. Default is "APN_SPACE".
	- apnLayer: Name of your Parcel layer or layer with APN vales. Set the default here if you want to use this code on multiple layers
    - mergeRule: Rule for merging the fields. Default is "Join".
    """
	
	#Names of fields you want in your output layer
    keepers = [apnField, inputField]
	
	#Maps fields from APN layer and target layer to new layer
    fieldmappings = arcpy.FieldMappings()
        
    fieldmappings.addTable(inputLayer)
    fieldmappings.addTable(apnLayer)

	#Remove all non-APN values
    for field in fieldmappings.fields:
        if field.name not in keepers:
            if 'APN' in field.name:
                pass
            else:
                fieldmappings.removeFieldMap(
                    fieldmappings.findFieldMapIndex(field.name))

    fNames = arcpy.ListFields(inputLayer)
    for f in fNames:
        if f.name == inputField:
            fidx = fieldmappings.findFieldMapIndex(f.name)
            fmap = fieldmappings.getFieldMap(fidx)
            fmap.mergeRule = mergeRule
            fmap.joinDelimiter = ","
            theField = fmap.outputField
            theField.length = fieldLength
            theField.name = fieldName
            theField.aliasName = fieldName
            fmap.outputField = theField
            fieldmappings.replaceFieldMap(fidx, fmap)
			
	#The spatial join
    arcpy.SpatialJoin_analysis(apnLlayer, inputLayer, outputLayer, "", "",
                               fieldmappings, "", "", "")

			
comma_join(*FACILITY_LAYER*,*FACILITY_ID_FIELD*,*NEW JOINED FEATURE PATH*, *NEW_FACILITY_ID_FIELD_NAME*,*SET A LENGTH OF THE FIELD*)