nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

arcpy.da.UpdateCursor on joined tables?

ErikMartin

Do data access update cursors work on joined tables? This thread indicates that Search Cursors do, but I keep getting a "cannot update join table" error. I have confirmed that I am trying to update the original table, not the joined table. Here's the pertinent code:

arcpy.MakeFeatureLayer_management(FC, "FC_lyr") FC_lyr = "FC_lyr" arcpy.AddJoin_management(FC_lyr, "joinField", joinedFCTable, "joinField")  fields =("{}.DataField".format(FC_lyr), "{}.OtherDataFiedl".format(joinedFCTable)) where = '"{}.OBJECTID" IS NOT NULL'.format(joinedFCTable)  with arcpy.da.UpdateCursor(FC_lyr, fields, where) as rows:     for row in rows:         row[0] = row[0] + row[1]

As an aside, I am trying to use the da.UpdateCursor because it is far faster than using field the calculator (code that I already have working) & I am trying to optimize speed.

Thanks,
-Erik

Find more posts tagged with

Accepted answers

ChrisSnyder

where it is working there are the same number of records in the update and join tables (one record in each table). Where it isn't working, the join table only has a few matches in the update table

What you need to do is to add some logic to deal with the situation where there are no matching keys in the lut, although I'm currious since you should be getting an error that says something to the effect of "key error - key not found". Like Matt said, I think you are getting issues with Null values (which in a cursor come out as a value of None.

So for example:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])]) updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"]) for updateRow in updateRows:     joinFieldValue = updateRow[0]     if joinFieldValue in lutDict and joinFieldValue != None:                 updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0]          updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1]     else:         updateRow[1] = -9999         updateRow[2] = -9999     updateRows.updateRow(updateRow) del updateRow, updateRows

All comments

DavidBrett

Thanks for posting this. I needed something similar for the results of the Near Analysis and getting the 'near' attributes back to the original feature without using AddJoin_management.

nearDict = dict([(r[0], (r[1])) for r in arcpy.da.SearchCursor(XFMR, ["OBJECTID","EQUIPMENT_ID"])])


with arcpy.da.UpdateCursor(TIGER_Place_Layer, ["NEAR_FID","EQUIPMENT_ID"]) as cursor:
  for row in cursor:
  joinFieldValue = row[0]
  row[1] = nearDict[joinFieldValue]
  cursor.updateRow(row)
  del cursor, row

ErikMartin

Thanks, Chris... It really is cool how much you can do with Python data structures. I have used dictionaries before, but in much simpler key:value pair format with simple int or string keys, not tuple keys. The way we've been using them here brings it to a whole other level of power (and complexity!).

Thanks again.

ChrisSnyder

Awsome!

BTW: 90% of my Python-specific parlor tricks come from this single page: http://docs.python.org/2/tutorial/datastructures.html

Lists
Tuples
Sets
Dictionaries
List Comprehensions
Filter
Zip
etc.

It's all there...

ErikMartin

Bingo! That was the piece that was missing. Again, many, many thanks to both of you!

ChrisSnyder

where it is working there are the same number of records in the update and join tables (one record in each table). Where it isn't working, the join table only has a few matches in the update table

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])]) updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"]) for updateRow in updateRows:     joinFieldValue = updateRow[0]     if joinFieldValue in lutDict and joinFieldValue != None:                 updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0]          updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1]     else:         updateRow[1] = -9999         updateRow[2] = -9999     updateRows.updateRow(updateRow) del updateRow, updateRows

ErikMartin

Also tried

arcpy.AddMessage(str(joinDict[joinFieldValue][13]))

and

strJoinFieldValue = str(joinFieldValue)
arcpy.AddMessage((joinDict[strJoinFieldValue][13]))

to no avail...

ErikMartin

Every value in the dict throws the same error... can't get it to work.

MathewCoyle

You could try str() for your print messages though that shouldn't be an issue. Do you get this for any record in your dict or does it only pop up after a while?

ErikMartin

Here's a line of it:

{4931: (1466.31859047, 0.7298187853203999, 0.181307540288, 0.0, 0.0, 0.0, 0.0, 0.0, 1.45650000000222, 280800.0, 266454.070051103, 0, 3, 2524.95936472164, 312.0, 1303.0, 214200.0, 72900.0, 0)}

Note that there is only ever 1 key/value pair in the "join" dictionary at a time. In one case where I'm having problems, there is only one record (of many) that it will join to in the "update" table. I still cannot get this working. Later, there is another instance where there are potentially >1 records that the single "join" key would join to. Will cross that bridge when I get to it...

ErikMartin

Here's a line of it:

{4931: (1466.31859047, 0.7298187853203999, 0.181307540288, 0.0, 0.0, 0.0, 0.0, 0.0, 1.45650000000222, 280800.0, 266454.070051103, 0, 3, 2524.95936472164, 312.0, 1303.0, 214200.0, 72900.0, 0)}

MathewCoyle

Can you print out a line of your dict showing the key and values?

ErikMartin

Thanks, Matthew, but no luck. The problem occurs before I even try to make a calculation. So even just this line throws the str/int error:

arcpy.AddMessage(joinDict[joinFieldValue][0])

Adding in int here didn't help (nor would I expect it to).

arcpy.AddMessage(int(joinDict[joinFieldValue][0]))

Again, "joinFieldValue" is correctly populating with the join key (which happens to be an int).

I'll keep circling the problem... thanks for your help.

MathewCoyle

Not sure why you are getting that issue. It may be a case of nulls coming across as an empty string. You can get around this by simply adding int() around each argument when you make your calculations. You could alternatively use float() if there are decimal numbers.

updateRow[1] =  int(updateRow[1]) + int(joinDict[joinFieldValue][0])

ErikMartin

So I'm using this code in a couple places in my script. I have it working successfully two of the times it is used, but two other times it is used, I keep getting an error. I have been through the code character by character and there are no syntax errors -- what works in one place doesn't work in the other place. The different instances of the code all use FCs that are derived from the same source FC -- a simple point layer (subsets are made from this source layer and I'm trying to "join" between these subsets)-- so all of the field types are identical between the occurrences where it is working and where it isn't working. The only difference I can find between the occasions where it is working and where it isn't working is that where it is working there are the same number of records in the update and join tables (one record in each table). Where it isn't working, the join table only has a few matches in the update table (so this would be equivalent to joining and field calculating only on the selected records where ("joinTable.OBJECTID" is not null).

The error I am given is the "cannot concatenate 'str' and 'int' objects". It is thrown whenever I try to access the join table dictionary with this line

updateRow[1] =  updateRow[1] + joinDict[joinFieldValue][0]

or, in fact, any time I try to use: joinDict[joinFieldValue][0]

so even this throws the same concatenation error:

arcpy.AddMessage(joinDict[joinFieldValue][0])

The joinFieldValue is successfully obtained from updateRow[0] and even if I hard code a joinvalue, like:

arcpy.AddMessage(joinDict[4220][0])

I get the same error

Do I need to add a conditional in somehow so that it only tries to access the dictionary if there is a match (is 'none' being returned and being treated as a string?) Is there something else I am missing? Again, I am confident there are no syntax errors and I am not trying to actually concatenate ints and strings.

Thanks for the continued support!
-Erik

ErikMartin

OK, thanks, I'll keep digging and will surely find the problem at some point. Thanks for posting the alternate syntax -- it is definitely much easier to read.

Thanks again,
-Erik

ChrisSnyder

Hmmm.... I might have missed a bracket or something in my example.... Here's an "real" example that I know works:

slopeDict = dict([(r[0], (r[1], r[2], int(r[3] + .5))) for r in arcpy.da.SearchCursor(slopePctZoneStatTbl, ["VALUE","MIN","MAX","MEAN"])])

Another way to write this without so many confounded ([])()([[]]) symbols:

slopeDict = {}
searchRows = arcpy.da.SearchCursor(slopePctZoneStatTbl, ["VALUE","MIN","MAX","MEAN"])
for searchRow in searchRows:
    keyValue = searchRow[0]
    minValue = searchRow[1]
    maxValue = searchRow[2]
    meanValue = int(searchRow[3] + .5)
    slopeDict[keyValue] = (minValue, maxValue, meanValue)
del searchRow, searchRows

BTW: This assumes that your key values are all unique (I should have said that up front).

As for your concatenation issue, this illustration might help:

>>> print "hello" + 1
ERROR!
>>> print "hello" + str(1)
"hello1"
>>> print int("1") + float("1.1")
2.1
>>> print "hello" + "-" + str(1.1234)
hello-1.1234

ErikMartin

I'm having some trouble getting this to work. I'm getting a "'dict' object is not callable error when I use the simple code below:

lutTbl = "New_Shapefile2"
targetTbl = "New_Shapefile"

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["ID","value","value2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["ID","value","value2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

I've tried lots of combinations of square brackets and paraentheses, but to no avail so far... is there some simple syntax i'm missing?

When I use my actual code (which has far more fields, so I'll spare the confusion) I'm actually getting a "can't concatenate 'str' and 'int' objects error. This error does NOT have to do with my field type, but

ChrisSnyder

This makes perfect sense

Practice makes perfect... It took me a while to get proficient with dictonaries (aka "hash tables").

However, once I "got it"... one of the most powerfull tricks I have, and allows for some pretty cool stuff.

Some very basic usefull dictionary methods:

dict = {}
dict[1] = ("cat","Elvis")
dict[2] = ("dog","Barky")

>> len(dict) #how many keys?
2

>>> dict.keys() #dumps the keys out to a list
[2,1]

>>> "monkey" in dict #test for the existence of a certain key
False

#print each key and it's 2nd associated value in the dictionary (2nd value is index 1)
for key in dict:
    print str(key) + " - " + str(dict[key][1])

1 - Elvis
2 - Barky

ErikMartin

Not sure why you would need to make two dictionaries (maybe if you had two look up tables?).

Dictionaries are a great way to store tabular information... with the concept that you have a "key" field and then a bunch of other values that are associated with the key... sounds like a RDBMS table, right?

dict = {}
dict["cat"] = [1, "fish", 5.7654]
dict["dog"] = [3, "garbage", 15.5]

In this case the key is a string "cat", which is associated with a list of values (age, diet, and weight maybe).
Keys must be unique, but can be strings, integers, floats, or even "tuples" such as a value of (1,2). Tuple keys can be extreemly usefull.

You retreive values like this:

#get the diet of "cat"
>>> print dict["cat"][1]
'fish'

Dictionaries are great since they are EXTREEMLY fast to access, but a downside is that their size is limited by how much RAM you have.

Anyway to do math per my earlier example:
lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

This makes perfect sense... thanks to you both!
-Erik

ChrisSnyder

Not sure why you would need to make two dictionaries (maybe if you had two look up tables?).

Dictionaries are a great way to store tabular information... with the concept that you have a "key" field and then a bunch of other values that are associated with the key... sounds like a RDBMS table, right?

dict = {}
dict["cat"] = [1, "fish", 5.7654]
dict["dog"] = [3, "garbage", 15.5]

In this case the key is a string "cat", which is associated with a list of values (age, diet, and weight maybe).
Keys must be unique, but can be strings, integers, floats, or even "tuples" such as a value of (1,2). Tuple keys can be extreemly usefull.

You retreive values like this:

#get the diet of "cat"
>>> print dict["cat"][1]
'fish'

Dictionaries are great since they are EXTREEMLY fast to access, but a downside is that their size is limited by how much RAM you have.

Anyway to do math per my earlier example:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

MathewCoyle

Would I make 2 dictionaries from 2 SearchCursors? One on the update data and a second on the joined data?

I would only look at creating a dictionary of your reference data. You can then cursor through your table you want to update referencing the key in your dictionary with the join field to access the dictionary value. If you don't have much experience with dictionaries I'm sure it is very confusing, but once you try it out they are quite simple.

ErikMartin

Thank you both for your prompt responses. These examples are very helpful. I've been able to get this working to update field values using joined values, but my understanding of dictionaries is pretty rudimentary at this point... Do either of you have any hints or example of how to do some simple arithmetic? My two calculations are simply

updateValue = joinValue + updateValue

and

updateValue = joinValue * updateValue

I have yet to get the syntax right using the dictionary method. Thanks,
-Erik

Would I make 2 dictionaries from 2 SearchCursors? One on the update data and a second on the joined data?

ErikMartin

Thank you both for your prompt responses. These examples are very helpful. I've been able to get this working to update field values using joined values, but my understanding of dictionaries is pretty rudimentary at this point... Do either of you have any hints or example of how to do some simple arithmetic? My two calculations are simply

updateValue = joinValue + updateValue

and

updateValue = joinValue * updateValue

I have yet to get the syntax right using the dictionary method. Thanks,
-Erik

ChrisSnyder

Using a dictionary and an update cursor in tandem is the solution.... It's actually pretty straight forward:

A basic example in v10.1 syntax that "joins" two fields from the lutTbl to the targetTbl:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = lutDict[joinFieldValue][0]
    updateRow[2] = lutDict[joinFieldValue][1]
    updateRows.updateRow(updateRow)
del updateRow, updateRows

MathewCoyle

I've had nothing but problems trying to update fields in a joined table. I would recommend reading this post and examining the attached scripts for ideas on how to get around these issues.

http://forums.arcgis.com/threads/71572-Using-Python-Dictionaries-with-Update-Cursors-instead-of-quot-Join-and-Calculate-quot

I also have a post from a while back using similar methodology in a more specific case example.

http://forums.arcgis.com/threads/55099-Update-cursor-with-joined-tables-work-around-w-dictionaries

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.