0

I have an ArcMap table with about 10000 records. I want to calculate values for an empty column (Result) based on the values in another column (FVal). You can see a small example of the table below. For each ID, there are four records. I need to calculate the following ratio for each ID: (Row 1 + Row 4) / (Row 2 + Row 4). For example, for ID = 0, "Result" is (34.6 + 24.5)/(18.4 + 44.7) = 0.9366.

How can I do this with Field Calculator by using Python or VB Script in ArcMap?

ID - Order - FVal - Result

0 - 1 - 34.6

0 - 2 - 18.4

0 - 3 - 24.5

0 - 4 - 44.7

1 - 1 - 1041.6

1 - 2 - 1254.3

1 - 3 - 1059.2

1 - 4 - 1395.6

2
  • It is possible with a search cursor in an advanced field calculation (python). Can you explain a bit more about how to decide which returned row is 1, 2, 3 and 4. Cursors are returned in no particular order but there are ways to sort the values. Do you have any python ability? Have you got any code to start from or are you looking at a blank page unsure where to even start? Aug 23, 2017 at 20:48
  • What have you tried using the Python Parser? I would not recommend using VBscript.
    – PolyGeo
    Aug 23, 2017 at 20:51

1 Answer 1

2

I think you're looking for something like this:

def CalcRatio(ID,Order,FVal):
    vDict = {}
    with arcpy.da.SearchCursor("YourLayerName",['Order','FVal'],'ID = {}'.format(ID)) as sCur:
        for Row in sCur:
            vDict[Row[0]]=Row[1] # Dict[Order] = FVal
    try:
        value = ( vDict[1] + vDict[4] ) / ( vDict[2] + vDict[4]) # what happened to 3?
    except:
        value = -1
    return value

which you would then call in the advanced field calculator with

CalcRatio(!ID!,!Order!,!FVal!)

Assuming those are your field names.

You must change the layer name in the search cursor to either the exact layer name in ArcMap or to the full path of the feature class to enable the script to 'find' the right data.

Bad things will happen if your order values aren't correct, they need to be present in the table as 1, 2, 3 and 4 on separate rows, to this end I have added a try: except: block with a fail value of -1 so you can find the rows that don't have all the values; duplication of order values will cause arbitrary reassignment (the final value will be one of the given values but you can't tell which one). If your order is a string field you will need to quote the values in the line doing the calculation thus:

value = ( vDict['1'] + vDict['4'] ) / ( vDict['2'] + vDict['4'])
4
  • Thank you very much Michael for the detailed answer. I tried it but I got the following error message: ERROR 000989: Python syntax error: Parsing error SyntaxError: invalid syntax (line 4). What can the reason be? Meanwhile, I am sure that Order values are correct. You're right, it should be (r1+r3)/(r2+r4).
    – K. Johnson
    Aug 24, 2017 at 9:03
  • Line 4 should be "for Row IN sCur:"
    – cm1
    Aug 24, 2017 at 19:04
  • 1
    It worked when I changed both "Order" with "ID" in the the last part of line 3. Thank you very much again.
    – K. Johnson
    Aug 25, 2017 at 17:37
  • Ah yes, I'll change that. Aug 27, 2017 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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