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

Append only ids that don't exist in another class

ThomasHamming

I have 2 points classes with the same attributes, and I am looking to append data from one to the other. This is simple enough, but the first class has a lot of data that is already in the second class. I am looking to append only the rows/ids that don't exist in the other class already, and I can't seem to find an easy way to do this in pro. Any idea on how to do this?

For example, if this were a program it might look something like this:

for row in class1:

if row.id not in class2:

class2.append(row)

Vind meer berichten getagd met

Analysis

Desktop

Feature Services

Accepted answers

JohannesLindner

target_class = "path:/to/class_2"
append_class = "path:/to/class_1"
id_field = "RowID"

# read the ids that are already in the target class
existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])]

# create an SQL where clause "IdField NOT IN (1, 2, 3)"
if isinstance(existing_ids[0], str):
    id_list = ["'{}'".format(i) for i in existing_ids]
else:
    id_list = [str(i) for i in existing_ids]
where_clause = "{} NOT IN ({})".format(id_field, ", ".join(id_list))

# create a layer of all append features that are not in the target class, append
append_layer = arcpy.management.MakeFeatureLayer(append_class,  "append_layer", where_clause)
arcpy.management.Append(append_layer, target_class)

All comments

JohannesLindner

Oops, used a wrong variable name... fixed

JustPete

Hi Johannes,

This for the code, but it is returning this error:

NameError                                 Traceback (most recent call last)
In  [3]:
Line 18:    fields = [f.name for f in arcpy.ListFields(fc)]

NameError: name 'fc' is not defined

JohannesLindner

doesn't create a new feature layer

The three easiest ways to solve the original question in Python are:

create a layer with a definition query and append that layer (this is what my original answer does)
create a layer of the whole feature class, select the wanted rows and append that layer
do it all with arcpy.da.*Cursors

Options one and two create a temporal layer. If you run the script in the Python Window of an ArcGIS Project, that layer will be added to the current map. If that is your problem, you can add these lines to the end to remove the layer.

try:
    m = arcpy.mp.ArcGISProject("current").activeMap
    lyr = m.listLayers("append_layer")[0]
    m.removeLayer(lyr)
except:
    print("Could not remove the temp layer")

If you don't want to create the layer for a different reason, you can use option 3 (untested, will probably be slower for large datasets):

target_class = "path:/to/class_2"
append_class = "path:/to/class_1"
id_field = "RowID"

# read the ids that are already in the target class
existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])]

# create an SQL where clause "IdField NOT IN (1, 2, 3)"
if isinstance(existing_ids[0], str):
    id_list = ["'{}'".format(i) for i in existing_ids]
else:
    id_list = [str(i) for i in existing_ids]
where_clause = "{} NOT IN ({})".format(id_field, ", ".join(id_list))

# get a list of all field names
fields = [f.name for f in arcpy.ListFields(target_class)]
# exclude some of those fields to avoid raising errors
# replace the field "Shape" with "SHAPE@"
exclude_fields = ["OBJECTID", "GlobalID", "Shape_Length", "Shape_Area"]
fields = [f for f in fields if f not in exclude_fields]
fields = ["SHAPE@" if f == "Shape" else f for f in fields]

# start inserting into the target class
with arcpy.da.InsertCursor(target_class, fields) as i_cursor:
    # start reading from the append_class, use the query we defined earlier
    with arcpy.da.SearchCursor(append_class, fields, where_clause) as s_cursor:
        # copy the rows
        for row in s_cursor:
            i_cursor.insertRow(row)

JustPete

Hello,

Is there a way ion which the syntax can be run but doesn't create a new feature layer, and just appends the target dataset? It may already do this, I'm a python novice

RexRobichaux

Thanks for the prompt response Johannes! Correct- the field name in both feature classes is "STATION_ID". I've tried a subset selection in both ArcGIS Pro and ArcMap and both worked fine (with say 4-5 stations)... which further made me scratch my head. I've attached the zip scratch/dev file geodatabase to this post to see if that helps. If/when I get this working and pushed to test/prod, the WQM_STATIONS feature class will be access/stored in an enterprise Oracle geodatabase, however the target FC (WQM_STATIONS_FINAL) will still be in a FGDB.

PS- it's expected for there to be a good deal more attributes in the WQM_STATIONS_FINAL FC...I'll likely have to make a field mapping at some point as well for the append if I can just get past this SQL expression error. Thanks again for offering to take a look at this!

WQM_STATIONS_DELTA_LOAD.gdb.zip

JohannesLindner

Syntax looks fine. Could maybe be something with your weird id format.

Is the field in the append class really called STATION_ID? Are you able to use the query in the Select By Attributes tool?

Can you post the gdb, either here or in a pm?

RexRobichaux

Hello Johannes,

Awesome and elegant solution! I'm trying to use this script to achieve a similar delta load between two feature classes but I'm running into an error I can't seem to get past. Regardless of how I try to structure the WHERE clause...arcpy continues to throw the 000358 error code indicating something isn't right with the SQL syntax. I'm printing out the (large) where clause (I have about 18k unique STATION_IDs that it's parsing through)...but everything looks find from a syntax perspective. After two days of testing/tweaking...I'm about to give up. Do you see anything amiss here? For development, both feature classes are local in a file geodatabase. I've tested in Python 3.x and 2.x and receive the same error in either environment.

import arcpy

target_class = "C:\\Rex\\WQM_STATIONS_FINAL\\WQM_STATIONS_DELTA_LOAD.gdb\\WQM_STATIONS_FINAL"
append_class = "C:\\Rex\\WQM_STATIONS_FINAL\\WQM_STATIONS_DELTA_LOAD.gdb\\WQM_STATIONS"
id_field = "STATION_ID"

# read the ids that are already in the target class
existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])]

# create a SQL where clause "STATION_ID NOT IN (1, 2, 3)"
if isinstance(existing_ids[0], str):
    id_list = ["'{}'".format(i) for i in existing_ids]
    #print id_list
else:
    id_list = ["'{}'".format(i) for i in existing_ids]
    #print id_list
where_clause = '{} NOT IN ({})'.format(id_field, ", ".join(id_list))
print (where_clause)
# create a layer of all append features that are not in the target class, append
append_layer = arcpy.management.MakeFeatureLayer(append_class,  "append_layer", where_clause)
arcpy.management.Append(append_layer, target_class)

Thanks Johannes (and/or anyone else) that might have any ideas!

JonathanWhite1

No matter what I do it just gives me this error below. I have tried just about everything. The only thing I can think of is perhaps this will not work with AGOL hosted tables? The target layer is in an AGOL relationship class tables while the append layer is just in AGOL feature set tables. I have also attempted to use a local file geodatabase table as the append class but I get the same error.

Traceback (most recent call last):
File "S:\GISDataActive\ParcelMap\AutomatedUpdates\Appendaddress.py", line 24, in <module>
append_layer = arcpy.management.MakeFeatureLayer(append_class, "append_layer", where_clause)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 9395, in MakeFeatureLayer
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 9392, in MakeFeatureLayer
retval = convertArcObjectToPythonObject(gp.MakeFeatureLayer_management(*gp_fixargs((in_features, out_layer, where_clause, workspace, field_info), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Features: Dataset https://services2.arcgis.com/wEula7SYiezXcdRv/arcgis/rest/services/Parcel_Updates/FeatureServer/1 does not exist or is not supported
Failed to execute (MakeFeatureLayer).
Failed script AddressAppend...
Failed to execute (AddressAppend).

JohannesLindner

Sorry it took so long to answer, I was on vacation... If you haven't found a soultion yet:

It's probably because you used backslashes instead of forward slashes in your paths. Single backslashes are special characters in Python strings, they are used to encode characters that you can't simply type, e.g. "\n" for a linebreak.

Use one of these methods:

# forward slashes
"path:/to/your/fc"
# double backslashes
"path:\\to\\your\\fc"
# define it as raw string
r"path:\to\your\fc"

JonathanWhite1

I am trying to get that script to work in python notebook but no luck. Just copied above and added paths for target_class, append_class and specified the id_field. I'm super new to this so its probably something simple I am not understanding.

SyntaxError Traceback (most recent call last) File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\Lib\ast.py, in parse: Line 35: return compile(source, filename, mode, PyCF_ONLY_AST) SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape (<string>, line 2)

ThomasHamming

Thank you, this worked!

JohannesLindner

target_class = "path:/to/class_2"
append_class = "path:/to/class_1"
id_field = "RowID"

# read the ids that are already in the target class
existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])]

# create an SQL where clause "IdField NOT IN (1, 2, 3)"
if isinstance(existing_ids[0], str):
    id_list = ["'{}'".format(i) for i in existing_ids]
else:
    id_list = [str(i) for i in existing_ids]
where_clause = "{} NOT IN ({})".format(id_field, ", ".join(id_list))

# create a layer of all append features that are not in the target class, append
append_layer = arcpy.management.MakeFeatureLayer(append_class,  "append_layer", where_clause)
arcpy.management.Append(append_layer, target_class)

ThomasHamming

Thanks, and this is a solution but not an ideal one. The class I am appending from has over 10,000 points, and a normal append operation would take hours to complete. Theoretically only adding missing points would take much less time.

Robert_LeClair

I wonder if a Pairwise Erase workflow would accomplish this task. The input features parameter is FC1. The erase features parameter is FC2. The output feature class is those features that are not coincident. Then use the output feature class to append the data to FC2.

JayantaPoddar

A workaround. How about Append followed by Delete Identical?

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

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