Corner case on arcpy.CalculateField_management

694
5
06-21-2018 08:44 AM
JoshuaChisholm
Regular Contributor

Hey GIS experts,

I ran into a strange problem with the arcpy.CalculateField_management tool. I'm not sure if this is a bug or just an error in my thinking, but I thought I'd post it just in case someone else has the same issue.

If you run the commands in this order they work fine.

  1. arcpy.MakeTableView_management(mainTable, "tableView")
  2. arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100) 
  3. arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short")
  4. arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")
  5. Result are as expected (the Province_Long field has data in it)

If you run the commands in this order they do not work.

  1. arcpy.MakeTableView_management(mainTable, "tableView")
  2. arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short")
  3. arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100) 
  4. arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")
  5. Result are not as expected (the Province_Long field has no data in it)
    No error is thrown, meaning that the field Province_Long exists and can be found, but for some reason the data is not being retrieved from the joined table. This only occurs when you are calculated from a field from the joined table.

I also built a stand-alone script if someone wants to replicate the issue on their end. To change the script from not working to working, swap the two lines of code under #Swap these two lines

import arcview
import arcpy

#=====Allow overwrite=====
originalOverwriteOutput = arcpy.env.overwriteOutput
arcpy.env.overwriteOutput = True

#=====Build main table=====
mainTable=arcpy.CreateTable_management(arcpy.env.scratchGDB, "MainTable")
arcpy.AddField_management(mainTable, "Address", "TEXT", 100)
arcpy.AddField_management(mainTable, "Province_Short", "TEXT", 100)
with arcpy.da.InsertCursor(mainTable, ["Address","Province_Short"]) as cursor:
    cursor.insertRow(["123 Sesame Street","ON"])
    cursor.insertRow(["10 Downing Street","NS"])
    cursor.insertRow(["221B Baker Street","QC"])
    cursor.insertRow(["4 Privet Drive","BC"])
    
#=====Build lookup table=====
lookupTable=arcpy.CreateTable_management(arcpy.env.scratchGDB, "LookupTable")
arcpy.AddField_management(lookupTable, "Prov_Short", "TEXT", 100)
arcpy.AddField_management(lookupTable, "Prov_Long", "TEXT", 100)
with arcpy.da.InsertCursor(lookupTable, ["Prov_Short","Prov_Long"]) as cursor:
    cursor.insertRow(["NL","Newfoundland and Labrador"])
    cursor.insertRow(["PE","Prince Edward Island"])
    cursor.insertRow(["NS","Nova Scotia"])
    cursor.insertRow(["NB","New Brunswick"])
    cursor.insertRow(["QC","Quebec"])
    cursor.insertRow(["ON","Ontario"])
    cursor.insertRow(["MB","Manitoba"])
    cursor.insertRow(["SK","Saskatchewan"])
    cursor.insertRow(["AB","Alberta"])
    cursor.insertRow(["BC","British Columbia"])
    cursor.insertRow(["YT","Yukon"])
    cursor.insertRow(["NT","Northwest Territories"])
    cursor.insertRow(["NU","Nunavut"])


#=====Run lookup=====
arcpy.MakeTableView_management(mainTable, "tableView")

#==================================================
#Swap these two lines
arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short") #If this line is first, calc field does nothing
arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100) #If this line is first, it works properly
#==================================================

arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")

#=====Print output=====
with arcpy.da.SearchCursor(mainTable, ["Address","Province_Short","Province_Long"]) as cursor:
    for row in cursor:
        print("Address: "+str(row[0])+", Province_Short: "+str(row[1])+", Province_Long: "+str(row[2]))
        arcpy.AddMessage("Address: "+str(row[0])+", Province_Short: "+str(row[1])+", Province_Long: "+str(row[2]))

#=====Reset overwrite=====
arcpy.env.overwriteOutput = originalOverwriteOutput‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Tags (1)
0 Kudos
5 Replies
JoshuaBixby
MVP Esteemed Contributor

What product (ArcGIS Pro or ArcMap) and what version are you running?  I just ran your script on ArcMap 10.6, and I get the same correct answers regardless of which line is first.

JoshuaChisholm
Regular Contributor

Hello Bixby,

I'm using ArcMap 10.4.1.

Thanks for testing it out! I guess it's not an issue anymore.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Hey Josh!... try PYTHON_9.3 don't know what or if that would make a difference.  

Your group should look into PRO soon, Python 3.6 is out and the support life of python 2.7 ends in 2010

JoshuaChisholm
Regular Contributor

Hey Dan! Thanks for replying I've tried PYTHON_9.3, same problem.

Sounds like it's time for PRO...I guess that means my education has become dated 

(also I think 2010 was a type. Looks like 2.7 support ends in 2020)

DanPatterson_Retired
MVP Esteemed Contributor

support ends... but don't expect anything new beyond a fix...

Besides python 3.8 is in development.. it is almost time for the 4.0 series

0 Kudos