topic Calculate Field Tool and Python in Python Questions

Calculate Field Tool and Python

Corey_C_Denninger — Tue, 12 Jul 2011 13:07:17 GMT

I'd think this one should be easy.
How do I use my input parameters, to automatically update my Calculate Field expression, within a Python script? I thought I had it, but I receieve an error.

In my script I would like portions of the expression in the Calculate Field line to change as my input parameters change. If I force the expression like this, it works:
"( !SUM_POP10! / !SUM_SUM_POP10! )*100", "PYTHON")

But I need both of the fields (either side of the "/" line) in that expression to change based upon one of my input paramters I choose, here:
popfield = gp.GetParameterAsText(2) #population field

Here is how I have it and where I beleive the Error is coming from; not sure what it doesn't like:

arcpy.CalculateField_management(outputfc, percentfield, '"('+str(popfield)+'/SUM_'+str(popfield)+')*100"', "PYTHON")

Again, as the "popfield =" paramter changes, so does the expression. So as I choose a new "SUM_POPxx" field, the expression automatically changes with it.

I'd appreciate any assistance. Thank you.

Re: Calculate Field Tool and Python

StacyRendall1 — Sat, 11 Dec 2021 18:01:25 GMT

arcpy.CalculateField_management(outputfc, percentfield, '"('+str(popfield)+'/SUM_'+str(popfield)+')*100"', "PYTHON")
Again, as the "popfield =" paramter changes, so does the expression. So as I choose a new "SUM_POPxx" field, the expression automatically changes with it.

you probably need to attach ! to either side of your popfield, i.e.:

popfield = gp.GetParameterAsText(2) #population field

popfield_new = '!'+popfield+'!'

arcpy.CalculateField_management(outputfc, percentfield, '"('+popfield_new+'/SUM_'+popfield_new+')*100"', "PYTHON")

Let me know how you get on...

Re: Calculate Field Tool and Python

KimOllivier — Wed, 20 Jul 2011 08:13:04 GMT

My suggestion is to use a cursor to calculate fields if you are in a Python script. Leave CalculateField for the modelbuilders!

Since CalculateField just runs a cursor, the speed will be the same.

A cursor can be much easier to program, and read.
There are no special characters to escape such as bangs!
It is much easier to debug.
You can trap 'unexpected data' such as nulls or divide by zero without a complete crash which you will get with CalculateField.
You can use a selection query in opening the cursor to limit the records more easily than having to use a layer definition.

Re: Calculate Field Tool and Python

Corey_C_Denninger — Wed, 20 Jul 2011 16:06:48 GMT

Thanks Kim. I'd agree with that, except like many GIS folk, my scripting needs some work. Since the script already exists in its current state, I'd rather use it that way.

Stacy - I added it just as you had it and no luck. Now I get an the following ERROR: "The value type is incompatible with the field type." Hmmm...

Re: Calculate Field Tool and Python

StacyRendall1 — Wed, 20 Jul 2011 21:42:48 GMT

OK. Not sure what is happening, but this is what debugging is all about. To find the problem you need to rigorously eliminate every possibility. Check everything is as you expect it, check more, and check again! Arcpy scripts seldom work straight away...

The message that it is producing leads me to think that you have made the percent field an incompatible type with the data that is being calculated. I.e. percentfield is a short integer but the output is a float. If that doesn't help, try the steps outlined below (if it does, look through them anyway, might help you next time there is an issue).

Essentially, the '"('+popfield_new+'/SUM_'+popfield_new+')*100"' part of your calculate field statement should look exactly like it does when you force the statement.

You can check if your new popfield is looking right with an:

arcpy.AddMessage("Pop. Field:"+popfield_new)

placed just before the Calculate Field statement, so it should print !SUM_POP10!, if SUM_POP10 is your input.

If that looks OK, replace the AddMessage with this one:

arcpy.AddMessage('"('+popfield_new+'/SUM_'+popfield_new+')*100"')

which should give you "( !SUM_POP10! / !SUM_SUM_POP10! )*100" if SUM_POP10 is your input.

If that looks OK, but it still fails, do the calculate field in Arc, making sure to use the same fields you are passing in the script.

Re: Calculate Field Tool and Python

Corey_C_Denninger — Thu, 21 Jul 2011 11:13:38 GMT

Stacy - Thanks for the info. I will try all that. Just a note on the ERROR message, I thought the same thing you did about wrong field type, but it is the correct type and works fine when I just hard code the script....it populates the field with the expected values...so that ERROR msg is strange and perhaps plain wrong. I'll let ya know how it all goes. Thanks again.

Re: Calculate Field Tool and Python

markdenil — Thu, 21 Jul 2011 16:10:17 GMT

First off: '"('+popfield_new+'/SUM_'+popfield_new+')*100"'
has nested single quotes that will confuse matters. You need to escape the double quotes so they don't form a pair, and thus won't nest the inner single quotes.

Next, it would seem to me that with a number of fields names like SUM_POPxx,
the xx is the only real variable.

for each in [10, 20, 30]:
    firstField = "SUM_POP" + str(each)
    nextField = "SUM_" + firstField
    calcString = '\"(!' + firstField + '!/!' + nextField + '!)*100\"'
    print calcString
    arcpy.CalculateField_management(outputfc, percentfield, calcString, "PYTHON")

M.Denil

Re: Calculate Field Tool and Python

Corey_C_Denninger — Thu, 21 Jul 2011 19:00:05 GMT

Thanks Denil. All of these solutions seem to work in one way or another. What I am experiencing each time now is that error msg that implies my field value type is wrong or incompatiable. This is just not the case. I am not dividing by any zeros, but there are <Null> values and perhaps that is the issue (although it shouldn't be).
Again, if I open the feature class table manually and use Field Calculator to do the formula, it works just fine, no isses...but in the script....ERROR.

ERROR MSG: "<class 'arcgisscripting.ExecuteError'>: ERROR 999999: Error executing function.
The value type is incompatible with the field type. [PER_POP_WUCA4_10]
Failed to execute (CalculateField).

Failed to execute (CalcPercent)."

Any ideas?

Re: Calculate Field Tool and Python

StacyRendall1 — Sat, 11 Dec 2021 18:01:28 GMT

Yo; found the problem. You have so many quotes in your calculation, they are actually messing it up, and I think the thing being returned is being converted into a string - hence the error, as you are adding it to a number field.

What you need to do is get rid of the overall quotes. Using the method that @mdenil showed, where calcString = something is specified beforehand then printed, you were initially printing:

calcString = "(!SUM_POP10!/!SUM_SUM_POP10!)*100" (itself a string)

but you actually just want:

calcString = (!SUM_POP10!/!SUM_SUM_POP10!)*100 (it is still a string, just not wrapped in excessive quotes)

So, the code from my first post now becomes:

popfield = arcpy.GetParameterAsText(2) #population field

popfield_new = '!'+popfield+'!'

arcpy.CalculateField_management(outputfc, percentfield, '('+popfield_new+'/SUM_'+popfield_new+')*100', "PYTHON")

I also just noticed that at one stage in your original post you are using gp, and another you are using arcpy. I don't really understand all the layers of arccrap, but it might be worth trying to get rid of anything referring to gp, and just using arcpy. So, arcpy.GetParameterAsText(), arcpy.AddMessage(), etc.

Let me know how it goes...

Re: Calculate Field Tool and Python

Corey_C_Denninger — Mon, 25 Jul 2011 12:15:52 GMT

You're a riot Stacy!!! 😄 Thanks for the assistance.

We are soooo close. I've been trying to modify the paramter and/or the formula to make it work, but I am clearly missing something. See ERROR:

"<class 'arcgisscripting.ExecuteError'>: ERROR 000539: Error running expression: (44266.1586547927/SUM_44266.1586547927)*100 <type 'exceptions.SyntaxError'>: invalid syntax (<string>, line 1)
Failed to execute (CalculateField)."

It is obviously including the word "SUM_" into the math, and there fore not only trying to combine and divide string with numbers, but still using the first field to do that math for the denominator, which is obviously incorrect: 44266.1586547927/SUM_44266.1586547927)*100

Re: Calculate Field Tool and Python

StacyRendall1 — Sat, 11 Dec 2021 18:01:30 GMT

oops... That is to do with where the ! is placed - my bad!

Either:

popfield = arcpy.GetParameterAsText(2) #population field

arcpy.CalculateField_management(outputfc, percentfield, '(!'+popfield+'!/!SUM_'+popfield+'!)*100', "PYTHON")

Or:

popfield = arcpy.GetParameterAsText(2) #population field

popfield_new = '!'+popfield+'!'
sum_popfield_new = '!SUM_'+popfield+'!'

arcpy.CalculateField_management(outputfc, percentfield, '('+popfield_new+'/'+sum_popfield_new+')*100', "PYTHON")

I would say that the second one is better - the first kind of muddies what is going on in the calculation, while in the second your field references are absolutely explicit...

Cheers!

Re: Calculate Field Tool and Python

Corey_C_Denninger — Sat, 11 Dec 2021 18:01:33 GMT

Stacy - Thank you so much for your patience and assistance. Works great. I'm sure I do not have the code as efficient as it could be, but it works great and I learned some more about Python along the way. At this rate, I'll be a decent programmer by 2020. 😉 I'll keep checking the Python, GIS, and stuff blog for more helpful tips.

Here is how I have the code and it makes sense to me

import arcpy, os
gp = arcpy

arcpy.env.overwriteOutput = True

# Parameters
inputfc = arcpy.GetParameterAsText(0) #Input fc location and name
statstable = r'd:\My Documents\ArcGIS\Default.gdb\STATS' #Stats Table (temporary) output name (don't attempt to put into a feature dataset)
popfield = arcpy.GetParameterAsText(1) #population field
tablename = os.path.basename(statstable) #temporary table
layername = "templayer"
casefield = arcpy.GetParameterAsText(2) #CWM, or BASINNAME, or COUNTYNAME, WUCANAME_n, etc.,
percentfield = arcpy.GetParameterAsText(3) #Percent of Basin, or Percent of CWM, Percent of County, PER_POP_WUCA1_15, etc.
outputfc = arcpy.GetParameterAsText(4) #Output fc location and name

#########################################################################################################################

arcpy.MakeFeatureLayer_management(inputfc, layername)

arcpy.Statistics_analysis(layername, statstable, [[popfield, "SUM"]], casefield)
arcpy.AddJoin_management(layername, casefield, statstable, casefield)
arcpy.CopyFeatures_management(layername, outputfc)
arcpy.RemoveJoin_management(layername, tablename)

arcpy.AddField_management(outputfc, percentfield, "DOUBLE","","","","","")

arcpy.CalculateField_management(outputfc, percentfield, '(!'+popfield+'!/!SUM_'+popfield+'!)*100', "PYTHON")

arcpy.DeleteField_management(outputfc,['SUM_'+popfield,"FREQUENCY","OBJECTID_1",str(casefield)+"_1"])

One final question, what do the '!' do in Python and specifically in this script?

Re: Calculate Field Tool and Python

ChristopherFricke1 — Tue, 26 Jul 2011 12:45:47 GMT

Exclamation marks in the Calculate Field calculation demarcate Fields in the table you are working with.

Ex. !FIPS! = the value in the fips field

In VB it used to be the square bracket. This is just one of those annoying things that you have to remember.
Another way to write this that might be easier is: "(!%s!/!SUM_%s!)*100" % (popfield, popfield)

This uses python's string formatting structure. I find it is a bit easier to read than having all of the plus signs and stuff.

code example:

arcpy.CalculateField_management(outputfc, percentfield, "(!%s!/!SUM_%s!)*100" % (popfield, popfield), "PYTHON")

Re: Calculate Field Tool and Python

StacyRendall1 — Sun, 12 Dec 2021 16:25:23 GMT

Corey, no worries.

As Christoph said, the exclamation marks let Arc know that it is a field you are referencing within the string (the string telling it what to calculate), not a variable or something. So, it isn't actually anything to do with Python! I guess they made it different from VB, as square brackets might cause confusion with Python lists.

With these Arc codes I tend to flip-flop between using Python string formatting (%s and stuff) and adding strings together, depending on what is most readable in each particular instance...

Your code itself is currently about as efficient as it could be. If you wanted to improve it from here on in (I don't know in this case if there is much improvement possible, it might depend on the size of your datasets as to whether it is worth doing anything), you need to start thinking about what the code is actually doing, and if possible make the procedure more efficient. Some of the things that Arcpy lets you do, like cursors, can make it easier or more efficient to do certain things. First off, you need to start out with a pseudo-code description of what you want to do (preferably before you do anything else, but it never seems to actually happen that way). This is a low-level pseudo-code of your method:

import things

get parameters from Arc

statstable: sum layer popfield for each casefield

layer: join to statstable

layer: copy to output

layer: remove join

output: add field

output: calculate field - percent of sum within each case

output: delete sum field

At a higher level:

import things

get parameters from Arc

copy input features to output

output: add field

output: calculate field - percent of sum within each case

Now we have outlined the procedure, we can think about if it is possible to improve anything. You have to almost remove yourself from thinking about which Arc tool will do the job, and instead find the simplest(/most efficient) way to do something. For example, in one code that I had developed from a Model Builder model I was using a bunch of Select by Attriubute queries, doing a calculation and writing the result back. That was the only way I could think of to do it within Arc/Model Builder. The code took minutes to run on my small test dataset. Then I realised that I didn't have to be constrained by the way it has to be done in Arc; so I got rid of all the Select By Attributes, instead using a Search Cursor to extract the data to a Python dictionary, then perform the calculation on that; afterwards it took about 7 seconds.

However, in this case, I don't think you could get time savings from making any changes, but you could Python-ise it for the sake of learning a bit more... If you are interested, have a go doing something like this (you will need to do some research into Pyhton dictionaries and Cursors...):

import things

get parameters from Arc

copy input features to output

use Search Cursor to iterate over input rows
 Python dictionary: accumulate popfield value, using case field as key
 # i.e try: popDict[inputRow.GetValue(casefield)] += inputRow.GetValue(popfield)
 #     except KeyError: popDict[inputRow.GetValue(casefield)] = inputRow.GetValue(popfield) # first time it comes across each case field, it doesn't exist as a key, so just add it

output: add field

use update curosr to iterate over output rows
 get the pop_casefield_sum sum value from the dictionary
 get the popfield value from the output row
 update the field value for the row to (popfield/pop_casefield_sum)*100

Just yell out if you decide to try this and need any help!

p.s. You can now delete the 'gp = arcpy' on line 2 of your current code...

Re: Calculate Field Tool and Python

Corey_C_Denninger — Wed, 27 Jul 2011 12:42:08 GMT

Thank you very much Stacy!

Re: Calculate Field Tool and Python

EdwardBriggler — Sat, 11 Dec 2021 18:01:35 GMT

This was a BIG help. I was stuck on the concatenating part of my script too!

 
# Author: Edward Briggler
# Date: 1/4/2012
# Purpose: To add the 7 phase fields to a feature class and calculate based on selecting by subtype.
# Bring in Arc and Sys. 
import arcgisscripting, sys
# Create the Geoprocessor...
gp = arcgisscripting.create()
gp.Workspace = gp.GetParameterAsText(0)
fc = gp.GetParameterAsText(1)
f = gp.GetParameterAsText(2)
# concatenate the bangs to let the equations know it is a field
new_f = '!'+f+'!'
# only held in memory
tv = "table_view_memory"
# expressions to evaluate the length of Primary Line (in the length of specified field)
## Involves concatenating the *2 and *3 to get the expression right. HAS TO BE A STRING!!!
exp = new_f
exp2 = new_f+'*2'
exp3 = new_f+'*3'
# Turn Feature Class into a layer...can only do the following geoprocesses while a layer or table view.
gp.MakeFeatureLayer_management(fc, tv)
# Add Fields to Layer
gp.addfield_management(tv, "A_PHASE", "FLOAT")
gp.addfield_management(tv, "B_PHASE", "FLOAT")
gp.addfield_management(tv, "C_PHASE", "FLOAT")
gp.addfield_management(tv, "AB_PHASE", "FLOAT")
gp.addfield_management(tv, "AC_PHASE", "FLOAT")
gp.addfield_management(tv, "BC_PHASE", "FLOAT")
gp.addfield_management(tv, "ABC_PHASE", "FLOAT")
# Select by SUBTYPECD and run each expression accordingly
##gp.MakeTableView_management(fc, tv)
gp.SelectLayerByAttribute_management(tv,"NEW_SELECTION", "SUBTYPECD = 1")
gp.CalculateField_management(tv, "A_PHASE",exp , "PYTHON" )
gp.SelectLayerByAttribute_management(tv,"NEW_SELECTION", "SUBTYPECD = 2")
gp.CalculateField_management(tv, "B_PHASE",exp , "PYTHON" )
gp.SelectLayerByAttribute_management(tv,"NEW_SELECTION", "SUBTYPECD = 3")
gp.CalculateField_management(tv, "C_PHASE",exp , "PYTHON" )
gp.SelectLayerByAttribute_management(tv, "NEW_SELECTION", "SUBTYPECD = 4")
gp.CalculateField_management(tv, "AB_PHASE",exp2 , "PYTHON" )
gp.SelectLayerByAttribute_management(tv,"NEW_SELECTION", "SUBTYPECD = 5")
gp.CalculateField_management(tv, "AC_PHASE",exp2 , "PYTHON" )
gp.SelectLayerByAttribute_management(tv,"NEW_SELECTION", "SUBTYPECD = 6")
gp.CalculateField_management(tv, "BC_PHASE",exp2 , "PYTHON" )
gp.SelectLayerByAttribute_management(tv, "NEW_SELECTION", "SUBTYPECD = 7")
gp.CalculateField_management(tv, "ABC_PHASE",exp3 , "PYTHON" )

Re: Calculate Field Tool and Python

AlisonMontgomery — Sat, 11 Dec 2021 18:01:38 GMT

Hello,

This was such a helpful script to find, and it worked...sort of.

I tried several Calculate Field combinations initially, and they all ran in Python (Python WIN to be exact) just fine, but wouldn't run in ArcMap.

After finding this code, it ran in Python, it ran in ArcCatalog, and then ran in ArcMap. But after one run in ArcMap, it's stopped working. It's fine in ArcCatalog still. I've posted below. Maybe I've missed something in the code.

It gives the error 000539 and says that my CountField is invalid. I know it isn't, as it is an integer, and the fact that it has run perfectly fine everywhere else. I also know it's not the GME software, which closes out prior to arcpy being used, and again, since it has run before.

Any ideas would be helpful.

#Alison Montgomery - Spatial Point Allocation Script
#Created: 06.13.2013
#For use with ArcGIS 10.x, Python Version 2.7.2

import arcpy, sys, os, subprocess
from arcpy import env

#Supply the following arguments prior to running:
#Workspace (full path using backslashes)
#Catchment Polygons (full path, backslashes)
#Point Data (full path using backslahes)
#Count Field for the output: <15 characters to denote the point dataset.
#An output txt file (full path, must be backslashes -> eg. C:\Users\Alison\Desktop\filename.txt)
#Density Field for output: <8 characters
#Area Field from Shapefile
########
#Each argument must be in double quotes, and they must be separated by a space.

#Input fields that will be written into text file for GME processing.
inputPoly = arcpy.GetParameterAsText(0)
inputPoint = arcpy.GetParameterAsText(1)
Field = arcpy.GetParameterAsText(2)

code = 'countpntsinpolys(poly="' + inputPoly + '", pnt="' + inputPoint + '", field="' + Field + '");'

#Input a text file name, write above code to text file.
newFile = arcpy.GetParameterAsText(3)
newFileObj = open(newFile, 'w')
newFileObj.write(code)
newFileObj.close()

#Write textfile name into code line readable by GME call.
lineCall = "run(in=\\\""'' + newFile + ''"\\\")"

#Call GME, execute text file code, "-c" closes GME after processing.
os.system(r'C:\Program Files (x86)\SpatialEcology\GME\SEGME.exe')
subprocess.call(r'C:\Program Files (x86)\SpatialEcology\GME\SEGME.exe -c ' + lineCall + ';');

#Input name for Density field, and input the shapefile's area field.
DensityField = arcpy.GetParameterAsText(4)
CountField = Field
area = arcpy.GetParameterAsText(5)

#Add the new density field to shapefile.
arcpy.AddField_management(inputPoly, DensityField, "FLOAT", "10", "4", "", "", "NULLABLE", "NON_REQUIRED", "")

#Calculate density field using count of points, divided by the area field.
arcpy.CalculateField_management(inputPoly, DensityField, '(!'+CountField+'!/!'+area+'!)', "PYTHON_9.3", "")