Updating first occurence of duplicate with lowest value in given field using ArcPy cursor

Question

I have a few hundred GPS data points (saved in an ArcGIS Pro 2.9 file geodatabase) that logged simultaneously with different speeds. I am looking to write a Python script that will compare each row's DateTime field, and if they are identical, update the first occurrence with the lowest speed (int field) logged.

INPUT:

|OID| DateTime |Speed|
| 1 | 12/11/2021 16:41:21 | 14 |
| 2 | 12/11/2021 16:41:21 | 12 |
| 3 | 12/11/2021 16:41:32 | 20 |
| 4 | 12/11/2021 16:41:32 | 25 |

TARGET:

|OID| Time |Speed|
| 1 | 12/11/2021 16:41:21 | 12 |
| 2 | 12/11/2021 16:41:32 | 20 |

Below is the code I used to delete exact duplicates:

check_fields = ['OBJECTID', 'SpeedDate' # concatenated speed and date fields]
found_values = defaultdict(set)
with arcpy.da.UpdateCursor(input_dataset, check_fields) as cursor:
 for row in cursor:
 for i, column in enumerate(check_fields):
 if row[i] in found_values[column]:
 cursor.deleteRow()
 break
 else:
 for column, value in zip(check_fields, row):
 found_values[column].add(value)

I'm pretty new to using UpdateCursor and ArcPy in general. I assume the first part of this code will still be useful in creating a set of unique values.

How do I go about assigning the first occurrence a value based on a second row comparison?

Answer 1

Create a list of tuples using da.SearchCursor. Sort the list by date and speed, so the lowest speed for each date comes last. Create a dictionary of datetime:lowest speed. Create a dictionary of OID:lowest speed, update.

import arcpy
fc = r'C:\GIS\ArcMap_default_folder\Default.gdb\datetime_speed'
fieldlist = ['OID@','DateTime','Speed']
all_rows = [row for row in arcpy.da.SearchCursor(in_table=fc, field_names=fieldlist)]
#[(1, datetime.datetime(2010, 4, 24, 0, 0), 0.22), (2, datetime.datetime(2010, 4, 24, 0, 0), 0.9), (13, datetime.datetime(2010, 4, 19, 0, 0), 0.21), (14, datetime.datetime(2010, 4, 19, 0, 0), 0.06), (16, datetime.datetime(2010, 4, 19, 0, 0), 1.0)]
all_rows.sort(key=lambda x: (x[1], x[2]), reverse=True)
#The lowest speed is last for each date:
#[(2, datetime.datetime(2010, 4, 24, 0, 0), 0.9), (1, datetime.datetime(2010, 4, 24, 0, 0), 0.22), (16, datetime.datetime(2010, 4, 19, 0, 0), 1.0), (13, datetime.datetime(2010, 4, 19, 0, 0), 0.21), (14, datetime.datetime(2010, 4, 19, 0, 0), 0.06)]
tempdict = {row[1]:row[2] for row in all_rows}
#Store each date as key and lowest speed in dictionary:
#{datetime.datetime(2010, 4, 24, 0, 0): 0.22, datetime.datetime(2010, 4, 19, 0, 0): 0.06}
#Create a dictionary of OID:lowest speed
updatedict = {row[0]:tempdict[row[1]] for row in all_rows}
#{2: 0.22, 1: 0.22, 16: 0.06, 13: 0.06, 14: 0.06}
#Update the feature class. I store the lowest speeds in a new field
with arcpy.da.UpdateCursor(fc, ['OID@','lowest_speed']) as cursor:
 for row in cursor:
 if row[0] in updatedict:
 row[1] = updatedict[row[0]]
 cursor.updateRow(row)

enter image description here

Answer 2

I think a simpler approach is to use python to call the summary statistics tool to group by date field and return minimum speed value. This can all be done with 1 line of code; so no use of dictionaries or sets required. Just review the code sample at the bottom of the help page to get you going.

• Hornbydd: But your coding approach is interesting too.

Answer 3

Logically, you don't really have a first record and second/subsequent record. If both data points were captured at the same time, as evidenced by the same date-time stamp, then you simply have a slow-speed record and faster records.

I mention this because you don't have to re-assign the slowest speed to the "first" record, you just have to remove all the records but the slowest one. The easiest way to do it is to rely on SQL to sort the data in the cursor before you process it.

The following assumes you are working in ArcGIS Pro.

(arcgispro-py3) C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3>python
Python 3.7.11 [MSC v.1927 64 bit (AMD64)] :: Anaconda, Inc. on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import arcpy
>>> from arcpy.management import CreateTable, AddField
>>> from datetime import datetime
>>>
>>> # Define records for test
>>> values = (
... ("2021年12月11日 16:41:21", 14),
... ("2021年12月11日 16:41:21", 12),
... ("2021年12月11日 16:41:32", 20),
... ("2021年12月11日 16:41:32", 25)
... )
>>>
>>> # Create test table
>>> table = CreateTable("memory", "tmp_tbl")
>>> AddField(table, "DateTime", "DATE")
<Result 'memory\\tmp_tbl'>
>>> AddField(table, "Speed", "LONG")
<Result 'memory\\tmp_tbl'>
>>>
>>> # Populate test table with test records
>>> with arcpy.da.InsertCursor(table, ("DateTime", "Speed")) as cur:
... for dt, speed in values:
... cur.insertRow((datetime.fromisoformat(dt), speed))
...
1
2
3
4
>>> # Print out records to verify table population
>>> print(*arcpy.da.SearchCursor(table, "*"), sep="\n")
(1, datetime.datetime(2021, 12, 11, 16, 41, 21), 14)
(2, datetime.datetime(2021, 12, 11, 16, 41, 21), 12)
(3, datetime.datetime(2021, 12, 11, 16, 41, 32), 20)
(4, datetime.datetime(2021, 12, 11, 16, 41, 32), 25)
>>>
>>> # Loop over records deleting all records but slowest by date-time groups
>>> sql = "ORDER BY DateTime, Speed"
>>> with arcpy.da.UpdateCursor(table, ("DateTime", "Speed"), sql_clause=(None, sql)) as cur:
... dt_prev, speed = next(cur)
... for dt, speed in cur:
... if dt == dt_prev:
... cur.deleteRow()
... dt_prev = dt
...
>>> # Print out records to verify deletions worked correctly
>>> print(*arcpy.da.SearchCursor(table, "*"), sep="\n")
(2, datetime.datetime(2021, 12, 11, 16, 41, 21), 12)
(3, datetime.datetime(2021, 12, 11, 16, 41, 32), 20)
>>>