1

I have a table (Feature class in FGDB) with 2 columns, one is the "name" and the other is a list of year values as text "Value1".

How do I obtain the lowest 5 values for EACH name?

I am trying to do this with arcpy or even select by attributes however I am lacking SQL skills to make this happen as expected.I tried this with arcpy however it comes down to SQL for this statement. I tried MIN(date) and that is only for one value.

arcpy.SelectbyAttribute_management(r"<my path>"), "New_Selection", ' [Field1] = 'lowest5' AND GROUPBY [NAME] 
  • 1
    Can you give us a screen shot of the table? What sort of feature class/table is it? Is it shapefile, personal GDB, file GDB etc.. can you show us where you're up to with arcpy? – Michael Stimson Aug 16 '17 at 22:12
  • I can't do a screenshot for security reasons, however it is in a FGDB. – Robert C Aug 16 '17 at 22:29
  • 1
    File geodatabases aren't real databases, they don't support the full SQL. If you're ok with python I would use a search cursor to get a list of unique names, then select by each unique name and search cursor again to get the year values as a list, sort the list smallest to largest and return the 5th value in the list to define the where clause for each unique name... where do you need to go from here? – Michael Stimson Aug 16 '17 at 22:35
  • 2
    There should be no need for SQL to do this. Try Summary Statistics to group and order, and cursors to read first 5 values in each group in the output table. – PolyGeo Aug 16 '17 at 22:39
  • 1
    Yup, this is an exercise for Summary Statistics – Vince Aug 16 '17 at 22:40
4

This is one way to do it:

def SelectLowestN(layer,n,groupfield,valuefield):
    import operator
    list1 = sorted([i for i in arcpy.da.SearchCursor(layer,[groupfield,'OID@',valuefield])],key=operator.itemgetter(0,2))
    oids=[]
    d={}
    for item in list1:
        key=item[0]
        if key not in d:
            d[key]=[]
        d[key].append(item[1])
    for key in d:
        oids+=d[key][:n]
    sql="{0} IN ({1})".format(arcpy.AddFieldDelimiters(layer,"OBJECTID"),", ".join(map(str,oids)))
    arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer, 
                                           where_clause=sql)

Call the function:

SelectLowestN("Points111",5,"Name","Year_int")

Result:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.