Arcpy replace string in feature class table header

278
6
Jump to solution
03-24-2022 02:02 PM
JaredPilbeam2
MVP Regular Contributor

Hello,

I want to be able to remove unwanted values from column headers. When you geocode it adds User_ to the beginning of every string. And for the end-user of the table, who's not a tech, this is very convoluted.

JaredPilbeam2_3-1648155135687.png

So, what I can do to change the values using Pandas on an excel table I want to be able to do using ArcPy on a feature class. Is this possible? From what I know of cursors you can only change field values. Thanks for any help.

#convert feature class to a table and remove unwanted characters
arcpy.conversion.TableToExcel(out_fc, 'geocode2.xlsx')

#Remove unwanted values from column headers (i.e. USER_) then save as a *.csv
import pandas as pd
df = pd.read_excel(ws + '\geocode2.xlsx')
df.columns = df.columns.str.replace('USER_', '')
df.to_csv(r'')

 

0 Kudos
1 Solution

Accepted Solutions
JeffK
by MVP Regular Contributor
MVP Regular Contributor

Yes, cursors only work on the data and not fields.  I think you’ll have to create an interim dataset where you can map the User_ fields to the desired output.  Or add the fields to the end dataset and use cursors to move the values and then delete the old fields but I think mapping during migration is better.

or use alterfields on them and change the names if possible.

View solution in original post

6 Replies
RickMomsen1
New Contributor II

Funny - I'm dealing with this exact issue.

If you are using Desktop and ArcPy 2.7 - you're in luck! The MakeFeatureLayer and MakeTableView tools utilize the FieldInfo option to alter field names in the layer, and the new field names will be maintained when exported (FeatureClassToFeatureClass). 

If you are using Pro and ArcPy 3.x (and it looks like it by your syntax) - my condolences. The functionality has been removed from the MakeFeatureLayer tool. Even though the FieldInfo and MakeTableView explicitly show you can change field names, it does not work for me.

I'll be following this thread to see other responses.

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

Yes, cursors only work on the data and not fields.  I think you’ll have to create an interim dataset where you can map the User_ fields to the desired output.  Or add the fields to the end dataset and use cursors to move the values and then delete the old fields but I think mapping during migration is better.

or use alterfields on them and change the names if possible.

KevinBell1
New Contributor III

 

flds = arcpy.ListFields('yourData')

for f in flds:

if f.name.split('_')[0] == 'USER':
    arcpy.AlterField_management("yourData", f.name, f.name.split('_')[1])
else:
    pass
print 'fixed aliass'

0 Kudos
DanPatterson
MVP Esteemed Contributor
import arcpy
fc0 = r"C:\arcpro_npg\npg\Project_npg\tests.gdb\multi"
flds = ["SHAPE@X", "SHAPE@Y", "Shape_length", "Shape_Area"]
arr = arcpy.da.TableToNumPyArray(fc0, field_names=flds, skip_nulls=True)
nmes = arr.dtype.names
# -- have a look
arr[:1]
array([( 300005.45,  5000005.18,  90.71,  57.50)],
      dtype=[('SHAPE@X', '<f8'), ('SHAPE@Y', '<f8'), ('Shape_length', '<f8'), ('Shape_Area', '<f8')])

nmes  # -- ('SHAPE@X', 'SHAPE@Y', 'Shape_length', 'Shape_Area')

# -- change some of the names
new_nmes = [i.replace("SHAPE@", "") for i in nmes]
arr.dtype.names = new_nmes

arr[:1]
array([( 300005.45,  5000005.18,  90.71,  57.50)],
      dtype=[('X', '<f8'), ('Y', '<f8'), ('Shape_length', '<f8'), ('Shape_Area', '<f8')])

new_nmes  # -- ['X', 'Y', 'Shape_length', 'Shape_Area']

# -- send it back
arcpy.da.NumPyArrayToTable( ... in_array ...,... out_table ...)

... sort of retired...
0 Kudos
JaredPilbeam2
MVP Regular Contributor

Thanks for all the suggestions. I've been trying arcpy.AlterField_management() for the most part.

@KevinBell1What you had didn't work verbatim. I think because my data sometimes has more than one underscore. Anytime I attempted to grab the value after the delimiter with split()[1] it told me Iist index out of range. So, I started using a combination of split() and partition().

I'm able to print out the values I want, but when I add the line to alter the field I get an error.

 

flds = arcpy.ListFields(r'...pathto\Outputs.gdb\testdelete')#full path to feature class
for f in flds:
    mytuple = f.name.split("_")
    if mytuple[0] == "USER": #if USER before the underscore
        print(f.name.partition("_")[2]) #print what's after the underscore
        arcpy.AlterField_management(flds, f.name, f.name.partition("_")[2])
    else:
        pass
        print('pass me up')
pass me up
pass me up
pass me up
pass me up
pass me up
pass me up
pass me up
Reporting_Period
---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
<ipython-input-133-c6d9bc0ffe7b> in <module>
      4     if mytuple[0] == "USER":
      5         print(f.name.partition("_")[2])
----> 6         arcpy.AlterField_management(flds, f.name, f.name.partition("_")[2])
      7     else:
      8         pass

C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py in AlterField(in_table, field, new_field_name, new_field_alias, field_type, field_length, field_is_nullable, clear_field_alias)
   5427         return retval
   5428     except Exception as e:
-> 5429         raise e
   5430 
   5431 @gptooldoc('AssignDefaultToField_management', None)

C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py in AlterField(in_table, field, new_field_name, new_field_alias, field_type, field_length, field_is_nullable, clear_field_alias)
   5424     from arcpy.arcobjects.arcobjectconversion import convertArcObjectToPythonObject
   5425     try:
-> 5426         retval = convertArcObjectToPythonObject(gp.AlterField_management(*gp_fixargs((in_table, field, new_field_name, new_field_alias, field_type, field_length, field_is_nullable, clear_field_alias), True)))
   5427         return retval
   5428     except Exception as e:

C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py in <lambda>(*args)
    510         val = getattr(self._gp, attr)
    511         if callable(val):
--> 512             return lambda *args: val(*gp_fixargs(args, True))
    513         else:
    514             return convertArcObjectToPythonObject(val)

RuntimeError: Object: Error in executing tool

 

 Fields:

trable.png

0 Kudos
JaredPilbeam2
MVP Regular Contributor

Update: I put the fill path in the first parameter of the function and it ran without error. But, I can only get it to produce the desired results from the Notebook, actually. And, of course, I need it to work in a stand-alone. Maybe it's a Python environment issue.

0 Kudos