Searching for values in other tables

1293
8
Jump to solution
11-27-2017 03:09 AM
ANNAPRIOR1
New Contributor II

Hi,

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.

Thanks.

0 Kudos
1 Solution

Accepted Solutions
BruceHarold
Esri Regular Contributor

Hi

This tool will let you select values based on shared values in another table view:

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

Regards

View solution in original post

8 Replies
XanderBakker
Esri Esteemed Contributor

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

Example:

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[0] for r in arcpy.da.SearchCursor(tbl1, (fld1))]

# create a list of the values in table 2, column 2
lst2 = [r[0] 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[0] 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")
BruceHarold
Esri Regular Contributor

Hi

This tool will let you select values based on shared values in another table view:

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

Regards

MitchHolley1
MVP Regular Contributor

What tool are you referring to?  The link is not working for me. 

0 Kudos
XanderBakker
Esri Esteemed Contributor

It is accessible to:

Download contains toolbox and script:

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

MitchHolley1
MVP Regular Contributor

Thank you, Xander!

0 Kudos
BruceHarold
Esri Regular Contributor

I think this is the zip payload.

ANNAPRIOR1
New Contributor II

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.

XanderBakker
Esri Esteemed Contributor

Hi ANNA PRIOR , I'm glad you were able to resolve it. 

Did you use the toolbox provided by Bruce Harold ? If so, please mark his post as the correct one ("Mark Correct") so that others can find that solution too. If it was another solution please mark the corresponding post as the correct answer.

0 Kudos