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 |
To
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-... - 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!
I suspect there are several ways to resolve this and some clever SQL. But this is how I would do it.
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
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.
@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?
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.
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-...
You might ultimately find that file geodatabase SQL is too buggy/problematic.