# Creating a subset of XY data by most recent date

I have a large set of well test data that I would like to pull just the most recent test data from. The point data has several years of tests in it, at co-incident points. All of the overlapping points have identical x & y coordinates.I was wondering how best to delete the older test data for each point, leaving me with just one point for each xy.

The date of test is based off of 2 fields [SAMP_DATE] (which is data type "DATE") and [SAMP_TIME] (which is data type "String"). There are multiple samples taken on the same date (at different times), so I will need to use both fields.

Hoping someone can help me figure this out via a repeatable definition query, as I have to pull out the same data for different sample tests (eg. Arsenic, Nitrate, etc.).

Here is what my attribute table looks like, if it helps:

I attempted Using the sort & delete identical method described in the comments below by @Bera & @ FelixIP, but did not achieve the result I wanted. Here is a photo of the results using that method (for one point) The original table is on right & results of Sort/Delete identical processing on the left.

• Sort by date - Delete Identical by fields X and Y – BERA Aug 20 '18 at 19:25
• @BERA will that leave me with only the most recent [SAMP_DATE] and [SAMP_TIME]? How does the deletion hierarchy work when multiple fields are involved? Will an advanced sort (date followed by time) do the trick? – cl3 Aug 20 '18 at 19:29
• As BERA said, sort by date and time, both descending. Delete spatial duplicates. It takes less time to test than ask for clarification. – FelixIP Aug 23 '18 at 19:04
• @FelixIP I did attempt the method described by Bera. Image of results was added to question. It did not produce the wanted outcome. – cl3 Aug 23 '18 at 19:30
• I see. Convert date and time to single double in new field. Or swap to correct date yyyy.mm.dd. – FelixIP Aug 23 '18 at 19:36

## 7 Answers

You can use arcpy. Add your fc to the map and modify and execute code below in the python window of ArcMap then export the selection to a new fc using Copy Features etc. (Your None values in time field made the sorting a bit complicated.)

``````import arcpy,datetime

fc = r"marine_points" #Change to the name of your layer in table of contents
fields = ['X','Y','SAMP_DATE','SAMP_TIME','OID@'] #Change field names to match yours, dont change order or OID@!

all_points = [i for i in arcpy.da.SearchCursor(fc,fields)] #List all points
#Sort by date and time
all_points = sorted(all_points, key=lambda x: (x[2],(x[3] is None, datetime.datetime.strptime(x[3],'%H:%M') if x[3] not in (None, ' ') else None)))
d = {(v[0],v[1]):v[-1] for v in all_points} #Create a dictionary of (X,Y) and OIDS, only keeping last/latest date-time
#Create a sql query and select points by objectid
sql = """{0} IN({1})""".format(arcpy.Describe(fc).OIDFieldName, ','.join([str(oid) for oid in d.values()]))
arcpy.SelectLayerByAttribute_management(in_layer_or_view=fc,
where_clause=sql)
``````

• this script worked the first time I ran it for a contaminant, but all of the subsequent runs gave this error: [Traceback (most recent call last): File "<string>", line 8, in <module> File "<string>", line 8, in <lambda> File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib_strptime.py", line 565, in _strptime_datetime tt, fraction = _strptime(data_string, format) File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib_strptime.py", line 362, in _strptime (data_string, format)) ValueError: time data '24:00' does not match format '%H:%M' ] – cl3 Aug 28 '18 at 14:10
• @cl3 24:00 is not a valid time. Do you want this to be 00:00 the next day or 23:59 the current date? – BERA Aug 28 '18 at 14:23
• Yup, I figured it out. I just field calculated all of the 0s to 00:00 & 24 to 23:59s. Thanks for the help. I really appreciate it when people post code snippets like this. I like learning my way around Python in little chunks like this. – cl3 Aug 28 '18 at 14:26

Maybe you could use the dissolve geoprocessing tool with Point as the dissolve field and specify Date as the statistics field with the MAX statistic type.

http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/dissolve.htm

It has many statistics type available too which has the range, first, last types too.

I suggest you to use GeoPandas python package. You can use it within ArcMap Python Console if you install it.

Sample data:

``````import geopandas as gp
from datetime import datetime

# open data file
df = gp.read_file("C:/PATH/TO/YOUR/FILE.shp")
``````

``````# convert "SAMP_DATE" to datetime and add to new DATE column
# as far as I know, GeoPandas gets date type as string
df["DATE"] = df["SAMP_DATE"].apply(lambda x:datetime.strptime(x, '%m/%d/%Y'))
``````

Note: If date format in your data is different, change `'%m/%d/%Y'` pattern. It seems the pattern is `'%m/%d/%Y'` in your data. Mine is `'%Y-%m-%d'`.

``````# sort, group, get first (most recent)
df_recent = df.sort_values(['X', 'Y', 'DATE', 'SAMP_TIME'], ascending=False) \
.groupby(['X', 'Y']) \ # group by points having same coordinates
.first() \ # get first row in group
.reset_index()
``````

Result of `.sort_values()` and `.groupby()`:

Note: `groupby` preserves the order of rows within each group. `SAMP_TIME` is string and `'15:50'` string is bigger than `'10:30'` string. So, you don't need to convert that string to datetime etc.

Result of `.first()`: (most recent data you want)

``````# remove DATE column
df_recent.drop("DATE", axis=1, inplace=True)

# convert DataFrame to GeoDataFrame
df_recent= gp.GeoDataFrame(df_recent, crs=df.crs)

# Save the most recent data
df_recent.to_file("C:/PATH/TO/NEW/FILE.shp")
``````

All code without comments:

``````import geopandas as gp
from datetime import datetime

df = gp.read_file("C:/PATH/TO/YOUR/FILE.shp")
df["DATE"] = df["SAMP_DATE"].apply(lambda x:datetime.strptime(x, '%m/%d/%Y'))
df_recent = df.sort_values(['X', 'Y', 'DATE', 'SAMP_TIME'], ascending=False).groupby(['X', 'Y']).first().reset_index()

df_recent.drop("DATE", axis=1, inplace=True)
df_recent= gp.GeoDataFrame(df_recent, crs=df.crs)
df_recent.to_file("C:/PATH/TO/NEW/FILE.shp")
``````

Create new field and populate it using:

``````def fix(aDate):
aList=aDate.split("/")
return '%s%s%s' %(aList[2],aList[1].zfill(2),aList[0].zfill(2))
#------
fix (!SAMP_DATE!)
``````

Sort table using this field and SAMP_TIME, both in descending order. Make sure it is yyyy.mm.dd order in a new field. Deleteical points.

Here are steps I followed and got correct results. Do let me know me know how it works for you as it was tested with my own sample data

1. Create new field as `StringTime`
2. calculate ```StringTime = datetime.datetime.strptime( !Date! ,"%d-%m-%Y").strftime('%Y%m%d')```
3. Dissolve layer using field `X_Point` and `Y_Point` and in statistics use `Max(StringTime)` and `Max(Time)`

It's been a while since I have used ArcGIS but I think that Summarize Attributes may do what you want. Summarize on the X & Y fields, taking the minimum of the date & time fields. It wouldn't be a definition query, it would create a new table, but I think that it will achieve the result you want.

I would go to python and arcpy for this sort of problem.

Using a dictionary with the key being the `SHAPE@WKT` and the value being a datetime object created from the `SAMP_DATE` and `SAMP_TIME`, code will be able to see if the point is already in the dictionary and if the datetime is more recent.

To populate the dictionary, create an arcpy.da.SearchCursor and iterate over the fields from above.

There will be 2 cases to handle.

1. The key is not in the dictionary
2. The key is in the dictionary

What to do for those cases...

1. If the `SHAPE@WKT` key is not in the dictionary, create the datetime from the other two fields and insert it.
2. If the key is in the dictionary, create the datetime from the other two fields and compare it to the value already stored in the key. `>` or `<` will allow you to compare what is more recent. If the existing datetime is newer, don't do anything. If the existing datetime is older, replace it with the newer one and repeat.

Now what you do with this dictionary of unique new points is up to you. You could store the `OID@` with the datetime in a set and when the first cursor is done, create a delete cursor and delete any oid not in the dict.keys. Or any time an item is replaced, add it's oid to a list that you can loop over and delete. There are a lot of options at this point. And that will be up to you!