How to access adjacent rows with cursor?

Question

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?

Answer 1

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?

Answer 2

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
       #
       # here you have access to the current and previous value
       #

       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..

Answer 3

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)

Answer 4

The Data Access module is quite fast and you could create a SearchCursor to save all values 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!

Answer 5

First you need a search cursor; I don't think you can get values with an update cursor. Then in each iteration use aNext = row.next().getValue('a') to get the value of next row.

To get the value of the row previous I would set up a variable outside of the for loop equal to zero. This gets updated to equal the current rows value of 'a'. You can then access this variable in the next iteration.

This would then satisfy your equation of B = A(rowid-1) + A(rowid+1)