Hi everyone,

255
6
09-16-2020 07:48 PM
MaryBandziukas
New Contributor II

As part of a larger python program, I am trying to join a table and a feature class, both residing in a gdb.  My code for the join is as follows:

inFeatures = wksp + "\[name of file, which is pretty long]"

featField = "GenericID"

joinTable = wksp + "\joinGIDcounts"

tableField = "GID"

print ("joining tables...")

arcpy.JoinField_management (inFeatures, featField, joinTable, tableField)

print ("end script")

I have these issues:

1. the program never gets to the last print statement, it just keeps running

2. when I close the program, it says "your program is still running, do you want to kill it?", and I say yes.

3. I check the inFeatures table in ArcMap and the fields from joinTable are there and joined correctly

4. I try to delete the fields, because this is only a test of the first 200 records of a table with a few thousand records

5. I get a message saying cannot delete fields because of an existing lock.  But I killed the Python program when I closed it.

Does anyone know why I am stuck here?

Many thanks

0 Kudos
6 Replies
DanPatterson
MVP Esteemed Contributor

The problem may be in the names of the workspace and the files... especially if they aren't raw encoded and/or have spaces or other unsavory characters.

Make sure you "remove joins" prior to messing around with a standalone script.

I would also give this tool a run to see if anything is amiss

Validate Join (Data Management)—ArcGIS Pro | Documentation 

Then try to see if you can do the join through the interface PRIOR to worrying about whether it will run in a script.


... sort of retired...
MaryBandziukas
New Contributor II

Thanks, Dan, for your reply.  You may have hit it on the head; my path does indeed have spaces and a period, because whoever set up the directories numbered them, such as:  3. Geometric Design\Official Drawings ...  I have no way around this. 

The join does work fine in ArcMap, which is how I program - do it first in ArcMap, then write the script.  

However, the Join Validation Report gives me a cardinality warning that there are non-distinct matching records, which I am aware of and which doesn't cause errors in the join when done in ArcMap, but maybe in Python I need to present a join table with only unique records.  

It is probably pretty obvious that I don't write many programs - I am just discovering that there are nuanced differences between how ArcMap and Python handle things.   

Interestingly, the whole beginning of the script works OK using this wksp - spaces, period and all - i.e. read the feature class, place ID numbers into a python list, use .count to find out how many of each ID number there are and put the counts into a second list, zip the lists into a list of tuples, convert the tuples into a nympy structured array and convert the array to a table.  Python will confirm that the table exists, continuing to use this same workspace.  It's just the last step, the join, that hangs up.  Which leads me to believe the non-unique records might be more of an issue than the path. 

I noticed another interesting oddity too:  when I first converted the array to a table, I started the table name with upper case letters.  When I did an arcpy.ListTables(), it found other tables with lower case names, but not my new one, or another one I had previously created in ArcMap also beginning with upper case letters.  So I changed the name and took out the upper case characters, and then arcpy found it.  I have a habit of prefacing my gdb tables with GDBT_ (geodatabase table) so I can differentiate them from imported spreadsheets with the same name.  

And finally, my feature class filename begins with BK_ (uppercase), and arcpy.ListFeatureClasses() does find it.  So it's not simply a matter of upper/lower case.  

In any case, I think I'll try paring my join table to unique records first, and if necessary I may try moving everything to another temporary path without spaces etc, to see if it works there. 

Thanks so much for your help

0 Kudos
DanPatterson
MVP Esteemed Contributor

If you are going from structured arrays to tables then to joins, why not

ExtendTable—ArcGIS Pro | Documentation 

Saves a load of problems.

You will have to deal with the cardinality issue in any event.  Just make sure that the field you are joining to has unique values and you have an equivalent field in your numpy array


... sort of retired...
0 Kudos
MaryBandziukas
New Contributor II

so many choices

ExtendTable joins an array to a table, I need to join a table (or array) to a feature class.  Would it still work?  

Also, the feature class does not have unique values in its join field; if I provide only unique values in the join table, I will have a N:1 join, which should work fine.  e.g. if I have 3 records with the same GenericID (the join field) in the feature class, they will each be matched to the same record in the join table, which has two fields - GID (genericID - the join field) and countGID, which in this case would have the value of 3, and which tells me that this particular segment joined to 3 "valid intersection nodes" as a result of a spatial join. 

I noticed that the inputs into arcpy.JoinField_management are supposed to be table views, and I didn't convert my files, just input them as feature class and table.  Perhaps that also needs attention.  I did it because I wanted to see if it would work (and then forgot that part).  Maybe not 

Thanks for your continuing attention!  The whole purpose of the script is to avoid having to export to and process in excel and then re-import the spreadsheet back into the gdb.  I hope I can get it; it will make file management a bit easier

0 Kudos
DanPatterson
MVP Esteemed Contributor

a table coming from an array can be joined to a featureclass since it has a table.

I skip excel (and Pandas for that matter) and just use numpy and its ndarray and chararray handle number and text processing 


... sort of retired...
0 Kudos
MaryBandziukas
New Contributor II

OK, thanks.  I am completely new to numpy and pandas.  I'm trying to move away from excel.  This is my first question posted to GeoNet, so thanks for showing me what a great resource it is.  Looking forward to learning what numpy can do (and maybe Pandas)

0 Kudos