Creating a subset of XY data by most recent date

Question

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.

Answer 1

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.

Answer 2

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")

Answer 3

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)

Answer 4

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)

Answer 5

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!

Answer 6

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.

Answer 7

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.