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

4548
12
Jump to solution
06-19-2015 06:57 AM
AnthonyAtkins1
New Contributor II

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!"

Reply
0 Kudos
1 Solution

Accepted Solutions
IanMurray
Frequent Contributor

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

View solution in original post

12 Replies
IanMurray
Frequent Contributor

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.

Reply
0 Kudos
AnthonyAtkins1
New Contributor II

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.

Reply
0 Kudos
WesMiller
Regular Contributor III

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

  1. Make table view
  2. Add join
  3. Calculate field
Reply
0 Kudos
AnthonyAtkins1
New Contributor II

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!

Reply
0 Kudos
IanMurray
Frequent Contributor

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

View solution in original post

AnthonyAtkins1
New Contributor II

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!

Reply
0 Kudos
IanMurray
Frequent Contributor

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. 

Reply
0 Kudos
JamesCrandall
MVP Frequent Contributor

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

Reply
0 Kudos
AnthonyAtkins1
New Contributor II

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

Reply
0 Kudos