nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Find duplicates, update only one record

2Quiker

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<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Najděte více příspěvků označených jako

Accepted answers

forestknutsen1

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

<SPAN class="keyword token">import</SPAN> arcpy

fc_path <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\my_temp\junk3\geo\fgdb.gdb\wells'</SPAN>

ids <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fc_path<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ID'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        ids<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># make empty dict</SPAN>
ids_dict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>

<SPAN class="comment token"># get a list of unique ids, e.g. remove duplicates, set does this </SPAN>
<SPAN class="comment token"># then just convert it back to a list</SPAN>
uids <SPAN class="operator token">=</SPAN> list<SPAN class="punctuation token">(</SPAN>set<SPAN class="punctuation token">(</SPAN>ids<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># for each uid make a dict item with the key the uid and an empty list as the value </SPAN>
<SPAN class="keyword token">for</SPAN> uid <SPAN class="keyword token">in</SPAN> uids<SPAN class="punctuation token">:</SPAN>
    ids_dict<SPAN class="punctuation token">[</SPAN>uid<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

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

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc_path<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'ID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'DUP'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">for</SPAN> k<SPAN class="punctuation token">,</SPAN> v <SPAN class="keyword token">in</SPAN> ids_dict<SPAN class="punctuation token">.</SPAN>items<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
            <SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">==</SPAN> k <SPAN class="operator token">and</SPAN> v <SPAN class="keyword token">is</SPAN> <SPAN class="operator token">not</SPAN> None<SPAN class="punctuation token">:</SPAN>
                row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> len<SPAN class="punctuation token">(</SPAN>v<SPAN class="punctuation token">)</SPAN>
                ids_dict<SPAN class="punctuation token">[</SPAN>k<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> None
                cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

forestknutsen1

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

bixb0012

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.

forestknutsen1

I like your's best Joshua, nice and clean

bixb0012

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:

<SPAN class="keyword token">from</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da <SPAN class="keyword token">import</SPAN> UpdateCursor
<SPAN class="keyword token">from</SPAN> collections <SPAN class="keyword token">import</SPAN> Counter

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class</SPAN>

<SPAN class="keyword token">with</SPAN> UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"well_id"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Dup"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
    cnt <SPAN class="operator token">=</SPAN> Counter<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">)</SPAN>
    cur<SPAN class="punctuation token">.</SPAN>reset<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">:</SPAN>
        cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> cnt<SPAN class="punctuation token">.</SPAN>pop<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
    

 <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

2Quiker

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?

forestknutsen1

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.

2Quiker

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

danretired

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

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

The csv as an example

Class, Counts
 D36521,   1
 D36522,   1
 D36525,   2
 D36528,   3
 D36532,   2<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

forestknutsen1

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.