2

EDIT: What I forgot to mention when I first had this question is that I was trying to do these calculations on a layer which had joins, BUT where the join was on data that was added from an Excel file. ArcGIS does not seem to like data from Excel files.

I am trying to use the arcpy.CalculateField_management function and running into some trouble.

I have a shapefile called district_plans, and to this shapefile I have joined a table of data (the table of data is named kdp). I want to change a field in district_plans to the data in one of the columns of kdp.

Trying the following

arcpy.CalculateField_management("district_plans","district_plans.drawplan", "!kdp$.plan_4!","PYTHON")

yields the following error message

Runtime error  Traceback (most recent call last):   File "<string>", line 1, in
<module>   File "c:\program files x86)\arcgis\desktop10.2\arcpy\arcpy\management.py",
line 3183, in CalculateField     raise e ExecuteError: Failed to execute. Parameters 
are not valid. ERROR 000728: Field district_plans.drawplan does not exist within 
table Failed to execute (CalculateField). 

Trying the following

arcpy.CalculateField_management("district_plans","drawplan", "!kdp$.plan_4!","PYTHON")

yields the following error message

Runtime error  Traceback (most recent call last):   File "<string>", line 1, in 
<module>   File "c:\program files x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", 
line 3183, in CalculateField     raise e ExecuteError: ERROR 999999: Error executing 
function. Failed to execute (CalculateField). 

The following variations all yield errors:

arcpy.CalculateField_management("district_plans","district_plans.drawplan", "!plan_4!","PYTHON")

arcpy.CalculateField_management("district_plans","drawplan", "!plan_4!","PYTHON")

I am able to manually perform the field calculation using the field calculator on the drawplan column in the attribute table of district_plans, so I am not sure why the Python version does not work.

  • do you get an error with replacing "PYTHON" argument with "PYTHON_9.3" ? – mwil Mar 3 '15 at 21:41
  • @mwil Yes, same type of errors even if I replace "PYTHON" with "PYTHON_9.3". – derNincompoop Mar 3 '15 at 21:47
  • are you using this in python window? or as an standalone script? And try this: arcpy.CalculateField_management("district_plans","district_plans.drawplan", "!kdp$.plan_4!","PYTHON_9.3","#") – Farid Cheraghi Mar 3 '15 at 22:36
  • does your join table name contains $ sign? In your question you have said, "the table of data is named kdp" – Farid Cheraghi Mar 3 '15 at 22:38
  • 2
    Try to first export your excel sheet to dbf or geodatabase table and then create your map join, and then calculate. I'm pretty sure the problem is from the excel data source! – Farid Cheraghi Mar 3 '15 at 23:05
2

The issue was that the data joined to the district_plans layer came from an Excel file as pointed out by @Farid Cher and @Michael Miles-Stimson. Although I was not able to get the Table to Table function to work, I found the following code (located in this GitHub repo) which takes a Pandas dataframe and writes it out as a .dbf.

import pysal as ps
import numpy as np

def df2dbf(df, dbf_path, my_specs=None):
    '''
    Convert a pandas.DataFrame into a dbf.
    __author__  = "Dani Arribas-Bel <darribas@asu.edu> "
    ...
    Arguments
    ---------
    df          : DataFrame
                  Pandas dataframe object to be entirely written out to a dbf
    dbf_path    : str
                  Path to the output dbf. It is also returned by the function
    my_specs    : list
                  List with the field_specs to use for each column.
                  Defaults to None and applies the following scheme:
                    * int: ('N', 14, 0)
                    * float: ('N', 14, 14)
                    * str: ('C', 14, 0)
    '''
    if my_specs:
        specs = my_specs
    else:
        type2spec = {int: ('N', 20, 0),
                     np.int64: ('N', 20, 0),
                     float: ('N', 36, 15),
                     np.float64: ('N', 36, 15),
                     str: ('C', 14, 0)
        }
        types = [type(df[i].iloc[0]) for i in df.columns]
        specs = [type2spec[t] for t in types]
    db = ps.open(dbf_path, 'w')
    db.header = list(df.columns)
    db.field_spec = specs
    for i, row in df.T.iteritems():
        db.write(row)
    db.close()

    return dbf_path  

After joining the data contained in the .dbf file everything works fine.

0

Alternatively to the Table to Table tool, you could try using the Excel to Table tool. From my experience the Table to Table tool does not always convert the Microsoft Excel sheets properly, while the Excel to Table tool always did the job fine.

Edit: I just realized that this tool is only available for ArcGIS Desktop 10.2 and onwards.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.