topic Selecting maximum values of a field, by group in ArcGIS Pro Questions

Selecting maximum values of a field, by group

YusefSamari — Fri, 03 May 2024 12:10:42 GMT

Hi. I am using ArcGIS Pro 3.1.3. I have a table with columns for species, the month each species is most frequently recorded in, and the number of grid squares each species has been recorded from. I have sorted it ascending by month, then descending by grid squares. I want to select the top 5 species by number of grid squares recorded from, for each month (or less if that month has less than 5 species). For example, I would like to go from:

Species	Month	Squares
A	1	20
B	1	20
C	1	19
D	1	18
E	1	16
F	1	10
G	1	9
H	2	40
I	2	32
J	2	28
K	2	17
L	2	10
M	2	9
N	3	20
O	3	19

Species	Month	Squares
A	1	20
B	1	20
C	1	19
D	1	18
E	1	16
H	2	40
I	2	32
J	2	28
K	2	17
L	2	10
N	3	20
O	3	19

Can anyone please help with this? I think the solution should be something similar to what is discussed in this thread - https://community.esri.com/t5/geodatabase-questions/select-maximum-values-in-select-by-attributes/m-p/1369377#M8757 - but I haven't been able to adapt the SQL queries there to my needs. Also not sure what to do in the case of tied values...

Thanks!

Re: Selecting maximum values of a field, by group

DuncanHornby — Fri, 03 May 2024 16:08:52 GMT

I suspect there are several ways to resolve this and some clever SQL. But this is how I would do it.

Firstly the table needs to be a file geodatabase table so you can add a new field. So If your data is in Excel then you need to export it.
Add a new field, call it keep and set it to SHORT.
The following logic takes advantage of the fact you have pre-sorted the data. Open field Calculate tool and apply the following to keep field.

The code block is:

count = 0 last = 0 def tag(month): global count,last if last == 0: # First row last = month count = 1 keep = 1 elif (month == last) and (count < 5): keep = 1 count += 1 elif (month == last) and (count >= 5): keep = 0 count += 1 elif (month != last): keep = 1 count = 1 last = month return keep

Now run a simple select by attribute to select rows where keep = 1
Export selection to a new table.

Re: Selecting maximum values of a field, by group

Bud — Sat, 04 May 2024 16:57:07 GMT

@YusefSamari What kind of geodatabase is the data stored in?

Can you attach sample Excel data (or whatever works, such as a zipped file geodarabase or zipped mobile geodatabase) to this post?

Re: Selecting maximum values of a field, by group

YusefSamari — Mon, 06 May 2024 09:18:38 GMT

Many thanks for this. I will try it out later and report back. I ran out of time last week because I needed to move forward on this so I just used the 'brute force' option in model builder of selecting each month, sorting by squares field, selecting 'OBJECTID <= 5' then merging them.

Re: Selecting maximum values of a field, by group

YusefSamari — Mon, 06 May 2024 09:20:13 GMT

The data is in a File Geodatabase. Sample data is exactly like the sample data above - the 'Species' column is a text field, the other two are long integer.

Re: Selecting maximum values of a field, by group

Bud — Mon, 06 May 2024 14:58:35 GMT

If you're having trouble with the SQL, I would suggest copying the data to a geodatabase type that supports SQL clients, such as a mobile or file geodatabase: https://community.esri.com/t5/geodatabase-questions/select-maximum-values-in-select-by-attributes/m-p/1369748/highlight/true#M8761

You might ultimately find that file geodatabase SQL is too buggy/problematic.