I'm using the field mapping object in ArcGIS Pro to try set up a spatial join with specific field values in the output. Apologies as I am very new to programming in general, so this may be rudimentary / code is not polished.
I have two shapefiles:
1) Line water pipe data with attributes (pipes)
2) Polygon areas of interest (currentAOI)
Since there are multiple pipe segments (with differing attribute info) for each larger area of interest, I need to specify a merge rule so that only the information for the longest pipe segment within the AOI is maintained. I realize that I can set merge rules for each field, but I want the other resulting fields to be dependent on the merge rule for only one of the fields.
I set a rule for the length field, but the "first" merge rule is automatically applied to each of the other individual fields. Instead, I need whatever field values associated with the longest length value to be retained. See the "one to many" photo example where you can see all the other values, and the resulting "one to one" join where it mixes and matches the resulting field values.
Here is the field mapping code:
fieldMappings = arcpy.FieldMappings()
fm_id = arcpy.FieldMap()
fm_length = arcpy.FieldMap()
fm_material = arcpy.FieldMap()
fm_diameter = arcpy.FieldMap()
fm_year = arcpy.FieldMap()
# create fields
ID = "ID"
length = "Length_mi"
material = "Material"
diameter = "Diameter"
year = "Year_"
fm_id.addInputField(currentAOI, ID)
fm_length.addInputField(pipes, length)
fm_material.addInputField(pipes, material)
fm_diameter.addInputField(pipes, diameter)
fm_year.addInputField(pipes, year)
# Set the output field properties for FieldMap objects
id_name = fm_id.outputField
id_name.name = 'ID'
fm_id.outputField = id_name
len_name = fm_length.outputField
len_name.name = 'Length_mi'
fm_length.outputField = len_name
mat_name = fm_material.outputField
mat_name.name = 'Material'
fm_material.outputField = mat_name
diam_name = fm_diameter.outputField
diam_name.name = 'Diameter'
fm_diameter.outputField = diam_name
year_name = fm_year.outputField
year_name.name = 'Year_'
fm_year.outputField = year_name
fieldMappings.addFieldMap(fm_id)
fieldMappings.addFieldMap(fm_length)
fieldMappings.addFieldMap(fm_material)
fieldMappings.addFieldMap(fm_diameter)
fieldMappings.addFieldMap(fm_year)
fm_length.mergeRule = "max"
####################################################################################
spJoinPipe = arcpy.analysis.SpatialJoin(currentAOI, pipes, str(outputPath) + "/currentAOI_Pipes_SpJoin_one.shp", field_mapping=fieldMappings)
dropFields2 = ["Join_Count", "TARGET_FID"]
arcpy.management.DeleteField(spJoinPipe, dropFields2)
Any help is greatly appreciated. Thank you.