Sorting field values within column in table using ArcPy

Question

I have a feature class table named fcwells. It has a bunch of different columns but the column I need sorted is 0Meters. Many fields under 0Meters have multiple values separated by commas and it is these values that I need sorted alphanumerically. For example a field could have KW-4, KW-6, 100-N, AA-90, and 100-Z. The python script should run and sort this field like this: AA-90, KW-4, KW-6, 100-N, 100-Z. So the table should be re-written with the correct sorted order in these fields.

Here is what I have so far:

def all_values(fcwells, 0Meters):

        with arcpy.da.SearchCursor(fcwells, ["0Meters"]) as cursor:

            return sort({row[0] for row in cursor})

myValues = all_values

print (myValues)

The code runs but the data is not sorted correctly in the feature class.

Every example I see online are sorts of entire tables by a column, rather than sorting of values within fields under a specific column.

I am using ArcPy. This is a stand-alone script that will be used as a nightly process on a server.

Update: here is the entire python script for reference.

import arcpy
import os
import datetime
import sys
import string
import calendar
import traceback
import smtplib
from email.mime.text import MIMEText

try:
    #get date and time stamp for logging
    d = datetime.datetime.now()

    #Log file custodial section
    #set file count maximum
    maxFiles = 10

    #log file folder path
    path = r'\\h\data\sitedata\gis\Xfer\LogFiles\WellsUpdateProcess\Dev'
    
    #create a list of files with .log in their name include file path
    #create list container
    onlyFiles = []
    #list all of the files in the log file folder
    for f in os.listdir(path):
        #selct only files
        if os.path.isfile(os.path.join(path,f)):
            #that have .log in there name
            if ".log" in f:
                #add these to the list
                onlyFiles.append((os.path.join(path,f)))

    #sort the list of files by modification time oldest to newest
    onlyFiles.sort(key=os.path.getmtime)

    #if the log file count exceeds the maxFiles count delete excess files
    delCount = (len(onlyFiles)) - maxFiles
    while delCount != 0:
        #delete the file
        os.remove(onlyFiles[0])
        #remove file entry from list
        del onlyFiles[0]
        #decrement counter
        delCount -=1

    #End Log file custodial section 
    #Create new log file
    #Logfile path and name
    logFile = path + "\WellsUpdate-" + d.strftime("%Y%m%d") + ".log"


    #open log file
    log = open(logFile, 'w')
    log.write("----------------------------" + "\n")
    log.write("----------------------------" + "\n")
    log.write("Log: " + str(d) + "\n")
    log.write("\n")

    #Start process
    starttime = datetime.datetime.now()
    log.write("Begin process:\n")
    log.write("     Process started at " + str(starttime) + "\n")
    log.write("\n")

    #open toolbox 
    tbx = arcpy.ImportToolbox("\\\h\data\sitedata\gis\GeoProcess Tools\Dev\GISSVS_WELLS_Tools.tbx","WellsUpdateTools")
    
    #run the HEISPUpdate process model completing the following tasks
    #Update the HEIS_P_View View from the HEIS database
    #Set all Northings that have a value of NULL to 0
    #Set all Eastings that have a value of NULL to 0
    #Create the HEIS_P_Wells XY Event Layer from the HEIS_P_View
    #Create the HEIS_P_Wells feature class from the HEIS_P_Wells event layer
    log.write("Begin process HEISPUpdate " + str(datetime.datetime.now())+ "\n") #new as of 6/5/19
    tbx.HEISPUpdate()

    #clear data in fields so recalculate can take place
    log.write("Begin process to clear field values " + str(datetime.datetime.now())+ "\n")
    #Local variables:
    fcimwelwel = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.imwelwel"
    fcHEIS_P_Wells = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.HEIS_P_Wells"
    with arcpy.da.UpdateCursor(fcimwelwel,['WIDS_0M','WIDS_50F','WIDS_100F','WIDS_50M','WIDS_100M']) as cursor:
        for row in cursor:
            cnt = 0
            while cnt <= 4:
                row[cnt] = ' '
                cnt +=1
            cursor.updateRow(row)
            
    #definiton function with an identifier called pointsinpolygons, and 4 parameters, polyFC, polyField, wellField, and whereFL
    def pointsinpolygons(polyFC,polyField,wellField,whereFL): 
        #store the current time in a variable called loopstarttime; import datetime is required at the top
        loopstarttime = datetime.datetime.now()
        #tests for the existance of a specified data object named polylayer
        if arcpy.Exists("polylayer"):
               #delete the current polygon layer to prepare for the next polygon 
               arcpy.Delete_management("polylayer")
        #make a feature layer from the polygon feature class.       
        polylayer = arcpy.MakeFeatureLayer_management(polyFC,"polylayer",whereFL) 
        #Well feature layer
        #tests for the existance of a specified data object named wellFL
        if arcpy.Exists("wellFL"):
               #delete the current polygon layer to prepare for the next polygon 
               arcpy.Delete_management("wellFL")
        #make a feature layer from the fcimwelwel feature class       
        wellFL = arcpy.MakeFeatureLayer_management(fcimwelwel,"wellFL")
        #counts the number of features in a feature class named wellFL and stores them in a variable named result
        result = arcpy.GetCount_management (wellFL) 
        #print ("well count = " + (str(result)))
        #select all polygon features that contain wells.
        plyswithwells_lyr = arcpy.SelectLayerByLocation_management(polylayer,"CONTAINS",wellFL)
        #counts the number of features in a layer named plyswithwells_lyr and stores them in a variable named result
        result = arcpy.GetCount_management(plyswithwells_lyr) 
        #print ("polygons containing wells " + (str(result)))
        #loop through all of the polygons that contain wells
        with arcpy.da.SearchCursor(plyswithwells_lyr,[polyField]) as cursor:
           #do the following for each polygon in the cursor 
           for polygon in cursor: 
               whereclause = """{0} = '{1}'""".format(arcpy.AddFieldDelimiters(polyFC,polyField),polygon[0]) 
               #print (whereclause)
               #make a feature layer from the selected polygon
               arcpy.MakeFeatureLayer_management(polyFC,"ply_lyr",whereclause)
               #select all of the wells that are completely within the polygon
               well_lyr = arcpy.SelectLayerByLocation_management(wellFL,"COMPLETELY_WITHIN","ply_lyr")
               #counts the number of features in a layer named well_lyr and stores them in a variable named result
               result = arcpy.GetCount_management(well_lyr) 
               #print (result)
               #loop through the list of wells and update the well field with the polygon field value
               with arcpy.da.UpdateCursor(well_lyr,[wellField]) as Wcursor:
                   #do the following for each well in the Wcursor
                   for well in Wcursor:
                       #the first instance in well is equal to the first instance in polygon? update the row
                       well[0] = polygon[0]
                       #update the row for well in Wcursor
                       Wcursor.updateRow(well)
               #tests for the existance of a specified data object named ply_lyr        
               if arcpy.Exists("ply_lyr"):
                   #delete the current polygon layer to prepare for the next polygon
                   arcpy.Delete_management("ply_lyr")
           #removes cursor from the namespace;         
           del cursor
           #removes polygon from the namespace;
           del polygon
           #removes Wcursor from the namespace; 
           del Wcursor 
           #get end time
           loopendtime = datetime.datetime.now()
           print (" Completed successfully in " + str(loopendtime - loopstarttime) + "\n")

    log.write("Begin running the calculated field updates " + str(datetime.datetime.now())+ "\n")
    
    #Process the Counties feature class.
    fcCounty = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.Counties_wa83s" #Washington State Counties    
    pointsinpolygons(fcCounty,"JURLBL","County","") #should only have 4 counties          
    
    #Process the closure zones feature class.
    fcehremclz = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.ehremclz" #closure zones
    pointsinpolygons(fcehremclz,"NAME","EHREMCLZ","")
           
    #Process the Tank Farm features from the bggenexs feature class.
    fcTankFarm = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.bggenexs" #building layer with Tank Farms
    whereclauseTankFarm = "MAP_ID = '20031112_TANK_FARM_PG'" 
    pointsinpolygons(fcTankFarm,"FACIL_NAME","TANKFARM",whereclauseTankFarm)
           
    
    #Process the Groundwater Area of Interest feature class.
    fcehgwtai = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.ehgwtai"
    pointsinpolygons(fcehgwtai,"GWINT_ARE","EHGWTAI","")


    #Process the PLSS quarter quarter section feature class.
    fcPLSS = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.cdplssqq" #PLSS
    pointsinpolygons(fcPLSS,"PLSS","PLSS","")


    #Process the Admin Boundaries feature class.
    fcAdminBoundary = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.AdministrativeBoundary" #Administrative Boundary
    pointsinpolygons(fcAdminBoundary,"sdsFeatureName","Monument","")
           

    #Process the Well Area Boundary feature class.
    fcWellAreaBoundary = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.WellAreaBoundary" #Well Area Boundary
    pointsinpolygons(fcWellAreaBoundary,"Name","WellArea","")


    #Update the DSTRICT_ID Field with the value from the WellArea field
    with arcpy.da.UpdateCursor(fcimwelwel,['WellArea','DSTRICT_ID']) as cursor:
        for row in cursor:
            row[1] = row[0]
            cursor.updateRow(row)

    
    def pointsinpolygonsWithbuffer(polyFC,polyField,wellField,whereFL,searchDist):
        loopstarttime = datetime.datetime.now()
        if arcpy.Exists("polylayer"):
               #delete the current polygon layer to prepare for the next polygon 
               arcpy.Delete_management("polylayer")
        #make a feature layer from the polygon feature class.       
        polylayer = arcpy.MakeFeatureLayer_management(polyFC,"polylayer",whereFL) 
        #Well feature layer
        if arcpy.Exists("wellFL"):
               #delete the current polygon layer to prepare for the next polygon 
               arcpy.Delete_management("wellFL") 
        wellFL = arcpy.MakeFeatureLayer_management(fcimwelwel,"wellFL")
        result = arcpy.GetCount_management (wellFL)
        #print ("well count = " + (str(result)))
        #select all polygon features that contain wells.
        plyswithwells_lyr = arcpy.SelectLayerByLocation_management(polylayer,"WITHIN_A_DISTANCE",wellFL,searchDist) 
        result = arcpy.GetCount_management(plyswithwells_lyr)
        #print ("polygons containing wells " + (str(result)))
        #loop through all of the polygons that contain wells
        with arcpy.da.SearchCursor(plyswithwells_lyr,[polyField]) as cursor:
           #do the following for each polygon in the cursor 
           for polygon in cursor: 
               whereclause = """{0} = '{1}'""".format(arcpy.AddFieldDelimiters(polyFC,polyField),polygon[0])
               #print (whereclause)
               #log.write("The where clause equals" + whereclause + "\n")
               #make a feature layer from the selected polygon
               arcpy.MakeFeatureLayer_management(polyFC,"ply_lyr",whereclause)
               #select all of the wells that are completely within the polygon
               well_lyr = arcpy.SelectLayerByLocation_management(wellFL,"WITHIN_A_DISTANCE","ply_lyr",searchDist) 
               result = arcpy.GetCount_management(well_lyr)
               #print (result)
               #loop through the list of wells and update the well field with the polygon field value
               with arcpy.da.UpdateCursor(well_lyr,[wellField]) as Wcursor: 
                   for well in Wcursor:
                       #if well[0] == None: #if well[0] == ' ': #removed to allow processing when fields processed to add blank
                       if well[0] == ' ':          
                           well[0] = polygon[0]
                       else:
                           well[0] = well[0] + ',' + polygon[0]
                       Wcursor.updateRow(well)
               if arcpy.Exists("ply_lyr"):
                   #delete the current polygon layer to prepare for the next polygon   
                   arcpy.Delete_management("ply_lyr") 
           del cursor
           del polygon
           del Wcursor
           loopendtime = datetime.datetime.now()
           print (str(loopendtime - loopstarttime))

    log.write("Begin running the calculated field updates for WIDS sites with buffers " + str(datetime.datetime.now())+ "\n")
    log.write("Begin running the calculated field updates for 0M " + str(datetime.datetime.now())+ "\n") 
    #Process WIDS Sites 0m buffer.
    #fcehsit = "Database Connections\\GISDB-DEV HGIC_DEV (SDE).sde\\HGIC_DEV.SDE.ehsit" #WIDS sites
    fcehsit = "Database Connections\\GISDB-DEV HGIS_DEV (SDE).sde\\hgis_dev.HGISOWNER.WIDS_combined" 
    searchDistance = 0
    pointsinpolygonsWithbuffer(fcehsit,"SITE_NUM","WIDS_0M","",searchDistance)
    log.write("Finished the 0M section " + str(datetime.datetime.now())+ "\n")
       
    log.write("Begin sorting the calculated field updates for 0M " + str(datetime.datetime.now())+ "\n")
    
# loop through each value in each field under WIDS_0M and sort it #later add in a check for commas
    def all_values(fcimwelwel, WIDS_0M):
        with arcpy.da.UpdateCursor(fcimwelwel, ["WIDS_0M"]) as cursor:
            for row in cursor:
                values = row[0].split(',')
                values = sorted((v.strip() for v in values))
                row[0] = ', ' .join(values)
                cursor.updateRow(row)
                         
    
    log.write("Finished sorting the 0M section " + str(datetime.datetime.now())+ "\n")
    
        
    #get end time
    endtime = datetime.datetime.now()

    #Update log file with process completed information
    log.write("     Completed successfully in " + str(endtime - starttime) + "\n")
    log.write("\n")
    log.close()

    #Prepare process completed successfully email message
    #setup email parameters
    fromProcess = '.gov'
    toAnalyst = '.gov'
    emailServer = smtplib.SMTP('.gov')

    msg = MIMEText("The Well update process completed successfully in " + str(endtime - starttime)+ "\n" + "See the log file" + "\n" + logFile + "\n" + "for details")
    msg['Subject'] = 'Well Update Process Report for ' +  d.strftime("%Y%m%d")
    msg['From'] = fromProcess
    msg['To'] = toAnalyst

    #send email message that process has completed successfully
    emailServer.sendmail(fromProcess,[toAnalyst], msg.as_string())
    emailServer.quit()    
    
except:
    #if an exception has been thrown some cleanup must take place to close the log then report it as the error log
    print ("exception found in module")
    #close the log file
    log.close()
    #open log file as the error log
    elog = open(logFile, 'a')
    #Get the traceback object
    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
    
    #Concatenate information together concerning the error into a message string
    pymsg = "\nPYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(sys.exc_info()[1])
    
    #Return python error messages for use in log file
    arcpy.AddError(pymsg)
    msgs = "\nArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
    arcpy.AddError(msgs)
    
    #write the error messages to the log file
    elog.write("" + pymsg + "\n")
    elog.write("" + msgs + "")
    elog.close()    

finally:
    #cleanup
    log.close()  

Answer 1

For each row, split the target string to create a list of individual values, sort that, then join it back together into a string. If you use an UpdateCursor then this can be done in place (make a copy of your feature class if you want a backup).

with arcpy.da.UpdateCursor(fcwells, ["0Meters"]) as cursor:
  for row in cursor:
    values = row[0].spit(',')
    values = sorted((v.strip() for v in values))
    row[0] = ', '.join(values)
    cursor.updateRow(row)

Answer 2

You do not need cursor for this operation, I think, the field calculator would do the job. I would use something like with Python interpreter in field calculator:

', '.join(sorted(map(str.strip, !0Meters!.split(','))))

Be advised though, numeric values comes prior to the alphabetic characters when sorted. So the outcome of the expression above will be:

100-N, 100-Z, AA-90, KW-4, KW-6