3

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.

enter image description here

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

enter image description here

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.

enter image description here

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

enter image description here

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

enter image description here

enter image description here

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?

  • @SonofaBeach These methods would work, but maybe not always if multiple fields are being processed. What if you had two tables, both with Id and Id_1? I'd really like to have a function with the same logic esri uses for their field naming. – Emil Brundage Jul 25 '19 at 21:08
  • One way could be to check for duplicates before join field and rename them using Alter Field. – BERA Jul 26 '19 at 17:35
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:

  • I'm not looking for a way to change output field names. I'm looking for the programmatic logic ArcGIS uses to determine field names when duplicates exist in two or more tables. I'd like this so that I can create robust tools which don't rely on knowing field names. – Emil Brundage Jul 29 '19 at 21:04
  • @EmilBrundage I see, sorry I misunderstood. I have had issues with this too unfortunately as there isn't an easy built-in option like QGIS's field index numbers. – jyingling Jul 29 '19 at 21:09
1

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).

  • 1
    I'm aware of this process, and I'm guessing it will work 99% of the time, but I think knowing the logic ArcGIS uses for updating field names is the best option for robust tools. – Emil Brundage Jul 29 '19 at 22:05
  • 1
    I agree that it would be good to find a more definitive solution. Are you able to demonstrate a case where this would not work? If so, this may give us an area to focus on for a better solution. – Son of a Beach Jul 29 '19 at 22:08
  • 1
    ESRI's logic is obviously flawed, it's gonna be pretty ugly to reimplement that. This answer seems much more straightforward – mikewatt Jul 29 '19 at 22:26
1

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:

enter image description here

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

  • That's a start, but it doesn't address the 64 character limit for field names. Also when the field ID_4 exists it becomes ID_45, then ID_45_46. – Emil Brundage Jul 30 '19 at 15:09
  • It is exactly the same as going from id_1 to id_12, id_12_45. – FelixIP Jul 30 '19 at 20:05
  • What about the ..._29_30_3_52 part? – alphabetasoup Jul 30 '19 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.