topic Re: Select unique values from field using selectbyattribute in ArcGIS API for Python Questions

Select unique values from field using selectbyattribute

wwnde — Mon, 12 Mar 2018 01:51:28 GMT

I am a vety new to python. I have done the script below which identifies unique values and lists them. That goes ok. The next thing, I want it to iterate through the filed LinkKey as it selects similar LinkKeys and saves them in a work space.

All i get is a message saying zero records selected. I suspect the issue is in my whereclause.

Any help?

Re: Select unique values from field using selectbyattribute

RandyBurton — Sun, 12 Dec 2021 09:54:51 GMT

It looks like you are comparing LinkKey (or "field") to a text value instead of a number in your whereclause. Try:

# omit the single quotes around the second %s

whereclause = "%s = %s" % ('LinkKey', x)

# optional format

whereclause = "{} = {}".format('LinkKey', x)‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

wwnde — Wed, 14 Mar 2018 02:27:29 GMT

Thanks Randy, done that but it doesnt work it returns

ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

Re: Select unique values from field using selectbyattribute

DanPatterson_Retired — Sun, 12 Dec 2021 09:54:53 GMT

William... do the selectbyattributes manually, go to the Results window, and copy the python snippet.

You will get the correct syntax for the where clause. While you are doing the test, do one query for a numeric field and one for a text field and save the examples, so the next time you try to shortcut by writing the syntax out, you will have a guideline to follow

# ---- SBA for text
arcpy.management.SelectLayerByAttribute("sample_1000", "NEW_SELECTION", "Facility = 'Hall'", None)

# ---- SBA for numbers
arcpy.management.SelectLayerByAttribute("sample_1000", "NEW_SELECTION", "Time = 1", None)
‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

wwnde — Wed, 14 Mar 2018 03:24:46 GMT

Thanks Dan, Thats why I did first, it worked for individual records,. I have no issue selecting an individual item in this case a specific LinkKey, the issue is when I want it to iterate and pick similar sets of duplicated 'LinkKeys'. Say if there is a dozen set of 'p' link keys and half a dozen 'j' link keys, I want it to select p, save and proceed to select j.....

Re: Select unique values from field using selectbyattribute

DanPatterson_Retired — Sun, 12 Dec 2021 09:54:56 GMT

you build your 'where clause outside

for i in vals:
    exp = "\"Facility = '{}'\"".format(i)  # build your expression
    print(exp)                             # and substitute it in SBA
    
"Facility = 'Hall'"
"Facility = 'Church'"
"Facility = 'Hospital'"‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

wwnde — Wed, 14 Mar 2018 04:44:57 GMT

Thanks Dan, as mentioned earlier, I am only a toddler and only flowing through logic. I have put the script as below and it returns error,

"Traceback (most recent call last):
File "C:\avhome\Aftertraining Scripts\topology24.py", line 26, in <module>
for i in Vals:
TypeError: 'NoneType' object is not iterable"

Didnt I define Vals as is suppossed to be?

Re: Select unique values from field using selectbyattribute

DanPatterson_Retired — Sun, 12 Dec 2021 09:54:58 GMT

You really need to thrown some print statements into your script so you can see what you got.

I bet if you check your unique values list, there is a None in there.

Better still check for None

a = ['A', None, 'B']

for i in a:
    if i is not None:
        print('good entry')
    else:
        print('got a {}'.format(str(i)))
        
good entry
got a None
good entry‍‍‍‍‍‍‍‍‍‍‍

and if you get a 'bad' entry (ie None), then skip the selectbyattributes

Re: Select unique values from field using selectbyattribute

JoeBorgione — Sun, 12 Dec 2021 09:55:01 GMT

I'm just a big toddler in python, but after looking at your code, I tried a slightly different approach. I get confused with counter variables and avoid them by stepping through a list based on the length of the list:

fc = 'PLOTS_Test'
arcpy.MakeFeatureLayer_management(fc,'Test_lyr')
field_list = []
with arcpy.da.SearchCursor( fc, 'LinkKey') as cursor:
    for row in cursor:
        field_list.append(row[0])

for i in range(len(field_list)):
    where= 'LinkKey = {}'.format(field_list)
    arcpy.SelectLayerByAttribute_management('Test_lyr','ADD_TO_SELECTION',where)
print 'Records Selected = {}'.format(arcpy.GetCount_management('Test_lyr'))
arcpy.CopyFeatures_management('Test_lyr','sasal')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I took out kala, as I'm not sure you actually need it, and just work directly with the feature layer from the feature class.

edited moments later: sometimes the GetCount_management can throw you a curve. It honors the selection and will return the number selected, IF there is a selected set. If nothing is selected, you get the count of all the records. A way around that is using arcpy.Describe, which doesn't give you a number of selected records, but can tell you if you have selected records. I've used it this way:

if arcpy.Describe("view").FIDSet =='':
  pass
elif int(arcpy.Describe("view").FIDSet[0]) >= 1:
  #do stuff here...‍‍‍‍‍‍‍‍

and edited a little later after that....

I took a look at get list of highlighted features within a selection and the response from Richard Fairhurst provided a little inspiration to get the number of selected records:

if int(arcpy.Describe("view").FIDSet[0]) >= 1:
   i = 0
   rows = arcpy.da.SearchCursor("view","*")
   for row in rows:
      i = i+1
        
print 'Rows selected = {}'.format(i)
‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

RandyBurton — Sun, 12 Dec 2021 09:55:04 GMT

In your code you are using a function to find a set of unique values, but the function isn't returning anything (therefore, it equals None). Add a line to "return uniqueValues".

def unique(fc, field):
    values = [row[0] for row in arcpy.da.SearchCursor(fc,(field))]
    uniqueValues = set(values)
    print(uniqueValues)

    return uniqueValues  # unique should return a value

Vals = unique(fc, field)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Also if the goal is to count the number of records with a certain attribute, you can modify the function to return a count of the unique records and you will not need to use SelectLayerByAttribute. You would use just one Search Cursor.

import arcpy

fc = r'C:\Path\To\file.gdb\feature'
field = 'FieldName' # not an alias

d = {}

with arcpy.da.SearchCursor(fc, (field)) as rows:
    for row in rows:
        if row[0] not in d:  # add to dictionary with value of 1
            d[row[0]] = 1
        else: # it is in dictionary, just incrment counter
            d[row[0]] += 1


for k, v in d.iteritems(): # print results
    print k, v


# ===  same idea, but as a function

def unique2(fc, field):
    ud = {}
    with arcpy.da.SearchCursor(fc, (field)) as rows:
        for row in rows:
            if row[0] not in ud:
                ud[row[0]] = 1
            else:
                ud[row[0]] += 1
    return ud

Vals2 = unique2(fc, field)

print 'Vals2:'
for k, v in Vals2.iteritems():
    print k, v‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

ChrisParkin2 — Sun, 12 Dec 2021 09:55:07 GMT

I did something similar earlier in the week. essentially you need to iterate over the unique list, you don't need the 2nd cursor.

import arcpy
arcpy.env.workspace = r'C:\Workarea\Temp\geonetexample.gdb'
# Build list of unique LinkKeys
unique_LinkKey_List = []
with arcpy.da.SearchCursor('PLOTS_Test_lyr', ['LinkKey']) as cursor:
    for key in cursor:
        if key[0] not in unique_LinkKey_List:
            unique_LinkKey_List.append(key[0])

# iterate over unique keys
for key in unique_LinkKey_List:
    whereClause = "LinkKey = '%s'" % key
    arcpy.SelectLayerByAttribute_management('PLOTS_Test_lyr', 'NEW_SELECTION', whereClause)
    
    outFC = 'Key_FC_%s' % key
    arcpy.CopyFeatures_management('PLOTS_Test_lyr', outFC)
    cnt = arcpy.GetCount_management('PLOTS_Test_lyr')
    arcpy.AddMessage('Exported %s Records as %s' % (cnt, outFC))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

wwnde — Fri, 16 Mar 2018 22:47:18 GMT

Joe, very useful. it worked, importantly it introduced me to the arcpy.Describe. Thanks

Re: Select unique values from field using selectbyattribute

wwnde — Sun, 12 Dec 2021 09:55:09 GMT

Thanks, Randy, Dan, Joe and Chris. Very helpful insights. Didn't have time in the week to come back to it. Borrowing from your posts, it ended up well. Have come up with the below code and fed it into a model builder tool. It worked. Thanks

# Import arcpy module
#Identify unique linkkeys, select and save them for input into bulk distance network analyst model
import arcpy


# Set environmental variables

arcpy.env.overwriteOutput = True

arcpy.env.workspace =r"C:\Users\William\Desktop\mt barker\DemoInventory.gdb"
fc = 'PLOTS_Test'
field = 'LinkKey'

#def unique values in LinKey method 1 and it works
def unique(fc, field):
 values = [row[0] for row in arcpy.da.SearchCursor(fc, field)]
 uniqueValues = set(values)

 print(uniqueValues)
 
 return uniqueValues # unique should return a value 

unique_LinkKey = unique(fc, field)#as a function


print 'unique_LinkKey:'


PLOTS_Test_lyr = arcpy.MakeFeatureLayer_management("PLOTS_Test", "PLOTS_Test_lyr")#layer from the feature class

for individual in unique_LinkKey:
 whereClause = "LinkKey = '%s'" % individual
 print(whereClause)
 arcpy.SelectLayerByAttribute_management(PLOTS_Test_lyr, 'NEW_SELECTION', whereClause)
 
 
 arcpy.CopyFeatures_management(PLOTS_Test_lyr, "sasa1")#Copying features from selection
 cnt = arcpy.GetCount_management("PLOTS_Test_lyr") # Counting the selected and copied features
 print("The number of selected records is: " + str(cnt))
 arcpy.SelectLayerByAttribute_management(PLOTS_Test_lyr, "CLEAR_SELECTION")
 print("The number of selected records is: " + str(cnt))

Re: Select unique values from field using selectbyattribute

DanPatterson_Retired — Sun, 12 Dec 2021 09:55:12 GMT

To simply get the unique values from a field, whether you are using cursors for other purposes or not, you can use the table/featureclass and pull out the unique values from it

# --- one import
import numpy as np

# --- you already know the table source
in_tbl = 'C:\\GIS\\A_Tools_scripts\\Numpy_arc\\Numpy_arc.gdb\\sample_1000'

# --- ditto for the field name, use an arcpy.da function
arr = arcpy.da.TableToNumPyArray(in_tbl, field_names=['County'])

# --- and a simple numpy function
uni = [i[0] for i in np.unique(arr)]

# --- there it is (actually, they were the unique values in the field)
uni
['A', 'B', 'C', 'D']‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

wwnde — Mon, 19 Mar 2018 07:27:04 GMT

Though this script does what I need to do, I would be much happier if it can select within the selected features based on field CWY. CWY has five sets of attributes which could apply to any of the attributes in ROUTE_NE_ID. How can I do an exp for the below command without necessarily a definition for unique values in CWY again?

arcpy.SelectLayerByAttribute_management(road_lyr, 'SUBSET_SELECTION', exp)

Re: Select unique values from field using selectbyattribute

ChrisParkin2 — Sun, 12 Dec 2021 09:55:15 GMT

I'd probably use a definition query to make the initial selection, then use a search cursor on the definition query.

At least that's what I did last week.

Though I've probably misunderstood the question.

# Chris Parkin, 14/3/2018
import arcpy

mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]

# This must be sorted chronologically beforehand
visitLayer = arcpy.mapping.ListLayers(mxd, 'Visits_Inspectors_XY_XY_SortedDatetime', df)[0]

# Build list of inspectors
inspectors = []
with arcpy.da.SearchCursor(visitLayer, ['Assigned_inspectors']) as cursor:
    for inspector in cursor:
        if inspector[0] not in inspectors:
            inspectors.append(inspector[0])

lastIter = ['#','#','#','#','#','#','#','#','#','#']
for inspector in inspectors:
        visitLayer.definitionQuery = "Assigned_inspectors = '%s'" % inspector
        with arcpy.da.UpdateCursor(visitLayer, ['last_action_date', 'X_Inspector', 'Y_Inspector', 'X_Visit', 'Y_Visit', 'X_Start', 'Y_Start', 'X_End', 'Y_End', 'Day']) as cursor:
            for inspector in cursor:
                if inspector[9] == lastIter[9]:
                    # Start
                    inspector[5] = lastIter[3]
                    inspector[6] = lastIter[4]
                    # End
                    inspector[7] = inspector[3]
                    inspector[8] = inspector[4]
                else:
                    # Start
                    inspector[5] = inspector[1]
                    inspector[6] = inspector[2]
                    # End
                    inspector[7] = inspector[3]
                    inspector[8] = inspector[4]
                cursor.updateRow(inspector)
                lastIter = inspector
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Select unique values from field using selectbyattribute

wwnde — Tue, 20 Mar 2018 02:20:49 GMT

Hi Chris

That may not be suitable here, this script is part of a larger model. It searches a database and gives outputs to be fed in consequent processes. May not be feasible to pull out an mxd and iterate. All I need is a suitable expression that will pull out a subselection and copy features. Does that make sense?