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; } } }

joined tables in arcpy and calculate fields

SaM1

Hello!

I need to join two feature classes based on the ID attribute and calculate fields in the feature layer in which I have joined the table. I created a model in model builder and export it into a python script. It takes too long to complete the script as there are 20 fields in which the script is running filed calculation tool. I am looking for some workaround to accomplish this task quickly and more efficiently. I am running this script in the SDE version.

I would greatly appreciate any help!

Thanks,

SAM

Encuentra más publicaciones etiquetadas con

Accepted answers

Anonymous User

SaM1,

I don't see any SQL in your code so its coming from somewhere else. The code is just boilerplate, so you will have to adjust it depending on what you need. There's comments that explain what each line is doing.

urow[listofFields.index('T1')] = urow[listofFields.index('S1')] is duplicate/ not doing anything. I provided that as example for if you need to calculate a field with more steps rather than a simple a=b, which the loop/range is taking care of. That line is for an example of more complex actions, like a=b+c/w+ds.

All comments

Anonymous User

SaM1,

SaM1

Thank you very much!! I am able to put my values in option 3, but I get this error when I run code Runtime error
Traceback (most recent call last):
File "<string>", line 2, in <module>
RuntimeError: An invalid SQL statement was used.

here is my code below:

import arcpy

from arcpy import env

targetFC = "MYTABLE"
sourceFC = "SLOWTABLE"

targetFieldslist = ['T1','T2','T3','T4','T5','T6','T7','T8','T9','T10','T11','T12','T13','T14','T15','T16','T17','T18','T19','T20']

sourceFieldlist = ['S1','S2','S3','S4','S5','S6','S7','S8','9','S10','S11','S12','S13','S14','S15','S16','S17','S18','S19','S20']

##Combining field lists

listofFields = targetFieldslist + sourceFieldlist

with arcpy.da.UpdateCursor(targetFC,listofFields) as uCur:
for urow in uCur:
for i in range(0,20):
urow[i] = urow[i + 20]
urow[listofFields.index('T1')] = urow[listofFields.index('S1')]
urow.updateRow(row)

I really appreciate your help!

thanks!

Anonymous User

You'll have to fill in the parameters such as the fields, the field name that you are joining on, and inputs to match your code, but these two should give you an idea of the process.

# option 2:
listofFieldsToUpdate = ['field1', 'field2', 'field3', ...] # your fields you want updated including the join field name
listofSourceFields = ['source1', 'source2', 'source3', ...] # your fields that have values, paired in order to the listofFieldsToUpdate the join field name

mappedFields = dict(zip(listofFieldsToUpdate, listofSourceFields)) # Dictionary of dest : source fields for mapping i.e. {'field1' : 'source1', 'field2' : 'source2', ...}
# You could just code the dictionary then use dict.keys() and dict.values() as the fields for the two respective cursors.

with arcpy.da.UpdateCursor(baseFC, listofFieldsToUpdate) as uCur:
    for urow in uCur:
        # get the values from the source table using where_clause
        sql = f'''JoinFieldname = {row[listofFieldsToUpdate.index('JoinFieldname')]}'''
        fieldvalDict = {}

        with arcpy.da.SearchCursor(sourceFC, listofSourceFields, sql) as sCur:
            for row in sCur:
                # populate values to dictionary
                fieldvalDict = {k: v for k, v in zip(sCur.fields, row)}

        # check if one result exists and update values
        if len(fieldvalDict) == 1:
            # set the field value by iterating over the field list, getting the mapped source field as the key to get the value from
            # fieldvalDict.
            for fld in listofFieldsToUpdate:
                row[uCur.fields.index(fld)] = fieldvalDict[mappedFields[fld]]

            # perform any additional calcs here by referencing specific fields:
            row[uCur.fields.index('field3')] = fieldvalDict['source2'] + fieldvalDict['source1']

            # update the values
            urow.updateRow(row)

        # notify if there is more than one match for the join field
        elif len(fieldvalDict) > 1:
            arcpy.AddMessage(f'multiple values were found for {row[listofFieldsToUpdate.index("JoinFieldname")]}')

        # notify if join field value is not found in source data
        else:
            arcpy.AddMessage('No value found')

# option 3:
#
listofFieldsToUpdate = ['field1', 'field2', 'field3', 'field4', 'field5', 'field6', 'field7', 'field8', 'field9', 'field10', 'field11', 'field12', 'field13', 'field14', 'field15', 'field16', 'field17', 'field18', 'field19', 'field20'] # your fields ou want to update.
listofSourceFields = ['source1', 'source2', 'source3', 'source4', 'source5', 'source6', 'source7', 'source8', 'source9', 'source10', 'source11', 'source12', 'source13', 'source14', 'source15', 'source16', 'source17', 'source18', 'source19', 'source20'] # your fields that have the desired values, paired in position to the cooresponding field in the listofFieldsToUpdate list. Could possibly use arcpy.ListFields() if feasible to create this.

listofFields = listofFieldsToUpdate + listofSourceFields # combined list of fields, destination set - then source set

with arcpy.da.UpdateCursor(baseFC, listofFields) as uCur:
    for urow in uCur:
        # assign the values using a range, offsetting the source fields by however many you need so they match.
        for i in range(0, 20):
            urow[i] = urow[i + 20]

        # update any fields here that are more than just simple assignment.
        urow[listofFields.index('fieldyouwanttoupdate')] = urow[listofFields.index('source1')] + urow[listofFields.index('source2')]
        urow.updateRow(row)

SaM1

I'd like to try the 2nd and 3rd options. Can you help me setting the update/search cursor? I have not tried it before. I greatly appreciate your help!

Thanks,

SAM1

Anonymous User

I've always found that Addjoin's datasets are just slow to work with. 20 fields isn't that much. I don't know if it is because it re-writes the whole table when something changes (like when a field is deleted), but there is a few options of different methods to get the same result.

1. Try copying the joined table to a temp layer in memory, do your calculations there, replace the original base table values.

2. Don't join at all and use a search cursor/ update cursor combination to lookup the values in the would-be joined-table on the join field.

3. Try an update cursor instead of the field calc on the joined table.

If either of these are feasible, I can provide code examples to get you going.

SaM1

Hi JeffK,

Here is my code below:

# Import arcpy module
import arcpy

# Local variables:
myTable = "MYTABLE"
slowTable = "SLOWTABLE"

# Process: Add Join
arcpy.AddJoin_management(myTable, "PN_ID", slowTable, "PN_ID", "KEEP_COMMON")

# Process: Calculate Field
arcpy.CalculateField_management(myTable_Joined, "ABQ.DBO.MYTABLE.CREATE_FME", "[SLOWTABLE.CREATEFMEID]", "VB", "")

# Process: Calculate Field (2)
arcpy.CalculateField_management(myTable_Joined, "ABQ.DBO.MYTABLE.NUMBEROFTHREADS", "[SLOWTABLE.THREADSCOUNT]","VB","")

There are 18 more fields for which i have to do field calculation. I am overwriting attributes from joined table to my main table.

Anonymous User

Share your script and we can provide better help. Are you running calculations on each of the 20 fields, or using the 20 fields to calculate another field(s)?

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

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