I am creating a pivot table in python and have come across the scenario that when there are more than 9 pivot fields created that the order of the fields is technically out of order (e.g., pivotFld_1, pivotFld_10, pivotFld_11, pivotFld_2, pivotFld_3, pivotFld_4....). I would like the order to be pivotFld_1, pivotFld_2, pivotFld_3, pivotFld_4....pivotFld_9, pivotFld_10, pivotFld_11....
I have written the following code to reorder the fields but I am wondering if there is a better way to accomplish this?
testTbl = "path\\pivot table"
testTbl_vw = "testTbl_vw"
arcpy.MakeTableView_management(testTbl, testTbl_vw)
testFlds = list(f.name for f in arcpy.ListFields(testTbl_vw))
seqTest = []
for f in testFlds:
if not f.find("pivotField_"):
seqTest.append(f.lstrip("pivotField_"))
newFlds = []
for f in seqTest:
if int(f) > 9:
newFlds.append(f)
for f in newFlds:
arcpy.AddField_management(testTbl_vw, "tmp_"+f, "LONG")
arcpy.CalculateField_management(testTbl_vw, "tmp_"+f, "!pivotField_"+f+"!", "PYTHON")
arcpy.DeleteField_management(testTbl_vw, "pivotField_"+f)
arcpy.AddField_management(testTbl_vw, "pivotField_"+f, "LONG")
arcpy.CalculateField_management(testTbl_vw, "pivotField_"+f, "!tmp_"+f+"!", "PYTHON")
arcpy.DeleteField_management(testTbl_vw, "tmp_"+f)
Suzanne,
Thanks for sharing your code!
It would be more efficient to use the Merge or append tools with field mappings to rearrange the fields (you need to copy to a new feature class to do this.
Another approach that would work (that is easier than your method above or messing with field mapping objects) would be to create an empty feature class, add the fields in the order you want, and append your mis-ordered feature class to it using the NO_TEST option. The fields that have matching names will match up and the data will be written in the order you want.
More advice here:
Re: Reorder Fields in a TableView Using Python
Thank you for suggestions, I appreciate hearing other approaches and will definitely look at your ideas further..
I suggest some code on the GIS SE website here. There is also a link back to a blog page on Geonet showing an example of using numpy which you could explore as an alternative approach for reordering tables.
Thank you for your feedback and link to the fieldmapping code example.