topic Re: Join multiple tables to multiple feature classes arcpy in Python Questions

Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Sat, 11 Dec 2021 09:16:26 GMT

Hello,

I need help to create a script that would allow me to loop through multiple tables in a geodatabase and join them to the correct feature class in another geodatabase. There are in total 77 tables and 77 feature classes. I would tell GIS to join those where a portion of the table name matches a portion of the Feature class name.

For example:

Table Name 1: "NAL11F201901"

Feature class 1: "T11"

Table Name 2: "NAL12F201901

Feature Class 2: "T12"

Important:

All of the tables and the feature classes have the same unique field name.
I need to join all of the fields.
The join needs to be permanent.

I am not sure if Add join is better to use or if I should use Join field.

I don't really know what to do next.

I would really appreciate any help with this.

Thanks!

import arcpy

in_features = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Parcels_Per_County_Final.gdb"
join_tables = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Property_Data_Per_County.gdb"

list_infeatures = arcpy.ListFeatureClasses()
list_join_tables = arcpy.ListTables(*NAL)
UniqueField = "CO_PARCELID"

for fc in list_infeatures:
    arcpy.AddJoin_management()‍‍‍‍‍‍‍‍‍‍‍

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Sat, 11 Dec 2021 09:16:28 GMT

I'll start the discussion with this little tidbit from the help pages:

The workspace environment must be set before using several of the list functions, including ListDatasets, ListFeatureClasses, ListFiles, ListRasters, ListTables, and ListWorkspaces.

Your variables mentioned in lines will only return the path to each of the respective databases. Try it with a print() statement,

Given the limitation of the list functions, you want to work things a little differently in order to get what you want. Something like this might work for you: (completely untested: run it on some backup data first!)

import arcpy

featureGDB = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Parcels_Per_County_Final.gdb"
tableGDB = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Property_Data_Per_County.gdb"

arcpy.env.workspace = featureGDB
featureWS = featureGDB
featureList = arcpy.ListFeatureClasses()

arcpy.env.workspace = tableGDB
tableList = arcpy.ListTables()
tableWS= tableGDB



for table in tableList:
    tableNum = table [3:-7]
    for feature in featureList:
        if feature == f'T{tableNum}':
            inData = f'{featureWS}\\{feature}'
            inField = 'the join field name in the feature class'
            joinTable = f'{tableWS}\\{table}'
            joinField = 'the join field name in the table'
            arcpy.JoinField(inData, inField, joinTable, joinField)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This makes a couple of assumptions: the table names are just who you showed them and the feature class names are just how you showed them. It also assumes you want to join the table data into the appropriate feature class. According to to Join Field help doc this will be a permanent join to the feature class.

Hope this helps-

Re: Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Tue, 31 Mar 2020 20:28:03 GMT

Thank you Joe for your help!

I am internally fixing something I found before I am able to run the script.

I have two questions in regards to the script:

1. Line 16: You created a variable called tableNum = table [3:7]. Arcpy recognizes that everytime you write table that is in fact calling a table in GIS? How does python know that we are in fact calling a table in GIS?

2. what do the \\ do to the f' strings?

Re: Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Tue, 31 Mar 2020 20:47:22 GMT

Line # 18 is saying:

if the name of the feature class is the same as the name of the table: T{tableNum} then, join them.

But as you can see above the name of the feature class is T11, T12, T13 etc...

and the name on the tables start with NAL and then have the number. Whenever the number matches, then the join must happen.

I am not sure if this is what the code says.

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Sat, 11 Dec 2021 09:16:31 GMT

Line 16: You created a variable called tableNum = table [3:7]. Arcpy recognizes that everytime you write table that is in fact calling a table in GIS? How does python know that we are in fact calling a table in GIS? With each pass through the tableList, I get a value for table and then I strip out the number that appears in that table name. I use that number to identify the feature class to join to. That's why the assumption that all you table names follow the same convention as well as the feature classes. If you have any of either that do not follow the convention in your original post, we have another problem to solve.

what do the \\ do to the f' strings? It may be overkill, but with I'm escaping the second backslash with the first backslash. A backslash is a special character in python and I want a literal backslash as part of the path. I just checked and it'll work with or without:

path = r'C:\temp'

file = 'xyz'

newPath = f'{path}\{file}'

newPath
Out[9]: 'C:\\temp\\xyz'

newPath = f'{path}\\{file}'

newPath
Out[11]: 'C:\\temp\\xyz'‍‍‍‍‍‍‍‍‍‍‍‍‍

It acts like the os.path.join() method.

Make sense?

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Tue, 31 Mar 2020 21:06:38 GMT

Lines 18 and 19 work together. Again, I've made the assumption that your featureList looks like:

['T11', 'T12', 'T13'] #and so on...‍

The first for loop at line 16 gets us the table name in the form of "NAL12F201901". But that's we have; just the name. Since you have your tables and feature classes in different workspaces, we have to accommodate for that fact. In line 17 we get just the number part of the table name. At line 18 we scan the featureList for an element which is T along with the number from the tableName. Line 19 says:

if feature == f'T{tableNum}':‍

in f strings, you enclose the variable name, and hence it's current value with the curly braces. If line 16 gives us 'NAL12F201901' as the value of variable table, the value of variable tableNum is 12. Consequently, the feature we are going to work with is T12.

May I suggest you create a couple of lists, one of table names and one of feature classes. Try my logic but don't worry about the arcpy stuff. Just use straight. I like to do this sort of thing in the Spyder ide because you can highlight the given text in the script window and with F9, execute it in the console window. Use print() statements along the way to expose your variable names....

Re: Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Sat, 11 Dec 2021 09:16:33 GMT

Perfect Joe, I understand and I will try it as you suggest with the print statements to better see what I am doing.

Before I do that I have one last question though .. in line 17 we are using slicing right:

tableNum = table [3:7]

would 3 to 7 give us: 11F2 ?

wouldn't it be [3:5] ?

Sorry if I am being stubborn, I really want to understand it.

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Tue, 31 Mar 2020 21:22:25 GMT

oops... should be :

tableNum = table [3:-7] ‍‍

good catch. Your approach works just as well.

Take a look at Python Strings | Python Education | Google Developers and the 'Hello' example of string slices....

Re: Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Tue, 31 Mar 2020 21:28:47 GMT

Great thanks!! I will run the code in a little bit. Will let you know how that goes

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Tue, 31 Mar 2020 21:38:05 GMT

The slice thing is just how my brain works: I slice off the first few characters and then work my way back from the end to slice off the excess. That's what's cool about python; several means to the same end...

Re: Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Tue, 31 Mar 2020 21:50:08 GMT

Joe, the code worked!! I am so happy! thank you so much! I was really stuck and needed to do this automatically. I used to do all of this manually for such a long time and I am learning to automate this with your help and with as many videos I can find online.

Actually, do you know of a good course that would teach me python applied to arcgis ? All I find are separate videos each one teaching different things. I would love to find something that goes from the very beginning until more advanced processes.

Anyways.. thank you again!

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Tue, 31 Mar 2020 22:24:57 GMT

I'm glad it worked. There is an instructor lead esri course; I took it it some time back. It was okay. I've mostly just picked it up on my own with a lot of help from this forum. Using ArcGIS Pro you can run a tool, get the syntax figured out and then create a stand alone script based on that.

There are lots of on-line python tutorials. I like the w3schools stuff as they spell things out pretty succinctly. Python documentation is all over the web, so if just preface a question with 'python' you can usually find what you are looking for like 'python return last 7 letters in a string' or 'python for loop' or 'python if else'.....

Re: Join multiple tables to multiple feature classes arcpy

NataliaGutierrez1 — Tue, 31 Mar 2020 23:38:27 GMT

Thank you again! Will keep on learning and using the online tools