How do I get the difference between two numbers (double) using field calculator?

[EricEagle]

I can't believe that this problem is stumping me, but stumped me it has.  All I'm trying to do is use the field calculator to derive a difference between the numbers (double) in two columns.  The numbers are all over the place; I can't do a subtraction, I'm just looking for the delta.

There is an Abs() visual basic function, but I can't find any documentation on how it should work, or on whether it works the way that abs() in python does.

If someone can explain which of the functions in Field Calculator I can use to derive a numerical difference, I'd appreciate it.

I'm on ArcGIS Desktop 10.5 by the way.

[JoshuaBixby]

The Field Calculator supports VB Script and Python parsers, which opens the door for a wide range of VBA and Python functions.  Does using Python abs() not work if you set the parser?

[EricEagle]

Hi Joshua, no, this does not work in the same way.

In python, I could just do

>>>abs(x - y)

in Field Calculator (using python) I put

abs(!field_1! - !field_2!)

and it does nothing.

[DanPatterson_Retired]

your fields are called field_1 and field_2... and you selected the python parser, then 'number' in the radio button, and you are putting the result in a numeric field (presumably a double field), and you tried it with the abs... maybe choose math.abs from the function list.

Barring that, what error message or what from the above is different than I describe?

[EricEagle]

Hi Dan,

The fields aren't called field_1 or field_2, I just put that for placeholders and brevity.

You are correct on all counts.  I have two tables, joined.  The two fields, one from each table, I am attempting to calculate are both double and both tables obey the same schema.  The field I am calculating into, is double as well.

When I try to use math.abs() it fails, telling me that the module has no attribute 'abs'.

When I do a basic subtraction, it does work, so I know there isn't any problem with the join itself.

[DarrenWiens2]

What are the data types for !field_1!, !field_2!, and the field you're calculating into? The syntax 'abs(!field_1! - !field_2!)' should work with the python parser. Is there a selection on your feature layer?

[EricEagle]

So here is what I had to do, and this may be an ArcMap bug in 10.5 - I will attempt to replicate and confirm.

First, I had to do a straight subtract.

Then, I had to run:

abs(!field_1! - !field_2!)

on the column.  I was not able to run the 'abs' module first; it would give me zeros.  Once I ran subtraction first, I could then run the 'abs' module in order to switch it from negative values.

[JoshuaBixby]

I am running ArcGIS 10.5.  I ginned up a quick test, and using the Python abs() function works, at least for the test.

>>> import random
>>> 
>>> tmp_table = arcpy.CreateTable_management("in_memory","tmp_table")
>>> 
>>> arcpy.AddField_management(tmp_table,"field_1","DOUBLE")
<Result 'in_memory\\tmp_table'>
>>> arcpy.AddField_management(tmp_table,"field_2","DOUBLE")
<Result 'in_memory\\tmp_table'>
>>> arcpy.AddField_management(tmp_table,"abs_diff","DOUBLE")
<Result 'in_memory\\tmp_table'>
>>> 
>>> cur = arcpy.da.InsertCursor(tmp_table, ["field_1","field_2"])
>>> for _ in range(5):
...     cur.insertRow([random.uniform(0,100), random.uniform(0,100)])
...     
>>> arcpy.CalculateField_management(tmp_table,
                                    "abs_diff",
                                    "abs(!field_1! - !field_2!)",
                                    "PYTHON_9.3")
.... 
<Result 'in_memory\\tmp_table'>
>>> arcpy.da.TableToNumPyArray(tmp_table,"*")
array([(1, 87.21215183954446, 75.1238884021629, 12.088263437381599),
       (2, 45.53323645886023, 9.920800759387848, 35.61243569947235),
       (3, 92.88420780744688, 63.48972631476961, 29.3944814926773),
       (4, 81.26177213063858, 92.81813834079237, 11.556366210153797),
       (5, 92.08135345135018, 53.952867356510815, 38.128486094839396)], 
      dtype=[('ObjectID', '<i4'), ('field_1', '<f8'), ('field_2', '<f8'), ('abs_diff', '<f8')])
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If you create a test/example data set, are you seeing the same problem or is it specific to this data set?  I did not do a join, so it could be related to that.

You say "both tables obey the same schema," do you have columns with the same names in both tables?  Are you using fully qualified column names, e.g. "table1.field_1"?

[EricEagle]

Sorry for the lag Joshua, for whatever reason, geonet does not email me updates to my questions.

I am using fully qualified column names, yes.  For Dan's question below, I can only add a field to the origin table; ArcMap will not allow me to add a field to a destination table.

At this point I do have a working output; this is something I did for a colleague, and if this is going to be an ongoing thing, I will just do it via script

Thanks!

[DanPatterson_Retired]

You aren't by chance trying to calculate in the joined table... not the table it is joined to but the other one?  If so, make your new field in the destination table and try it from there.  Regarding the math... it should be math.fabs