Iterating using search cursor and layer selection performance?

Question

I'm trying to use the results of a search cursor to iteratively make selections on a feature class and then create new feature classes (so you get a new feature class or shp for each unique value in a field... kind of an explode by attribute tool).

As an example - if you had a feature class containing 14763 records and one of the fields had 18 unique values in it, after running the script you would have 18 new feature classes in your gdb.

So far I have this:

def unique_values(table, field):
with arcpy.da.SearchCursor(table, [field]) as cursor:
    dict = sorted({x[0] for x in cursor})
    print type(dict)
    for i in range(len(dict)):

        whereD = dict[i]
        fname = whereD[0:5] + "_" + whereD[-4:]
        arcpy.MakeFeatureLayer_management(table, fname)
        query = "relatedBirds_Species = '" + whereD + "'"
        arcpy.SelectLayerByAttribute_management(fname, "NEW_SELECTION", query)
        arcpy.CopyFeatures_management(fname,fname+ "_c")

This works but it's incredibly slow when it runs. Does anyone know of a quicker way to get the same results?

Requested edits below

The Data

Geodatabase workspace The feature class I'm running this with has 18 unique values in the field in question. Total number of records in the original feature class is 14,673

Timings

As the code is above = 2' 35.04"

With the {where clause} added to MakeFeatureLayer (w/out copy feature either)= 1' 27.75" (thanks Stephen)

With Select_analysis = 2' 10.58"

I think most of the time was being taken to actually draw these in Arc as I was running this directly from the python window so I ran it again from cmd with arc closed.

Stephen wins at 57.93" (with copy feature put back in) running from cmd.

Answer 1

If you do decide to use Make Feature Layer, note that it allows you to specify a where clause:

MakeFeatureLayer_management (in_features, out_layer, {where_clause}...

I haven't tested to see whether this improves performance, but you could potentially save some time by running the query while creating the layer, thus omitting the Select By Attributes.

You could also step through your code in a debugger to determine which lines are slow.

Answer 2

I think it will be more ArcPythonic to use the Select (Analysis) tool in place of MakeFeatureLayer, SelectLayerByAttributes and CopyFeatures.

def unique_values(table, field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        dict = sorted({x[0] for x in cursor})
        print type(dict)
        for i in range(len(dict)):
            whereD = dict[i]
            fname = whereD[0:5] + "_" + whereD[-4:]
            query = "relatedBirds_Species = '" + whereD + "'"
            arcpy.Select_analysis(fname, fname+ "_c", query)

To see that Select outperforms Make Feature Layer followed by Copy Features I conducted the following performance test which I ran from IDLE using ArcGIS 10.2.2 for Desktop:

import arcpy,time

# Make test data 15,000 polygons with species A-F representing 10% each
fc = "C:/temp/test.gdb/testFishnet"
if arcpy.Exists(fc):
    arcpy.Delete_management(fc)
arcpy.CreateFishnet_management(fc,"0 0","0 1","0.01","0.01","100","150",labels="NO_LABELS",geometry_type="POLYGON")
arcpy.AddField_management(fc,"SPECIES","TEXT",field_length="1")
dictSpecies = {0:"A",1:"B",2:"C",3:"D",4:"E",5:"F",6:"G",7:"H",8:"I",9:"J",}
with arcpy.da.UpdateCursor(fc,["OID","SPECIES"]) as cursor:
    for row in cursor:
        row[1] = dictSpecies[row[0] % 10]
        cursor.updateRow(row)

arcpy.env.workspace = "C:/temp/test.gdb"

# Clean up any pre-existing feature classes before timing
for sp in ["A","B","C","D","E","F","G","H","I","J"]:
    if arcpy.Exists("Species" + sp):
        arcpy.Delete_management("Species" + sp)

# Time Select on each of 10 species
start = time.clock()
for sp in ["A","B","C","D","E","F","G","H","I","J"]:
    arcpy.Select_analysis(fc,"Species" + sp,'SPECIES = ' + "'" + sp + "'")
elapsed = (time.clock() - start)
print "Time taken using Select: {0:.2f} seconds".format(elapsed)

# Clean up any pre-existing feature classes before timing
for sp in ["A","B","C","D","E","F","G","H","I","J"]:
    if arcpy.Exists("Species" + sp):
        arcpy.Delete_management("Species" + sp)

# Time MakeFeatureLayer+CopyFeatures on each of 10 species
start = time.clock()
for sp in ["A","B","C","D","E","F","G","H","I","J"]:
    arcpy.MakeFeatureLayer_management(fc,'"' + sp + '"','SPECIES = ' + "'" + sp + "'")
    arcpy.CopyFeatures_management('"' + sp + '"',"Species" + sp)
elapsed = (time.clock() - start)
print "Time taken using MakeFeatureLayer+CopyFeatures: {0:.2f} seconds".format(elapsed)

The results of four runs (two with Select done first, and two with it done second) were:

Time taken using Select: 9.94 seconds

Time taken using MakeFeatureLayer+CopyFeatures: 15.64 seconds

Time taken using Select: 10.34 seconds

Time taken using MakeFeatureLayer+CopyFeatures: 14.87 seconds

Time taken using MakeFeatureLayer+CopyFeatures: 14.75 seconds

Time taken using Select: 9.76 seconds

Time taken using MakeFeatureLayer+CopyFeatures: 14.40 seconds

Time taken using Select: 10.28 seconds

On this test data using MakeFeatureLayer and CopyFeatures rather than Select is about 50% slower.

As an aside, because you have a requirement to create feature classes, if you just wanted layers rather than new feature classes, I suspect making a copy of the layer object and then just changing its DefinitionQuery property to be the where clause would be the quickest of all - I am guessing 1-2 seconds.

Answer 3

this tool SplitLayerByAttributes already does what you need.

and a similar thread on this topic is How can I iterate Selection by Attributes?.