I'm relatively new to GIS and Python and am trying to find a way to build a model that will feedback to be whether there is a matching value from one table in another.
In other words, I need to know which (if any) of my values from 'column 1' of 'table 1' appear in 'column 3' of 'table 2'. I know how to do this manually, however there are over 18000 values in 'column 1' of 'table 1' and 28000 in 'column 3' of 'table 2' so automating this will save a lot of time.
Solved! Go to Solution.
Can you functionally explain how you would like to see this work. You can easily do this manually and provide this information at table 1 perhaps as an additional field, but you want to use a model (Model Builder?) for this and create a tool that verifies the existence of a selected value of your 'column 1' in 'column 3' of table 2?
If you just want to report which values from table 1 occur in table 2 or which ones don't you should use sets. See some examples here: Some Python Snippets (scroll down to Compare lists and dictionaries).
import arcpy tbl1 = r'your path to table 1' fld1 = 'column1' tbl2 = r'your path to table 2' fld2 = 'column2' # create a list of the values in table 1, column 1 lst1 = [r for r in arcpy.da.SearchCursor(tbl1, (fld1))] # create a list of the values in table 2, column 2 lst2 = [r for r in arcpy.da.SearchCursor(tbl2, (fld2))] # Compare the two lists and get the items in list 1 but not in list 2 lstin1notin2 = list(set(lst1) - set(lst2)) # Compare the two lists and get the items in list 2 but not in list 1 lstin2notin1 = list(set(lst2) - set(lst1)) # Compare two lists and get the items that are in both lists lstinboth = list(set(lst1) & set(lst2))
This is for comparing two lists, but you cam also check a single value in a list of values:
import arcpy value1 = 'a value from table 1 column 1' tbl2 = r'your path to table 2' fld2 = 'column2' # create a list of the values in table 2, column 2 lst2 = [r for r in arcpy.da.SearchCursor(tbl2, (fld2))] if value1 in lst2: print("value 1 is in list 2") else: print("value 1 is not in list 2")
Thank you so much! Myself and my colleagues have been tearing our hair out for days trying to work this out and this works perfectly and will honestly save us hours a week.