topic Re: Appending- how to avoid duplicates? in Python Questions

Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 13:50:41 GMT

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?

Re: Appending- how to avoid duplicates?

DanPatterson_Retired — Mon, 27 Mar 2017 13:53:17 GMT

If they are truly identical.... Find identical

Re: Appending- how to avoid duplicates?

JayantaPoddar — Mon, 27 Mar 2017 13:57:10 GMT

and Delete Identical.

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 13:57:32 GMT

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...

Re: Appending- how to avoid duplicates?

JayantaPoddar — Mon, 27 Mar 2017 14:07:01 GMT

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

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 14:11:10 GMT

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?

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 14:17:41 GMT

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.)

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 14:24:31 GMT

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.

Re: Appending- how to avoid duplicates?

BruceHarold — Mon, 27 Mar 2017 14:35:37 GMT

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.

Re: Appending- how to avoid duplicates?

MitchHolley1 — Fri, 10 Dec 2021 22:44:15 GMT

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

import arcpy

shp1 = r'...path to shapefile1...'
shp2 = r'...path to shapefile2...'

shp1keys = []


#Field arguments is the common field between both datasets
with arcpy.da.SearchCursor(shp1, ['field_with_dups']) as cursor:
    for row in cursor:
        shp1keys.append(row[0])
del cursor

#Field arguments with common field plus 'Comment' field to write duplicates
with arcpy.da.UpdateCursor(shp2, ['field_with_dups', 'Comment']) as cursor:
    for row in cursor:
        if row[0] in shp1keys:
            row[1] = 'DUPLICATE'
            cursor.updateRow(row)
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 14:44:29 GMT

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

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 14:46:55 GMT

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.

Re: Appending- how to avoid duplicates?

BruceHarold — Mon, 27 Mar 2017 15:06:23 GMT

You could certainly script that.

Re: Appending- how to avoid duplicates?

JoshuaBixby — Mon, 27 Mar 2017 15:25:50 GMT

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.

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 15:36:16 GMT

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!))

Re: Appending- how to avoid duplicates?

TheoFaull — Mon, 27 Mar 2017 15:40:45 GMT

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

Re: Appending- how to avoid duplicates?

BruceHarold — Mon, 27 Mar 2017 16:11:05 GMT

Scripting section in the Help:

An overview of scripting ArcGIS Server administration—ArcGIS Server Administration (Windows) | ArcGIS Enterprise

Tools from a colleague:

https://www.arcgis.com/home/item.html?id=12dde73e0e784e47818162b4d41ee340

...you may need to upgrade.

Re: Appending- how to avoid duplicates?

NeilAyres — Mon, 27 Mar 2017 18:22:21 GMT

Generally when updating data which is used by a service we truncate then append the new data. That doesn't seem to upset things. Deleting is a no-no.

Re: Appending- how to avoid duplicates?

TheoFaull — Tue, 28 Mar 2017 08:38:13 GMT

Is that 'Truncate Table' you use? And does your method work even if there's a LOCK file on the data?

Re: Appending- how to avoid duplicates?

NeilAyres — Tue, 28 Mar 2017 09:37:22 GMT

Where is the data - fgdb or SDE?

Normally for publishing purposes (and not for editable features), we copy over from an enterprise system to a fgdb every night.

That uses truncate and append. Doesn't affect the services based on these features.

Not an expert on how all this works myself, just aware of the process.