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]
Improve this question
7
  • 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, 2017 at 22:12
  • I can't do a screenshot for security reasons, however it is in a FGDB.
    – Robert C
    Aug 16, 2017 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, 2017 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, 2017 at 22:39
  • 1
    Yup, this is an exercise for Summary Statistics
    – Vince
    Aug 16, 2017 at 22:40

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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