topic Re: Find duplicates, update only one record in Python Questions

Find duplicates, update only one record

2Quiker — Sun, 12 Dec 2021 07:50:48 GMT

I would like to only populate one of the duplicate record but with number of duplicates like below.

How can do this or what is the best way to achieve this with python?

well_id      Dup

D36528   3

D36528

D36528

D36532   2

D36532

D36521

D36522

D36525   2

D36525‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Find duplicates, update only one record

forestknutsen1 — Sun, 12 Dec 2021 07:50:50 GMT

You could use dictionaries to count the number of repeating ids. Maybe something like this...

ids = ['D36528',
       'D36528',
       'D36528',
       'D36532',
       'D36532',
       'D36521',
       'D36522',
       'D36525',
       'D36525', ]

ids_dict = {}

uids = list(set(ids))

for uid in uids:
    ids_dict[uid] = []

for id in ids:
    ids_dict[id] = ids_dict[id] + [id]

for k, v in ids_dict.items():
    print '{k}, {n}'.format(k=k, n=len(v))

Output:

D36532, 2
D36528, 3
D36521, 1
D36522, 1
D36525, 2

Re: Find duplicates, update only one record

DanPatterson — Sun, 12 Dec 2021 07:50:53 GMT

Or for something completely different... out to *.dbf or *.csv

import numpy as np
from arcpy.da import NumPyArrayToTable
#
ids = ['D36528', 'D36528', 'D36528','D36532', 'D36532',
       'D36521', 'D36522','D36525', 'D36525', ]
# ---- numpy magic
u, c = np.unique(ids, return_counts=True)
dt = np.dtype([("Class", "<U7"), ("Count", "i4")])
uc = np.asarray(list(zip(u, c)), dtype=dt)
# ---- output to dbf and csv
NumPyArrayToTable(uc, "c:/temp/out.dbf")
np.savetxt("c:/temp/out.csv", uniq_counts, fmt="%7s, %3s", 
           comments="", header="Class, Counts")‍‍‍‍‍‍‍‍‍‍‍‍‍

The csv as an example

Class, Counts
 D36521,   1
 D36522,   1
 D36525,   2
 D36528,   3
 D36532,   2‍‍‍‍‍‍

You can add the *.csv or *.dbf to arcmap or Pro, or join to an existing table

Re: Find duplicates, update only one record

2Quiker — Fri, 14 Aug 2020 17:34:10 GMT

How do reading the valuse from a dictionary use dict for with arcpy.da.UpdateCursor?

Re: Find duplicates, update only one record

forestknutsen1 — Sat, 15 Aug 2020 01:05:16 GMT

I am not sure I understand your question. Maybe it would be helpful if we had a little more info...

what format is your original data is in? e.g. csv, feature class in a fgdb, database... etc.
what is your target format? e.g. feature class in a fgdb
can you give us an overview of what you are trying to do?

I would use an update cursor to write the info from the dictionary back to a feature class in a fgdb for sure.

Re: Find duplicates, update only one record

2Quiker — Mon, 17 Aug 2020 14:46:48 GMT

Data is in a feature class in a fgdb.

I want to be able to see how many times an attribute is duplicated in a the field well_Id but I don't need all of them to have a number in the Dup filed but if that is the only way then that is ok. How do I take k & n and write to the feature class thought update cursor?

Re: Find duplicates, update only one record

forestknutsen1 — Sun, 12 Dec 2021 07:50:56 GMT

You could use a search cursor to read in the ids and then an update cursor the write the count out the feature class.

import arcpy

fc_path = r'C:\my_temp\junk3\geo\fgdb.gdb\wells'

ids = []

with arcpy.da.SearchCursor(fc_path, 'ID') as cursor:
    for row in cursor:
        ids.append(row[0])

# make empty dict
ids_dict = {}

# get a list of unique ids, e.g. remove duplicates, set does this 
# then just convert it back to a list
uids = list(set(ids))

# for each uid make a dict item with the key the uid and an empty list as the value 
for uid in uids:
    ids_dict[uid] = []

# this is what sets you up for counting. it finds the matching well id keys 
# and adds the key to the list value so you get in the dict {'w1': [w1, w1, w1]}
# then all you need to do count the length of the value list.
for id in ids:
    ids_dict[id] = ids_dict[id] + [id]

with arcpy.da.UpdateCursor(fc_path, ['ID', 'DUP']) as cursor:
    for row in cursor:
        for k, v in ids_dict.items():
            if row[0] == k and v is not None:
                row[1] = len(v)
                ids_dict[k] = None
                cursor.updateRow(row)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Find duplicates, update only one record

2Quiker — Mon, 17 Aug 2020 20:24:52 GMT

Thank for sharing.

Can you explain what lines 11-19 are doing please?

Re: Find duplicates, update only one record

forestknutsen1 — Mon, 17 Aug 2020 22:16:30 GMT

I added comments to the code above. I would get it into a python IDE and step through it with a debugger. Doing this when I am taking on something new speeds up my learning... it takes away the "back box" effect.

Re: Find duplicates, update only one record

JoshuaBixby — Sun, 12 Dec 2021 07:50:58 GMT

In terms of semantics, you are calling the field you want to populate "Dup", but you are putting the total count of records in that field unless there is only 1 record. As Dan and Forest have already implied, it is probably better to go with a field name like "Count" and then put the total count of records for all records. If you want to know which records have a duplicate, triplicate, etc...; you simply query on Count being greater than 1.

Along the lines of Forest's answer, but using slightly more compact code without having to create two cursors:

from arcpy.da import UpdateCursor
from collections import Counter

fc = # path to feature class

with UpdateCursor(fc, ["well_id", "Dup"]) as cur:
    cnt = Counter(row[0] for row in cur)
    cur.reset()
    
    for row in cur:
        cur.updateRow([row[0], cnt.pop(row[0], None)])
    

 ‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Find duplicates, update only one record

forestknutsen1 — Tue, 18 Aug 2020 21:18:50 GMT

I like your's best Joshua, nice and clean

Re: Find duplicates, update only one record

JoshuaBixby — Tue, 18 Aug 2020 21:37:17 GMT

The Counter object was purpose built for situations like this. Granted, it is fairly easy to implement with a dict or defaultdict, but there are some handy performance and syntax optimizations using the custom object.

Re: Find duplicates, update only one record

forestknutsen1 — Tue, 18 Aug 2020 23:06:33 GMT

I see that now -- I googled the Counter object right after reading your post. I am going to add it to my python toolbox.