1

I would like to make a comparison between two fields of two different tables with a Python script. Fields for comparison are ROAD_NAME and NAME.ROAD. The idea is to check whether each element of NAME.ROAD field exists in the field ROAD_NAME. Whether it is necessary to check if it has the right DISTRICT, CITY and good in case there is a mistake we must rectify at the tab1 DATA SOURCE : TAB1 --> FGDB TAB2--> Excel

I'm using ArcGIS 10.2 Advanced.

enter image description here

the code that I can write up to now is the following:

cursor1=arcpy.da.SearchCursor("TAB1",("ROAD_NAME","DISTRICT","CITY"))
cursor2=arcpy.da.SearchCursor("TAB2",("NAME.ROAD","DISTRICT","CITY"))
stockage={}
for row in cursor1:
    stockage[row[0]]=row[0]
for row in cursor2:
    if row[0]==stockage[row[0]]:
        pass
    else:
         ....
  • Be careful of the distinction between comparison (==) and assignment (=) – Vince Apr 20 '16 at 21:15
  • Please edit the question to specify the data sources (e.g. FGDB, Excel...) and the number of features to be compared. – Vince Apr 20 '16 at 21:34
  • 1
    Don't you get an error on the Excel file when you try to use a cursor on it? I don't think a cursor can be used with Excel. You also have changed the wording so that it looks like now all you need to do is a standard join. But since Excel has no ObjectID field, Excel does not support record selections and that may limit what you can do. I never use Excel and always convert Excel to get the benefits of a real database. The time involved to convert is much less of a pain than the frustrations of directly trying to use Excel. I do not understand why anyone would use it directly. – Richard Fairhurst Apr 20 '16 at 22:36
6

Never use embedded cursors. For each record in the outer table you will have to read the entire other table, even if only one record in the table matches. This is because cursors are strictly linear and do not allow any random access to the records. This means that two tables of 10,000 records each (20,000 records if each was read just once) will result in the second table reading the equivalent of 100,000,000 records to complete the loop (which is 99,990,000 records too many).

Dictionaries are random access collections with immediate look up of any record based on a key value. Load a dictionary from a search cursor of the table that you want to use as the look up control, and then you will only have to read the second table once to match to any of the unique key values in the dictionary. Multifield keys are just tuples. See my Blog and look at the example of Using a Multi-Field Python Dictionary Key to Replace a Concatenated Join Field.

from time import strftime  

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")  

import arcpy  

sourceFC = r"C:\Path\SourceFeatureClass"  

sourceFieldsList = ["ROAD_NAME", "DISTRICT", "CITY", "ValueField"]  

# Use list comprehension to build a dictionary from a da SearchCursor where the key values are based on 3 separate feilds  
valueDict = {(r[0:2]):(r[3:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}  

updateFC = r"C:\Path\UpdateFeatureClass"  

updateFieldsList = ["NAME.ROAD", "DISTRICT", "CITY", "ValueField"]  

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable  
        keyValue = (updateRow[0:2])
        # Find any keyValue that is not in the Dictionary  
        if not keyValue in valueDict:  
            # Key not found in dictionary so do something to unmatched entry
            updateRow[3] = "fields unmatched"
            updateRows.updateRow(updateRow)

del valueDict  

print "Finished script: " +strftime("%Y-%m-%d %H:%M:%S") 
  • You could also use my tool to create a single Long field key in two tables that represents the match between two tables that originally only match on a multi-field key. geonet.esri.com/blogs/richard_fairhurst/2015/06/07/… Options allow you to use -1 to represent unmatched keys. You can flag unmatched in only the second table, unmatched records of either table not found in the other, or assign unique values to all records to see number gaps in either table that misses records from the other. – Richard Fairhurst Apr 20 '16 at 22:14
  • The tool makes the two tables able to use the standard Add Join tool to see the fields of the two tables combined into a single tableview in ArcMap. The code I provided will match the two tables, but would not let you create a single tableview of the fields from both tables, since it does not create a single field that can act as a representative key equivalent to the unique multi-field values. – Richard Fairhurst Apr 20 '16 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.