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

Join multiple tables to multiple feature classes arcpy

Jörn — Mon, 01 Mar 2021 16:57:25 GMT

Hi!

I have a list of tables and a list of feature classes and I would like to join them, based on their names. The tables and feature classes I would like to join have the same name, but the tables hold and additional annex. I think my problem is similar to the one I found here already (https://community.esri.com/t5/python-questions/join-multiple-tables-to-multiple-feature-classes-arcpy/td-p/181549/page/2). I tried to copy the script and adapt it to my needs. The script is running without errors but I dont get the results. I would be very gratefull for suggestions. I would also appreciate a solution in model builder where I also could not find the way. Thank you!!

I use ArcMap 10.7 and run the script in the ArcMap python console.

import arcpy #Define Geodatabase Path featureGDB = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb" tableGDB = r"C:\C_M2\arcpy\list\list_distance.gdb" #Create Feature List Variable arcpy.env.workspace = featureGDB featureList = arcpy.ListFeatureClasses() #Create Table List Variable arcpy.env.workspace = tableGDB tableList = arcpy.ListTables() #Set table Names to featureList Names equivalent for table in tableList: tableNum = table [0:-5] #Finde Table and Feature from Lists with same Name and join them for feature in featureList: if feature == "%tableNum%": arcpy.JoinField_management(in_data="C:\\C_M2\\arcpy\\list\\list_Events_SBA_wp.gdb\\%WP_20200723_001%", in_field="OBJECTID", join_table="C:\\C_M2\\arcpy\\list\\list_distance.gdb\\%WP_20200723_001_dist%", join_field="OBJECTID", fields="DISTANCE")

Re: Join multiple tables to multiple feature classes arcpy

DavidPike — Mon, 01 Mar 2021 17:08:59 GMT

I've not really studied the code, but the wildcard operator % will not work (to my knowledge).

You would need a regular expression or something like:

if tableNum in feature:

Re: Join multiple tables to multiple feature classes arcpy

Jörn — Mon, 01 Mar 2021 17:24:40 GMT

Thank you @DavidPike for the suggestion. But do you think that the "%" operator works in the path name of the parameters for joinfield_management? The idea was to use the "%" operator in substition to the f' string, which was used in the posted solution in the link. I think f' strings only work in Python 3 and my version is 2.7.

Re: Join multiple tables to multiple feature classes arcpy

DavidPike — Mon, 01 Mar 2021 19:10:54 GMT

why not

if feature == tableNum:

And what are the % signs doing in your path names for the join? I think you're mixed up with model builder inline variable substitution - basically get rid of all the % signs, I have no idea what they're doing. the f string in Joe's post was to place a 'T' before the variable, from what I see, you don't need to do anything like that.

You also have a mix of raw and non raw encoded paths, I prefer r'' paths as they look cleaner.

import arcpy #Define Geodatabase Path featureGDB = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb" tableGDB = r"C:\C_M2\arcpy\list\list_distance.gdb" #Create Feature List Variable arcpy.env.workspace = featureGDB featureList = arcpy.ListFeatureClasses() #Create Table List Variable arcpy.env.workspace = tableGDB tableList = arcpy.ListTables() #Set table Names to featureList Names equivalent for table in tableList: tableNum = table [0:-5] #Finde Table and Feature from Lists with same Name and join them for feature in featureList: if feature == tableNum: in_data = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb\WP_20200723_001" in_field = "OBJECTID" join_table = r"C:\C_M2\arcpy\list\list_distance.gdb\WP_20200723_001_dist" join_field = "OBJECTID" fields = ["DISTANCE"] arcpy.JoinField_management(in_data, in_field, join_table, join_field, fields)

Re: Join multiple tables to multiple feature classes arcpy

JoeBorgione — Mon, 01 Mar 2021 19:55:15 GMT

F strings are new to python 3.x but the format() method is it's predecessor in 2.x python.

Re: Join multiple tables to multiple feature classes arcpy

Jörn — Tue, 02 Mar 2021 09:12:36 GMT

Thanks a lot @DavidPike and @JoeBorgione !

I indeed am confusing with model builder ...

Now, as far as I can interpret the results, the table "WP_20200723_001_dist" joins the "WP_20200723_001" feature the number of times I have features in my featureList (98).

What I want is, that "WP_20200723_001_dist" and "WP_20200723_001" only join one time and that in the second "round" "WP_20200723_002_dist" joins "WP_20200723_002" and so forth till "WP_20200723_098_dist" finally joins "WP_20200723_098".

I thought that the for in statement is useful for this as it iterates through the lists one by one, if I got it right. But I am still unaware of how I should describe the data path dynamically in a form to receive the desired results 🙄

Re: Join multiple tables to multiple feature classes arcpy

Jörn — Tue, 02 Mar 2021 13:04:38 GMT

Ok I think I got it:

import arcpy #Define Geodatabase Path featureGDB = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb" tableGDB = r"C:\C_M2\arcpy\list\list_distance.gdb" #Create Feature List Variable arcpy.env.workspace = featureGDB featureList = arcpy.ListFeatureClasses() #Create Table List Variable arcpy.env.workspace = tableGDB tableList = arcpy.ListTables() #Set table Names to featureList Names equivalent for table in tableList: tableNum = table [0:-5] #Finde Table and Feature from Lists with same Name and join them for feature in featureList: if feature == tableNum: in_data = featureGDB + "\\" + feature in_field = "OBJECTID" join_table = tableGDB + "\\" + table join_field = "OBJECTID" fields = ["DISTANCE"] arcpy.JoinField_management(in_data, in_field, join_table, join_field, fields)

Thank you!!