# Using ArcMap Field Calculator to set Row 1 (Column D)= Row2 (Column B)?

This is probably a fairly basic question but I have a long list of latitude and longitude values that represent telemetry points that will be the start point of the line. Before I convert them to line, I need to add a column for the ending latitude and longitude values of the line. This will be equal to the lat longs of the next point. Each point is represented by a column "uniqueID" This is one example of what I need done but there are several other instances in the database. The example is shown in excel and is easily calculable with an "=B2" expression in excel but I need a formula for it in ModelBuilder. • Do you use Python, or would you like to stay within the field calculator? – Aaron Dec 15 '14 at 19:13
• You won't be able to use Field Calculator for such an operation - it works with single records and cannot access more at the same time. Can you elaborate on why you need to do this, as in what method are you using that requires you have both start and end as attributes of a single record? The Points to Line tool can take just a list of points and create a line out of them sequentially based on a sort field. It sounds like you already have them in the correct order, so uniqueID could be used as a sort field. – Chris W Dec 15 '14 at 19:19
• I have very little python experience, so my preference is Field Calculator, or even SQL if possible. I use Xy to Line because this starts in table format but I guess could just as easily create points from the start. There are other instances where i need to access other rows for calculations though. For instance I calculate the difference in distance from origin from point A to Point B and change in speed from segment to segment. – S. Burke Dec 15 '14 at 19:45

I agree with Chris that you don't need to do this calculation to create a line from the points. It is impossible for the Field Calculator to directly read ahead one row and write to the row behind. It could read a row before and write to the next line with a Global variable, but the other way around is impossible. The Field Calculator cannot sort a table and must read the table from beginning to end in order every time.

As long as your records are sorted and the UniqueIDs are consecutive you can copy the table (making sure the UniqueIDs did not change) and add a long field name something like RowAhead to the original and calculate it to be equal to: UniqueID + 1

After the RowAhead field is created and calculated you can create a join with the original table being the target table using its RowAhead field and the copy being the join table using its UniqueID field. Then you can calculate the fields of the record ahead to the record behind, because they will be joined. You may have to select all records with a matching joined record if this is a shapefile or dbf table to avoid an error for attempting to assign a Null value to the last record.

A python cursor and dictionary could also do this exercise in a standalone Python script without physically duplicating the table if the table was first stored in the dictionary using the UniqueID as the key, and then an UpdateCursor could access the key of the record ahead using its current UniqueID+1 as the dictionary key. A conditional check would have to validate that the key was in the dictionary and skip over writing to the last record (which has no record ahead). For a very large record set this technique is much faster than the field calculator and a join when data access cursors are used (on the order of 10 to 50 times faster), but unless you were dealing with a large number of point sets that would create many lines, most of your data sets would be too small to detect a significant performance difference. But if you deal with a lot of tables with over 10,000 records and many fields to calculate, the Python approach is well worth learning, since it can save you hours of waiting on calculations to complete.

Edit: Since you want to work on a selection that is not consecutive, but that is sorted in order, you can use the Field Calculator to calculate a Long field named RowBehind and reverse the join order so that you can use the ObjectID as the Target table field and the RowBehind as the Join table field. It reverses the RowAhead operation. Here is the calculation to create RowBehind for any selected set of records:

Parser: Python

Show Codeblock: Checked

Pre-Logic Script Code:

``````RowOID = -1
def RowBehind(OID):
global RowOID
lastOID = RowOID
RowOID = OID
return lastOID
``````

RowBehind =: RowBehind( !UniqueID!)

Combined with a standalone Python script or Python Script tool that used a dictionary and cursor so you would not have to create this field or create a new table. You would use the lastOID variable to store -1 to begin, substitute that value as the key for the current record being read and store all the field values of the current record into a list in the dictionary, and then put the current record OID value from the cursor row into the lastOID variable so that it is ready for the next record. When you use the updateCursor the current ObjectId value will access the next record's field data when it is used as the dictionary key. You can write both fields in a single pass of the Update cursor.

So do this:

``````from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\UpdateFeatureClass"

sourceFieldsList = ["UniqueID", "ColumnA", "ColumnB"]

lastOID = -1
# Build a summary dictionary from a da SearchCursor with unique key values of a field storing a list of the sum of that value and the record count.
valueDict = {}
with arcpy.da.SearchCursor(sourceFC, sourceFieldsList) as searchRows:
for searchRow in searchRows:
keyValue = lastOID
lastOID = searchRow
tempList = []
if not keyValue in valueDict:
# Add value list under the keyValue for the dictionary.
for n in range (1,len(sourceFieldsList)):
tempList.append(searchRow[n])
valueDict[keyValue] = tempList

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["UniqueID", "ColumnC", "ColumnD"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
for updateRow in updateRows:
# store the Join value of the row being updated in a keyValue variable
keyValue = updateRow
# verify that the keyValue is in the Dictionary
if keyValue in valueDict:
# transfer the values stored under the keyValue from the dictionary to the updated fields.
for n in range (1,len(updateFieldsList)):
updateRow[n] = valueDict[keyValue][n-1]
updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")
``````
• The Unique ID + 1 idea is awesome, but will not work for all of my data. I have several filters that will eliminate points from the dataset. I had not intended to recalculate the unique ID based on the removed segments. – S. Burke Dec 15 '14 at 20:12
• See my edit for reversing the join so that the current row ObjectID can be joined ahead to a RowBehind field in the next record, that will respect a record selection that is in ascending order, but not necessarily consecutively numbered. – Richard Fairhurst Dec 15 '14 at 20:51
• I am getting the following error: File "C:/Users/sean/Desktop/Python/Update.py", line 21, in <module> valueDict[keyValue][n-1] = searchRow[n] KeyError: -1 – S. Burke Dec 16 '14 at 17:49
• I have corrected the error in the code. I forgot to build the list and make it the value under the dictionary key directly. – Richard Fairhurst Dec 16 '14 at 18:55
• Jackpot!! This worked liked a champ and the operation was performed in under a second. Thank you Richard. – S. Burke Dec 16 '14 at 19:37

You cannot do this with a simple Field calculation as it requires accessing more than one record in a single calculation which the Field Calculator cannot do. You'd need a different method. One would be the Points to Line tool I mention in my comment.

However to actually do what you're wanting, one possible method would be to make a copy of your table, field calculate uniqueID -1, then use Join Field based on uniqueID and map the 'begin' fields to 'end' fields in the original table (you could also handle this with creating two new fields in the copy table with the correct names and field calculating the values over from the begin fields, then only adding the new, correctly named end fields with Join Field). Since the uniqueIDs are shifted down one, they'll join to the previous points.

• exactly what I was going to suggest. – radouxju Dec 15 '14 at 19:35

If by previous point you mean the previous feature in the table, here's what you do: Open your field calculator and choose Python as your Parser. Click 'Show Codeblock'. In your code block input the following code:

``````def updateLast (input):
try:
valreturn = val
val = input
return valreturn
except:
global val
val = input
``````

``````updateLast(!{fieldname}!)
``````

...where {fieldname} is the name of the field you wish to use as your input. In your picture example you use the next record and not the previous, so if this is the case this won't work.

If you are after the values from the next point things get a bit more complicated. Use the following as your code block:

``````def updateNext (lyr, fld, OID):
try:
itemCount += 1
except:
global itemCount
global rowCount
global OIDFld
itemCount = 1
rowCount = int(arcpy.arcpy.GetCount_management(lyr).getOutput(0))
OIDFld = arcpy.Describe(lyr).OIDFieldName
check = False
try:
sql = '"' + OIDFld + '" > ' + lastOID
cursor = arcpy.da.SearchCursor (lyr, [fld, "OID@"], sql_clause = sql)
except:
global lastOID
cursor = arcpy.da.SearchCursor (lyr, [fld, "OID@"])
for row in cursor:
if check == True:
return row
if row == OID:
lastOID = row
check = True
if itemCount == rowCount:
del itemCount
del lastOID
del rowCount
del OIDFld
del row
del cursor
``````

and the following as your calculate:

``````updateNext ("{layername}", "{fieldname}", !{OIDfld}!)
``````

...where {layername} is the name of the layer in your table of contents, {fieldname} is the name of the field you wish to use as your input, and {OIDfld} is the layer's ObjectID field.

• Thanks this almost gets me what I needed. What I was asking, and (I am sorry if it was unclear) is to set the values of (Row 1 Column "C" and "D"= Row 2 Column "A" and "B"). The return of your formula was (Row 2 Column "C" and "D" = Row 1 Column "A" and "B") – S. Burke Dec 15 '14 at 20:40
• I edited the wording of my original post and found an error in my wording. Sorry for that. Is there a method of calculating it to the value of the next point? – S. Burke Dec 15 '14 at 21:30
• After several tests, both my codes seem to be working without issue. Requires 10.1 + for the type of cursor I use. Let me know if you are using a previous version – Emil Brundage Dec 15 '14 at 23:31
• I do not recommend using a cursor in a field calculation ever. It repeats the entire cursor loading and reading for each record calculated, which is crazy inefficient. Embedded cursors like that are never necessary if you are willing to abandon the Field Calculator and make a real python script (which the second calculation is 90% anyway). Check my standalone script which accomplishes the same thing with only two cursor load and reads. For every additional record the table has my script will increase in speed over this calculation on more or less an exponential growth curve. – Richard Fairhurst Dec 16 '14 at 15:31
• I agree that pure Python is the way to go for this (along with 90% of your GIS needs). – Emil Brundage Dec 16 '14 at 15:39

Along the same lines as Emil's answer, here is a field calculator solution that does "read ahead," through a SearchCursor, matching results in a whereclause, where the current row is matched with the row containing FID + 1 (you will have to be aware whether your data uses FID, OID, OBJECTID or other).

Expression:

``````future( "h2o", "Shape_Leng", "FID", !FID! )
``````

where h2o is the layer name, Shape_Leng is the field containing the values to copy over, and FID is the name of the unique identifier field (change both "FID"s if your field name differs).

Code Block:

``````def future(lyr, field, FIDfield, currentFID):
with arcpy.da.SearchCursor(lyr, (field), FIDfield + """ = """ + str(currentFID+1) ) as cursor:
for row in cursor:
return row
``````
• I would never use a cursor within a Field Calculation except on extremely small record sets. Beyond 50 records or so a standalone Python script is the best option to avoid embedded cursors. Code that avoids an embedded cursor using a dictionary similar to what I wrote in my post can process over 3/4 million records in under 5 minutes. This Field Calculation would be lucky to complete in 5 days on the same record set. – Richard Fairhurst Dec 16 '14 at 15:43
• I know you wouldn't, Richard :) I'm just pointing out that it is possible to read ahead with the field calculator. – phloem Dec 16 '14 at 17:31

Thanks everyone for their help. Being a complete novice at scripting I learned a bit from this. Although I got several answers that did the trick, this was the most efficient answer.

``````from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:FeaturePath"

sourceFieldsList = ["UniqueID", "Column A", "Column B"]

lastOID = -1
# Build a summary dictionary from a da SearchCursor with unique key values of a field storing a list of the sum of that value and the record count.
valueDict = {}
with arcpy.da.SearchCursor(sourceFC, sourceFieldsList) as searchRows:
for searchRow in searchRows:
keyValue = lastOID
lastOID = searchRow
tempList = []
if not keyValue in valueDict:
# Add value list under the keyValue for the dictionary.
for n in range (1,len(sourceFieldsList)):
tempList.append(searchRow[n])
valueDict[keyValue] = tempList

updateFC = r"C:FeaturePath"

updateFieldsList = ["UniqueID", "Column C", "Column D"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
for updateRow in updateRows:
# store the Join value of the row being updated in a keyValue variable
keyValue = updateRow
# verify that the keyValue is in the Dictionary
if keyValue in valueDict:
# transfer the values stored under the keyValue from the dictionary to the updated fields.
for n in range (1,len(sourceFieldsList)):
updateRow[n] = valueDict[keyValue][n-1]
updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%
``````

It took 12 secs on my machine to create 3 rows high table from row neighbours and do some calcs for 10000 records

``````def Func(fid):
mxd = arcpy.mapping.MapDocument("CURRENT")
lyr=arcpy.mapping.ListLayers(mxd,"POINTS")
q='"FID" in %s' %str((fid-1,fid,fid+1))
threeRows=arcpy.da.TableToNumPyArray(lyr,("CID","TOTAL"),q)
if len(threeRows)==2:return -999
return threeRows-threeRows

Func (!FID!)
``````