How to update a feature's attributes via a CSV file with Python

[AnthonyAtkins1]

I have a CSV file that contain fields that denote, account number, a path to a tiff image and type of account (sewer, water, etc). I'd like to update my Account feature class with the path of the tiff file (for hyperlinking) for matching accounts. At first this sounded pretty simple, just use a search cursor and an update cursor within nested for loops and tada...feature updated!  Well for some reason it's not working and I don't know why. I've tried using nested for loops, I've tried creating the cursors via with statements and i can't get it to work.

here's the portion of the code I'm having issue with:

searchCSV = arcpy.da.SearchCursor(outputcsvlocation2, ["AccountNumber","Path","Type"])
updateAccount = arcpy.da.UpdateCursor(fc, ["AcctNum","B_SCAN", "W_SCAN", "S_SCAN", "ST_SCAN"])

for row in searchCSV:
    for line in updateAccount:
        if row[0] == line[0]:
            print"This is where I'd update the feature!"

To keep things simple, I've been using smaller datasets; my csv file only has two records, with account numbers 101010000 & 101011002 and my feature class only has five features with account numbers: 101010000, 101000100, 101011000, 101011001, 101011002.

Following my logic, my print statement should have printed twice, but it didn't print at all?  I know my issue is some sort of logic, but I can't quite see it. please help!

fyi, I also tried this:

with arcpy.da.SearchCursor(outputcsvlocation2, ["AccountNumber","Path","Type"]) as searchCSV:
    with arcpy.da.UpdateCursor(fc, ["AcctNum","B_SCAN", "W_SCAN", "S_SCAN", "ST_SCAN"]) as updateAccount:
        for row in searchCSV:
            for line in updateAccount:
                if row[0] == line[0]:
                    print"This is where I'd update the feature!"

[IanMurray]

I would look at see what types your values are.  I'm guessing that from your fc, the values are all numbers(check the field type), but the csv is likely treating everything as a string, not integers like the fc field values.  Try using the int(line[0]) and see if that changes anything.

[AnthonyAtkins1]

Ian, thanks for your reply.  I do believe that was part of the issue as the attribute in the csv file was being viewed as unicode while the attribute int he feature was being viewed as an Integer. So adding the int(line[0]) allows the code into the if statement, it is only doing so once.  This is something I noticed when I was running it on the entire dataset, that the outer for loop isn't looping. after the first feature is tested and the inner loop loops, the program stops.  So after adding the int() function, I only get the print statement to print once.

[WesMiller]

If your csv file has firld names in it you could:

1. Make table view
2. Add join
3. Calculate field

[AnthonyAtkins1]

Thanks Wes for your reply.  The issue with that is two fold; one, there could be multiple files in the CSV file for only one in the account feature.  Two, I need to update a separate hyperlink field based on the type of account, so if it's a sewer account, I need to update the "SCAN_S" field or if it's a Water account, I need to update the "SCAN_W" field.  There's a chance that an account can have both a water and sewer scan so I'd need to update both. If the join idea was viable, I'd be able to set up the logic in my calculate field, but since this is going to be a reoccurring task, I thought building a script might be best.

Thanks!

[IanMurray · Accepted solution]

Nested Cursors can be a bit finicky, and fairly slow to boot.  Richard Fairhurst​ has a pretty good blog about using dictionaries with cursors instead of nesting cursors.  May be better for what you are trying to do.

Turbo Charging Data Manipulation with Python Cursors and Dictionaries

[AnthonyAtkins1]

Thank you so much for posting this! My issue was a combination of the nested cursors and comparing different data types (thanks for pointing that out also!)

I'm still pretty new to Python, but I've been programming in several different languages for quite some time now and I was afraid it was going to be something like the format of my for loops (something basic)

Well the code I finally got to work looks like this:

valueList = ["AccountNumber", "Path", "Type"]
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(outputcsvlocation, valueList)}
count = 1
with arcpy.da.UpdateCursor(fc, ["AcctNum","B_SCAN", "W_SCAN", "S_SCAN", "ST_SCAN"]) as updateAccount:    
    for row in updateAccount:
        keyValue = row[0]
        if str(keyValue) in valueDict:            
            print "How many matches? " + str(count)
            count = count + 1
del valueDict

The counter is to get a rough idea of how many features will end up with images and compare that to how many are currently located in the feature.

Thanks again everyone for your help!

[IanMurray]

Glad you found a working solution.  To be honest, I've always been a bit intimidated by dictionaries, so I don't use them enough, but I know from being on this forum for a while how useful they can be.  Really Richard is the one you should be thanking for posting such excellent help for using dictionaries with cursors for data access and manipulation.  If I got to script more often, I'd probably use dictionaries more often, but since I don't get to in my current line of work I make python work for what I do. 

[JamesCrandall]

It would help if you posted a sample of the csv and the Feature Class attributes.

[AnthonyAtkins1]

I've attached a sample file for both!  Thank you very much for looking into this!