topic Re: Find Identical tool Replacement in Python Questions

Find Identical tool Replacement

ClintonCooper1 — Wed, 30 Oct 2013 12:28:35 GMT

I am trying to l create a python script that will be a substitute to the find identical tool. I ran it last night on a large dataset, and it is taking 10+ hours to run. I believe I can run a search cursor and update cursor that will be light years ahead in performance. So far, I have gotten this far with my script:

import arcpy  from arcpy import env env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"  table = "WAYNE"  list = []  with arcpy.da.SearchCursor(table, ["FULL_ADDRESS_NAME"]) as cursor:     for row in cursor:         list.append(row[0])  del row, cursor  with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:     for updateRow in updateRows:         nameValue = updateRow[0]         if nameValue in list:             updateRow[1] = lutDict[nameValue]             updateRows.updateRow(updateRow) del updateRow, updateRows

To be specific for what I am doing, I need to search through a field (that had duplicate values) and return a new value that is a unique number for all the different set of duplicates. For example:

search ID   new Unique ID
aaa        1
aaa        1
bbb        2
ccc        3
ccc        3
aaa        1
ddd        4

So there would be an increment, but based if the number on the field of search ID is unique, and each successive value that is the same search ID would have the same value.

Any thoughts on how to accomplish this? Thanks in advance!!

Re: Find Identical tool Replacement

DouglasSands — Wed, 30 Oct 2013 12:43:31 GMT

import arcpy  from arcpy import env env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"  table = "WAYNE"  list = []  with arcpy.da.SearchCursor(table, ["FULL_ADDRESS_NAME"]) as cursor:     for row in cursor:         list.append(row[0])  del row, cursor  with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:     for updateRow in updateRows:         nameValue = updateRow[0]         if nameValue in list:             updateRow[1] = lutDict[nameValue]             updateRows.updateRow(updateRow) del updateRow, updateRows

This is a good start. A few things though. For one, I would avoid naming any variables you define things like list, dict etc that might get confused with list(), dict() etc. Also, you will probably throw errors because you are calling the row variable updateRow but also calling the method updateRow. This is generally a bad idea, you should keep names unique so the code is clear.

But your general idea is almost right on. My thought would be that you only need the update cursor, you can do without the first cursor if you use a dictionary instead of a list to store the values. Try this (granted that I can't test it for myself):

import arcpy  from arcpy import env env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"  table = "WAYNE"  uniqueValues = {} newID = 1  with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:     for row in updateRows:         nameValue = row[0]         if nameValue in uniqueVals:             row[1] = uniqueValues[nameValue]         else:             newID += 1             uniqueValues[nameValue] = newID             row[1] = newID         updateRows.updateRow(row)               del row, updateRows

And you are right about the cursors - in general they tend to be much faster.

Re: Find Identical tool Replacement

ClintonCooper1 — Wed, 30 Oct 2013 13:59:45 GMT

This worked great! Thank you!

Now if I wanted to find all the duplicates and sum them up, and print that value for each record (basically a like the Frequency tool) what would I need to change to get that to work? Thanks again!

Clinton

Re: Find Identical tool Replacement

DouglasSands — Wed, 30 Oct 2013 15:24:32 GMT

The Summary Statistics tool works exactly like frequency if you specify the inputs correctly, and it only requires ArcGIS Basic (ArcEditor). The python syntax is available at the link.

Re: Find Identical tool Replacement

ClintonCooper1 — Wed, 30 Oct 2013 15:45:44 GMT

The Summary Statistics tool works exactly like frequency if you specify the inputs correctly, and it only requires ArcGIS Basic (ArcEditor). The python syntax is available at the link.

well I am looking for a python data cursor solution for better performance.

Re: Find Identical tool Replacement

JasonScheirer — Wed, 30 Oct 2013 15:48:33 GMT

Summary statistics is written in C++. It will be significantly faster than any Python solution.

Re: Find Identical tool Replacement

ClintonCooper1 — Wed, 30 Oct 2013 15:54:42 GMT

Summary statistics is written in C++. It will be significantly faster than any Python solution.

even with having to add/join the data back into the original table? What I have been doing, is running the Frequency Tool (which creates a new table) and then using a search and update cursor to get the data back into the original file. I guess I am wondering if it would be faster to run a cursor in the original file that calculates the "frequency" and updates the data directly into the original table?

Re: Find Identical tool Replacement

DouglasSands — Sat, 11 Dec 2021 17:40:32 GMT

If you really want to do it all in python, as a first step, add a field called 'FREQ' to the table. Then run this modified version of the above code:

import arcpy

from arcpy import env
env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"

table = "WAYNE"

uniqueValues = {}
values = []
newID = 1

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        values.append(nameValue)
        if nameValue in uniqueVals:
            row[1] = uniqueValues[[nameValue]]
        else:
            newID += 1
            uniqueValues[nameValue] = [newID]
            row[1] = newID
        updateRows.updateRow(row)
            
del row, updateRows

uniqueCount = {}
for val in uniqueValues:
    uniqueCount[val] = values.count(val)

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME", "FREQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        row[1] = uniqueCount[nameValue]
        updateRows.updateRow(row)
        
del row, updateRows

The arcpy.da.UpdateCursor is definitely faster than the field calculator tool, at least in my testing (if you were going to summarize -> join -> calculatefield -> remove join), can't say that they will be than the summary stats alone.

Re: Find Identical tool Replacement

ClintonCooper1 — Sat, 11 Dec 2021 17:40:35 GMT

If you really want to do it all in python, as a first step, add a field called 'FREQ' to the table. Then run this modified version of the above code:

import arcpy

from arcpy import env
env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"

table = "WAYNE"

uniqueValues = {}
values = []
newID = 1

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        values.append(nameValue)
        if nameValue in uniqueVals:
            row[1] = uniqueValues[[nameValue]]
        else:
            newID += 1
            uniqueValues[nameValue] = [newID]
            row[1] = newID
        updateRows.updateRow(row)
            
del row, updateRows

uniqueCount = {}
for val in uniqueValues:
    uniqueCount[val] = values.count(val)

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME", "FREQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        row[1] = uniqueCount[nameValue]
        updateRows.updateRow(row)
        
del row, updateRows

Thanks Doug for the help! On my main file it takes about 10 minutes to run the summarize tool, and another 24 to update it, taking a total time of 34 minutes. I will run this script and report back if the script is faster. Again, thanks for the help. What good resources would you suggest to help with my learning curve in python for acrgis? I do have the python scripting for ArcGIS book, but it only goes over the very basics, and I am now starting to get into deeper code writing. Thanks again!!

Re: Find Identical tool Replacement

ClintonCooper1 — Sat, 11 Dec 2021 17:40:37 GMT

import arcpy

from arcpy import env
env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"

table = "WAYNE"

uniqueValues = {}
values = []
newID = 1

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        values.append(nameValue)
        if nameValue in uniqueVals:
            row[1] = uniqueValues[[nameValue]]
        else:
            newID += 1
            uniqueValues[nameValue] = [newID]
            row[1] = newID
        updateRows.updateRow(row)
            
del row, updateRows

uniqueCount = {}
for val in uniqueValues:
    uniqueCount[val] = values.count(val)

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME", "FREQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        row[1] = uniqueCount[nameValue]
        updateRows.updateRow(row)
        
del row, updateRows

Ran the code, and it is throwing a zero into the FREQ field for all values?

Re: Find Identical tool Replacement

DouglasSands — Sat, 11 Dec 2021 17:40:40 GMT

I'm not sure. Although now that I think about it, I have had trouble in the past assigning values to a row directly from a dictionary. Try this?

import arcpy

from arcpy import env
env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"

table = "WAYNE"

uniqueValues = {}
values = []
newID = 1

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        values.append(nameValue)
        if nameValue in uniqueVals:
            row[1] = uniqueValues[[nameValue]]
        else:
            newID += 1
            uniqueValues[nameValue] = [newID]
            row[1] = newID
        updateRows.updateRow(row)
            
del row
del updateRows

uniqueCount = {}
for val in uniqueValues:
    uniqueCount[val] = values.count(val)

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME", "FREQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        count = uniqueCount[nameValue]
        row[1] = count
        updateRows.updateRow(row)
        
del row
del updateRows

If that doesn't work, try inserting a print statement and printing values after the first cursor to see if it gets populated.

For resources, as someone who taught myself also I found that in general the ESRI online help for each tool has all of the syntax information you need for tools and always has examples. Thats the first place I go when I can't figure something out. Quickly googling "arcpy 10.1 <tool name>" usually gets good results. Also searching the web in general - lots of people have asked lots of questions that have been answered all over the place.

Good luck!

Re: Find Identical tool Replacement

ClintonCooper1 — Sat, 11 Dec 2021 17:40:43 GMT

added this line:

values = [row[0] for row in arcpy.da.SearchCursor(table, ("FULL_ADDRESS_NAME"))]

and it worked with this code:

import arcpy

from arcpy import env
env.workspace = r"C:\Users\ccooper\Desktop\DATA.gdb\WAYNE"

table = "WAYNE"

uniqueValues = {}
values = [row[0] for row in arcpy.da.SearchCursor(table, ("FULL_ADDRESS_NAME"))]
newID = 0

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME","FEAT_SEQ"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        if nameValue in uniqueValues:
            row[1] = uniqueValues[nameValue]
        else:
            newID += 1
            uniqueValues[nameValue] = newID
            row[1] = newID
        updateRows.updateRow(row)

            
del row, updateRows

uniqueCount = {}
for val in uniqueValues:
    uniqueCount[val] = values.count(val)

with arcpy.da.UpdateCursor(table, ["FULL_ADDRESS_NAME", "FREQ_NAME"]) as updateRows:
    for row in updateRows:
        nameValue = row[0]
        row[1] = uniqueCount[nameValue]
        updateRows.updateRow(row)
        
del row, updateRows

on my test data (1/100th) the size of my main file, it ran for 85 seconds.....much slower than my other method. Do you see any in efficiencies in my code that I could change to increase performance?

Re: Find Identical tool Replacement

ChrisSnyder — Sat, 11 Dec 2021 17:40:45 GMT

Summary statistics is written in C++. It will be significantly faster than any Python solution.

Not completely true... The Summary Stats tool (and Frequency tool) seems to run a pre-sort on the dataset as step 1 (at least that's what the tool status says it's doing), which doesn't seem to be neccessary.

The out of the box Summary Statistics tool takes 13 seconds to get the maximum OBJECTID value for each case field value in my table that has 81k records (5855 unique case field values).

By comparison, this Python code run on the same dataset takes 1.6 seconds to generate the same information:

import arcpy, time
myFC = r"C:\my_fgdb.gdb\my_fc"
statDict = {}
statField = "OID@"
caseField = "ELEV"
time1 = time.clock()
searchRows = arcpy.da.SearchCursor(myFC, [statField,caseField])
for searchRow in searchRows:
   statValue, caseValue = searchRow
   if caseValue in statDict:
      statDict[caseValue].append(statValue)
   else:
      statDict[caseValue] = [statValue]
sumDict = {}
for caseValue in statDict:
   sumDict[caseValue] = len(statDict[caseValue]), max(statDict[caseValue])
time2 = time.clock()

In addition, the ESRI Summary Statistics tool (and ther Frequency tool) give incorrect results in the output table when the case field values are either NULL or 0. Which was a bug that got fixed a long time ago, but seems to be back (at least in v10.1 SP1).

As a solution to little issues like this, I too have a little collection of Python-based code/tools I have written over the years that are either bug work arounds or major performace enhancments for some of the out of the box geoprocessing tools.

Re: Find Identical tool Replacement

ClintonCooper1 — Wed, 30 Oct 2013 21:46:23 GMT

In addition, the ESRI Summary Statistics tool (and ther Frequency tool) give incorrect results in the output table when the case field values are either NULL or 0. Which was a bug that got fixed a long time ago, but seems to be back (at least in v10.1 SP1).

As a solution to little issues like this, I too have a little collection of Python-based code/tools I have written over the years that are either bug work arounds or major performace enhancments for some of the out of the box geoprocessing tools.

Based on your solution, what can I do to fix my code as the times are way too high given what you have found?

Re: Find Identical tool Replacement

ChrisSnyder — Sat, 11 Dec 2021 17:40:48 GMT

So per your original post where I assume you had some data like:

ORIG_ID
aaa
aaa
bbb
ccc
ccc
aaa
ddd

And you want it to come out as this:

ORIG_ID SEQ_ID
aaa 1
aaa 1
bbb 2
ccc 3
ccc 3
aaa 1
ddd 4

This code should work:

myFC = r"C:\my_fgdb.gdb\my_fc"
valueSet = set([r[0] for r in arcpy.da.SearchRows(myFC, ["ORIG_ID"])])
valueList = list(valueSet)
valueList.sort()
arcpy.AddField_managment(myFC, "SEQ_ID", "LONG")
updateRows = arcpy.da.UpdateCursor(myFC, ["ORIG_ID","SEQ_ID"])
for updateRow in updateRows:
   updateRow[1] = valueList.index(updateRow[0]) + 1
   updateRows.updateRow(updateRow)
del updateRow, updateRows

Re: Find Identical tool Replacement

ChrisSnyder — Sat, 11 Dec 2021 17:40:51 GMT

Okay, sorry missed the part about you wanting a COUNT field as well... So how about something like:

import arcpy, collections
myFC = r"C:\my_fgdb.gdb\my_fc"
valueList = [r[0] for r in arcpy.da.SearchRows(myFC, ["ORIG_ID"])]
valueDict = collections.Counter(valueList)
uniqueList = valueDict.keys()
uniqueList.sort() #if you want SEQ_ID to be sorted numeric or alphabetic
arcpy.AddField_managment(myFC, "SEQ_ID", "LONG")
arcpy.AddField_managment(myFC, "COUNT", "LONG")
updateRows = arcpy.da.UpdateCursor(myFC, ["ORIG_ID","SEQ_ID","COUNT"])
for updateRow in updateRows:
   updateRow[1] = uniqueList.index(updateRow[0]) + 1
   updateRow[2] = valueDict[updateRow[0]]  
   updateRows.updateRow(updateRow)
del updateRow, updateRows

Re: Find Identical tool Replacement

ClintonCooper1 — Wed, 30 Oct 2013 23:27:47 GMT

This worked great, Thank you!!

Re: Find Identical tool Replacement

ChristalHigdon — Fri, 30 May 2014 20:38:32 GMT

I stumbled upon this after looking for a couple days into my favorite Error 999999 when running the Find Identical tool on a very large dataset in ArcGIS Desktop version 10.2 (in a Python script but also tried it through Toolbox). I noticed it was a 'fixed' bug in 10.1 but I'm guessing the datasets I work with are larger than what they tested as a 'large' dataset. Or else the bug is back in 10.2. Anyway, I was able to code the above solution directly into my Python script and it ran WAY faster than the other method (We're talking close to a 14 hour processing time). Thank you so much for posting this!! It helped me tremendously.