topic Model Builder calculate field expression in Python Questions

Model Builder calculate field expression

2Quiker — Thu, 17 Mar 2022 14:56:00 GMT

I have a model that I would like to use the the following code to update a field "Permits" based on how many text occurrences are in "ParcelNum1" field based on the "PermitNum" field. I get the following error and need help figuring out what the issues is and all fields are text fields. I need this in model builder.

Example

OBJECTID	PermitNum	ParcelNum1	Permits
1	aa1123	12345	12345, 12346
2	aa1123	12346
3	BB3361	67891	67891, 67892, 37893
4	BB3361	67892
5	BB3361	67893
6	UU9832	11123	11123
7	PP2225	6989	6988, 6989
8	PP2225	6988

Pre-Logic Script code:

dict1 = dict() with arcpy.da.SearchCursor('TableTest6',['ParcelNum1','Permit_Count','PermitNum']) as cursor: for row in cursor: if row[0] not in (None, "", " "): dict1.setdefault(row[0],[]).append(str(row[2])) def concat(nf): x = None if nf in dict1t: x = ",".join(dict1[nf]) return x

Permits = conact(!ParcelNum1! )

Error.

ERROR 000539: Runtime error
Traceback (most recent call last):
File "<string>", line 2, in <module>
RuntimeError: cannot open 'TableTest6'

Failed to execute (Calculate Field (4)).

Re: Model Builder calculate field expression

JakeSkinner — Wed, 16 Mar 2022 18:49:12 GMT

@2Quiker,

Does it matter if the Permits field is populated for all rows? Ex:

If not, you could do something like below:

dict = {} with arcpy.da.SearchCursor(table, ['PermitNum1', 'ParcelNum']) as cursor: try: for row in cursor: dict.setdefault(str(row[0]), []) dict[str(row[0])].append(str(row[1])) except: pass del cursor with arcpy.da.UpdateCursor(table, ['PermitNum1', 'Permits']) as cursor: try: for row in cursor: permitsList = dict[row[0]] permits = ', '.join(permitsList) row[1] = permits cursor.updateRow(row) except: pass del cursor

Re: Model Builder calculate field expression

2Quiker — Wed, 16 Mar 2022 20:42:21 GMT

Thanks for the response Jake but I was trying to this in Model Build with Calculate field.

Re: Model Builder calculate field expression

JakeSkinner — Wed, 16 Mar 2022 21:23:15 GMT

You can add this logic to the Calculate Field tool:

Or you can add a script to a model:

https://desktop.arcgis.com/en/arcmap/latest/analyze/modelbuilder/integrating-scripts-within-a-model.htm

Re: Model Builder calculate field expression

RhettZufelt — Wed, 16 Mar 2022 22:05:24 GMT

Are you running the entire model, or just the Calculate Field (4)? Have you tried to re-validate and run the entire model again?

The error says cannot open 'TableTest6' which is output from Add Field (3).

If these are temp tables, they do not persist across sessions, and often you need to re-run the tool(s) that generate the input (TableTest6) or it won't find them.

Re: Model Builder calculate field expression

2Quiker — Wed, 16 Mar 2022 23:00:31 GMT

I have re-validated and re-ran the model.

What do you mean by re-run the tool(s) that generated the input, example ?

Re: Model Builder calculate field expression

RhettZufelt — Wed, 16 Mar 2022 23:32:34 GMT

re-validate and re-run will do it.

Just meant to make sure that Add Field (3) runs right before the Calculate Field (4) runs to ensure that TableTest6 exists.

In the image, TableTest6 is the last one that shows a shadow behind it, meaning it has "ran". If you were to save that model at that state, then open it up later, it will still show the shadow, but TableTest6 wouldn't exist (if it is temp table) until the tool that creates it is ran again.

Do you see it put TableTest6 in the TOC?

Re: Model Builder calculate field expression

RhettZufelt — Wed, 16 Mar 2022 23:50:06 GMT

Upon closer inspection, noticed a few other things that might be causing issues.

You have a def called "concat(nf)", but the Expression block says "conact(!ParcelNum1!)" which is spelled incorrectly, so shouldn't even run that def. (also spelled this way in the search cursor which could cause it to not open TableTest6 if the field in the cursor doesn't exist)

also, sending !ParcelNum1! to it ( conact(!ParcelNum1! )), which, according to the table you supplied doesn't exist. It says ParcelNum.

also, !ParcelNum! is a field getting passed to the def as "nf", but I see no iterator to go through the rows of that field to evaluate it. I think it is the dictionary you are wanting to iterate through, not a field. Might need something like:

for key in dict1: x = ",".join(dict1[key])

And, I've never put stuff in the Code Block outside of a def before, so not sure if that would even run (though, the error tends to suggest it is). I would move all of the code within the def to ensure it runs, and in the order expected.

Re: Model Builder calculate field expression

2Quiker — Thu, 17 Mar 2022 15:45:22 GMT

Thank you for the replay, the example table I posted was incorrect and I have corrected it. I have verified that the fields are in the table and the code. The fields are 'PermitNum','ParcelNum1' & 'Permits". I have struggled with calculate fields calculations so I apologize if I am not understanding correctly. I have re-validated the entire model and re-ran it. This is in ArcCatolog and not in ArcMap or Pro.

I have the following based on comments but I am still getting the error.

def update(): dict1 = dict() with arcpy.da.SearchCursor("TableTest6",['ParcelNum1','Permit_Count','PermitNum']) as cursor: for row in cursor: if row[0] not in (None, "", " "): dict1.setdefault(row[0],[]).append(str(row[2])) if key in dict1t: x = ",".join(dict1[key]) return x

Error:

ERROR 000539: Error running expression: update()
Traceback (most recent call last):
File "<expression>", line 1, in <module>
File "<string>", line 3, in update
RuntimeError: cannot open 'TableTest6'

Failed to execute (Calculate Field (5)).

Re: Model Builder calculate field expression

2Quiker — Thu, 17 Mar 2022 21:06:17 GMT

This did technically work but only if I add the Table path to the code,

Table = "C:\temp\temp.gdb\table"

with arcpy.da.SearchCursor(Table,['ParcelNum1','Permit_Count','PermitNum']) as cursor:

Does not work with the Add Field output "TableTest6".

with arcpy.da.SearchCursor("TableTest6",['ParcelNum1','Permit_Count','PermitNum']) as cursor:

Re: Model Builder calculate field expression

RhettZufelt — Thu, 17 Mar 2022 21:13:10 GMT

Funny, I thought I posted the below post earlier, but was still sitting as draft....

Still having issues with TableTest6.
have you tried hard coding the path to that table?

with arcpy.da.SearchCursor(r'C:\path2table\TableTest6',['ParcelNum1','Permit_Count','PermitNum']) as cursor:

You can open the python window from Catalog and enter each command one at a time.
Can print individual variable results, etc. This can often give you more info on where it is failing.
R_

Re: Model Builder calculate field expression

RhettZufelt — Thu, 17 Mar 2022 21:29:13 GMT

Documentation for model builder says that items in the drop-down (TableTest6) is a variable.

You could try putting it in the cursor as a variable instead of text:

with arcpy.da.SearchCursor(TableTest6,['ParcelNum1','Permit_Count','PermitNum']) as cursor:

and see if that works.

Otherwise, just hardcode the path and you are don.