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; } } }

Appending- how to avoid duplicates?

TheoFaull

I have two point shapefiles, both have the exact same fields in them. There are some new records AND some duplciate records when comparing the two datasets.

I want to use the append tool as I don't want to create a new dataset, I just want to add data to the existing original shapefile.

However, when I append the two shapefiles, matching records are appended, thus leaving lots of duplicate records. How can I tell my script to only append new records and ignore duplicates?

Trouver plus de publications taguées avec

Accepted answers

All comments

JasonRogers1

This worked PERFECTLY for me! Thank you so much for this post!!!

NeilAyres

In the past I have seen speed improvements of about X 10. So yes, I would expect this to be quicker.

How many records are being deleted?

EinarD

I tried modifying the script to use the da module, but it only slightly improved performance. previous script with arcpy.UpdateCursor used 1070s, while the new script with arcpy.da.UpdateCursor used 1032s.

Here's the code I used this time:

import arcpy
fc = fc = r"C:\Users\Documents\ArcGIS\Projects\kun.gdb\kun_A"
fieldss = ["USER_Id", "date"]
keepList = []
with arcpy.da.UpdateCursor[fc, fieldss, sql_clause=(None, "ORDER BY date DESC"))
for row in cursor:
row_val = [0]
if row_val not in keepList:
keepList.append(row_val)
else:
cursor.deleteRow()

Am I using the da module correctly? after reading about the performance differend between arcpy.da. Cursors and arcpy.Cursors I would think the improvement should be larger.

mvolz47

Use a truncate and append process. I've been using this type of process for years on file gdbs that are tied to AGS mapservices and it works well.

You just need to test things when there is a schema change, as that would require stopping the service but hopefully this would be a very infrequent operation.

You might also need to compact the file gdb on some interval (quarterly, semi-annually) as performance can degrade over time as the OBJECTIDs keep getting larger in the file gdb.

If performance gets really bad, you could also create a new file gdb and just import the feature classes in from the existing renamed file gdb (This maintenance would require stopping the service). This would be even less frequent, maybe once a year or every other year.

NeilAyres

Use the da module.

as in arcpy.da.UpdateCursor(....)

EinarD

I've been using this script, posted here, to do basically what you asked for in the first place. It needs one field where you need to look for duplicate values and a date-field to sort your data. Before this code block runs I append my new/updated data to my master feature class, and then run this code to see if there is any duplicates, if yes it keeps only the newest.

<SPAN class="comment token"># Script loops through a feature class and deletes duplicate records, keeps newest entry</SPAN>
<SPAN class="keyword token">import</SPAN> arcpy

<SPAN class="comment token"># Set parameters</SPAN>
<SPAN class="comment token"># path to feature class:</SPAN>
fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Documents\ArcGIS\Projects\kun.gdb\kun_A"</SPAN>
<SPAN class="comment token"># unique field to search for duplicates</SPAN>
id <SPAN class="operator token">=</SPAN> <SPAN class="string token">"USER_Id"</SPAN> 
<SPAN class="comment token"># Field to sort by. D = Descending A = Ascending. choose D to keep newest entry</SPAN>
sort_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"date D"</SPAN>

<SPAN class="comment token"># print("Looking for duplicates")</SPAN>
cursor <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN>id<SPAN class="punctuation token">,</SPAN>sort_field<SPAN class="punctuation token">)</SPAN>
keepList <SPAN class="operator token">=</SPAN> list<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; row_val <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">.</SPAN>getValue<SPAN class="punctuation token">(</SPAN>id<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> row_val <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> keepList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; keepList<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row_val<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">elif</SPAN> row_val <SPAN class="keyword token">in</SPAN> keepList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>deleteRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">pass</SPAN>
&nbsp;&nbsp;&nbsp; 
<SPAN class="comment token"># print("Finito!")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍</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>

The downside of this code as i see it is that it's slow. For my use it has to loop through 4.5M records each time, thus taking 15-20minutes to complete. (So if anyone has tips for faster methods I'm all ears)

TheoFaull

Of course. I haven't gone into details just yet and thoroughly appreciate Mitch's response. But anyway, there are 20 fields and 25,000 records. All the data in these records is subject to change, excluding a couple of ID fields.

What I think I really need is to delete the existing shapefile, and replace with the updated version. However a LOCK file originating from the server (which accesses the shapefile) stops me from deleting this dataset. I need a script which:

1. Stop Server GIS service

2. Delete Points.shp

3. Create updated version of Points.shp in same directory with same name.

4. Start Server GIS service

TheoFaull

Bruce Harold‌

Yes it's the stopping service and starting service I can't figure out.

We have ArcGIS Desktop 10.4 with Python 2.7.10

and

ArcGIS for Server 10.0 with Python 2.6.5 on our seperate server machine (we host a lot of mapping data on this server including a web map interface which the whole company use. It's old but it works and we're reluctant to upgrade the software (no time to!))

bixb0012

Not to nitpick on semantics, but duplicate and identical are synonyms. If you are talking about a subset of fields, then what are the fields you want to compare to determine if a record should be updated/overwritten? You have described your overflow workflow goals, but you haven't described the data set beyond being points. The more specifics you share, the more specific the responses.

bharold

You could certainly script that.

TheoFaull

Yes true, but two duplicate records may not always be identical. The newer one may have updated information within it. So how could the script decide which record is the newer and updated record? Thus keeping it.

TheoFaull

Thanks Bruce but the data contains 1000s of records containing confidential tenant information. I doubt I'd be allowed to share

In an ideal world, I just need a simple script that deletes the existing dataset and places the new, updated dataset in the same directory and with the same name. That way all the MXDs won't need to be re-sourced each time.

The reason I can't do this method is because of an ever present LOCK file on the existing dataset (it's under constant access by our GIS server...).

Maybe a script that says:

1. Stop Server GIS service

2. Delete Points.shp

3. Create updated version of Points.shp in same directory with same name.

4. Start Server GIS service

MitchHolley1

You could have a script write a comment to a text field that are duplicates, then append records that do not have a comment.

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

shp1 <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'...path to shapefile1...'</SPAN>
shp2 <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'...path to shapefile2...'</SPAN>

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


<SPAN class="comment token">#Field arguments is the common field between both datasets</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>shp1<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'field_with_dups'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; shp1keys<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="keyword token">del</SPAN> cursor

<SPAN class="comment token">#Field arguments with common field plus 'Comment' field to write duplicates</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>shp2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'field_with_dups'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Comment'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <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="keyword token">in</SPAN> shp1keys<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">'DUPLICATE'</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">del</SPAN> cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<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>

bharold

The Data Interoperability extension has a couple of functions for this use case, the ChangeDetector and Matcher transformers. The former identifies added, deleted and unchanged features between two datasets, the latter finds matching features. Both allow you to choose the combination of geometry and attribute tests you need. If you can share your data I can build a sample, either in this thread or via email. I would need to know your preference of 10.5 or Pro 1.4.1 tool version.

TheoFaull

Also when appending the new dataset to the original, I want the new dataset to take priority over the existing one. ie. if two records match, then old records are overwritten by the new ones.

TheoFaull

Dissolve won't work as it create a new dataset. It's crucial to me that the final dataset, is the existing one. (Lots of ArcMap MXDs link to that shapefile.)

TheoFaull

There are multiple seperate points that share the exact same coordinates. So dissolving based on that would combine points together that are infact independent of each other.

There is a unique ID field however... I could dissolve the appended dataset based on that field?

JayantaPoddar

Add XY coordinates to your featureclass, then Dissolve on the X,Y fields.

TheoFaull

That's only available with an advanced license though. I only have Basic

And yes most of the records are duplicates. Maybe there is away to only append records that are new...

JayantaPoddar

and Delete Identical.

DanPatterson_Retired

If they are truly identical.... Find identical

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

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