topic Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update in Python Questions

How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Thu, 25 Feb 2021 17:17:13 GMT

https://support.esri.com/en/technical-article/000023355

I want to improve on the above script from ESRI that finds duplicate values and writes the values to a new field in a table. It's using an old cursor and it is very slow with large datasets. But, I'm running into problems due to lack of experience.

Their code verbatim for my purposes:

import arcpy ''' This script will count the number of occurences of a value in a field ("field_in") and write them to a new field ("field_out") ''' #path to GDB goes here arcpy.env.workspace = r"C:\pathto\DuplicateTesting.gdb\DuplicateTesting" #name of feature class goes here infeature = "backup_02232021" field_in = "name" field_out = "COUNT_"+field_in arcpy.AddField_management(infeature, field_out,"SHORT") lista= [] cursor1=arcpy.SearchCursor(infeature) for row in cursor1: i=row.getValue(field_in) lista.append(i) del cursor1, row cursor2=arcpy.UpdateCursor(infeature) for row in cursor2: i=row.getValue(field_in) occ=lista.count(i) row.setValue(field_out, occ) cursor2.updateRow(row) del cursor2, row

The following is as far as I got with my improvement. It's throwing an error on the line with the i variable. How can I continue with the UpdateCursor method to put the count of each item in the fields list into its corresponding new field?

Error:

Traceback (most recent call last): File "\\gisfile\GISstaff\Jared\Python Scripts\ArcGISPro\DuplicateFields_updated.py", line 21, in <module> i = row.getValue(row) AttributeError: 'list' object has no attribute 'getValue'

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DanPatterson — Thu, 25 Feb 2021 17:23:51 GMT

try print(row)

and you will see that it isn't what getValue wants

Row—ArcGIS Pro | Documentation

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DavidPike — Thu, 25 Feb 2021 18:48:01 GMT

fields = ["name", "location_string_output", "email_address", "cell_phone_number", "home_phone_number"] for field_out in fields: #add new fields arcpy.AddField_management(infeature, "COUNT_"+field_out,"SHORT") #list is reserved - use soemthing else values_list= [] with arcpy.da.SearchCursor(infeature, fields) as cursor1: #dont append the row to the list, append the values #this isnt perfect either, throwing everything into one list, #ie what if someone puts a home and mobile number as the same #it gets counted as well, so this needs to be improved later for row in cursor1: #access each field value by the index i then append for i in range(0,len(fields)): #you access a value by a row index, this starts at 0 from the #first field input - i.e. "name" field values are at row[0] values_list.append(row[i]) #values_list is now a giant list of every value in all those fields with arcpy.da.UpdateCursor(infeature, fields) as cursor2: for row in cursor2: #the same concept - loop over your fields - the original script was #only designed for 1 field so you need these loops (maybe!) for i in range(0,len(fields)): #get the value of the rield in the row value = row[i] #<-- throws AttributeError here #count how often it appears in values_list occ = values_list.count(i) #you want to know the field it occurs in also! #(i know you want to insert these values into the newly created #fields, but it will be handy to know the field #in the print statement for now #get that field value by using i as an index to access fields list #N.B. !!! this will print alot of values print(fields[i] + " Count - " + str(occ))

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Thu, 25 Feb 2021 19:16:21 GMT

David,

Thanks for the very detailed response! On the first go, it through the following error.

occ = list.count(i) #<-- line that threw the error Error: TypeError: descriptor 'count' requires a 'list' object but received a 'int'

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DavidPike — Thu, 25 Feb 2021 19:24:18 GMT

ah right, I saw that when I posted it but thought I edited it out pretty fast. It must still be pending or something. Then I found another mistake... Try this. I've not tested it.

infeature = r"C:pathto\DuplicateTesting.gdb\DuplicateTesting fields = ["name", "location_string_output", "email_address", "cell_phone_number", "home_phone_number"] for field_out in fields: #add new fields arcpy.AddField_management(infeature, "COUNT_"+field_out,"SHORT") #list is reserved - use soemthing else values_list= [] with arcpy.da.SearchCursor(infeature, fields) as cursor1: #dont append the row to the list, append the values #this isnt perfect either, throwing everything into one list, #ie what if someone puts a home and mobile number as the same #it gets counted as well, so this needs to be improved later for row in cursor1: #access each field value by the index i then append for i in range(0,len(fields)): #you access a value by a row index, this starts at 0 from the #first field input - i.e. "name" field values are at row[0] values_list.append(row[i]) #values_list is now a giant list of every value in all those fields with arcpy.da.UpdateCursor(infeature, fields) as cursor2: for row in cursor2: #the same concept - loop over your fields - the original script was #only designed for 1 field so you need these loops (maybe!) for i in range(0,len(fields)): #get the value of the rield in the row value = row[i] #<-- throws AttributeError here #count how often it appears in values_list occ = values_list.count(value) #you want to know the field it occurs in also! #(i know you want to insert these values into the newly created #fields, but it will be handy to know the field #in the print statement for now #get that field value by using i as an index to access fields list #N.B. !!! this will print alot of values print(fields[i] + " Count - " + str(occ))

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Thu, 25 Feb 2021 19:32:41 GMT

Yep, running now! The range() function was what I was missing (struggling to come up with).

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DavidPike — Thu, 25 Feb 2021 21:19:16 GMT

Should have also printed the value for testing :

print(fields[i] + " - value - " + str(value) + " - Count - " + str(occ))

Would be good to see your final code when you get the appending sorted within that same update cursor.

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Thu, 25 Feb 2021 21:53:50 GMT

Been spinning my wheels. I've tried this amongst other things:

with arcpy.da.UpdateCursor(infeature, fields) as cursor2: for row in cursor2: for i in range(0,len(fields)): value = row[i] occ = values_list.count(value) for field in field_out: cursor2.updateRow(field)

But it gives me this error:

File "pathto\DuplicateFields_updated.py", line 47, in <module> cursor2.updateRow(field) TypeError: sequence size must match size of the row

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DavidPike — Thu, 25 Feb 2021 22:14:26 GMT

Share the whole script. field_out only exists within your initial for loop (I think, but you've not shared the full script). Also a cursor cannot update a field which is not supplied in the fields list when instantiated.

updateRow() method acts on a row - updateRow(row) this updates the row. assign a value to the field by row index then updateRow(row):

if first field supplied is 'name', to update this fields row in the iteration to 'apples':

row[0] = 'apples'

cursor.updateRow(row)

fields = ["name", "location_string_output", "email_address", "cell_phone_number", "home_phone_number"] #list to contain the new fields new_fields = [] for field in fields: field_out = "COUNT_" + field #add the fields to all_fields new_fields.append(field_out) #list to contain all fields, this is used as fields list #in the update cursor - to do this we just add the lists together! all_fields = fields + new_fields

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Thu, 25 Feb 2021 22:19:36 GMT

Sorry.. here's the whole script. I'm in the middle of digesting your latest post.

infeature = r"pathto\DuplicateTesting.gdb\backup_02232021_TESTDELETE" fields = ["name", "location_string_output", "email_address", "cell_phone_number", "home_phone_number"] for field_out in fields: #add new fields arcpy.AddField_management(infeature, "COUNT_"+field_out,"SHORT") values_list= [] with arcpy.da.SearchCursor(infeature, fields) as cursor1: #dont append the row to the list, append the values #this isnt perfect either, throwing everything into one list, #ie what if someone puts a home and mobile number as the same #it gets counted as well, so this needs to be improved later for row in cursor1: #access each field value by the index i then append for i in range(0,len(fields)): #you access a value by a row index, this starts at 0 from the #first field input - i.e. "name" field values are at row[0] values_list.append(row[i]) #values_list is now a giant list of every value in all those fields with arcpy.da.UpdateCursor(infeature, fields) as cursor2: for row in cursor2: #the same concept - loop over your fields - the original script was #only designed for 1 field so you need these loops (maybe!) for i in range(0,len(fields)): #get the value of the field in the row value = row[i] #count how often it appears in values_list occ = values_list.count(value) #you want to know the field it occurs in also! #(i know you want to insert these values into the newly created #fields, but it will be handy to know the field #in the print statement for now #get that field value by using i as an index to access fields list #N.B. !!! this will print alot of values #print(fields[i] + " - value - " + str(value) + " - Count - " + str(occ)) field_out.append(occ)

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DanPatterson — Thu, 25 Feb 2021 23:50:21 GMT

just saying .... Solved: Re: How to indentify duplicate or unique value in ... - GeoNet, The Esri Community numpy 😉

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

Anonymous User — Fri, 26 Feb 2021 13:53:41 GMT

getValue needs an indexed position, but you are giving it the whole list.

If it's just one field: i = row.getValue(row[0]), but you have a list of fields so you need to iterate over them to get all the name and get the count.

Since you are not updating anything you can use a searchcursor instead of the update.

with arcpy.da.SearchCursor(infeature, fields) as cursor2: for row in cursor2: for fld in row: fldname = row.getValue(fld) # needs indexed position here occ = list.count(fldname)

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DanPatterson — Fri, 26 Feb 2021 14:05:11 GMT

if that is what you got when clicking a link, then you some serious malware on your computer

https:// quit spamming others

is the link from above

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Fri, 26 Feb 2021 17:31:17 GMT

So I guess where I'm still stuck is getting the count back into the new fields. I left the cursor2 block at the occ variable.

infeature = r"C:\pathto\backup_02232021_TESTDELETE" #name of feature class goes here fields = ["name", "location_string_output", "email_address", "cell_phone_number", "home_phone_number"] for field_out in fields: #add new fields arcpy.AddField_management(infeature, "COUNT_"+field_out,"SHORT") values_list= [] with arcpy.da.SearchCursor(infeature, fields) as cursor1: for row in cursor1: #access each field value by the index i then append for i in range(0,len(fields)): #you access a value by a row index, this starts at 0 from the #first field input - i.e. "name" field values are at row[0] values_list.append(row[i]) with arcpy.da.SearchCursor(infeature, fields) as cursor2: for row in cursor2: for i in range(0,len(fields)): #get the value of the field in the row value = row[i] #count how often it appears in values_list occ = values_list.count(value)

I'm not sure how to use the below code you provided to update the new fields with the value count?

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DavidPike — Fri, 26 Feb 2021 18:41:38 GMT

I might in-fact be tempted to create a dictionary of values to the number of occurrences, then just map those across into the update cursor - rather than having to run .count() each time.

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

Anonymous User — Fri, 26 Feb 2021 22:02:39 GMT

Instead of trying to do the whole list of fields at once, how about iterating over each field and passing it into the cursors?

edited for refractoring the cursor into list comprehension and removing the r in the string.

excluded = ['OBJECTID', 'Shape', 'Shape_Length', 'Shape_Area'] # exclude fields if you want # set the fieldnames infeatureflds = [f.name for f in arcpy.ListFields(infeature) if f.name not in excluded] for fld in infeatureflds: outfld = f"{fld}_cnts" arcpy.AddField_management(infeature, outfld, "SHORT") listValues = [row[0] for row in arcpy.da.SearchCursor(infeature, fld)] with arcpy.da.UpdateCursor(infeature, [fld, outfld]) as uCur: for row in uCur: row[1] = listValues.count(row[0]) uCur.updateRow(row) print("Done.")

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

DavidPike — Fri, 26 Feb 2021 20:51:11 GMT

Perfect idea.

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

JaredPilbeam2 — Fri, 26 Feb 2021 21:28:44 GMT

I'm at the mercy of you experts. I tried that and it worked flawlessly at first glance. I have a bunch of fields that don't need counted, so that excluded list is great.

Is this a type of f-string?

fr"{fld}_cnts"

I'm giving you guys all the credit for updating this little program. Thank you very much!

Re: How To: Identify duplicate or unique values in ArcGIS Pro-- Update

Anonymous User — Fri, 26 Feb 2021 21:59:39 GMT

The 'f' is python 3 shorthand for .format(). I have a habit of always adding the 'r' in front of strings and it isn't necessary in this case.

Also, you can collapse that first searchcursor that is appending the values to the list to just:

listValues = [row[0] for row in arcpy.da.SearchCursor(infeature, fld)]