Nested join table using python arcpy

AhmedAbdelnasser1 · ‎05-02-2017

I am using Generate near tool to find the closest 25 match between unmatched FC & Census FC

the output of the near tool is split into 25 tables with the rank value.

I have: 1- 25 tables (NEAR_1, NEAR_2, NEAR_3...etc) each one have the same columns(IN_FID,NEAR_FID,TOP_RANK)

2- 25 FC Census data (C2015_J1, C2015_J2, C2015_J3...etc)is copies of Census FC

3- the original layers I ran the Near tools on (census FC, unmatched FC)

What I need to do: 1- I want to create a table with all the data from the 25 near tables and the 25 census table

to do that I have to:

a)join the unmatched FC with the NEAR1 table

b)join the result FC of step a) with the census FC

c)repeat the same step for all the near tables and join the census on each one

2- using model builder I used something like the snapshot below

Notice that the output of every line is the input for the next one

3- I am trying to write a python code to do the same but I am really struggling with it, I think it might be something like this:

nearlist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\NEAR_1",\
"C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\NEAR_2"]

censuslist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\C2015_J1",\
"C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\C2015_J2"]
j=0
k=0
lastjoindtable="C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\UNMATCHED" #assign the unmatched FC to lastjoindtable variable
for j in nearlist:
try:
arcpy.env.qualifiedFieldNames = False
# Set local variables
j_inFeatures = lastjoindtable
j_layerName = "J_tembjoin_" + str(j)
j_joinTable = nearlist
j_joinField = "OBJECTID"
j_joinField2= "NEAR_" + str(j+1) + ".IN_FID"
outFeature = "Matching_Model.gdb"
tempjoin= "Matching_Model.gdb" + "\\" + str(layerName)
# Create a feature layer from the lastjoindtable featureclass
arcpy.MakeFeatureLayer_management (j_inFeatures, j_layerName)
# Join the feature layer to a table
arcpy.AddJoin_management(j_layerName, j_joinField, J_joinTable, j_joinField2)
# Copy the layer to a new permanent feature class
arcpy.CopyFeatures_management(j_layerName, outFeature)
j+=1
except Exception as err:
print(err.args[0])

for k in censuslist:
try:
#"the output of the join in the small for will be assigned to last joind table variable which will be used as inbut in the big for"
# Set local variables
k_inFeatures = tempjoin
k_layerName = "k_tembjoin_" + str(k)
k_joinTable = censuslist
k_joinField = "OBJECTID"
k_joinField2= "C2015_J" + str(k+1)+ ".NEAR_FID"
outFeature = "Matching_Model.gdb"
# Create a feature layer from the lastjoindtable featureclass
arcpy.MakeFeatureLayer_management (k_inFeatures, k_layerName)

# Join the feature layer to a table
arcpy.AddJoin_management(k_layerName, k_joinField, k_joinTable, k_joinField2)
# Copy the layer to a new permanent feature class
arcpy.CopyFeatures_management(k_layerName, outFeature)
lastjoindtable= "Matching_Model.gdb"+ "\\" + str(layerName)
k+=1
except Exception as err:
print(err.args[0])

IanMurray · ‎05-02-2017

A few things.

First, you are using both j and k as a variable twice, once as a number, once as a placeholder for the item in the list you are iterating over. I suggest leaving j and k as the numbers you are using as a counter and use something like n_list and c_list as the placeholder in your loops.

Second, you have several variables that you are using that have never been defined(layerName for example).

Third, you haven't set an environment variable so it won't recognize some of your variables unless you set a environment for it to look for them or use a fully qualified file path(outFeature for example).

Fourth, for your j_joinTable and k_joinTable variables, you are redundant using a index to get the file name from the list. The placeholder variable you are using to iterator is the value of that filename and use can just use the placeholder as the input for j_joinTable and K_joinTable. If you follow my suggestion from above it would look like the following.

j_joinTable = n_list

k_joinTable = c_list

This is also a little redundant but fits your convention for assigning variables.

Fifth be consistent in your variable naming, you have J_joinField when you declare a variable, with the rest using a lowercase j, but then when you call it later, it uses the lower case j like I think you meant to. Ditto with the uppercase k at the end of your script, its not a variable thats been declared before.

AhmedAbdelnasser1 · ‎05-03-2017

Thanks, Ian for your help, but I still don't get what you are saying in some points.

let me give you the full picture or try to describe it in a better way:

Second, you have several variables that you are using that have never been defined(layerName for example).

Yes, as this only part of the code I declared them before.

Third, you haven't set an environment variable so it won't recognize some of your variables unless you set an environment for it to look for them or use a fully qualified file path(outFeature for example).

Although all the model working in the same workspace so as far as I understand I don't have to us the fully qualified file path.

Fourth, for your j_joinTable and k_joinTable variables, you are redundant using an index to get the file name from the list. The placeholder variable you are using to iterator is the value of that filename and use can just use the placeholder as the input for j_joinTable and K_joinTable. If you follow my suggestion from above it would look like the following.

j_joinTable = n_list

k_joinTable = c_list

This is also a little redundant but fits your convention for assigning variables.

I didn't get this point.

so what is the difference between the two I will be only adding a variable instead of using the index, could you please explain to me how this make a difference or send me any link that describes such a case.

Fifth be consistent in your variable naming, you have J_joinField when you declare a variable, with the rest using a lowercase j, but then when you call it later, it uses the lower case j like I think you meant to. Ditto with the uppercase k at the end of your script, it's not a variable that's been declared before.

Thank you, I fixed those issues.

I am although attaching the full code to make it clear.

Thank you.

IanMurray · ‎05-03-2017

Thanks for the extra code, I wasn't aware what you posted earlier was part of a larger piece of code.

I think the most important thing you are missing is what I mentioned first, and ends up being important for the 5th point I made. You are double assigning your j and k variable which is going to cause alot of issues.

As you can see above, when you used j as a placeholder variable for your loop, it assigns the variable j to the value the file name (or whatever value is in your list) in the list you are iterating over. j is now no longer equal to 0, and the subsequent j += 1 will not do what you planned it to do.

This is why I recommended you use a different variable for the placeholder, so you your variable j to continue to function as a counter as you want, as well as getting the file name as a seperate variable if you need it.

As for my fourth point its not a critical thing, but its more correct pythonicly to use the value that already in your placeholder then to redundantly use an index to call that same value from the list.

The crux of your issue was the double assigning of your variables j and k, fix that and you should be fine.

Hope this helps

AhmedAbdelnasser1 · ‎07-13-2017

Thanks, Ian & sorry for the late response

That was really helpful.

Right now I am facing this issue:

>>> nearlist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\NEAR_1",\
...           "C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\NEAR_2"]
...                      
... censuslist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\C2015_J1",\
...           "C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\C2015_J2"]
... jcount=0
... kcount=0
... jnearcount=1
... knearcount=1
... lastjoindtable="C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\UNMATCHED" #assign the unmatched FC to lastjoindtable variable as start
... for j in nearlist:
...  try:
...      arcpy.env.qualifiedFieldNames = False
...      j_inFeatures = lastjoindtable
...      j_layerName =  "J_tembjoin_" + str(jcount)
...      j_joinTable = j
...      j_joinField = "OBJECTID"
...      j_joinField2= "NEAR_" + str(jnearcount) + ".IN_FID"
...      outFeature = "Matching_Model.gdb"
...      tempjoin= "Matching_Model.gdb" + "\\" + str(j_layerName)
...      arcpy.MakeFeatureLayer_management (j_inFeatures,  j_layerName)
...      arcpy.AddJoin_management(j_layerName, j_joinField, J_joinTable, j_joinField2)
...      arcpy.CopyFeatures_management(j_layerName, outFeature)
...      jcount+=1
...      jnearcount+=1
...      for k in censuslist:
...       k_inFeatures = tempjoin
...       k_layerName =  "k_tembjoin_" + str(kcount)
...       k_joinTable = k
...       k_joinField = "OBJECTID"
...       k_joinField2= "C2015_J" + str(knearcount)+ ".NEAR_FID"
...       outFeature = "Matching_Model.gdb"
...       arcpy.MakeFeatureLayer_management (k_inFeatures,  k_layerName)
...       arcpy.AddJoin_management(k_layerName, k_joinField, k_joinTable, k_joinField2)
...       arcpy.CopyFeatures_management(k_layerName, outFeature)
...       lastjoindtable= "Matching_Model.gdb"+ "\\" + str(k_layerName)
...       kcount+=1
...       knearcount+=1
...  except Exception as err:
...           print(err.args[0])
... 
Failed to execute. Parameters are not valid.
ERROR 000725: Output Layer: Dataset J_tembjoin_0 already exists.
Failed to execute (MakeFeatureLayer).

Failed to execute. Parameters are not valid.
ERROR 000725: Output Layer: Dataset J_tembjoin_0 already exists.
Failed to execute (MakeFeatureLayer).

>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Appreciate if you could help me with that.

Thanks,

DanPatterson_Retired · ‎07-13-2017

Line 23 should be

for k in range(len(CensusList)):

you indentation looks off as well... this could be a copy past issue, but check

AhmedAbdelnasser1 · ‎07-13-2017

Thanks, Dan.

I tried your code but It is giving me a syntax error.

However, I found some logical errors & update it in the code above.

DanPatterson_Retired · ‎07-13-2017

you didn't update the line I mentioned. Why not open up a script editor, put your code in a script, save the script to disk and run that code. The interactive window makes it difficult to see whether you are still having indentation issues. And if you get a syntax error... the solution is in what it says

AhmedAbdelnasser1 · ‎07-16-2017

mmm.. actually, I am working with Nottbad ++ , but I just took the snapshot from the python window.

I don't think I have indentation problem, I double checked.

I tried your code but I it kept giving me a syntax error!

I attached the code maybe this helps

DanPatterson_Retired · ‎07-16-2017

Double checking in Spyder IDE revealed you were using a mix of tabs and spaces and one line had a single space indent. There were also '\' at the end of line 1 and 3. I found a few other inconsistencies, but I obviously can't test..

Notepad++ is ok for testing code snippets, but you might want to consider a different one. I have a blog or two on python IDEs especially if you have Pro installed as well.

So... now clue if the code will work, but there were syntax errors... report the exact one next time

nearlist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\NEAR_1",
            "C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\NEAR_2"]

censuslist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\C2015_J1",
              "C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\C2015_J2"]
j=0
k=0
lastjoindtable = r"C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\UNMATCHED" #assign the unmatched FC to lastjoindtable variable
for j in nearlist:
    try:
        arcpy.env.qualifiedFieldNames = False
        # Set local variables
        j_inFeatures = lastjoindtable
        j_layerName =  "J_tembjoin_" + str(j)
        j_joinTable = nearlist[j]
        j_joinField = "OBJECTID"
        J_joinField2= "NEAR_" + str(j+1) + ".IN_FID"
        outFeature = "Matching_Model.gdb"
        tempjoin= "Matching_Model.gdb" + "\\" + str(layerName)
        # Create a feature layer from the lastjoindtable featureclass
        arcpy.MakeFeatureLayer_management (j_inFeatures,  j_layerName)
        # Join the feature layer to a table
        arcpy.AddJoin_management(j_layerName, j_joinField, J_joinTable, j_joinField2)
        # Copy the layer to a new permanent feature class
        arcpy.CopyFeatures_management(j_layerName, outFeature)
        j+=1
    except Exception as err:
        print(err.args[0])

for k in censuslist:
    try:
        #"the output of the join in the small for will be assigned to last joind table variable which will be used as inbut in the big for"
        # Set local variables
        k_inFeatures = tempjoin
        k_layerName =  "k_tembjoin_" + str(k)
        k_joinTable = censuslist[k]
        k_joinField = "OBJECTID"
        k_joinField2= "C2015_J" + str(k+1)+ ".NEAR_FID"
        outFeature = "Matching_Model.gdb"
        # Create a feature layer from the lastjoindtable featureclass
        arcpy.MakeFeatureLayer_management (k_inFeatures,  k_layerName)

        # Join the feature layer to a table
        arcpy.AddJoin_management(k_layerName, k_joinField, k_joinTable, k_joinField2)
        # Copy the layer to a new permanent feature class
        arcpy.CopyFeatures_management(k_layerName, outFeature)
        lastjoindtable= "Matching_Model.gdb"+ "\\" + str(layerName)
        K+=1
    except Exception as err:
        print(err.args[0])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍