Calculate Field examples
Entering values with the keyboard is not the only way you can edit values in a table. In some cases, you might want to perform a mathematical calculation to set a field value for a single record or even all records. You can perform simple as well as advanced calculations on all or selected records. In addition, you can calculate area, length, perimeter, and other geometric properties on fields in attribute tables. The sections below include examples of using the field calculator. Calculations can be performed using either Python or VBScript.
Python is the recommended scripting language for ArcGIS. Use Python if you want access to geoprocessing functionality, including feature geometry. The adoption of Python as the scripting language for ArcGIS 10.0 provides many opportunities for performing calculations.
Use VBScript if you have VBA or VBScript experience and are comfortable with the scripting syntax. Saved .cal files from previous versions of ArcGIS may work or require minimal modifications. If you have VBA code from past releases that use ArcObjects, you will need to modify your calculations to work in 10.0.
- Python enforces indentation as part of the syntax. Use two or four spaces to define each logical level. Align the beginning and end of statement blocks, and be consistent.
- Python calculation expression fields are enclosed with exclamation points (!!).
- When naming variables, note that Python is case sensitive, so yield is not the same as Yield.
- VBScript does not allow you to explicitly declare any data types; all variables are implicitly Variant. Statements like Dim x as String should be removed or simplified to Dim x.
- After entering statements, you can click Save if you want to write them to a file. The Load button will prompt you to find and select an existing calculation file.
Simple calculations
Simple string examples
Strings are supported by a series of Python string functions, including capitalize, rstrip, and replace.
Capitalize the first character of the string in the field CITY_NAME.
!CITY_NAME!.capitalize()
Strip any white space from the end of the string in the field CITY_NAME.
!CITY_NAME!.rstrip()
Replace any occurrences of "california" with "California" found in the field STATE_NAME.
!STATE_NAME!.replace("california", "California")
Characters in a string field can be accessed by indexing and slicing in Python. Indexing fetches characters at an index position; slicing fetches a group of characters.
Example |
Explanation |
Result |
---|---|---|
!fieldname![0] |
The first character. |
"a" |
!fieldname![-2] |
The second-last character. |
"e" |
!fieldname![1:4] |
The second, third, fourth, and fifth characters. |
"bcd" |
Python also supports the string formatting using the % operator.
Combine FieldA and FieldB separated by a colon.
"%s:%s" % (!FieldA!, !FieldB!)
VBScript string functions
Strings are supported by a series of VBScript string functions, including Left, InStr, and Chrt. Below are some VBScript examples for commonly used string functions in the Field Calculator.
Left function: Return a Variant (String) containing a specified number of characters from the left side of a string.
MyStr = Left([MyField], 1)
Right function: Returns a Variant (String) containing a specified number of characters from the right side of a string.
MyStr = Right([MyField], 1)
Mid function: Returns a Variant (String) containing a specified number of characters from a string.
MyString = "Mid Function Demo" 'Create text string FirstWord = Mid(MyString, 1, 3) ' Returns "Mid" LastWord = Mid(MyString, 14, 4) 'Returns "Demo" MidWords = Mid(MyString, 5) 'Returns "Function Demo"
InStr function: Returns a Variant (Long) specifying the position of the first occurrence of one string within another.
MyPosition = InStr([address], " ")
Replace function: Returns a string in which a specified substring has been replaced with another substring a specified number of times.
NewString = Replace([comments], "#", "!")
Chr function: Returns a String containing the character associated with the specified character code.
' Replace a carriage return character with an exclamation NewString = Replace([comments], chr(13), "!")
& operator: Used to force string concatenation of two expressions.
MyStr = [MyField1] & " " & [MyField2]
Simple math examples
Python provides tools for processing numbers. Python also supports a number of numeric and mathematical functions, including math, cmath, decimal, random, itertools, functools, and operator.
Operator |
Explanation |
Example |
Result |
---|---|---|---|
x + y |
x plus y |
1.5 + 2.5 |
4.0 |
x - y |
x minus y |
3.3 - 2.2 |
1.1 |
x * y |
x times y |
2.0 * 2.2 |
4.4 |
x / y |
x divided by y |
4.0 / 1.25 |
3.2 |
x // y |
x divided by y (floor division) |
4.0 / 1.25 |
3.0 |
x % y |
x modulo y |
8 % 3 |
2 |
-x |
negative expression of x |
x = 5 -x |
-5 |
+x |
x is unchanged |
x = 5 +x |
5 |
x ** y |
x raised to the power of y |
2 ** 3 |
8 |
Multiplication
!Rank! * 2
Calculate volume of a sphere given a radius field.
4 / 3 * math.pi * !Radius! ** 3
Using code blocks
With Python expressions and the Code Block parameter, you can:
- Use any Python function in the expression.
- Access geoprocessing functions and objects.
- Access properties of feature geometry.
- Access the new random value operator.
- Reclassify values using if-then-else logic.
- Use other geoprocessing tools.
How the code block is used is determined by the parser used. The Field Calculator supports Python and VB Script parsers.
Parser |
Code Block |
---|---|
Python |
Supports Python functionality. The code block is expressed using Python functions (def). Geometry properties are expressed using geoprocessing objects such as Point objects where appropriate. |
VB Script |
Calculations are performed using VBScript. |
Python functions are defined using the def keyword followed by the name of the function and the function’s input parameters. Values are returned from the function using a return statement. The function name is your choice (don't use spaces or leading numbers).
Remember, Python enforces indentation as part of the syntax. Use two or four spaces to define each logical level. Align the beginning and end of statement blocks, and be consistent.
Code samples–math
Round a field's value to two decimal places.
Expression: round(!area!,2) Parser: Python
Use the math module to help convert meters to feet. The conversion is raised to the power of 2 and multiplied by the area.
Parser: Python Expression: MetersToFeet((float!shape.area)) Code Block: def MetersToFeet(area): return math.pow(3.2808,2) * area
Calculate fields using logic with Python
Classify based on field values.
Parser: Python Expression: Reclass(!WELL_YIELD!) Code Block: def Reclass(WellYield): if (WellYield >= 0 and WellYield <= 10): return 1 elif (WellYield > 10 and WellYield <= 20): return 2 elif (WellYield > 20 and WellYield <= 30): return 3 elif (WellYield > 30): return 4
Calculate fields using logic with VBScript
Conditionally executes a group of statements, depending on the value of an expression.
Parser: VB Script Expression: PopulationDensity = density Code Block: Dim density If [POP90_SQMI] < 100 Then density = "low" elseif [POP90_SQMI] < 300 Then density = "medium" else density = "high" end if
Code samples–geometry
For more on converting geometry units, see the section 'Geometry unit conversions' below.
Calculate the area of a feature.
Parser: Python Expression: !shape.area!
Calculate the maximum X-coordinate of a feature.
Parser: Python Expression: !shape.extent.XMax!
Calculate the vertex count of a feature.
Parser: Python Expression: MySub(!shape!) Code Block: def MySub(feat): partnum = 0 # Count the number of points in the current multipart feature partcount = feat.partCount pntcount = 0 # Enter while loop for each part in the feature (if a singlepart feature # this will occur only once) # while partnum < partcount: part = feat.getPart(partnum) pnt = part.next() # Enter while loop for each vertex # while pnt: pntcount += 1 pnt = part.next() # If pnt is null, either the part is finished or there is an # interior ring # if not pnt: pnt = part.next() partnum += 1 return pntcount
For a point feature class, shift the x coordinate of each point by 100.
Parser: Python Expression: shiftXCoordinate(!SHAPE!) Code Block: def shiftXCoordinate(shape): shiftValue = 100 point = shape.getPart(0) point.X += shiftValue return point
Geometry unit conversions
Shape and length properties of the geometry field can be modified with unit types expressed with an @ sign.
- Areal unit of measure keywords:
- ACRES | ARES | HECTARES | SQUARECENTIMETERS | SQUAREDECIMETERS | SQUAREINCHES | SQUAREFEET | SQUAREKILOMETERS | SQUAREMETERS | SQUAREMILES | SQUAREMILLIMETERS | SQUAREYARDS | SQUAREMAPUNITS | UNKNOWN
- Linear unit of measure keywords:
- CENTIMETERS | DECIMALDEGREES | DECIMETERS | FEET | INCHES | KILOMETERS | METERS | MILES | MILLIMETERS | NAUTICALMILES | POINTS | UNKNOWN | YARDS
If the data is stored in a geographic coordinate system and a linear unit (for example, feet) is supplied, the length calculation will be converted using a geodesic algorithm.
Converting the areal units on data in a geographic coordinate system will yield questionable results since decimal degrees are not consistent across the globe.
Calculate a feature's length in yards.
Parser: Python Expression: !shape.length@yards!
Calculate a feature's area in acres.
Parser: Python Expression: !shape.area@acres!
Code samples–dates
Calculate the current date.
Parser: Python Expression: time.strftime("%d/%m/%Y")
Calculate the current date and time.
Parser: Python Expression: time.strftime("%d/%m/%Y %H:%M")
Code samples–strings
Return the three right-most characters.
Parser: Python Expression: !SUB_REGION![-3:]
Replace any cases of an uppercase "P" with a lowercase "p".
Parser: Python Expression: !STATE_NAME!.replace("P","p")
Concatenate two fields with a space separator
Parser: Python Expression: !SUB_REGION! + " " + !STATE_ABBR!
Convert to proper case
The following examples show different ways to convert words so that each word has the first character capitalized and the rest of the letters in lowercase.
Parser: Python Expression: ' '.join([i.capitalize() for i in !STATE_NAME!.split(' ')])
Parser: Python Expression: string.capwords(!STATE_NAME!, ' ') Expression Type: import string
Parser: Python Expression: MySub(!STATE_NAME!) Code Block: def MySub(myfieldname): import string return string.capwords(myfieldname, ' ')
Accumulative and sequential calculations
Calculate a sequential ID or number based on an interval.
Parser: Python Expression: autoIncrement() Code Block: rec=0 def autoIncrement(): global rec pStart = 1 #adjust start value, if req'd pInterval = 1 #adjust interval value, if req'd if (rec == 0): rec = pStart else: rec = rec + pInterval return rec
Calculate the accumulative value of a numeric field.
Parser: Python Expression: accumulate(!FieldA!) Code Block: total = 0 def accumulate(increment): global total if total: total += increment else: total = increment return total
Calculate the percentage increase of a numeric field.
Parser: Python Expression: percentIncrease(float(!FieldA!)) Code Block: lastValue = 0 def percentIncrease(newValue): global lastValue if lastValue: percentage = ((newValue - lastValue) / lastValue) * 100 else: percentage = 0 lastValue = newValue return percentage
Random values
Use the numpy site-package to calculate random float values between 0.0 and 1.0.
Parser: Python Expression: getRandomValue() Code Block: import numpy.random as R def getRandomValue(): return R.random()