I am trying to write a script that recategorizes 70 attributes in to 1 of 5 attributes.

I have two tables, each with two columns that I am focusing on. The first table has one column with 20k rows that all contain 1 of the 70 strings.

The second table is the key used as a key. The first column has 70 rows, each listing one attribute type. The second column tells what that attribute converts in to. There are 5 options, so each of the 70 will fall in to one of those five.

What I need to script to do is read through the key one row at a time, then find all matching rows in the other table's column. For all of those matches, a second column in the original table needs to be populated with the corresponding attribute (from the 5 options).

I have trouble explaining this, but it is really just recategorizing 70 attributes into 5 attributes. These are all strings. The original table is a layer in ArcMap, and the key table is an excel file. I am not sure how to write this script.

Here is what I have so far:

import xlrd
from xlrd import open_workbook

xl_workbook = xlrd.open_workbook(conversion_table)
xl_sheet = xl_workbook.sheet_by_index(0)
num_cols = xl_sheet.ncols   # Number of columns
for row_idx in range(0, xl_sheet.nrows):    # Iterate through rows
    print ('-'*40)
    print ('Row: %s' % row_idx)   # Print row number
for col_idx in range(0, num_cols):  # Iterate through columns
    cell_obj = xl_sheet.cell(row_idx, col_idx)  # Get cell object by row, col
    print ('Column: [%s] cell_obj: [%s]' % (col_idx, cell_obj))

# print water land use category column
conversion_column = xl_sheet.col(0)
list1 = list()
print list1

# print land use field from roadway
fc = original_table
field = "LAND_USE"
cursor = arcpy.SearchCursor(fc)
row = cursor.next()
list2 = list()
while row:
    results = (row.getValue(field))
    row = cursor.next()
print list2

I'm using version 10.3.1.

  • What version of ArcGIS are you using?
    – PolyGeo
    Jun 12 '17 at 20:31
  • I'm using version 10.3.1 Jun 12 '17 at 20:39
  • In that case I recommend investigating the newer data access cursors, Excel to Table tool and Python list comprehension.
    – PolyGeo
    Jun 12 '17 at 20:46
  • Yeah, it would be a lot easier to just do it by hand, but it needs to be a script so that when other people update the excel file they can click to run the script and update the layer. They might add/change attributes later on. Jun 12 '17 at 20:57
  • The functions I recommended are what I would use to write that short script.
    – PolyGeo
    Jun 12 '17 at 21:19

A dictionary is essential for this task. First create a dictionary of all your keys and their associated new values. Then use an UpdateCursor to update your table.

Something like this:

##the table to be updated
updateTab = r"C:\path\to\updateTable"
#the field name of the field containing the values to convert
updateTabValFld = "ValueFieldName"
#the field name of the field that will be populated with the new values
updateTabUpdateFld = "UpdateFieldName"

##the table with the keys
keyTab = r"C:\path\to\keyTable"
#key table key field name
keyTabKeyFld = "KeyFieldName"
#key table value field name
keyTabValFld = "UpdateValueFieldName"


import arcpy

#create empty dictionary to be populated with keys and values
di = {}
#iterate key table with a search cursor
with arcpy.da.SearchCursor (keyTab, [keyTabKeyFld, keyTabValFld]) as curs:
    for key, val in curs:
        #update dictionary
        di [key] = val

#update table with new values by use of an update cursor
with arcpy.da.UpdateCursor (updateTab, [updateTabValFld, updateTabUpdateFld]) as curs:
    for val, up in curs:
        #create new row with dictionary value
        row = (val, di [val])
        #update row
        curs.updateRow (row)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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