Find Highest Values in a Field

776
7
06-21-2019 05:22 AM
ZacharyHart
Occasional Contributor III

By this I don't mean the highest value in a field. What I'm looking for is to find the highest (or lowest) 5 (or whatever value) records in a field. In this case, the field is a distance, so none of the examples I've found related to finding the highest occurrence of say a string value will will work here.

Could this be done with a query alone?

0 Kudos
7 Replies
forestknutsen1
MVP Regular Contributor

Are you working in a file geodatabase or sde?

0 Kudos
ZacharyHart
Occasional Contributor III

An important question! Not and enterprise GDB, using fGDB mostly for intermediate data in a longer process. thanks.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I don't have time to rough out any code, but just use a cursor and SQL clause to sort the records either ascending or descending, and then grab the first x number of records from the cursor.

ZacharyHart
Occasional Contributor III

I found some something that works over on SE.  A thanks to ianbroad for this one, I was able to modify it...so super simple. If anyone has any additional suggestions, that would be great too!

fc=Feature
field = "DIST"
values = [row[0] for row in arcpy.da.SearchCursor(fc, field)]
top_eight = sorted(values)[:8]
query = "\"{0}\" in {1}".format(field, tuple(top_eight))
arcpy.MakeFeatureLayer_management(fc, "top_8_layer", query)
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Zachary Hart‌, the code you provided will provide the lowest/bottom 8 values, not the highest/top.

>>> l = [1,4,5,3,7,8,6,9,2,0]
>>> sorted(l)[:8]
[0, 1, 2, 3, 4, 5, 6, 7]
>>> 

To get the last eight items in your sorted list:

>>> l = [1,4,5,3,7,8,6,9,2,0]
>>> sorted(l)[-8:]
[2, 3, 4, 5, 6, 7, 8, 9]
>>> 

Or, I suggest using reverse=True with sorted:

>>> l = [1,4,5,3,7,8,6,9,2,0]
>>> sorted(l, reverse=True)[:8]
[9, 8, 7, 6, 5, 4, 3, 2]
>>> 
DanPatterson_Retired
MVP Emeritus
# ---- if you know the field name and type, you can get the max or min or whatever
#      directly.  If you need multiple values, argmax or sort and slice

mx = np.max(arcpy.da.TableToNumPyArray(in_fc, "Points").view('int'))  # or 'float'
DanPatterson_Retired
MVP Emeritus

re-reading your question, to you mean the 'highest' as in maximum or 'highest' as in most frequently occurring value?

If it is the latter, It can be done to

ar = arcpy.da.TableToNumPyArray(in_fc, "Points").view('int')  # or 'float'

uni, cnts = np.unique(ar, return_counts=True)

vals = np.array(list(zip(uni, cnts)))

vals  # ---- unique vs count
array([[2, 2],
       [4, 1]], dtype=int64)