I'm assuming this is pretty straightforward, and I am missing something fairly simple.
I am attempting to bring in a column of data values, 'FieldA', from a table, 'Table_1', to populate an empty field, 'FieldA', in a new, empty table, 'Table_2'. These 'FieldA' values will be used as a key in 'Table 2' for subsequent field calculations. Is there a direct way to do this?
I have been attempting to use a search cursor and update cursor combination to write the field values to the new table, though I am not sure how to successfully employ the cursors to update the single field without an existing key between the two tables (where I would use a dictionary).
Here is my unsuccessful, botched code:
search_feats = arcpy.da.SearchCursor(Table_1,["FieldA"])
i = 0
with arcpy.da.UpdateCursor(Table_2,["FieldA"]) as upd_cur:
for upd_row in upd_cur:
i=i+1
upd_row[0] = search_feats (i)
upd_cur.updateRow(upd_row)
del upd_cur
What may be a better way to approach this?
Solved! Go to Solution.
In my previous response, I used the old InsertCursor. Try the da.InsertCursor, and since it is only one field, not a tuple, just one set of () around row.
cursor = arcpy.da.InsertCursor("Table_2",['FieldA'])
for row in tbList:
cursor.insertRow([row]) # as Bixby suggested below, using square brackets
del cursor
if there is a 1:1 relationship, just use 'join' and calculate field, from the script examples in their respective tools
This might be the straightforward way I was hoping to keep the existing 'Table_2' structure (without having to add and reorder fields), but deleting the new field after a quick join and calculation might be the best way to go.
Thanks!
I tend to use numpy and arcpy.da.ExtendTable, it still is a join, but your calculations can be vectorized and the join is permanent
You might check out: Turbo Charging Data Manipulation with Python Cursors and Dictionaries. I think Example 2 might work for you.
That blog post is AWESOME. I have been using it for many of my field updates lately, and it has been a game changer for speeding up scripts. I was actually hoping to integrate something similar for this particular issue but became stumped when I didn't have a key for both tables. I am also not quite sure if a dictionary is helpful, if the search cursor is only reading values from one field.
If you have suggestions as to how I might make this method work for updating one existing field on a new table that does not already have a key (the new field that is being populated here will act as the key for subsequent field calculations), I will gladly take them. I'm a python novice and am looking for recommendations anywhere I can get them. Thank you so much!
Does FieldA in Table1 contain unique values or not?
Yes, they are unique values.
If you want to get the distinct values from Table_1.FieldA and insert them into a new, empty table in FieldA, you can use a search cursor to retrieve the values and an insert cursor to put them in the new table. Perhaps something like:
tbList = [] # empty list
for row in arcpy.da.SearchCursor('Table_1', ["FieldA"]):
if row[0] not in tbList:
tbList.append(row[0]) # copy distinct rows to list
inCursor = arcpy.da.InsertCursor('Table_2', ["FieldA","MyCalc"]) # list of all fields in new table
for row in tbList:
inCursor.insertRow((row,None)) # field values to insert
del inCursor
You can create your calculation field ("MyCalc" for example) when you create your table or add it later. Hope this helps.
I think this is exactly what I am looking for.
However, I am getting a "sequence size must match size of the row" error on line 10. I did not include a "myCalc" field though, as I have already added all of the necessary fields to the new table (i.e. Table_2). (Maybe I am wrong in excluding it?)
tbList = [] # empty list
for row in arcpy.da.SearchCursor('Table_1', ["FieldA"]):
if row[0] not in tbList:
tbList.append(row[0]) # copy distinct rows to list
inCursor = arcpy.da.InsertCursor('Table_2', ["FieldA"]) # list of all fields in new table
for row in tbList:
inCursor.insertRow((row,None)) # field values to insert
del inCursor
If I print tbList, this is what I currently get: