topic Re: Selecting the most recent records based on unique values in another field in ArcGIS Pro Questions

Selecting the most recent records based on unique values in another field

SteveMasters — Tue, 02 Jan 2024 18:33:36 GMT

Hi,

I have built the model in the picture, which works fine. But the next step I want to do is for each of the new feature classes that are created, I want only the most recent record of each unique record from the species field. But I want all the associated fields to come through into the new feature class. See table picture below, for example I only want the most recent record of Sparrowhawk.

I am not sure how to do this and if I can add it into the model as part of the process?

Thanks

Re: Selecting the most recent records based on unique values in another field

Bud — Tue, 02 Jan 2024 19:07:33 GMT

~~What kind of geodatabase is your data in? Have you considered using a database view instead?~~

Never mind. I didn't read the question close enough.

Re: Selecting the most recent records based on unique values in another field

RichardHowe — Wed, 03 Jan 2024 08:51:34 GMT

First off you would need your "Date" field to be of type Date. Nothing programmatic is going to be able to analyse that currently with variants of free text, date formats and ranges. I would suggest using year (if the field was numeric) but I note some duplicates in that case. Once you have a nicely formatted date field then life would be simpler with python, but via model builder, off the top of my head...

Add a split by attributes, use "Species" field to split them and give you one feature class per species. Run a "Select by attributes" on each using:

Date = (SELECT MAX(Date) FROM <insertfeatureclassnamehere>)

Then merge each of the selections back into a single feature class

Re: Selecting the most recent records based on unique values in another field

DavidPike — Wed, 03 Jan 2024 17:55:02 GMT

I think the database view is a good idea, would be some subquery with a GROUP BY species and MAX (date) as @RichardHowe suggests

Re: Selecting the most recent records based on unique values in another field

DavidPike — Wed, 03 Jan 2024 17:56:35 GMT

Probably the best model builder way as I think subqueries (in ArcGIS select by tool) are limited to scalar.

Re: Selecting the most recent records based on unique values in another field

SteveMasters — Thu, 04 Jan 2024 17:35:39 GMT

If I was to create a database view, just struggling with correct syntax to go through the feature class in the file geodatabase what do put after the FROM part see picture. Or am I completely way off!

Thanks

Re: Selecting the most recent records based on unique values in another field

DavidPike — Thu, 04 Jan 2024 17:39:25 GMT

Ah I tried a FGDB view and couldn't subqueries with GROUP BY working. I'd really recommend what @RichardHowe suggested as the most practical way.

Re: Selecting the most recent records based on unique values in another field

SteveMasters — Fri, 05 Jan 2024 10:53:12 GMT

Thanks for this @RichardHowe. I had a go with the model below, and ended up with the species all split out into feature classes, but then am unsure how to get them all back through the select by attribute process and then merge back. Do you have any other tips? Sorry I am new to model builder and python.

Also is there way for the process to cycle through the FGDB and do it for all? I could get a iterator to work and then input to the split by attributes.

Re: Selecting the most recent records based on unique values in another field

RichardHowe — Fri, 05 Jan 2024 11:59:59 GMT

It's be a while since I used model builder tbf. If memory serves you can only have one iterator per model. So you would need to save that model and then embed it in a new one which will allow you to add another iterator which you could use with the "Make feature Layer" tool which you include the aofrementioned query with (giving you one feature per feature class), then use all those feature layers as inputs for the merge tool.

Re: Selecting the most recent records based on unique values in another field

SteveMasters — Fri, 05 Jan 2024 15:51:43 GMT

I am struggling to get it work, I wonder if you might be able to demonstrate what you mean @RichardHowe?

I am happy for it to be python script if easier.

Thanks

Re: Selecting the most recent records based on unique values in another field

DavidPike — Fri, 05 Jan 2024 17:46:33 GMT

Because the output feature classes from the split by attributes tool come from an iterator you can't just connect them to the merge tool (which I think you understand from your previous reply).

I think the recommended way is described here https://community.esri.com/t5/arcgis-pro-questions/merge-outputs-of-an-iterator-modelbuilder/td-p/1180000 by using the Collect Values tool https://pro.arcgis.com/en/pro-app/latest/tool-reference/modelbuilder-toolbox/collect-values.htm to collect the paths of the output feature classes. You would then use the collected values as input to the merge. Obviously before you do the merge you'd want to Select By Attributes on the MAX(Date) as Richard demonstrated.

Re: Selecting the most recent records based on unique values in another field

RichardHowe — Mon, 08 Jan 2024 09:15:34 GMT

Ah yeah, good call. I had forgotten about the collect values option (as I said a while since I ever used MB). I think this should work fine.

If not though, my suggestion was to save what you have @SteveMasters and then create a second blank model. You will then be able to drag and drop your saved model into that blank model (just like any other tool) and then re-use iterator.

Re: Selecting the most recent records based on unique values in another field

SteveMasters — Mon, 08 Jan 2024 18:14:57 GMT

Thanks @DavidPike and @RichardHowe for the collect value suggestions. How do I run the select by attributes tool, to select for max year or rec before collecting the values? I cant add a workspace as an input. If I was to collect the values and then run it, all I'd be doing is splitting and recombine the dataset. Maybe i need to go back to the original dataset before splitting them out into the individual sites?

Sorry this is bit painful! Feel free not to respond!

Thanks

Re: Selecting the most recent records based on unique values in another field

RichardHowe — Tue, 09 Jan 2024 08:39:12 GMT

We already established that query wouldn't work on the initial dataset because you can't subquery the group. The split is important first so that then you can ask it for the newest value within each group.

If colect values isn't allowed as an input the "create feature layer" (a quick look suggest it might not be) then I go back to my original suggestion. Save what you have, create a brand new blank model and then drag and drop your saved model from catalog into the new model and iterate over the output gdb for each feature class and make those the inputs for the "create feature layer"

Re: Selecting the most recent records based on unique values in another field

DavidPike — Tue, 09 Jan 2024 18:29:45 GMT

Yeh I had a look at this and you'll need to go with Richard's suggestion of a submodel as you can't have 2 iterators. The collect values wont work so you need to have an iterator to go over the feature classes in that workspace (output from split by attributes). The iterator would run that select by attributes query for each feature class (which Richard supplied earlier) -> Export to new feature classes -> Collect values -> Merge.

This kinda stuff is where you'd really want Python instead.

Re: Selecting the most recent records based on unique values in another field

DavidPike — Tue, 09 Jan 2024 19:52:09 GMT

This is likely not the fastest, best or most logical way to do it, but it should do the job.

import arcpy #input feature class in_fc = r'C:\path to your FC' #dictionary to store objectid of newest sighting date for each species date_dict = {} #cursor to iterate through each row in fc #if date is more recent than current species value for key #update to the matching oid of that date in dictionary with arcpy.da.SearchCursor(in_fc, ['OBJECTID', 'your species field', 'your date field']) as cursor: for row in cursor: #unpack row oid, species, date = row #if no dictionary value yet, add 1st entry if species not in date_dict: date_dict[species]=[date, oid] #check existing date is older, if so - replace it elif date > date_dict[species][0]: date_dict[species]=[date, oid] #create string of object ids for selection query input oid_list = [value[1] for value in date_dict.values()] selection_string = '(' + ','.join(map(str,oid_list)) +')' #turn into feature layer for selection feature_layer = arcpy.MakeFeatureLayer_management(in_fc, "feature_layer") #select by attributes using oid list/string selection = arcpy.management.SelectLayerByAttribute(feature_layer, "NEW_SELECTION", f'OBJECTID IN {selection_string}') #save your new fc out_fc = r'C:\path to new fc output' arcpy.CopyFeatures_management(selection, out_fc)

Re: Selecting the most recent records based on unique values in another field

JoshuaBixby — Wed, 10 Jan 2024 01:01:12 GMT

David, some Python-related thoughts on your code:

Python defaultdict removes the need to check for a keys existence before comparing a key's value to a new value.
Python min and max functions are purpose-built for comparing values and selecting the lowest or highest
Tuple/list unpacking can be done in the for loop, a separate line isn't needed.
Generator expressions were partially introduced to reduce the need to use filter() and map(), whose functional-programming style/nature somewhat contrasts with many other aspects of the Python language.

import arcpy import datetime from collections import defaultdict # input feature class in_fc = r'C:\path to your FC' # default dictionary to store objectid of newest sighting date for each species date_dict = defaultdict(lambda: (datetime.datetime.min, -1)) # cursor to iterate through each row in fc # store the maximum (most recent) date for each species and its ObjectID with arcpy.da.SearchCursor(in_fc, ['OBJECTID', 'your species field', 'your date field']) as cursor: for oid, species, date in cursor: date_dict[species] = max(date_dict[species], (date, oid)) # create sql statement for selecting ObjectIDs sql = f"OBJECTID IN ({','.join(str(oid) for date,oid in date_dict.values())})" # turn into feature layer for selection feature_layer = arcpy.MakeFeatureLayer_management(in_fc, "feature_layer") # select by attributes using oid list/string selection = arcpy.management.SelectLayerByAttribute(feature_layer, "NEW_SELECTION", f'OBJECTID IN {selection_string}') # save your new fc out_fc = r'C:\path to new fc output' arcpy.CopyFeatures_management(selection, out_fc)

Re: Selecting the most recent records based on unique values in another field

DavidPike — Wed, 10 Jan 2024 18:43:51 GMT

Thanks Joshua, much neater (and optimised). I wonder about the (date, oid) tuple and whether something like having 'date' and 'oid' as keys in their own dictionary (these keys being values of date_dict..) and if that would be faster or slower.

My only other thoughts are another idea of turning the feature class into some data format that could work with the SQL subquery with max(date) and groupBy species, but the juice might not be worth the squeeze on that one.

Re: Selecting the most recent records based on unique values in another field

SteveMasters — Thu, 11 Jan 2024 12:19:37 GMT

Still trying in model builder. The above I tried with just one of the feature classes I need iterate through to get to my result. So this will go through the input feature class and split by attribute on 'species', works they all end up in the output geodatabase as separate feature classes. But then I need to iterate through these using the select by attribute to get the max year of record. But it fails at this point as the output of iterator won't go in. It says 'table not found'.

Re: Selecting the most recent records based on unique values in another field

RichardHowe — Thu, 11 Jan 2024 14:04:06 GMT

You will have to create a layer from the feature classes ("create feature layer" GP tool) then use the select by attribute on that