Python Code: "Compare two fields and select missing records"

Question

The code I wrote is not right!! It's selecting more than what is actually missing. I think I'm wrong on the matched code line. I need the script to make a list of the "FLAG" field and a list of the "TFLAG" field. Then compare and select all the missing records from the "TFLAG " field. For Example, if 105H10 is missing from the "TFLAG" field then it will select that record in the "FLAG" field so I know to fix it and add the record.

import arcpy, traceback
mxd = arcpy.mapping.MapDocument("CURRENT")
lstLayers = arcpy.mapping.ListLayers(mxd)
flayer = arcpy.mapping.ListLayers(mxd, "AADT")[0]
alayer = arcpy.mapping.ListLayers(mxd, "AADTAnnoLabel")[0]
FRows = arcpy.SearchCursor(flayer)
ARows = arcpy.SearchCursor(alayer)
ffields = arcpy.ListFields(flayer, "FLAG", "String")
afields = arcpy.ListFields(alayer, "TFLAG", "String")
FList = []
AList = []
for row in FRows:
 Fvalue = row.getValue("FLAG")
 FList.append(str(Fvalue))
for row in ARows:
 Avalue = row.getValue("TFLAG")
 AList.append(str(Avalue))
matched = set(FList) & set(AList)
for x in matched:
 print x
 exp = '"FLAG" = ' + "'" + x + "'"
 arcpy.SelectLayerByAttribute_management("AADT", "ADD_TO_SELECTION", exp)
 arcpy.SelectLayerByAttribute_management("AADT", "SWITCH_SELECTION")

• PolyGeo: Have you considered using the Feature Compare tool which creates output that can be read with a SearchCursor?
• Michelle Couden: @PolyGeo No, How do you do that??
• Patty Jula: Are you working with fields in two different tables?

Answer 1

I think your loop at the end is the problem. You will go though your list of matched flags, and for each one select the exp then switching then for the next item you would add the matched and switch again. You may want to simply move the switch selection out of the loop, that way you will select everything in matched then switch. Alternatively you could change exp to an select in (item 1, item 2, etc...) and get rid of the loop.

this should select everything in your matched list, then switch the selection

for x in matched:
 exp = '"FLAG" = ' + "'" + x + "'"
 arcpy.SelectLayerByAttribute_management("AADT", "ADD_TO_SELECTION", exp)
arcpy.SelectLayerByAttribute_management("AADT", "SWITCH_SELECTION") #removed from loop

alternate solution that may at least help you identify where the problem is:

for x in matched:
 exp = '"FLAG" = ' + "'" + x + "'"
 arcpy.SelectLayerByAttribute_management("AADT", "ADD_TO_SELECTION", exp)
 arcpy.SelectLayerByAttribute_management("AADT", "SWITCH_SELECTION") 
 arcpy.MakeFeatureLayer_management("AADT",str(x))

this will give you a new layer (named for the item in matched) for each item in your matched list, of all the records where it is not found

• Michelle Couden: So I did try to drop the Switch selection but it still selected records that were not missing????
• Hotpepper: I was not suggesting reomving the switch, just moving it out of the loop - see edited answer
• Michelle Couden: Sorry, it didn't work. It should have only selected about 100 and it selected 800. It still is selecting records that are in the table. It should only be selecting those that are missing.
• Hotpepper: try the second option and see if you can find the problem
• Michelle Couden: Sorry, but there was no new layer that was created and it still gave no sign of where the problem is and why it keeps selecting more records than are missing.

Answer 2

Instead of making an intersecting set,

matched = set(FList) & set(AList)

you can go ahead and make a difference of the sets:

unmatched = set(Flist) - set(Alist)

With this you can select by attributes and add all of the corresponding features to the selection.