topic Re: Compare fields for matches in Python Questions

Compare fields for matches

TonyAlmeida — Sun, 12 Dec 2021 07:05:31 GMT

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

Re: Compare fields for matches

RandyBurton — Wed, 04 Mar 2020 20:13:30 GMT

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.

Re: Compare fields for matches

BlakeTerhune — Wed, 04 Mar 2020 20:19:35 GMT

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.

Re: Compare fields for matches

DanPatterson_Retired — Wed, 04 Mar 2020 20:31:30 GMT

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

Re: Compare fields for matches

TonyAlmeida — Sun, 12 Dec 2021 07:05:34 GMT

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

Re: Compare fields for matches

TonyAlmeida — Wed, 04 Mar 2020 21:30:59 GMT

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.

Re: Compare fields for matches

DanPatterson_Retired — Wed, 04 Mar 2020 22:00:21 GMT

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

Re: Compare fields for matches

TonyAlmeida — Wed, 04 Mar 2020 22:27:19 GMT

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.

Re: Compare fields for matches

BruceHarold — Wed, 04 Mar 2020 22:33:30 GMT

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

Re: Compare fields for matches

DavidPike — Wed, 04 Mar 2020 23:11:52 GMT

I'm not sure your list comprehension is working in your cursor. Check your list is being populated if not, replace with a noob append on fclist.

Re: Compare fields for matches

DanPatterson_Retired — Wed, 04 Mar 2020 23:30:32 GMT

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

Re: Compare fields for matches

TonyAlmeida — Sun, 12 Dec 2021 07:05:36 GMT

This list is working i add a print statement for the list. Not sure what part my code is failing.

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]
print (fc1List)

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

Re: Compare fields for matches

DavidPike — Thu, 05 Mar 2020 00:18:04 GMT

Looking at the code it seems absolutely fine. Is the comparison messing up on different data types, e.g. string and float?

Re: Compare fields for matches

DanPatterson_Retired — Sun, 12 Dec 2021 07:05:39 GMT

Ran a test on my own data, could be what it is returning.

# ---- this returns tuples of values
with arcpy.da.SearchCursor(tbl1,["Text_vals"]) as cursor:
    fc1List = [row for row in cursor]
    
fc1List
[('zilch',)]

# ---- This returns values
with arcpy.da.SearchCursor(tbl1,["Text_vals"]) as cursor:
    fc1List = [row[0] for row in cursor]
    

fc1List
['zilch']‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So you can't compare single values to tuples in case that is what you are getting.

Try seeing if the checks work before you go to the updatecursor

# tbl1, tbl2 are two tables with a common field (Text_vals)

with arcpy.da.SearchCursor(tbl1, ["Text_vals"]) as cursor:
    fc1List = [row[0] for row in cursor]
print (fc1List)

tableFieldList = ['Text_vals']  # ---- field to check
with arcpy.da.SearchCursor(tbl2, tableFieldList) as tablecursor:
    for tablerow in tablecursor:
        if tablerow[0] in fc1List:
            print("is in")
        else:
            print("not in")

# ---- print and run results           
['zilch'.....

is in
....‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Compare fields for matches

RandyBurton — Sun, 12 Dec 2021 07:05:41 GMT

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

Re: Compare fields for matches

DanPatterson_Retired — Sun, 12 Dec 2021 07:05:44 GMT

exactly my point..that is the slice that is needed. (my example, your example

fc1List = [row[0] for row in cursor]

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

If tuples are being returned, then it has to be sliced

'zilch' in [('zilch',)]
Out[1]: False
'zilch' in [('zilch',)[0]]
Out[2]: True
'zilch' in ['zilch']
Out[3]: True‍‍‍‍‍‍