Calculating new values for column based on another column with Field Calculator in ArcMap?

Question

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

• Michael Stimson: 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?
• PolyGeo: What have you tried using the Python Parser? I would not recommend using VBscript.

Answer 1

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'])

• K. Johnson: 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).
• cm1: Line 4 should be "for Row IN sCur:"
• K. Johnson: It worked when I changed both "Order" with "ID" in the the last part of line 3. Thank you very much again.
• Michael Stimson: Ah yes, I'll change that.