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

Compare field values from 2 different tables, Locate value from table 2 in that row, use UpdateCursor to Update field in table 1

MPach

I'm kind of new to this, but kind of not. I just don't use it as often as I would like in some cases. With that out of the way here's what I'm attempting to do.

I have two tables that I am working with 1) Manhole Table 2) Gravity Mains Table. The important rows for this script are in the manhole table: INVERTELEV (invert elevation) and FACILITYID (an identifier). From the Gravity Mains table there are 2 important rows: FROMMH (this will be a manhole Facility ID #) and UPSTREAMIN (the upstream invert of the gravity main).

What I am attempting to do is with all the Gravity Main FROMMH values in a list take each value from that list and compare it to a value in the Manhole table FACILITYID field until I find a match. When I find a matching value I want to copy the value in the INVERTELEV field from the manhole table and update the UPSTREAMIN value in the Gravity Mains table. It seems simple enough, but I think I'm making this more complicated than it has to be and I'm confusing myself with nested loops.

I've attach a python file that I'm working on with some notes. Any help on how to do this would be greatly appreciated.

FYI, one thing I am kind of unclear about is with the Update cursor. Does this UpdateCursor object update the data at the current position, meaning the current row it's working on. What exactly does that mean? Does it mean that if I find the row with the matching FACILITYID and FROMMH that when I go to use the row.SetValue() method it will update that row. That's what it sounded like when I read it, but I'm just trying to clarify. I know I could probably setup a sample to test this, but I figured I might as well ask while I'm posting this request for help.

Thanks

SentToCommunityForHelp.zip

JoshuaClifford1_8-1712935178001.png

Encuentra más publicaciones etiquetadas con

Accepted answers

Anonymous User

I would think about creating dictionaries of values from the two tables first, and then perform your match operation on dictionaries to get one list (could be a list of tuples, or a dictionary) of values. Then open one cursor per table to update destination tables using that list/dictionary value in a sql query within your update cursor so its updating that row's value. I might have got things mixed in the example code, but hopefully it will give you an idea.

#Read mainsList one at a time and search for the value in the MH table. Then update 
for row in cursorWriteValue:
    FIDMHRowValue = (row.getValue(fieldInMHTable))
    MHFacIDList.append(FIDMHRowValue)

for FacID in mainFacIdList:
    if FacID in MHFacIDList:  
       sql = """FACILITYID = FROMMH value (FacID)?"""
       with arcpy.UpdateCursor(updateFieldInMainTable, [ UPSTREAMIN], sql) as cur:
            for row in cur:
               row[0] = FacID[1] #value from the tuple or dictionary, however you want to do it.
               cur.updateRow()

JoeBorgione

+1 on the dictionary approach suggested by @Anonymous User . I keep this as a bookmark and refer back to it all the time:

https://community.esri.com/t5/python-blog/turbo-charging-data-manipulation-with-python-cursors-and/ba-p/884079

All comments

Anonymous User

#Read mainsList one at a time and search for the value in the MH table. Then update 
for row in cursorWriteValue:
    FIDMHRowValue = (row.getValue(fieldInMHTable))
    MHFacIDList.append(FIDMHRowValue)

for FacID in mainFacIdList:
    if FacID in MHFacIDList:  
       sql = """FACILITYID = FROMMH value (FacID)?"""
       with arcpy.UpdateCursor(updateFieldInMainTable, [ UPSTREAMIN], sql) as cur:
            for row in cur:
               row[0] = FacID[1] #value from the tuple or dictionary, however you want to do it.
               cur.updateRow()

DavidPike

import arcpy

#set workspace to folder of shapefiles or geodatabase
arcpy.env.workspace = r"C:\Users\MarkPa\Desktop\GIS_Data\Projects\Sewer\Harmon\OutfallStats\Depth of mains" 


gravity_main_fc = r"C:\Users\MarkPa\Desktop\GIS_Data\Projects\Sewer\Harmon\OutfallStats\Depth of mains\GravityMainTestSet.shp" 
manhole_fc = r"C:\Users\MarkPa\Desktop\GIS_Data\Projects\Sewer\Harmon\OutfallStats\Depth of mains\MHTestSet.shp"


#create a list of lists, [[facility id, invert elev], [facility id, invert elev] ...]
manhole_list = []

#run a cursor to append to the manhole_list
#row is a tuple of values for each row within the fields specified in the cursor
#row[0] is facility id, row[1] is invertelev
with arcpy.da.SearchCursor(manhole_fc, ["FACILITYID", "INVERTELEV"]) as cursor:
    for row in cursor:
        manhole_list.append([row[0], row[1])


#update gravity main fc
#row[0] is from mh, row[1] is upstream in
with arcpy.da.UpdateCursor(gravity_main_fc, ["FROMMH", "UPTREAMIN"]) as cursor:
    for row in cursor:
        for item in manhole_fc:
            if row[0] == item[0]:
                row[1] = item[1]

        cursor.updateRow(row)

JoeBorgione

+1 on the dictionary approach suggested by @Anonymous User . I keep this as a bookmark and refer back to it all the time:

https://community.esri.com/t5/python-blog/turbo-charging-data-manipulation-with-python-cursors-and/ba-p/884079

MPach

@JoeBorgione Oh man, thank you for that. After reading @RichardFairhurst article I realized that I don't even need to write a python script, but it might help out a bit. Why didn't I just think to use a join and make one fields values equal to the other, duh. I knew I was over complicating things. That always seems to be a thing for me. I was looking for more difficult solution instead of the one that was right in front of me. Probably just because I really do need to start using Python more again, it's fun.

Anyway, I appreciate the responses and I will try both of those approaches @Anonymous User and @DavidPike . Thank you both very much.

Mark

JoeBorgione

I knew I was over complicating things. That always seems to be a thing for me.

I thought I had a corner on that market...

That's the beauty of this forum; enough of us have forehead bruises from banging our heads against a screen and like to keep others from doing the same!

MPach

That's why I posted. I was done banging my head, lol. 🤣

DavidPike

I've removed my post as a solution as I think the dictionary method is much better. Why I thought looping through a list each time to find matching values is pretty unforgivable.

Anonymous User

Glad it helped and sorry for not leaving a better example using dictionaries. I planned to come back with some sample code that I use in my scripts but couldn't make it in time. After I saw that @JoeBorgione shared the link to some awesome examples, mine would just be redundant.

I don't think anyone is scoffing at the list iteration idea, at least they better not be. Lists may be easier for some to follow, dictionaries easier for others. If the author can understand it and it produces the same result, I think its a valid solution. I'll be honest and admit it took me a while to grasp working with dictionaries. Once I got it though, there were quite a few scripts that were updated.

DanDeegan

Your article changed how I approach most things in python. I use them to store field details and later when adding fields. I don't use joins. More fun overall.

MPach

Awesome, Glad my thought blanking could help someone. That's why this community is here. Cheers!

JoeBorgione

Post your code rather than attaching a zip file: click on the three dots to expand the menu tool bar, select the </> icon (insert code) and choose python. That way several sets of eyes can take a look at it...

JoeBorgione

Here is my basic approach to using a dictionary and an update cursor:

import arcpy

target = r'C:\path\to\the\table_to\update'
upDateFields = ['CommonKey', 'FieldN']

source = r'C:\path\to\the\data_that_has\what_target_needs'
sourceFields = ['CommonKey', 'Field1']
sourceDict = {r[0]:(r[1]) for r in arcpy.da.SearchCursor(source,sourceFields)}

with arcpy.da.UpdateCursor(target,updateFields) as updateRows:
        for updateRow in updateRows:
           keyValue = updateRow[0]
           if keyValue in sourceDict :
               updateRow[1] = sourceDict[keyValue]
               updateRows.updateRow(updateRow)
           else:
                pass

Having poured through the script for a minute or two, it seems like you are over thinking it a bit. Not a criticism, just an observation. The basic premise is to create a dictionary of the source data using the common key between the source and the target as the key in the dictionary, and then the associated value in that dictionary is what the target gets updated with.

Lets say the common key between the source and the target is a field called FacilityID . Using dictionary comprehension create a the dictionary with a search cursor as shown above and it looks something like this:

{1:'value1',
 2:'value2',
 3:'value3'}

All you need to do then is look and see if the dictionary key appears in the target, and then used the associated value with that key to update the target field.

A row in a search cursor or update cursor is in the format of a tuple so the first element is row[0].

Make sense?

Ú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.