Compare fields for matches

424
15
Jump to solution
03-04-2020 10:39 AM
TonyAlmeida
Occasional Contributor

I need to compare fields from two separate tables and mark them with copy or no copy. It seems easy enough but i am having a hard time and not sure what I am doing wrong. I just get all "No" in the Copy field.

Here is what i have.

fc1 = 'C:\Temp\blah1.dbf' 
fc2 = 'C:\Temp\blah2.dbf'

cursor1 = arcpy.da.SearchCursor(fc1, ["Field1", "Copy"])  
cursor2 = arcpy.da.SearchCursor(fc2, ["Field2"])

with arcpy.da.UpdateCursor(fc1, ['Field1', 'Copy']) as cursor:
    for row in cursor1:
        for row2 in cursor2: 
            if row[0].strip().lower()!= row[0].strip().lower(): 
                for row1 in cursor1:
                    row[1] = 'No'
                else:
                    row[1] = 'Copy'
                cursor.updateRow(row1)
del cursor
0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

Does something like this help:

fc1 = "C:\Temp\blah1.dbf"
fld1 = ["Field1", "Copy"]


fc2 = "C:\Temp\blah2.dbf"
fld2 = ["Field2"]


valueList = [r[0] for r in arcpy.da.SearchCursor(fc2, fld2)]


with arcpy.da.UpdateCursor(fc1, fld1) as cursor:
    for row in cursor:
        if row[0] in valueList:
            row[1] = 'Copy'  # May wish to swap 'Copy' and 'No'
        else:
            row[1] = 'No'
        cursor.updateRow(row)

View solution in original post

0 Kudos
15 Replies
RandyBurton
MVP Regular Contributor

Are the values in "Field1" and "Field2" unique identifiers and can only appear once in each feature?  If so, you could read the value of fc2's Field2 into a list/dictionary.  Then use an update cursor to mark the Copy field if Field1 is not in the Field2 list.

TonyAlmeida
Occasional Contributor

No Field1 and Field2 are not unique identifiers. I was trying compare fc1 Field1 to fc2 Field2 to see if there is duplicate/copy.

I tried working with a list/dictionary but it didn't work out and these seems the simplest.

fc1 = 'C:\Temp\blah1.dbf' 
fc2 = 'C:\Temp\blah2.dbf'

fc1List = []

with arcpy.da.SearchCursor(fc2,["Field2"]) as cursor:
    fc1List = [row for row in cursor]

tableFieldList = ["Field1", "Copy"]

with arcpy.da.UpdateCursor(fc1, tableFieldList) as tablecursor:
    for tablerow in tablecursor:
        if tablerow[0] in fc1List:
            tablerow[1] = 'Copy'
        else:
            tablerow[1] = 'No'
        tablecursor.updateRow(tablerow)
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

If the tables are generally duplicates with some differences, it might be worth a try combining the tables into a gdb table, then use either

Find Identical—Data Management toolbox | Documentation 

Delete Identical—Data Management toolbox | Documentation 

you can limit the fields to use in the comparison as well

0 Kudos
TonyAlmeida
Occasional Contributor

That would work if this was just a one time task but I will have to do this weekly which why i am trying to trying to do it with python. I appreciate the info tho.

0 Kudos
BruceHarold
Esri Regular Contributor

If I'm reading the logic of this correctly then you should be able to find the rows having shared values between two tables in one pass with this tool:

https://pm.maps.arcgis.com/home/item.html?id=e638afe0695a4ad38388cb8d9b350446 

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Tony, both the tools can be used in a script, check the code samples but a Merge_management to a temporary table and a FindIdentical_management or DeleteIdentical_management might do the trick

BlakeTerhune
MVP Regular Contributor

You want to copy the value from the source if it's different than the destination value? How many rows are you working with? If it's a reasonable amount of data (less than 10000 maybe) I would just update everything no matter the value. If you always want the destination to match the source, just truncate and append everything. If you can't do that, just join the two and field calc the destination field to equal the source field.

0 Kudos
TonyAlmeida
Occasional Contributor

No i don't want to copy the values i just want fc1 "Field1" to fc2 "Field2" and see if there is a match/ duplicate /copy and update fc1 "Copy" field with "Copy" or "No". There is probably about 4k records in each table.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

You are cycling through cursor1 twice once outside the cursor2 loop and again within the cursor2 loop

0 Kudos