2

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)

3
  • 1
    I would use ArcPy, some Stack Overflow research, and an UpdateCursor to do this. – PolyGeo Sep 27 '19 at 2:32
  • Was having trouble researching those. I need some guidance on how to solve this. – Frank Sep 27 '19 at 2:42
  • I wonder if the individuals who voted this as off-topic actually read the post or just the title. The title may be a little misleading on its own merit, but the content of the post is most certainly on-topic. Maybe the title should read "Adding Excel-style ColumnNames and RowNumbers to a Grid Index or Fishnet Grid in ArcGIS" if Excel by itself constitutes a trade-mark or copyright infringment, how about "Adding spreadsheet-style ColumnNames and RowNumbers to a Grid Index or Fishnet in ArcGIS" – Robbie Symborski Sep 29 '19 at 17:43
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

enter image description here

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

enter image description here

3
  • Thank you. I got your code to work on my grid. I got the results I needed. I wanted to ultimately write a custom tool for this, but maybe another time I'll figure out how. Your code was challenging, I don't think I would have figured that out myself. It looks like your code needs the PageName field so it can run. I wonder how this would problem could be done on a fishnet. – Frank Sep 27 '19 at 13:16
  • I added some verbose comments to the code to help it be less challenging especially for anyone who is considering rolling this into a Tool. – Robbie Symborski Sep 30 '19 at 12:06
  • Excellent work. This helped me a lot – Frank Sep 30 '19 at 16:35
1

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

1
  • I don't think this will work because the columns and rows are combine into one field. Maybe if the columns and rows could be split it'll work. – Frank Sep 27 '19 at 5:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.