# Compare and update values from different source using ArcPy cursors?

Shapefile: "table1.shp"

1. Field name: "Name"
2. Field name: "Result"

Flat file: "referenceTable"

1. Field name: "STREET"
2. Field name: "AKA"

I need to compare the value "Name" in "table1.shp" to the value "STREET" in "referenceTable"

If the values are the same then put the "AKA" value into the "Result" in "table1.shp"

Here is my code. I got stuck on accessing the elements from the array

import arcpy, os, sys, string
from array import *

table1 = "G:\\xStreetNew\\table1.shp"
table1_column = ['Name', 'Result']
referenceTable = "G:\\xStreetNew\\Test.gdb\\referenceTable"
referenceTable_column = ['STREET', 'AKA']

referenceTable_List = []
with arcpy.da.SearchCursor(referenceTable, referenceTable_column) as rCur:
for r_row in rCur:
reference_row = r_row[0].split(",")
referenceTable_List.insert(0,reference_row)
##print sorted(referenceTable_List)

Table1_Update_Cursor = arcpy.da.UpdateCursor(table1, "Name")
for row in Table1_Update_Cursor:
if row[0] == None:
row[0] = row[0]
else:
row[0] = row[0].replace(";",",")
Table1_Update_Cursor.updateRow(row)

table1_List = []
with arcpy.da.SearchCursor(table1, table1_column) as tCur:
for t_row in tCur:
row = t_row[0].split(",")
table1_List.insert(0,row)
##print sorted(table1_List)

Table1_row = len(table1_List)
Table1_column = len([table1_List])
ref_row = len(referenceTable_List)
for i in range(0,Table1_row):  #Table Row
for ii in range(0,Table1_column):   #Table Column
print table1_List[i][ii]


Below was my old post. Sorry for the confusion.

I have a shapefile named "table1.shp".

1. I would like to get all values from fielname "Name" from this table and put them into a list.
2. Able to output each element from the list

So far, I'm able to create a list but not too sure how to get to value "AVENUE 33" or "CHAPMAN ST"

This is my code:

import arcpy, os, sys, string
from array import *

table1 = "G:\\xStreetNew\\table1.shp"
referenceTable = "G:\\xStreetNew\\Test.gdb\\referenceTable"
cursor = arcpy.da.UpdateCursor(table1, "Name")
for row in cursor:
if row[0] == None:
row[0] = row[0]
else:
row[0] = row[0].replace(";",",")
cursor.updateRow(row)
table1_column = ['Name']
table1_curObj = arcpy.da.SearchCursor(table1, table1_column)
table1_List = []
for row in table1_curObj:
table1_row = row[0].split(",")
table1_List.insert(0,table1_row)
print table1_List[0]

• Welcome to GIS SE. As a new user, please be sure to take the Tour. Unfortunately, Python/arcpy questions, like all coding questions here, are expected to include a best effort at coding the solution in the body of the question (it makes it easier for the volunteers here to help get you working code). You can edit the question when you have an MCVE (and vote to reopen it if it's been placed on hold) – Vince Mar 17 '16 at 17:28
• give us your code...my guess is you will need 3 cursors. 1. searchcursor for test4 2. searchcursor for referencetable 3. update cursor for test4 – ziggy Mar 17 '16 at 17:49
• Not sure I understand your question. Do you mean that you want to create a list where each element is a unique street name? Like ['AVENUE 32', 'AVENUE 33', 'CHAPMAN ST', 'CARLYLE ST'], etc.? – RHB Mar 19 '16 at 2:16

## 1 Answer

As said in the comments, a few cursors should help. Also, you changed your question a bit, so perhaps this approach isn't what you were looking for.

The following uses two cursors: one to go through Table1 to do the updates, and one to search the reference table for the AKA given the streets.

import arcpy

table1 = r"G:\xStreetNew\table1.shp"
fieldnames_update = ['Name', 'Result']
referenceTable  = r'G:\xStreetNew\Test.gdb\referenceTable'
fieldnames_ref = ['AKA']

with arcpy.da.UpdateCusor(table1, fieldnames_update) as uCur:
for urow in uCur:
streets = urow[0].split(';')
aka_list = []
for street in streets:
where_clause = '"STREET" = \'%s\'' % street
with arcpy.da.SearchCursor(referenceTable, fieldnames_ref, where_clause) as sCur:
for row in sCur:
aka_list.append(row[0])
urow[1] = ','.join(aka_list)
uCur.updateRow(urow)

print 'Done'


Unfortunately, this is untested code, but should get you in the right direction.

The uCur will return just the Name and Result fields here. For each row, we split the Name on the semicolon, then go through each street split out to look up the AKA in the reference table. Once we find the AKA, we add to a list since we could have multiple streets. Once we're done, we join all the AKA values together with a comma. Finally, we use the update cursor to update the Result field with our newly joined string.

Some considerations.

1) The search cursor can be slow if you're updating thousands of rows. If the reference table isn't too large, it might be very beneficial to stuff all the AKA results in a dictionary. Then we can just use the update cursor to look-up the values in the dictionary, which is very quick. This is at the cost of putting the reference table in memory.

aka_dict = {}
fieldnames_ref2 = ['STREET', 'AKA']
with arcpy.da.SearchCursor(referenceTable, fieldnames_ref2) as sCur:
for row in sCur:
aka_dict[row[0]] = row[1]

with arcpy.da.UpdateCusor(table1, fieldnames_update) as uCur:
for urow in uCur:
streets = urow[0].split(';')
aka_list = []
for street in streets:
aka_list.append(aka_dict[street])
urow[1] = ','.join(aka_list)
uCur.updateRow(urow)

print 'Done'


Personally, I'd try this.

2) Using a dictionary brings up an excellent consideration: your reference table better have only unique values in the Street column. If there are two values for Main St, the dictionary will not be happy.

3) Be very careful with your indenting. Your sample code as printed here is problematic; I hope that is just a copy/paste issue to GIS.SE.

4) Again, this is untested code, so there may be some issues here. Test, test, test.

• Richard, Thank you so much for help. I'm trying to run your code and got error at aka_list.append(aka_dict[street]). KeyError: u'AVENUE32,AVENUE 33'. I think it's complaining about the "street" index. I'm trying to figure out the problem. As you mentioned...test, test,test. – sys Mar 22 '16 at 16:46
• Please also note that I had editted my question to match with the original post. Sorry for the confusion. Thank you – sys Mar 22 '16 at 16:48
• I found the problem... I added this line if street in aka_dict.keys(): before append the aka_dict and it worked. – sys Mar 22 '16 at 19:18