# Generating Values in Attribute Table using ArcGIS Desktop

In the attribute table within GIS, I have a certain column with ascending values, and I want to assign ID numbers (for easier readability and display) to a corresponding column. So for instance:

``````Feet Away   |   ID
200.1      |   1
302.0      |   2
500.9      |   3
...        |   ...
highest    |  highest
``````

I would want to be able to generated the 1, 2, 3, etc.

I do not know how to generate the ID field given that the 'Feet Away' field is already generated and in order. I know that if I had access to some java or javascript function, I would make a loop which started at the first element (the ID field corresponding to 200.1 in the above, or 1), iterate it for as long as there is a value in the feet away column, and fill the ID field with a number one larger than the previous number added. But I do not know how to do that with the attribute table.

How do I generate a column of increasing integers?

Do I need to add code from a different source?

It seems like a pretty easy computer science problem, I just do not know how to do it within a GIS context.

• If the features are already in rank order of value, you can simply use the FID field values to populate a new field (use field calculator on your ID field, and use [FID] +1 to get a ranked value), since the FID values are already sequential, but start at zero, hence the +1 in the calculation Jan 23, 2014 at 17:58
• @TDavis I have ranked the feature ("Feet Away") in ascending order, but the FID field that is naturally included with attribute table goes in this order for the first four rows: 51, 67, 72, 12. So how would I use the [FID]+1 algorithm you speak of? Using it for those values would give me new ids of 52, 68, 73, 13; I would ideally want it to be 0, 1, 2, 3. Jan 23, 2014 at 18:23
• It sounded like you meant your features were in order to begin with, prior to sorting them by 'feet away'. if it's just a one-off process, opening the dbf in excel or openoffice and sorting by 'feet away', and then using a formula (b1=a1+1;...) and paste that down the whole column. if you have a really large file or will need to repeat this, a python or vb script in the field calculator would be needed Jan 23, 2014 at 18:45
• there have been a few posts about doing this, using the search terms sequential or ranked. this looks like what you are asking, gis.stackexchange.com/questions/21807/… Jan 23, 2014 at 18:48
• @TDavis manipulating a SHP file's dbf in excel is generally considered risky, at best. This is a good way to corrupt the SHP.
– Aaron
Jan 23, 2014 at 19:15

Well, if you've got a table that natively (sorted by FID or ObjectID as applicable) is NOT sorted how you want it, and you want to view the table sorted by a specific field(s) with a unique ID field in order of the sort, you don't need to mess with any scripting at all.

Just use the SORT geoprocessing tool that is included in ArcGIS (In ArcToolbox > Data Management Tools > General > Sort ). This tool should do what you're asking for because there will be a new OID/FID for the resulting dataset that is in order with the sorted values.

More info on the Sort GP tool is available at http://resources.arcgis.com/en/help/main/10.1/index.html#/Sort/001700000057000000/

If you are familiar with java(script) then you should be able to use python without too much trouble.

The basic concept would be to create an update cursor, iterate over the rows and assign a value to your field, incrementing each time.

Here's a very minimal example, contained in a python toolbox. You could certainly do more spiffy things, like updating the progress bar or adding the field within the script.

Just past this into a text file with an extension of '.pyt'. That will show up as a toolbox in your GIS catalog that has a single tool called Calculate Ascending Values.

``````import arcpy

class Toolbox(object):
def __init__(self):
"""A python Toolbox to hold the CalcAscendingValues tool"""
self.label = "Python Tool Demo"
self.alias = ""

# List of tool classes associated with this toolbox
self.tools = [CalcAscendingValues]

class CalcAscendingValues(object):
def __init__(self):
"""Assigns ascending integer values to the field specified, starting at 1."""
self.label = "Calculate Ascending Values"
self.description = ""
self.canRunInBackground = False

def getParameterInfo(self):
"""Define parameter definitions"""
pInputTable = arcpy.Parameter()
pInputTable.displayName = 'Input Table'
pInputTable.name = 'input_table'
pInputTable.datatype = 'Table View'
pInputTable.parameterType = 'Required'
pInputTable.direction = 'Input'

pSortField = arcpy.Parameter()
pSortField.displayName = 'Sort Field'
pSortField.name = 'sort_field'
pSortField.datatype = 'Field'
pSortField.parameterType = 'Required'
pSortField.direction = 'Input'
pSortField.parameterDependencies = [pInputTable.name]

pValueField = arcpy.Parameter()
pValueField.displayName = 'Value Field'
pValueField.name = 'value_field'
pValueField.datatype = 'Field'
pValueField.parameterType = 'Required'
pValueField.direction = 'Input'
pValueField.filter.list = ['LONG','SHORT']
pValueField.parameterDependencies = [pInputTable.name]

pOutputTable = arcpy.Parameter()
pOutputTable.displayName = 'Output Table'
pOutputTable.name = 'output_table'
pOutputTable.datatype = 'Table'
pOutputTable.parameterType = 'Derived'
pOutputTable.direction = 'Output'
pOutputTable.parameterDependencies = [pInputTable.name]
pOutputTable.schema.fieldsRule = 'AllNoFIDs'

params = [pInputTable, pSortField, pValueField, pOutputTable]
return params

def execute(self, parameters, messages):
i = 1
cur = arcpy.da.UpdateCursor(parameters[0].valueAsText,
[parameters[2].valueAsText],
None,
None,
None,(None,'ORDER BY ' + parameters[1].valueAsText))
for row in cur:
cur.updateRow([i])
i = i + 1
del cur
return
``````
• Thanks for the very in depth response. I do not really know how to add functions to GIS, and have GIS recognize those functions (somewhat new at GIS admittedly). I copy/pasted what you wrote in a text file, put the proper .pyt extension, and my arcgis 10.1 did not seem to recognize the tool. Regardless, I tried the 'sort' function method written below, and it worked to the needs of the map that I had. Thanks! Jan 23, 2014 at 19:16
• You're very welcome. Anything that uses `arcpy` should work mostly out of the box, although some tools require different licenses. You may have to make sure that a .txt extension isn't getting added to the file (Windows hides it by default, and notepad will add it even if you don't want it to). Jan 23, 2014 at 19:41