How to select rows with highest value per group?

Question

I have a .DBF file that contains the number of establishments for each major industry in every county in Florida. I would like to select each of the industries with the largest number of establishments for each county.

Basically I would like to select each unique [GEO.id2] with the greatest [SUM_ESTAB]. I've provided a sample of the database in csv format here:

OBJECTID,GEO_id2,GEO_display_label,NAICS_display_label,FREQUENCY,SUM_ESTAB
1,12001,"Alachua County, Florida",Accommodation and food services,3,364
2,12001,"Alachua County, Florida",Administrative and support and waste management and remediation services,3,306
3,12001,"Alachua County, Florida","Agriculture, forestry, fishing and hunting",3,16
4,12001,"Alachua County, Florida","Arts, entertainment, and recreation",3,90
5,12001,"Alachua County, Florida",Construction,3,430
6,12001,"Alachua County, Florida",Educational services,3,86
7,12001,"Alachua County, Florida",Finance and insurance,3,297
8,12001,"Alachua County, Florida",Health care and social assistance,3,638
9,12001,"Alachua County, Florida",Industries not classified,3,7
10,12001,"Alachua County, Florida",Information,3,121
11,12001,"Alachua County, Florida",Management of companies and enterprises,3,22
12,12001,"Alachua County, Florida",Manufacturing,3,122
13,12001,"Alachua County, Florida","Mining, quarrying, and oil and gas extraction",3,2
14,12001,"Alachua County, Florida",Other services (except public administration),3,535
15,12001,"Alachua County, Florida","Professional, scientific, and technical services",3,794
16,12001,"Alachua County, Florida",Real estate and rental and leasing,3,342
17,12001,"Alachua County, Florida",Retail trade,3,802
18,12001,"Alachua County, Florida",Transportation and warehousing,3,61
19,12001,"Alachua County, Florida",Utilities,3,7
20,12001,"Alachua County, Florida",Wholesale trade,3,185
21,12003,"Baker County, Florida",Accommodation and food services,3,20
22,12003,"Baker County, Florida",Administrative and support and waste management and remediation services,3,17
23,12003,"Baker County, Florida","Agriculture, forestry, fishing and hunting",3,3
24,12003,"Baker County, Florida","Arts, entertainment, and recreation",3,4
25,12003,"Baker County, Florida",Construction,3,50
26,12003,"Baker County, Florida",Educational services,3,3
27,12003,"Baker County, Florida",Finance and insurance,3,20
28,12003,"Baker County, Florida",Health care and social assistance,3,32
29,12003,"Baker County, Florida",Industries not classified,3,1
30,12003,"Baker County, Florida",Information,3,6
31,12003,"Baker County, Florida",Management of companies and enterprises,3,2
32,12003,"Baker County, Florida",Manufacturing,3,4
33,12003,"Baker County, Florida","Mining, quarrying, and oil and gas extraction",3,1
34,12003,"Baker County, Florida",Other services (except public administration),3,52
35,12003,"Baker County, Florida","Professional, scientific, and technical services",3,17
36,12003,"Baker County, Florida",Real estate and rental and leasing,3,5
37,12003,"Baker County, Florida",Retail trade,3,69
38,12003,"Baker County, Florida",Transportation and warehousing,3,23
39,12003,"Baker County, Florida",Wholesale trade,3,12
40,12005,"Bay County, Florida",Accommodation and food services,3,309
41,12005,"Bay County, Florida",Administrative and support and waste management and remediation services,3,201
42,12005,"Bay County, Florida","Agriculture, forestry, fishing and hunting",3,14
43,12005,"Bay County, Florida","Arts, entertainment, and recreation",3,60
44,12005,"Bay County, Florida",Construction,3,422
45,12005,"Bay County, Florida",Educational services,3,26
46,12005,"Bay County, Florida",Finance and insurance,3,217
47,12005,"Bay County, Florida",Health care and social assistance,3,463
48,12005,"Bay County, Florida",Industries not classified,3,3
49,12005,"Bay County, Florida",Information,3,59
50,12005,"Bay County, Florida",Management of companies and enterprises,3,15
51,12005,"Bay County, Florida",Manufacturing,3,79
52,12005,"Bay County, Florida","Mining, quarrying, and oil and gas extraction",3,2
53,12005,"Bay County, Florida",Other services (except public administration),3,397
54,12005,"Bay County, Florida","Professional, scientific, and technical services",3,423
55,12005,"Bay County, Florida",Real estate and rental and leasing,3,290
56,12005,"Bay County, Florida",Retail trade,3,761
57,12005,"Bay County, Florida",Transportation and warehousing,3,98
58,12005,"Bay County, Florida",Utilities,3,1
59,12005,"Bay County, Florida",Wholesale trade,3,160

Would this be easier done in python or is there a SQL statement that could pull this off?

Could you give a sample as comma-delimited plain text (not as screenshot)? — StefanBrand_EOX, Jun 27 '18 at 17:28
Concatenate ind and county into one column, summarise or frequency it. Sort ascending by frequency and delete duplicates. — FelixIP, Jun 27 '18 at 18:12
Possible duplicate of How to use the SQL in select by attribute to select the entries for each values in certain field? — fatih_dur, Jun 28 '18 at 05:00
I think i would be a shame to close the question since the possible duplicate is not answered and this question has a python element to it. — BERA, Jun 29 '18 at 06:46
I don't think this is a duplicate because the said duplicate is about using SQL to do the same, and the SQL solution do not work on .DBF files in this question — tinlyx, Jul 03 '18 at 14:11

BERA · Accepted Answer · 2018-06-29T06:27:43.323

From what I can see there are no duplicate industries in one county so no need to use industry field. You can use arcpy, execute code in the python window with the table added to the map and the largest rows in each county will be selected:

import arcpy
#Modify these two rows to match the name of your table and fields (dont change OID@)
layer = 'LESS_THAN_20_SMRY'
fields = ['GEO.display-label','SUM_ESTAB','OID@'] #[groupfield, valuefield,'OID@']

#List all rows
all_rows = [i for i in arcpy.da.SearchCursor(layer,fields)]
#Sort by valuefield
all_rows_sorted = sorted(all_rows, key=lambda x: x[1])

#Create a dictionary of group and the objectid with the highest value (since it will come last in sorted list and overwrite all others).
d={}
for row in all_rows_sorted:
    d[row[0]] = row[2]

oids_to_select = [k for k in d.values()]

#Create a SQL query from oid list and select
sql = """{0} IN({1})""".format(arcpy.AddFieldDelimiters(layer,arcpy.Describe(layer).OIDFieldname),
                               ','.join([str(o) for o in oids_to_select]))
arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer, where_clause=sql)

Screenshot from Pro but it is the same in ArcMap:

score 1 · Answer 2 · answered Jun 27 '18 at 19:22

According to this ArcGIS Technical Support article you can use:

SUM_ESTAB in (SELECT max( SUM_ESTAB ) FROM LESS_THEN_20_SMRY GROUP BY GEO.id2)

I cannot test this at home, but in QGIS this works:

"SUM_ESTAB" = maximum(to_int("SUM_ESTAB"), group_by:="GEO_id2")

PS.: There is this similar question: How to use the SQL in select by attribute to select the entries for each values in certain field?

How to select rows with highest value per group?

2 Answers2