Programmatic logic ArcGIS uses to determine added fields from geoprocessing when inputs have duplicate field names

Question

ArcGIS has a confusing method for updating field names when combining tables with duplicate field names. For example, I have two tables containing the field Id.

If I run JoinField_management, joining Id with key field OBJECTID the new field is Id_1.

One would suspect that should the process repeat the field would be added with field name Id_2. However, this is not the case. Instead the field is added as ID_12.

Repeat the process dozens of times and the field name logic gets more complex.

If a field name length is 64 the count adds one as one would expect.

Another note: ID_4 becomes ID_45, then ID_45_46, ID_45_46_47, etc.

Question: What is the programmatic logic used to determine field names from geoprocessing when duplicate fields are present?

Answer 1

In ArcPy you can use field mapping to determine what fields work as inputs and outputs and how they are combined - this doesn't work for JoinField_management but works for many other geoprocessing functions.

This script merges ID fields into the same output field when using the merge tool in data management:

layer1 = 'layer1' 
layer2 = 'layer2'

# create object for the fieldmappings and each individual fieldmap
fms = arcpy.FieldMappings()
fm_id = arcpy.FieldMap()

# add input fields to corresponding fieldmap object
fm_id.addInputField(layer1, "FID")
fm_id.addInputField(layer2, "OBJECTID")

# set output field name
out_id = fm_id.outputField
out_id.name = 'FID'
fm_id.outputField = out_id

# add ID fieldmap to fieldmappings object
fms.addFieldMap(fm_id)

# merges layers
arcpy.Merge_management([layer1, layer2], out_layer, fms)

Sources:

• Arc documentation on input/output fields
• Arc Pro documentation on FieldMappings
• Helpful sample code

Answer 2

I'd also be very interested to hear of a better, definitive way to resolve this issue. But in the mean time, you can use arcpy.ListFields() to get the list of fields before and after geoprocessing, and compare the before/after lists to determine which fields are new.

Eg, before geoprocessing, run: fieldsBefore = [f.name for f in arcpy.ListFields(table)]

Then after geoprocessing, run: fieldsAfter = [f.name for f in arcpy.ListFields(table)]

Then determine the list of new fields with:

newFields = [f for f in fieldsAfter if f not in fieldsBefore]

Alternatively, in cases where we can be certain that arcpy.ListFields() will always return the fields in order, and the new fields are always at the end of the list (I haven't verified if there are such cases), you could simply do:

[f.name for f in arcpy.ListFields(table)][-1]

To get the last field (or use -n instead of '-1` if you know how many new fields to expect).

Answer 3

Blunt tool might help to solve flawed logic. So let's use result of your test to guess name by simply counting number of operations:

import arcpy
sausage="ID_1_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30"
suffix=sausage.split("_")[1:]
for i in range(19):
    arcpy.JoinField_management("PA", "OBJECTID", "PB", "OBJECTID")
    lastName = [f.name for f in arcpy.ListFields("PA")][-1]
    if i==0:
        guessName="ID_"+"_".join(suffix[:1+i])
    else:
        guessName="ID_"+"_".join(suffix[1:1+i])
    if guessName==lastName: arcpy.AddMessage(guessName)

On the other hand, there is some logic when you look at output of above script:

Every next additional bit equal last one from previous + 1.