2

enter image description hereI have a problem similar to Dividing two or more rows in single attribute table? but that is for QGIS whereas I have to perform it in ArcGIS.

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
Improve this question
4
  • 3
    There's actually a Q&A or three that addresses this out there. You need a code block and a plan to handle the initial row. But first you need to provide your initial attempt. I'd use an UpdateCursor to apply an explicit ORDER BY, but it depends on the source format as to whether this is required.
    – Vince
    Commented Apr 18, 2021 at 11:52
  • I don't have any idea about python. But following this post (gis.stackexchange.com/questions/253084/…), I had given it a try and build the following code but eventually, it ends up in an error. I know it's all my fault.@Vince
    – knickpoint waterfall
    Commented Apr 18, 2021 at 13:53
  • 2
    Please put code and descriptions in the body of the Question by using the Edit button at the bottom of the post. Comments don't permit formatting and are therefore useless for Python. I will note that a SearchCursor is read-only and following code samples from the documentation, where all the needed columns are specified in the field list is demonstrated.
    – Vince
    Commented Apr 18, 2021 at 15:40
  • 3
    While it's possible to use a SearchCursor in CalculateField, you aren't using the right pattern (which would compile all the needed values in a single initial pass into a dictionary, then fetch from the dictionary thereafter). If you get syntax errors in the stock IDLE IDE, you're not ready to deploy in the CalcuateField code block. Cursor-in-Calculator is an advanced technique; for someone who doesn't have the indentation rules of Python straight, using just a DA UpdateCursor is going to be much simpler.
    – Vince
    Commented Apr 18, 2021 at 18:35

1 Answer 1

Reset to default
1

Try using the da.SearchCursor to list all values, then update/calculate using da.UpdateCursor:

import arcpy

fc = r'C:\GIS\ArcMap_default_folder\Default.gdb\fs_riks_Buffer'

freqfield = 'Frequency'
ratiofield = 'ratio'

sql = 'ORDER BY {0}'.format(arcpy.AddFieldDelimiters(datasource=fc, field=arcpy.Describe(fc).OIDFieldName))
frequencies = [row[0] for row in arcpy.da.SearchCursor(in_table=fc, field_names = freqfield, sql_clause=(None,sql))] 
#[49547, 11131, 6162, 25722, 3703, ... ]

ratios = iter([float(row[0])/row[1] for row in zip(frequencies, frequencies[1:])])
#[4.451262240589345, 1.8063940279130153, 0.23956146489386518, 6.946259789359979, 0.16187978142076503, ..., ]

with arcpy.da.UpdateCursor(in_table=fc, field_names = ratiofield, sql_clause=(None,sql)) as cursor:
    for rownum, row in enumerate(cursor, 1):
        if rownum != 1:
            row[0] = next(ratios)
            cursor.updateRow(row)

enter image description here

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.