Is there an easy way to identify missing ID numbers in a field?

Question

I have an ID number field and some of the numbers are missing. I know I can identify duplicate numbers in a number field, but can I identify missing numbers. There are almost 5000 numbers and to search for them manually is painful. Thanks.

JoshuaBixby · Answer

In terms of performance, the following will be better since it recycles the same cursor instead of recreating cursors: fc = # path to feature class with arcpy.da.SearchCursor(fc, "OID@") as cur: min_oid, = min(cur) cur.reset() max_oid, = max(cur) cur.reset() missing_oids = set(range(min_oid, max_oid + 1)) - set(oid for oid, in cur)‍‍‍‍‍‍‍‍

JoshuaBixby · Answer

Try using Python sets to your advantage: fc = # path to feature class min_oid, = min(arcpy.da.SearchCursor(fc, "OID@")) max_oid, = max(arcpy.da.SearchCursor(fc ,"OID@")) missing_oids = set(range(min_oid, max_oid + 1)) - set(oid for oid, in arcpy.da.SearchCursor(fc,"OID@"))‍‍‍‍‍

DanPatterson_Retired · Answer

You can calculate your own sequential id field, then compare... the expression is seq_count( first_id_in_field) Do note that geodatabases use 1 as first OBJECTID cnt = 0 # ---- change to 1 if using a geodatabase, 0 for shapefile def seq_count(val): global cnt if cnt >= val : cnt += 1 return cnt # __esri_field_calculator_splitter__ seq_count(0) # ----- change to 1 if using a geodatabase‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ You can then query where the OBJECTID or FID isn't equal to the result you calculate. The big problem is that as soon as it hits a difference then you have to edit it and restart the process. Another method is to calculate the difference between the previous and current. The following isn't tested so no guarantees... but I am sure someone will test and fix it diff = 0 def seq_diff(val): global diff if val - diff != 1 : diff = val return -999 else: diff += 1 return diff seq_diff(!OBJECTID!) # ---- or !FID! for shapefiles‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍