# How to access adjacent rows with cursor?

In the attached screenshot, the attributes contain two field of interest "a" and "b". I want to write a script to access the adjacent rows in order to make some calculations. To access a single row, I would use the following UpdateCursor:

fc = r'C:\path\to\fc'

with arcpy.da.UpdateCursor(fc, ["a", "b"]) as cursor:
for row in cursor:
# Do something


For example, with OBJECTID 4, I am interested in calculating the sum of the row values in field "a" adjacent to OBJECTID 4 row (i.e. 1 + 3) and adding that value to the OBJECTID 4 row in the "b" field. How can I access adjacent rows with the cursor to make these sort of calculations?

-

If this was me I would pass through the table once creating a dictionary where key is OBJECTID and item is the value in field "a". I would then then step through the table with an update cursor getting the OBJECTID and from that I could get the adjacent values from the dictionary, sum them and write them back to field "b".

But in your screenshot you have special cases for rows 3 and 8 as they only have one adjacent row. What would you intend for these?

-
Thanks, dictionaries are the way to go. –  Aaron Jul 3 at 16:34

While looping over the rows you need to keep track of previous values. This is one way to do it:

 previous_value = None
cursor = arcpy.UpdateCursor(fc, fields, sort_fields)
for i, in_row in enumerate(cursor):
current_value = in_row.getValue("ColName")
if not previous_value:
previous_value = current_value
continue
#
#

previous_value = current_value


or, if the table is not huge, I would probably build a dictionary, like d= {a: b} and then in the update cursor access data from the dictionary: d.get(a+1) or d.get(a-1) to do the math..

-

The Data Access module is quite fast and you could create a SearchCursor to save all vales from 'a' into a list then create an UpdateCursor to iterate through each row and select from the list to update the necessary 'b' rows. This way you don't need to worry about saving data between rows =)

So something like this:

fc = r'C:\path\to\fc'
fields = ["a","b"]
aList = []
index = 0

with arcpy.da.SearchCursor(fc,fields) as cursor:
for row in cursor:
aList.append(row[0])    # This saves each value of 'a'
with arcpy.da.UpdateCursor(fc,fields) as cursor:
for row in cursor:
if index-1 > 0 AND index+1 <= len(aList):     # Keeps 'b' null if out of list range
# or at end of list
row[1] = aList[index-1]+aList[index+1]
index += 1


This is a fairly crude solution but I just used it recently to get around a very similar problem. If the code doesn't work hopefully it sets you on the right track!

Edit: Changed last if statement from AND to OR Edit2: Changed back. Ahh the pressure from my first StackExchange post!

-
Instead of editing my answer another time I'll give my \$0.02 as to why I think this is a good solution to the problem. Iterating through rows makes it really easy to iterate through lists as well with index += 1. Although I didn't give a lot of thought to the mechanics of the code, it should convey the point I wanted to make to you. Good luck! –  Roman Jul 3 at 15:20
A few notes: 1) It can be faster for large datasets if you use a list comprehension to fill aList instead of appending each entry. 2) Use enumerate() instead of having a separate counter for index. –  Paul Jul 3 at 15:38

I have accepted @Hornbydd 's answer for leading me toward a dictionary solution. The attached script performs the following actions:

1. Loop through a FC and populate a dictionary with key = OID and value = "MyField" using a SearchCursor.
2. Start an UpdateCursor
3. Create logic to handle first and last rows (i.e. where there is no previous or consecutive row)
4. Link UpdateCursor row to dictionary OID and Field value
5. Do the processing...

import arcpy, collections

fc = r'C:\path\to\fc'

# Populate a dictionary with key = OID and value = "a"
names = collections.defaultdict(list)

for name1, name2 in arcpy.da.SearchCursor(fc, ("OBJECTID", "a")):
names[name1].append(name2)

# Use .values() class to access adjacent rows

with arcpy.da.UpdateCursor(fc, ["OBJECTID", "a", "b"]) as cursor:
for row in cursor:
# Get first and last rows for special processing
if row[0] == names.keys()[0] or row[0] == names.keys()[-1]:
"""Note that the first and last row will need special rules because
they cannot reference either the previous or next row.  In this case
the features are skipped"""
pass

else:
"""This needs to be corrected because OID = (row[0] - 1)"""
# Now link the dictionary names with row[0] (i.e. OBJECTID)
x = names.values()[row[0] - 2]  # e.g. OID 2 (pre)
y = names.values()[row[0] - 1]  # e.g. OID 3 (current row)
z = names.values()[row[0]]      # e.g. OID 4 (post)

# Now do the calculation
row[2] = x + z
cursor.updateRow(row)

-
Update cursors are able to read as well as write data. (Useful, for example, to read a value from fieldA and use it to calculate a new value for fieldB.) –  Erica Jul 3 at 0:34