Add Excel-style row_number and column_name attributes to a Grid Index in ArcGIS [closed]

Question

After using the Grid Index Feature tool it creates a grid polygon layer. The resulting feature class contains a field called [PageName], which displays like A1, A2, A3...etc in each row starting from left to right. In each cell I notice that it shows that the columns (left to right) are labeled as numbers (1,2,3,4... etc) and the rows (up and down) are labeled as letters (A,B,C...AA,AB... etc).

What I want to do is add attributes so that the Columns are labeled as letters and the Rows as numbers; similar to an Excel sheet. I would prefer to have the results in 3 different fields, one for Columns, one for Rows, and one for the CellID (Columns + Rows). How can I do this?

Edit 1: While trying to create a tool I have this so far for the rows but nothing happens.

import arcpy

# set parameters
fc = arcpy.GetParameterAsText(0)
field = arcpy.GetParameterAsText(1)

with arcpy.da.UpdateCursor(fc, field) as cursor:
    for row in cursor:
        row[0] = row[0]
        cursor.updateRow(row)

Edit 2: Clarified the above question more. My test grid I am working with is 46 Rows by 187 Columns. The field calculator this isn't exactly what I am looking for, but this is the progress I have so far if this helps someone. In a new field (Row) I used this code to generate row numbers.str((!OID!+186)/187)

Answer 1

Try this in a Label Expression for your grid (after removing comments) ... you will have to check 'Advanced' and make sure the Parser is set to Python. The expression will render the PageName as provided by the Grid Index Tool as well as a Second Line with the preferred Excel-style 'ABC....ZZ' columns and '123...' rows

import numpy
def FindLabel ( [PageName] ):
  pn = [PageName]
  #First we need to separate the PageNumber generated by the Grid Index Tool into its component parts:
  #where PageNumber has the form {A1, A2, ... Acx}
  #                              {B1, B2, ....Bcx}
  #                              {Z1, Z2, ....Zcx}
  #                              {AA1, AA2, ...AAcx}
  #                              {ZZ1, ZZ2, ...ZZcx}
  #and {cx} is the total number of columns (1-based from left to right)

  #this will parse the digits(column number) out of PageNumber into an array [res]
  res = [int(i) for i in pn if i.isdigit()]

  #next we compute the length of the digits portion(column_number) found in PageNumber
  length_of_digits = len(res)

  #and we compute the length of the character portion(row_name) found in PageNumber
  length_of_chars = len(pn) - length_of_digits

  #now that we have the length for each portion, we can extract the components out of PageNumber
  row_name = pn[:length_of_chars]
  column_number = pn[-length_of_digits:]

  #now we need to convert each component part into its Excel-style counter-part 
  #ie.  row_name('A')    into row_number(1)
  # &   column_number(1) into column_name('A')

  #need 2 branches to handle row_name 'A'...'Z' differently than 'AA'...'ZZ'
  if len(row_name) == 1:
      row_number = ord(row_name)-64
  elif len(row_name) == 2:
      row_number = (ord(row_name[0])-64)*26 + (ord(row_name[1])-64)

  #again need 2 branches to handle:
  #    column_number<27  for column_names 'A'...'Z'
  #and column_number>=27 for column_names 'AA'...'ZZ'
  if int(column_number)<27:
      column_name_first_letter = ""
      column_name_second_letter = chr(64 + int(column_number))
  else:
      column_name_first_letter = chr((int(column_number)-1)/26+64)
      column_name_second_letter = chr(numpy.mod(int(column_number)-1,26)+65)

  #this is just a safeguard incase anything went wrong in the extraction and conversion of the component parts
  column_name = str(column_name_first_letter) + str(column_name_second_letter)

  #at this point, we just need to combine the component parts into a valid string for labeling where:
  #   row_name      = Grid Index PageNumber {row    name   component}
  #   column_number = Grid Index PageNumber {column number component}
  #   column_name   = Excel Style           {column name   component}
  #   row_number    = Excel Style           {row   number  component}
  return row_name + " : " + column_number + "\n" + column_name + " : " + str(row_number)

If you want to apply this to your GRID permanently, you will have to add 2 columns to your GRID INDEX table (one column as text[2] for the column_name and another column as short_integer for the row_number).

Once you have added the columns, you can use the Field Calculator with this script. Right Click on your newly added field for column_name and choose Field Calculator. In the Field Calculator, change the Parser to Python and check the box that says "Show Codeblock". Paste the modified code below into the Pre-Logic Code Block.

import numpy
def FindLabel ( PageName ):
  pn = PageName
  res = [int(i) for i in pn if i.isdigit()]
  length_of_digits = len(res)
  length_of_chars = len(pn) - length_of_digits
  row_name = pn[:length_of_chars]
  column_number = pn[-length_of_digits:]

  if len(row_name) == 1:
      row_number = ord(row_name)-64
  elif len(row_name) == 2:
      row_number = (ord(row_name[0])-64)*26 + (ord(row_name[1])-64)

  if int(column_number)<27:
      column_name_first_letter = ""
      column_name_second_letter = chr(64 + int(column_number))
  else:
      column_name_first_letter = chr((int(column_number)-1)/26+64)
      column_name_second_letter = chr(numpy.mod(int(column_number)-1,26)+65)

  column_name = str(column_name_first_letter) + str(column_name_second_letter)
  return column_name 

Notice the square brackets for PageName have been removed and the return statement has been changed to only return the column_name. Lastly, in the box below the Pre-Logic Code, set your {NEW-COLUMN-NAME} = FindLabel ( !PageName! )

Repeat this process for your {NEW-ROW-NUMBER} field except change the return statement to:

return row_number

If your implementation is a Fishnet Generated Grid: The Fishnet Tool creates a grid that is numbered by [OBJECTID] from bottom-left to top-right and is read left to right. Implementing Labels that reflect {COLUMN_NUMBER} and {ROW_NUMBER} can be done using this vbScrtipt Expression for the label:

"COL NUM" & vbnewline & (([OID]-1) mod {total_column_count})+1 & vbnewline & "ROW NUM" & vbnewline & {total_row_count}-int(([OID]-1) / {total_column_count}})

To permanently Calculate Fields use :

{COLUMN_NUMBER} = (([OID]-1) mod {total_column_count})+1

{ROW_NUMBER}    = {total_row_count}-int(([OID]-1) / {total_column_count})

If you prefer Excel-style column names use :

{COLUMN_NAME_FIRST_CHAR} = replace(chr(int((([OID]-1) mod {total_column_count})/26)+64), "@", "")

{COLUMN_NAME_SECOND_CHAR} = chr(((([OID]-1) mod {total_column_count}) mod 26) + 65)

or

{EXCEL_COLUMN_NAME} = replace(chr(int((([OID]-1) mod {total_column_count})/26)+64), "@", "") & chr(((([OID]-1) mod {total_column_count}) mod 26) + 65)

beware : everything in {braces} should be replaced with values corresponding to your specific Fishnet Generated Grid implementation and all expressions are vbScript Expressions

Answer 2

There is a pivot table tool in ArcMap like there is in Excel. Maybe that would do it?