topic Re: Searching for values in other tables in Python Questions

Searching for values in other tables

ANNAPRIOR1 — Mon, 27 Nov 2017 11:09:58 GMT

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.

Re: Searching for values in other tables

XanderBakker — Sat, 11 Dec 2021 12:54:33 GMT

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

Re: Searching for values in other tables

BruceHarold — Mon, 27 Nov 2017 16:38:53 GMT

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

Re: Searching for values in other tables

MitchHolley1 — Mon, 27 Nov 2017 16:47:01 GMT

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

Re: Searching for values in other tables

XanderBakker — Mon, 27 Nov 2017 18:00:17 GMT

It is accessible to:

Download contains toolbox and script:

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

Re: Searching for values in other tables

BruceHarold — Mon, 27 Nov 2017 18:06:30 GMT

I think this is the zip payload.

Re: Searching for values in other tables

MitchHolley1 — Mon, 27 Nov 2017 18:23:42 GMT

Thank you, Xander!

Re: Searching for values in other tables

ANNAPRIOR1 — Tue, 28 Nov 2017 09:15:51 GMT

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.

Re: Searching for values in other tables

XanderBakker — Tue, 28 Nov 2017 11:41:11 GMT

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.