topic Re: Calculate Field Tool Does not Complete - Help in Python Questions

Calculate Field Tool Does not Complete - Help

ModernElectric — Wed, 10 Apr 2013 14:42:44 GMT

I need help really bad. I have been looking through all of the Forum posts and and Help sections to try to figure out what is wrong with no luck. Here is what I am doing:
I have a File Geodatabase (ArcMap 10.0) with a Feature Class (Service Location) which is the physical location of our electric meters. I have multiple fields that I need to update on a nightly basis. This data comes from our CIS system which holds account, customer and meter attribute information. I have a script to runs automatically every night that pulls an Oracle VW through an ODBC connection and brings it into my File Geodatabase as a Geodatabase Table. What I have to do is to update a specific field (Account Number) in the Feature Class with the same field in the table (ACCOUNTNUM)... Just an example. What I want to be able to do is to also have this script run automatically at night. I have around 9500 records in the feature class with about 15 different fields to do this to. The problem I am having is it takes up to 20 to 25 minutes for each field to update so at times - the script can take 8 to 9 hours to complete. From what I understand - this should take only a few minutes.... So what I am doing is not working.
I decided to go back to the "Drawing Board" to see if there is something I am missing with writing the code..... I decided to work with (1) field right now to see if I can make it work and get the update time down before I rewrite the rest of the code.

This is what I currently have and it does not seem to be working:

## Set the necessary product code import logging  # Import arcpy module import arcpy from arcpy import env   # if run in ArcGIS Desktop, show messages, also print to log def log(method, msg):     print msg     method(msg)  logging.basicConfig(         level=logging.INFO,         format='%(asctime)s %(levelname)-s %(message)s',         datefmt='%a, %d %b %Y %H:%M:%S',         filename='Test_Update.log',         filemode='w'         )  log(logging.info, "Updating Service Locations Feature Class Begins")  # Set environment settings env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"  # Set Local variables: inFeatures = "SERVICE_LOCATION"                         # Service Location Feature Class layerName = "SERVICE_LOCATION_LAYER"                    # Service Location Feature Layer fieldName1 = "SERVICE_ADDRESS" expression1 = "CIS_Account_Data.SERVICE_ADDRESS" joinField = "POLE_NUMBER" joinField2 = "MAPNO" joinTable = "CIS_Account_Data"  # Create a Feature Layer from Service_Location Feature Class log(logging.info, "Create Feature Layer for Service Location Feature Class") arcpy.MakeFeatureLayer_management(inFeatures, layerName)  # Join the feature layer to a table log(logging.info, "Join Table to Feature Layer Service Location") arcpy.AddJoin_management(layerName, joinField, joinTable, joinField2)      # Process: Calculate Field (Member Number) log(logging.info, "Update Member Number Field") arcpy.CalculateField_management(layerName, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON")  log(logging.info, "Complete")

I am not sure what is slowing this down. Am I looking at this process the wrong way?? Are there different tools that can be used to complete this mission??

Thank you for your help

Re: Calculate Field Tool Does not Complete - Help

ArkadiuszMatoszka — Wed, 10 Apr 2013 14:58:49 GMT

Hi,
I had the same problem with AddJoin + CalculateField efficiency. One solution could be creating indexes on fields you're using in join if you hadn't done this before. If this won't help (sometimes it's not enough) I would suggest using combination of arcpy.sa.SearchCursor and python dictionary to collect right data from table, and then arcpy.da.UpdateCursor to update featureclass fields. It take some time to write, but it's very efficient. If you need some details feel free to ask.

Best Regards.
Arek

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Wed, 10 Apr 2013 15:06:25 GMT

Hi,
I had the same problem with AddJoin + CalculateField efficiency. One solution could be creating indexes on fields you're using in join if you hadn't done this before. If this won't help (sometimes it's not enough) I would suggest using combination of arcpy.sa.SearchCursor and python dictionary to collect right data from table, and then arcpy.da.UpdateCursor to update featureclass fields. It take some time to write, but it's very efficient. If you need some details feel free to ask.

Best Regards.
Arek

Arek-

Thank you for the reply. Creating the Feature Layer and doing the Join seems to work just fine without any hold-up or lagging.... This is being done outside of ArcMap outside of an Edit Session... My intention is to have this done automatically at night and the only way I have figured out to do it is to have a Python Script and run a schedule task using the Windows Task Scheduler.

Forgive me - but I am still really new to Python and not sure how to use or write indexes and python dictionaries......I have done some (very little reading on it).....

Any ideas????

Thank you

Re: Calculate Field Tool Does not Complete - Help

Anonymous User — Wed, 10 Apr 2013 15:39:02 GMT

Arek-

Thank you for the reply. Creating the Feature Layer and doing the Join seems to work just fine without any hold-up or lagging.... This is being done outside of ArcMap outside of an Edit Session... My intention is to have this done automatically at night and the only way I have figured out to do it is to have a Python Script and run a schedule task using the Windows Task Scheduler.

Forgive me - but I am still really new to Python and not sure how to use or write indexes and python dictionaries......I have done some (very little reading on it).....

Any ideas????

Thank you

Here is an example of some code I use all the time for getting values from one table to another without needing to join them. I wrote this because I was frustrated by the amount of time it took to use the "join and calculate" method. This is a hundred times faster because it uses dictionaries with update cursors.

The parameters are just the target table (I called it source_table below), a field with common values in both tables, a list of fields to be updated, the table with the desired values, the join field, then the fields that contain the values. Below is the 10.0 code, I also have another version for 10.1 which is a little faster. Let me know if you want that.

import sys, traceback, arcpy, os from os import path as p  def AttributeUpdate(source_table, in_field, update_fields, join_table, join_key, join_values):     try:         """ Updates fields from one table to user defined fields in another table"""         # Check input parameters         if not arcpy.Exists(source_table):             print source_table + ' not found!\nPlease verify that full path of table exists'             sys.exit()         if not arcpy.Exists(join_table):             print join_table + ' not found!\nPlease verify that full path of table exists'             sys.exit()         if in_field not in [f.name for f in arcpy.ListFields(source_table)]:             print in_field + ' not found in ' + p.basename(source_table)             sys.exit()         if join_key not in [f.name for f in arcpy.ListFields(join_table)]:             print join_key + ' not found in ' + p.basename(join_table)             sys.exit()         for fld in update_fields:             if fld not in [f.name for f in arcpy.ListFields(source_table)]:                 print fld + ' not found in ' + p.basename(source_table)                 print 'Please verify that field names match in ' + p.basename(source_table)                 sys.exit()         for fldb in join_values:             if fldb not in [f.name for f in arcpy.ListFields(join_table)]:                 print fldb + ' not found in ' + p.basename(join_table)                 print 'Please verify that field names match in ' + p.basename(join_table)                 sys.exit()         if not type(join_values) == list:             join_values = list(join_values)         if not type(update_fields) == list:             update_fields = list(update_fields)          # Make sure there is matching number of join and update fields         update_dict = {}         if len(update_fields) == len(join_values):             for i in range(len(update_fields)):                 update_dict[join_values] = update_fields                              for k,v in update_dict.iteritems():                 # Create Dictionary                 path_dict = {}                 srows = arcpy.SearchCursor(join_table)                 for srow in srows:                     keyrow = srow.getValue(join_key)                     valrow = srow.getValue(k)                     path_dict[keyrow] = valrow                                  # Update Cursor                 urows = arcpy.UpdateCursor(source_table)                 for row in urows:                     upkey = row.getValue(in_field)                     try:                         if upkey in path_dict:                             row.setValue(v, path_dict[upkey])                             urows.updateRow(row)                     except:                         pass  # skip nulls                  print '\'%s\' field in "%s" updated successfully' %(v, p.basename(source_table))             del row, urows, srow, srows                    else:             print 'ERROR:  Number of update fields and value fields does not match'                      except:         print '\nERROR in AttributeUpdate Function.\nPlease Check input parameters and try again.'    if __name__ == '__main__':      shp = r'C:\IGIC\Advanced\Functions\DATA\Iowa_Co.shp'     dbf = r'C:\IGIC\Advanced\Functions\DATA\Census_2000.dbf'      # Attribute Update test     up_fields = ['POP2000','Perc_Urb','Perc_Rur']     jn_fields = ['POP2000','PCT_URB_00','PCT_RUR_00']     AttributeUpdate(shp,'FIPS',up_fields,dbf,'FIPS',jn_fields)

I usually just import this function into other scripts. I have called the script MemoryTableTools. Here is an example of how you can do that:

import MemoryTableTools  shp = r'C:\IGIC\Advanced\Functions\DATA\Iowa_Co.shp' dbf = r'C:\IGIC\Advanced\Functions\DATA\Census_2000.dbf' up_fields = ['POP2000','Perc_Urb','Perc_Rur'] jn_fields = ['POP2000','PCT_URB_00','PCT_RUR_00']  MemoryTableTools.AttributeUpdate(shp,'FIPS',up_fields,dbf,'FIPS',jn_fields)

Re: Calculate Field Tool Does not Complete - Help

Anonymous User — Sat, 11 Dec 2021 23:41:11 GMT

So for your case if you save the script as MemoryTableTools.py like I have, this code should work:

import arcpy
from arcpy import env
from MemoryTableTools import *

# Set environment settings
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"

# Set Local variables:
inFeatures = "SERVICE_LOCATION"                         
joinField = "POLE_NUMBER"
joinField2 = "MAPNO"
joinTable = "CIS_Account_Data"
up_fields = ['MEMBER_NUMBER']
jn_fields = ['MEMBERNO']

# Run Attribute Update
AttributeUpdate(inFeatures, 'POLE_NUMBER', up_fields, joinTable,'MAPNO',jn_fields)

Keep in mind that this works on multiple fields at a time if you need to, the above example just works on one field from your first post. Also, be sure to save the script in a path that can be found by python (PYTHONPATH). A directory like this:

C:\Python27\ArcGIS10.1\Lib

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Sat, 11 Dec 2021 23:41:14 GMT

Caleb-

Its going to take me awhile to figure out the dictionaries and all.... This new script you have me - I did exactly as you directed and get an error message: NameError: name 'AttributeUpdate' is not defined

Chris

So for your case if you save the script as MemoryTableTools.py like I have, this code should work:
import arcpy
from arcpy import env
from MemoryTableTools import *

# Set environment settings
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"

# Set Local variables:
inFeatures = "SERVICE_LOCATION"                         
joinField = "POLE_NUMBER"
joinField2 = "MAPNO"
joinTable = "CIS_Account_Data"
up_fields = ['MEMBER_NUMBER']
jn_fields = ['MEMBERNO']

# Run Attribute Update
AttributeUpdate(inFeatures, 'POLE_NUMBER', up_fields, joinTable,'MAPNO',jn_fields)
        
Keep in mind that this works on multiple fields at a time if you need to, the above example just works on one field from your first post. Also, be sure to save the script in a path that can be found by python (PYTHONPATH). A directory like this:

C:\Python27\ArcGIS10.1\Lib

Re: Calculate Field Tool Does not Complete - Help

Anonymous User — Sat, 11 Dec 2021 23:41:17 GMT

Hmm, that is strange. Did you save the script as MemoryTableTools.py and put it in your Python folder? I guess you must have since you did not get an import error. Perhaps you could try the namespace then:

import arcpy
from arcpy import env
import MemoryTableTools

# Set environment settings
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"

# Set Local variables:
inFeatures = "SERVICE_LOCATION"                         
joinField = "POLE_NUMBER"
joinField2 = "MAPNO"
joinTable = "CIS_Account_Data"
up_fields = ['MEMBER_NUMBER']
jn_fields = ['MEMBERNO']

# Run Attribute Update
MemoryTableTools.AttributeUpdate(inFeatures, 'POLE_NUMBER', up_fields, joinTable,'MAPNO',jn_fields)

Re: Calculate Field Tool Does not Complete - Help

MichaelVolz — Wed, 10 Apr 2013 16:19:07 GMT

Caleb:

Is there any reason why Chris' original script should take so long to run?

I am working with a much larger feature class and table performing the same type of join and calculate geoprocess and it completes in 5 minutes.

Is there any chance it could be due to field type differences between the join field in the table and the field that is being calculated in the feature class?

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Sat, 11 Dec 2021 23:41:19 GMT

Caleb-

Went through and did exactly what you said over again with the script you included in the latest E-Mail....And I get this:

ImportError: Bad magic number in C:/Python27/ArcGIS10.1/Lib\weakref.pyc

Also - when I open the script in Python IDLE - it says version: Python 2.6.5 - is this a big deal or do I need to upgrade?

Thanks

import arcpy
from arcpy import env
import MemoryTableTools

# Set environment settings
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"

# Set Local variables:
inFeatures = "SERVICE_LOCATION"                         
joinField = "POLE_NUMBER"
joinField2 = "MAPNO"
joinTable = "CIS_Account_Data"
up_fields = ['MEMBER_NUMBER']
jn_fields = ['MEMBERNO']

# Run Attribute Update
MemoryTableTools.AttributeUpdate(inFeatures, 'POLE_NUMBER', up_fields, joinTable,'MAPNO',jn_fields)

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Wed, 10 Apr 2013 16:24:40 GMT

Michael-

I went to my Feature Class and Table just to verify. The (2) fields I am using to Join the table to the feature class are the same. Also the (2) fields (Member Number) are both [TEXT] field type.

Caleb:

Is there any reason why Chris' original script should take so long to run?

I am working with a much larger feature class and table performing the same type of join and calculate geoprocess and it completes in 5 minutes.

Is there any chance it could be due to field type differences between the join field in the table and the field that is being calculated in the feature class?

Re: Calculate Field Tool Does not Complete - Help

Anonymous User — Wed, 10 Apr 2013 16:26:06 GMT

Caleb:

Is there any reason why Chris' original script should take so long to run?

I am working with a much larger feature class and table performing the same type of join and calculate geoprocess and it completes in 5 minutes.

Is there any chance it could be due to field type differences between the join field in the table and the field that is being calculated in the feature class?

Hi Michael,

I am not sure what the issues were. I guess I should have clarified that I was having slow performance on very large data sets and I was running several different field calculations. I just know that the processing time went from about 20 minutes with the join and calculate method down to under 2 minutes with AttributeUpdate() function I posted above.

I can do a speed test real quick and post the times for each process.

Re: Calculate Field Tool Does not Complete - Help

MichaelVolz — Wed, 10 Apr 2013 16:33:22 GMT

Caleb:

Your solution is very elegant compared to the script that Chris wrote and I helped him modify. I cannot speak for Chris, but your script is advanced and might be difficult for a person new to python scripting.

It just seems that Chris' script has a minor issue that can be quickly corrected (once its found) as he is working with such a small dataset.

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Wed, 10 Apr 2013 16:41:44 GMT

Caleb/Michael-

All is correct. What I am doing is simple ArcGIS Python scripting 101.... When it comes to the talk of dictionaries...indexes...etc (Advanced Python scripting) I am lost......

There has to be an easier way for a rookie python scripter like myself to be able to program a process that takes Field "Member Number" from a file Geodatabase table and copy it to a field in a file geodatabase "member number" after it makes a join.

Would it be easier:
   Add Join (Table to Feature Class) - using Map Number/MAPNO as the join attribute
   Export to a new Feature Class
   Calculate Field(s)
   Delete fields that use to be the table
   Rename feature class back to the original name

Is that overkill or would to work???

Caleb:

Your solution is very elegant compared to the script that Chris wrote and I helped him modify. I cannot speak for Chris, but your script is advanced and might be difficult for a person new to python scripting.

It just seems that Chris' script has a minor issue that can be quickly corrected (once its found) as he is working with such a small dataset.

Re: Calculate Field Tool Does not Complete - Help

MichaelVolz — Wed, 10 Apr 2013 16:54:46 GMT

Chris:

With your current script without the data dictionary, can you verify that the data type is the same in the original field of the feature class and the source field you are calculating from in the table?

Like I said before, I have a script that works like yours on a much larger dataset and it finishes in less than 5 minutes.

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Wed, 10 Apr 2013 17:12:12 GMT

Caleb-

   I got your process/script to WORK.......It took about 15 seconds for the MEMBER_NUMBER field to update from the CIS_Account_data table....

Now - I know I have alot of work to do since I have about 20 fields in my electric model and about 15 in my water model to have this done too...

What would be my next step so this works for all of my fields

Example:

Customer name:
    Feature Class - CUSTOMER_NAME
    Table - CUSTNAME

Thank you very much!!!!!

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Wed, 10 Apr 2013 17:31:38 GMT

Caleb and Michael-

I got it all to work. The script(s) with the dictionaries did EXACTLY what I wanted it to so with almost NO time at all. Now its just going to take me awhile to program the fields into the script and run an overnight test......

Next step - reading up on dictionaries and indexes so I can attempt to understand this large script that Caleb sent me..

Thank you all (Caleb & Michael) very much for all your help and input.. Think my blood pressure will come down now too...

Re: Calculate Field Tool Does not Complete - Help

MichaelVolz — Wed, 10 Apr 2013 17:35:32 GMT

Most of the thanks goes to Caleb.

Although my script runs in a reasonable timeframe, I can use Caleb's code to make my script even better. Thank you very much Caleb.

Re: Calculate Field Tool Does not Complete - Help

Anonymous User — Sat, 11 Dec 2021 23:41:22 GMT

@ Chris:

I am glad you were able to get it to work. As for having it do more fields you just need to make a list of fields to updated in table A and a list of the fields from table B that contain the values. However, you must make sure to keep the fields in the correct order for each list. For example:

list_A = ['field_1', 'field_2', field_3', etc...]  # for table A
list_B = ['field_A', 'field_B', field_C', etc...]  # for table B

I have never tested this on more than 7 or so fields at a time but I am sure that it will work the same for 20.

Now I will try to explain the dictionary usage:

Most of my script is error handling, but the main meat of the script that is actually updating the table is this part:

                # Create Dictionary
                path_dict = {}
                srows = arcpy.SearchCursor(join_table)
                for srow in srows:
                    keyrow = srow.getValue(join_key)
                    valrow = srow.getValue(k)
                    path_dict[keyrow] = valrow
                
                # Update Cursor
                urows = arcpy.UpdateCursor(source_table)
                for row in urows:
                    upkey = row.getValue(in_field)
                    try:
                        if upkey in path_dict:
                            row.setValue(v, path_dict[upkey])
                            urows.updateRow(row)
                    except:
                        pass  # skip nulls

So a dictionary is just made up of pairs consisting of keys and values like this { key : value}. You can look up a value by supplying the key...Values can be any value (even objects and entire lists). The above portion uses a search cursor on table B (with the values) to make a dictionary using each record in the join field (your common field) as a key. The corresponding value will be one of the fields from the "join_fields" parameter. It creates a separate dictionary for each one.

Next, an update cursor is used to search through the table and if the value in the join_field in table A is a key in the dictionary, it will update the corresponding "update_field" for that row. I have an exception set up so that it will skip nulls when working through shapefiles. I hope this makes sense.

@ Michael

I had not tested the field calculator in arcpy land since I upgraded to 10.1. However, after a quick test it seems that it runs much more efficiently now than it did in the past. I have since gotten a new computer so maybe that is why it was so slow in the past. I am glad you find this useful. I have another function from the same script that copies fields from one table to another with the same schema so you can avoid the "field mapping" type operation like in the AttributeUpdate(). I can post that if anyone is interested.

Re: Calculate Field Tool Does not Complete - Help

ModernElectric — Sat, 11 Dec 2021 23:41:25 GMT

Caleb-

Yes - some of that makes sense (Use of Dictionaries)... I plan on doing some more research to see if I can use this with other
things/ideas with my other geodatabases. I work with electric and water utility infrastructures and been trying to develop some scripts/processes so I can integrate other parts of our company to GIS (CIS data, SCADA, TWACs, ETC).

Again..thank you very much.... This is going to cut down ALOT of time in updating my feature classes with tables from our CIS Oracle database

Chris

@ Chris:

I am glad you were able to get it to work. As for having it do more fields you just need to make a list of fields to updated in table A and a list of the fields from table B that contain the values. However, you must make sure to keep the fields in the correct order for each list. For example:
list_A = ['field_1', 'field_2', field_3', etc...]  # for table A
list_B = ['field_A', 'field_B', field_C', etc...]  # for table B
I have never tested this on more than 7 or so fields at a time but I am sure that it will work the same for 20.

Now I will try to explain the dictionary usage:

Most of my script is error handling, but the main meat of the script that is actually updating the table is this part:
                # Create Dictionary
                path_dict = {}
                srows = arcpy.SearchCursor(join_table)
                for srow in srows:
                    keyrow = srow.getValue(join_key)
                    valrow = srow.getValue(k)
                    path_dict[keyrow] = valrow
                
                # Update Cursor
                urows = arcpy.UpdateCursor(source_table)
                for row in urows:
                    upkey = row.getValue(in_field)
                    try:
                        if upkey in path_dict:
                            row.setValue(v, path_dict[upkey])
                            urows.updateRow(row)
                    except:
                        pass  # skip nulls
So a dictionary is just made up of pairs consisting of keys and values like this { key : value}. You can look up a value by supplying the key...Values can be any value (even objects and entire lists). The above portion uses a search cursor on table B (with the values) to make a dictionary using each record in the join field (your common field) as a key. The corresponding value will be one of the fields from the "join_fields" parameter. It creates a separate dictionary for each one.

Next, an update cursor is used to search through the table and if the value in the join_field in table A is a key in the dictionary, it will update the corresponding "update_field" for that row. I have an exception set up so that it will skip nulls when working through shapefiles. I hope this makes sense.

@ Michael

I had not tested the field calculator in arcpy land since I upgraded to 10.1. However, after a quick test it seems that it runs much more efficiently now than it did in the past. I have since gotten a new computer so maybe that is why it was so slow in the past. I am glad you find this useful. I have another function from the same script that copies fields from one table to another with the same schema so you can avoid the "field mapping" type operation like in the AttributeUpdate(). I can post that if anyone is interested.

Re: Calculate Field Tool Does not Complete - Help

MichaelVolz — Thu, 11 Apr 2013 15:37:48 GMT

Chris and Caleb:

After further research I think I have found out why my script which performs the same action on a much larger dataset runs quickly. I create an index on the field in the table that I am using to join to the feature class before I create the join. I took the model from another person who built the model in modelbuilder and I exported it to a python script where I made some tweaks (That is why I was unaware of the index previously).

As such Chris, you could also add a arcpy.AddIndex_management step to your process in the CIS table which should dramatically speed up your original script (Only if you can not get Caleb's more advanced script to work for you).