Numbering like values in new attribute table field using field calculator of ArcMap

Question

I am using ArcMap 10.6.

In an attribute field, I have 1000s of records.

I am wondering if there is a way to use the field calculator to get this desired result:

Species | Label
A | 1
A | 1
D | 2
D | 2
F | 3
F | 3

I could use an if-elif statement, but I have 100s of unique values and would like to assign a number to them.

Answer 1

You could use the summary statistic tool to create a temporary table to aggregate by species name. Then add a new sequential number field and populate that with field calculator. Finally join the table to the original dataset, either leave it as a temporary join or pass in the field to make it permanent (see join field tool).

Answer 2

If you're talking about ArcMap, you could try something like this in the field calculator's Python code block (untested):

lookup = dict()
def get_id(value):
    global lookup
    try:
        uid = lookup[value]
    except KeyError:
        uid = lookup[value] = len(lookup)
    return uid

get_id(!some_field!)

Edit: annotated to explain the code:

# create a dictionary with nothing in it
lookup = dict()
define the function that will be called for every row in the table
def get_id(value):
    # tell the function to look for the dictionary defined outside of the function itself.
    # we can't define it inside of the function, otherwise it would get emptied on every row.
    global lookup
    # attempt to look up the ID associated with this value, store it in the uid variable.
    # it will only be present if we've already seen this value, otherwise we'll get a KeyError.
    try:
        uid = lookup[value]
    # in the case that we haven't yet seen the value, then set the ID to the current length
    # of the dictionary, which is the number of unique values we've seen so far.
    # also store it in the dictionary for later.
    except KeyError:
        uid = lookup[value] = len(lookup)
    # give the ID back to whatever called the function
    return uid

Edit 2: A lot of similar approaches are being floated, which are all great! But what's actually fastest? Let's see. I've added a bonus one here as well, that hasn't bee proposed yet.

import timeit
import random
random.seed(42)
field_values = [chr(random.randint(32, 127)) for _ in range(1010*6)]
def test_eafp():
    lookup = dict()
def get_id(value):
    try:
        uid = lookup[value]
    except KeyError:
        uid = lookup[value] = len(lookup)
    return uid

result = [get_id(v) for v in field_values]



def test_lbyl():
    lookup = dict()
def get_id(value):
    if value in lookup:
        uid = lookup[value]
    else:
        uid = lookup[value] = len(lookup)
    return uid

result = [get_id(v) for v in field_values]



def test_get_method():
    lookup = dict()
def get_id(value):
    uid = lookup[value] = lookup.get(value, len(lookup))
    return uid

result = [get_id(v) for v in field_values]



def test_missing_overload():
    class LookupDict(dict):
        def missing(self, key):
            value = self[key] = len(self)
            return value
lookup = LookupDict()
result = [lookup[v] for v in field_values]



if name == 'main':
    print 'Timings with {} random characters (best of 10)'.format(len(field_values))
    timer = timeit.Timer(test_eafp)
    print 'EAFP', min(timer.repeat(repeat=10, number=1)), 'seconds'
    timer = timeit.Timer(test_lbyl)
    print 'LBYL', min(timer.repeat(repeat=10, number=1)), 'seconds'
    timer = timeit.Timer(test_get_method)
    print 'dict.get()', min(timer.repeat(repeat=10, number=1)), 'seconds'
    timer = timeit.Timer(test_missing_overload)
    print 'overload missing', min(timer.repeat(repeat=10, number=1)), 'seconds'

Timings with 10000000 random characters (best of 10)
EAFP 0.762635946274 seconds
LBYL 0.871690034866 seconds
dict.get() 1.51433086395 seconds
overload __missing__ 0.721973896027 seconds

Answer 3

LBYL approach:

lookup = dict()
def get_id(value):
 uid = lookup.get(value,0)
 if uid ==0: uid = lookup[value] = len(lookup)+1
 return uid

But result is not sorted, unlike @Hornbydd one.

Update based on user @2856 comment to post below:

lookup = dict()
def get_id(value):
 uid = lookup[value] = lookup.get(value,len(lookup)+1)
 return uid

Answer 4

You can achieve this using a virtual layer.

In a single step, it would identify the distinct species, create a unique ID for each species and join back this ID to the original layer.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Replace the layer name for yours (a).

WITH labels as 
  (SELECT a.species, row_number() over(order by species) as label
  FROM a
  GROUP BY a.species)
SELECT a.*, labels.label
FROM a
JOIN labels ON a.species = labels.species