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()
Best Answer
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
UpdateCursorthen this can be done in place (make a copy of your feature class if you want a backup).