Calculating ratio between “rows” in ArcGIS field calculator

[badrinathkar]

I have to calculate the Ratio between rows (sequentially, i.e., row1/row2, row2/row3 and so on) of a specific field based on the ID field and have to assign the results to a separate field.

Here is the code and the error message:

 def CalcRatio(ID,Frequency😞
    vDict = {}
    with arcpy.da.SearchCursor("Test",['Frequency'],'ID = {}'.format(ID)) as sCur:
    for Row in sCur:
    vDict[Row[0]]=Row[1] # Dict[id] =Frequency
    try: value = ( vDict[1] / vDict[2])
    except: value = -1
    return value

Message:

ERROR 000539: Error running expression: CalcRatio( 1 , 50680) Traceback (most recent call last):
File "<expression>", line 1,
in <module> File "<string>", line 5,
in CalcRatio IndexError: tuple index out of range

Here is the data

OID Id  Frequency
0   1   50680
1   2   49740
2   3   48620
3   4   48300
4   5   48004
5   6   45600
6   7   44384
7   8   42720
8   9   41890
9   10  39700
10  11  38530
11  12  38207
12  13  38106
13  14  37303
14  15  37205
15  16  36527
16  17  35301
17  18  31674
18  19  28890
19  20  24612

arcgis-desktop

[JohannesLindner]

# The error is thrown by this line:
vDict[Row[0]]=Row[1] # Dict[id] =Frequency

# You only use one field (Frequency) in your cursor, so Row is a list with only one entry.

Personally, I hate using field calculator to calculate values based on multiple rows. I'd rather use pure Python:

# extract all rows and sort by ID
# data = [ [ID, Freq], [ID, Freq], ... ]
data = [row for row in arcpy.da.SearchCursor("Test", ["ID", "Frequency"])]
data.sort(key = lambda d: d[0])

# do the calculation
for i in range(len(data)):
    try:
        value = data[i][1] / data[i+1][1]
    except:
        # instead of catching all exceptions, you could also just catch the ones you expect:
        # last element will raise IndexError
        # Freq == NULL will raise TypeError
        # Freq == 0 will raise ZeroDivisionError
        value = -1
    data[i].append(value)

# convert data into a lookup dictionary
# data = {ID: value, ID: value, ...}
data = {d[0]: d[2] for d in data}

# write the results into the table
with arcpy.da.UpdateCursor("Test", ["ID", "ValueField"]) as cursor:
    for id, value in cursor:
        try:
            new_value = data[id]
        except KeyError:
            new_value = -1
        cursor.updateRow([id, new_value])

[JoshuaBixby · Accepted solution]

I agree with @JohannesLindner that this is best handled using cursors instead of Field Calculator.  If you are interested in doing a "look-behind" calculation, you can do it with a single pass of an update cursor.

from itertools import tee

tbl = # path to table or feature class
with arcpy.da.UpdateCursor(tbl, ["Frequency", "value"], sql_clause=(None, "ORDER BY Id")) as cur:
    n1, n = tee(cur)
    row_n1 = next(n1)
    for row_n1 in n1:
        row_n = next(n)
        cur.updateRow([row_n1[0], row_n[0]/row_n1[0]])

[JohannesLindner]

Didn't know about itertools.tee, seems cool but honestly a little confusing.

I'd have done that by storing the previous value in a variable.

[JoshuaBixby]

There are a couple of handy things about tee that aren't used in this simple situation.  For example, you can split an iterable into as many independent iterators as you like, it doesn't have to be just two.  Since the iterators are independent of each other, each can be at a various spot.  The tricky part with update cursors is that the furthest ahead iterator determines where the underlying cursor is in terms of the dataset, which is why "look-behind" calculations can be done but not "look-ahead" if you want to update the dataset in one pass.

[JohannesLindner]

Yeah, the cursor.updateRow was the most confusing thing.

Is this behavior specific to UpdateCursor/all arcpy.da.*Cursors? Or is this the expected behavior, that the child iterators are independent from each other, but not from the parent / the parent not from them?

[JoshuaBixby]

In this case, ArcPy cursors are simply iterables to tee, and the cursors are treated just like tee would treat any other iterable when splitting it.

The itertools — Functions creating iterators for efficient looping — Python 3.9.4 documentation states:

Once tee() has made a split, the original iterable should not be used anywhere else; otherwise, the iterable could get advanced without the tee objects being informed.

For ArcPy cursors, the iterators returned back from tee do not have an updateRow() method, which is why I refer back to the cursor object to update the row.  That said, you will notice that I am not iterating the original cursor because doing so would create a situation where the tee iterators miss rows of data.

ArcPy cursors are "forward-only" cursors, meaning they can only move one direction through the data set once defined.  Once the cursors has visited row x+1, it can't go back to row x unless the cursor is reset, which takes the cursor back to the start of the data set.  Since ArcPy cursors are forward-only, the tee iterator that is furthest through the data set determines where the original cursor object is in the data set.

[JohannesLindner]

Thanks for the explanation Joshua, really appreciate it.