# How do I fill in serial letters (AA, AB,AC…,BA,BB,BC…ZZ) to a shape?

(I am using ArcGIS software, ArcMap...)

MY PROBLEM
I have a shape file containing points. The shape file contains 3 columns named FID, Shape* and Label. In the column I want to fill in AA, AB, AC..., BA, BB, BC...ZZ. Since this is over 600 points (and I have 19 of these shape files), I do not want to do this manually. How do I proceed to fill this in automatically? Preferably by using field calculator.

I would prefer to be able to:

• Select say 100 of the points
• Use field calculator to name them AA-DX
• Make new selection of 100 (or some other count)
• Continue naming them from where I left off. So DY-HT
• Make new selection of 100 ..... and so on...

TRIED SO FAR
What I already have tried is to create a table in excel with a label column and AA, AB, and so on already filled in. And with a FID column filled in with 0, 1, 2, 3... Then I imported that table to ArcMap and joined the shape file to the new table based on the two FID columns. Unfortunately there is no system to the shape files FID column up against what I want them to be named. So what happens when I join is that I have FID 1 = AA, 2 = AB and so on. Whilst what I really want is FID 74 = AA, 35 = AB, and so on in almost a random number.

The excel code I used to generate AA, AB, AC and so on was: =IF(RIGHT(A1)="Z",CHAR(CODE(LEFT(A1))+1),LEFT(A1))&IF(RIGHT(A1)<>"Z",CHAR(CODE(RIGHT(A1))+1),CHAR(65)) I wrote AA in the first cell, and then this code in all underlying cells

EXAMPLE
To give you a better picture of the problem: I have several points divided on 3 islands. The points were created randomly, so point 1-250 are not all on the same island, and then 251-500 are not on the next same island. The points are just randomly scattered on the three islands. I want to select all points on island 1 and name them AA-DY (or whatever). Then selecting all points on the next island and naming them from DY and on to TG (or whatever). And then continuing on the next Island. Within the one island it does not matter what point becomes AA or what point becomes BR. if that follows FID it is ok but it does not really matter (so if point 2, 6, 12 and 34 were the only islands on the first island. point 2 would be AA, point 6 would be AB, 12 = AC and 34 = AD)

Anybody who can solve my problem?

-
You might want to load this data into a postgis data - then you can apply spatial logical with your AA to ZZ indexing. –  Mapperz May 8 '13 at 15:22

1. Create a numeric field for your island and fill it in. Number each island uniquely depending on the order you want them to appear in the series.
2. Sort based on the island field.
3. Use the following python snippet in field calculator:

chr(((!FID!-1)/26)+65) + chr(((!FID!-1)%26)+65)

This assumes that the FID increments by one after each feature and is never higher than 676.

-
Ok, so this was almost good, now i am useing chr(([FID]+1)/26)+61) to create the first letter sucsesfully.. now i need the second letter.. i need to use chr(SOMETHING) to create A, B, C, ... Y, Z, A, B, C, and so on.. ANYBODY? –  Martin May 10 '13 at 10:43

If you're ok to use Python this isn't too bad a problem.

There are only a few steps:

• Do a spatial join of your islands to your points. You can do this in the normal desktop application, or in Python using the Spatial Join (Analysis) function. What we need here is a unique identifier for each island attached to each of the points.

• Get a unique list of all the island identifiers. If there are a lot of islands, and just typing those in would be a pain you can make one out of a set object using a search cursor:

``````island_ids = set()

for row in arcpy.SearchCursor(islands):
``````
• Create a generator function that will return your AA, AB ... ids in order. We can do this really easily like so:
``````from string import ascii_uppercase

def point_ids():
while True:
for first in ascii_uppercase:
for second in ascii_uppercase:
yield first + second
``````

Assigning a new object to this function will give you a pointer to a generator. Because the function is in an infinite loop (`while True:`) every time you call `next()` you'll get the next id in sequence (`yield` returns a value and then resumes when next() is called again)

• Last for every island you need to do an update cursor over the set of points for that island:
``````new_id = point_ids()

for island in island_ids:
#specify the where query on the search cursor...
cursor = arcpy.UpdateCursor(points, "JOINFIELD = '%s'" % island)
for row in :
row.setValue("id_field", new_id.next()) #new_id.next() will get the next id
cursor.updateRow(row)
``````

And all your points should now have an id, except for any which aren't on an island. Hope this helps!

-

I would copy the points for each island into a new feature class, so that they are assigned new OBJECTID fields.

Then, you can assign all the new IDs all at once using the field calculator.

It may be possible to use the python field calculator (ArcGIS 10 or later) and write a function that converts the decimal OBJECTID to a base-25 digit using a variant of this function:

http://code.activestate.com/recipes/577586-converts-from-decimal-to-any-base-between-2-and-26/

Once the new values are assigned, you can recombine the points to get one resulting feature class.

-

if you change the list of islands, the shape file name, and the field name to update and query this should work if you run it in the python window. this will cycle through a list of your islands, select them one at a time, then update the code field to the letter combinations.

``````import arcpy
island_list =['island 1','island 2']#change to your list of islands

def letter_code(x):
if ord(x)>89:
return chr(65)
else:
return chr(ord(x)+1)
done = 'yes'
x='ZZ'
for isl in island_list:
if done=='yes':
qry = "island = '"+isl+"'" #change "island" to your field name

arcpy.SelectLayerByAttribute_management("your_shp","NEW_SELECTION",qry)**#change your_shp to your shape file name**
cur = arcpy.UpdateCursor("your_shp")#change your_shp to your shapefile name
done='no'

for row in cur:
if x[1]=='Z':
x=letter_code(x[0])+letter_code(x[1])
else:
x=x[0]+letter_code(x[1])
print x

row.code=x#letter_code(chr(x+i))+letter_code(chr(x+i+1))#change "row.code" to row.your field name where you want your letter combinations
cur.updateRow(row)

done = 'yes'
``````
-