Select by frequency

Question

I've got a number of shapefiles with some redundant data. I'm looking for a way to select where a single attribute occurs more than a certain number of times (in this case four) and remove them from the table. As I've searched online, all the results refer to the frequency tool, which is not what I need.

For example, in the image below, FIDs 11,12,13,and 14 are all of interest to me, but 15 and 16 are not. I want to select those and delete them.

I'm using Python because there are hundreds of points in a few dozen shapefiles that I've got, so automation is key.

I'm not looking for somebody to edit my code or write some code for me, I just need an idea of what tools I might use to accomplish this. I don't know how to go about selecting fields when the frequency of an attribute hits a certain frequency.

Answer 1

You'll want to use a cursor to figure out the rows that have values that have already occurred four times. Then you can create an SQL and a feature layer or table view with the SQL applied. Finally you can delete rows from the feature layer or table view.

Sample code:

#Table/shapefile
table = r"C:\Users\e1b8\Desktop\E1B8\Workspace\Workspace.gdb\abc123"

#Field to check
field = "VALUE"

#Max number of occurances
maxNum = 4

import arcpy

#dictionary used for counting
di = {}

#list of oids with value counts > maxNum
delOids = []

##iterate table with cursor
with arcpy.da.SearchCursor (table, [field, "OID@"]) as cursor:
    for value, oid in cursor:
        #check if value is in dictionary
        if not value in di:
            #update dictionary
            #key = table value, value = 1
            di [value] = 1
            continue

        #if value in dictionary, check if occurrence equals maxNum
        if di [value] == maxNum:
            #add value to list of oids to delete
            delOids += [oid]
            continue

        #value in dictionary and occurance is not greater than maxNum
        #add one to dictionary value
        di [value] += 1

#delete cursor
del cursor

#check if OIDs are found
if delOids:

    ##create sql for row deletion
    #get table OID field name
    oidFld = arcpy.Describe (table).OIDFieldName
    #add field delimiters to oid field name
    delimFld = arcpy.AddFieldDelimiters (table, oidFld)

    #create string with comma-seperated oids
    oidStr = ", ".join (map (str, delOids))

    #sql
    sql = "{0} IN ({1})".format (delimFld, oidStr)

    #create layer or table view
    try:
        arcpy.MakeFeatureLayer_management (table, "layer", sql)
    except arcpy.ExecuteError:
        arcpy.MakeTableView_management (table, "layer", sql)


    #delete rows
    arcpy.DeleteRows_management ("layer")

    #delete layer
    arcpy.Delete_management ("layer")

Before and after: