Join

[CCWeedcontrol]

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[ChrisSnyder]

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)

[CCWeedcontrol]

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.

[ChrisSnyder]

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?

[CCWeedcontrol]

I get.

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

[DanPatterson_Retired]

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?

[CCWeedcontrol]

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.

[ChrisSnyder]

BTW: Long live .shp

Woot woot!!!

[CCWeedcontrol]

I was able an example of some code that worked.

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

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  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[RandyBurton]

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",""),‍‍‍‍‍‍‍‍‍‍

[CCWeedcontrol]

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.