topic Re: Join in Python Questions

Join

CCWeedcontrol — Sun, 12 Dec 2021 07:25:37 GMT

I am doing a basic join from a table to feature class, it takes about 6 mins.

How can up make this faster?

import arcpy, fnmatch
from arcpy import env
from datetime import datetime as d
startTime = d.now()

env.overwriteOutput = 1
env.workspace = r"C:\Temp"
table = r"C:\Temp\PermitsIssued"  
  
shapefile = "TaxParcels"  

arcpy.MakeFeatureLayer_management(shapefile, "In_memory\parcelLyr")  

#Join table to feature layer  
arcpy.AddJoin_management("In_memory\parcelLyr", "PIN_ID", table, "PINNum", "KEEP_COMMON")

#Export joined layer to new feature clas  
arcpy.FeatureClassToFeatureClass_conversion("In_memory\parcelLyr", r"C:\Temp", "ParcelPermits.shp") 

print 'Done'

try:
    print '(Elapsed time: ' + str(d.now() - startTime)[:-3] + ')'

except Exception, e:
    # If an error occurred, print line number and error message
    import traceback, sys
    tb = sys.exc_info()[2]
    print "Line %i" % tb.tb_lineno
    print e.message‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Join

DanPatterson_Retired — Fri, 02 Feb 2018 21:54:37 GMT

The join is going to depend on the file size as a minimum.

What is your software? Map? PRO?

How much memory do you have?

64 bit enabled if using Map?

Are you monitoring system resources when you do the run?

other applications sucking up memory? (ie browsers etc)

Re: Join

CCWeedcontrol — Fri, 02 Feb 2018 22:25:56 GMT

ArcMap 10.4.1

32 gb ram

I just have 32 bit - i use to have 64 bit but i was having conflicts.

my system resources are cup 9%, Memory is 14 gb. with basic applications (browser, Microsoft)

When running the join script my cpu and memory don't change much.

Re: Join

DanPatterson_Retired — Fri, 02 Feb 2018 22:32:36 GMT

So if you are using arcmap, you are limited to 4 Gb, so all your extra ram is wasted basically

If you are working with locally stored data on a modern machine with one of those fancy new drives then your bottleneck may just be the size of the data you are joining and your software.

Re: Join

JamesMacKay3 — Fri, 02 Feb 2018 22:58:47 GMT

Have you built indexes on the fields used in the join?

Re: Join

ChrisSnyder — Sat, 03 Feb 2018 01:03:27 GMT

Use a dictionary and update cursor (which will be much faster than the classic join/calc or join/export)

Re: Join

JoshuaBixby — Sat, 03 Feb 2018 17:55:05 GMT

Start by not using shape files, seriously. The performance of shape files continues to degrade in newer releases of ArcMap, even ArcGIS Pro. There are certain geoprocessing operations (most in the Overlay toolset) where involving shape files can add an order of magnitude to the run times.

My suggestion is move data into file geodatabases and make sure you have indexed the fields you are joining on.

Re: Join

CCWeedcontrol — Mon, 05 Feb 2018 16:03:44 GMT

I did read some post about using dictionary and update cursor but i couldn't figure out how to set it up with my data. The ones i found have multiple fields that were joined.

Do you have a basic example of how to us dictionary and update cursor to do a join.

Re: Join

ChrisSnyder — Mon, 05 Feb 2018 17:49:37 GMT

Here's a general example of dictionary/cursor method. Could certainly be made more elegant depending on what you are trying to do....

import arcpy
mainTbl = r"C:\temp\blah.shp"
joinTbl = r"C:\temp\lookup.dbf"
fieldsToJoinList = ["FIELD1","FIELD2","FIELD3"]
joinField = "COMMON_ID"
arcpy.AddField_management(mainTbl, "FIELD1", "BLAH")
arcpy.AddField_management(mainTbl, "FIELD2", "BLAH")
arcpy.AddField_management(mainTbl, "FIELD3", "BLAH")
joinTblDict = {r[0]:r[1:] for r in arcpy.da.SearchCursor(joinTbl, [joinField]+fieldsToJoinList)}
updateRows = arcpy.da.UpdateCursor(mainTbl, [joinField]+fieldsToJoinList)
for updateRow in updateRows:
    if updateRow[0] in joinTblDict:
        updateRow[1] = joinTblDict[updateRow[0]][1]
        updateRow[2] = joinTblDict[updateRow[0]][2]
        updateRow[3] = joinTblDict[updateRow[0]][3]
    updateRows.updateRow(updateRow)
del updateRow, updateRows

Re: Join

ChrisSnyder — Mon, 05 Feb 2018 17:50:56 GMT

BTW: Long live .shp

Woot woot!!!

Re: Join

CCWeedcontrol — Sun, 12 Dec 2021 07:25:39 GMT

Thank you for sample. I have tried modifying what you posted to my code but after i run it the fields don't get updated. I don't get no error. Any ideas on what i am doing wrong?

import arcpy

mainTbl = "C:/Temp/BP.gdb/TaxParcels"
joinTbl = "C:/Temp/BP.gdb/Permits"
fieldsToJoinList = ["AppSubmitted","PermitIssued","Classification"]

#Addfields
fields = [
    ("AppSubmitted","Date","","","","","NULLABLE","NON_REQUIRED",""),
    ("PermitIssued","Date","","","","","NULLABLE","NON_REQUIRED",""),
    ("PermitNum","Text","30","","","","NULLABLE","NON_REQUIRED",""),
    ("ParcelNum","Text","24","0","","","NULLABLE","NON_REQUIRED",""),
    ("Classification","Text","4000","","","","NULLABLE","NON_REQUIRED","")
]
for field in fields:
    arcpy.AddField_management(*(mainTbl,) + field)
        
joinTblDict = {r[0]:r[1:] for r in arcpy.da.SearchCursor(joinTbl, ["PIN_Num"]+fieldsToJoinList)}
updateRows = arcpy.da.UpdateCursor(mainTbl, ["PIN_ID"] +fieldsToJoinList)
for updateRow in updateRows:
    if updateRow[0] in joinTblDict:
        updateRow[1] = joinTblDict[updateRow[0]][1]
        updateRow[2] = joinTblDict[updateRow[0]][2]
        updateRow[3] = joinTblDict[updateRow[0]][3]                                              
    updateRows.updateRow(updateRow)
del updateRow, updateRows
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Join

ChrisSnyder — Mon, 05 Feb 2018 22:18:43 GMT

I assume the fields are added to the mainTbl and dictionary gets populated with the correct values, right?

I don't see anything in the update cursor, but... Are the "PIN_ID" and "PIN_Num" field types the same ("123" vs 123 vs 123.0)

Re: Join

CCWeedcontrol — Mon, 05 Feb 2018 23:04:02 GMT

Yes they are both text fields. i did have the wrong filed in the joinTblDict, which is now fixed but now i get error.

line 25, in <module>
updateRows.updateRow(updateRow)
RuntimeError: The value type is incompatible with the field type. [PermitIssued]

but "PermitIssued" field is a date field on both the mainTb1 and joinTb1.

Re: Join

ChrisSnyder — Mon, 05 Feb 2018 23:39:58 GMT

Note sure what the issue is - maybe just monkey with the values. For example in the interactive window try inserting some values like:

updateRows.updateRow(("monkey", 1 , datetime.date(2017,2,5)))

and see if they take. I know with the da cursors that dates are converted on the fly to Python datetime objects. If you interrogate the values in the dictionary are they all of the anticipated data type and order?

Re: Join

DanPatterson_Retired — Mon, 05 Feb 2018 23:42:49 GMT

If I recall, date fields aren't permissible (could have changed)

EDIT

They are permitted, they just need to be exactly the same format

Have you tried to do the join manually using existing tools to confirm that the date fields show up in the Add Join or Join Fields tools?

Re: Join

CCWeedcontrol — Wed, 07 Feb 2018 17:14:10 GMT

Dan, i was able to do a join with the Join field (data management tool) in ArcCatalog., It joined just fine.

The dates from the table in the "PermitIssued" field are entered as 4/28/2017.

Re: Join

CCWeedcontrol — Wed, 07 Feb 2018 17:15:28 GMT

I get.

updateRows.updateRow(("monkey", 1 , datetime.date(2017,2,5)))
TypeError: sequence size must match size of the row

Re: Join

CCWeedcontrol — Sun, 12 Dec 2021 07:25:42 GMT

I was able an example of some code that worked.

/blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries

Although i don't get one field to get populated the "ParcelNum". Would it be best to add another updatecursor?

fc_dest = "C:/Temp/BuildingPermit.gdb/TaxParcels"
#Addfields
fields = [
    ("AppSubmitted","Date","","","","","NULLABLE","NON_REQUIRED",""),
    ("PermitIssued","Date","","","","","NULLABLE","NON_REQUIRED",""),
    ("PermitNum","Text","30","","","","NULLABLE","NON_REQUIRED",""),
    ("ParcelNum","Text","24","0","","","NULLABLE","NON_REQUIRED",""),
    ("Classification","Text","4000","","","","NULLABLE","NON_REQUIRED",""),
    ("Address","Text","100","","","","NULLABLE","NON_REQUIRED",""),
    ("ProjectInfo","Text","4000","","","","NULLABLE","NON_REQUIRED",""),
    ("Contractor","Text","255","","","","NULLABLE","NON_REQUIRED",""),
    ("Subdivision","Text","240","","","","NULLABLE","NON_REQUIRED",""),
    ("TwnRngSec","Text","32","0","","","NULLABLE","NON_REQUIRED",""),
    ("LivingSpace","Double","","","","","NULLABLE","NON_REQUIRED",""),
    ("TotalSqFt","Double","","","","","NULLABLE","NON_REQUIRED",""),
    ("Value_","Double","","","","","NULLABLE","NON_REQUIRED",""),
    ("Valuation","Double","","","","","NULLABLE","NON_REQUIRED",""),
    ("Status","Text","30","","","","NULLABLE","NON_REQUIRED","")
]
for field in fields:
    arcpy.AddField_management(*(fc_dest,) + field)
  
sourceFC = "C:/Temp/BuildingPermit.gdb/Permits"  
  
sourceFieldsList = ["PINNum", "AppSubmitted",'PermitIssued', 'PermitNum','Classification','Address', 'Contractor', 'Subdivision', 'TwnRngSec', 'LivingSpace', 'TotalSqFt', 'Value_', 'Valuation', 'Status']#,'ProjectInfo', 'Contractor', 'Subdivision', 'TwnRngSec', 'LivingSpace', 'TotalSqFt', 'Value_', 'Valuation', 'Status']  
  
# Use list comprehension to build a dictionary from a da SearchCursor  
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}  
  
updateFC = "C:/Temp/BuildingPermit.gdb/TaxParcels"  
  
updateFieldsList = ["PIN_ID", "AppSubmitted",'PermitIssued', 'PermitNum','Classification','Address', 'Contractor', 'Subdivision', 'TwnRngSec', 'LivingSpace', 'TotalSqFt', 'Value_', 'Valuation', 'Status']  
  
with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value of the row being updated in a keyValue variable  
        keyValue = updateRow[0]  
        # verify that the keyValue is in the Dictionary  
        if keyValue in valueDict:  
            # transfer the values stored under the keyValue from the dictionary to the updated fields.  
            for n in range (1,len(sourceFieldsList)):  
                updateRow[n] = valueDict[keyValue][n-1]  
            updateRows.updateRow(updateRow) 
  
del valueDict  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Join

RandyBurton — Sun, 12 Dec 2021 07:25:45 GMT

Should ParcelNum be included in both updateFieldsList and sourceFieldsList?

Also, if you are trying to set the size of the text fields for AddField, I think the value should be moved over a bit:

# AddField_management (in_table, field_name, field_type, {field_precision}, {field_scale}, {field_length}, {field_alias}, {field_is_nullable}, {field_is_required}, {field_domain})

fields = [
    ("PermitNum","Text","30","","","","NULLABLE","NON_REQUIRED",""),
# should be
    ("PermitNum","Text","","","30","","NULLABLE","NON_REQUIRED",""),‍‍‍‍‍‍‍‍‍‍

Re: Join

CCWeedcontrol — Wed, 07 Feb 2018 18:33:17 GMT

The ParcelNum is the parcel unique identifier and Pin_ID is the tables unique identifier. the fields in both the updateFieldsList and sourceFieldsList must be matching in order for the attributes to transfer.

Thanks for catching the size of text fields.