3

I am attempting to join 2 tables together, and the tools in arcmap, such as add field can't do what I want so I tried using python and arcpy. My approach is to use 2 nested loops and find the values in the 2nd table and update the first one. This is the code that I am using:

with arcpy.da.UpdateCursor(table1, ufield) as ucursor:
for urow in ucursor:
    print(urow[0])
    with arcpy.da.SearchCursor(table2, sfield, """"VALUE" = """ + str(urow[0])) as scursor:
        for srow in scursor:
            print(srow)
            urow[1] = srow[1]
            urow[2] = srow[2]
            urow[3] = srow[3]
            urow[4] = srow[4]
            urow[5] = srow[5]
            urow[6] = srow[6]
            urow[7] = srow[7]
            urow[8] = srow[8]
            urow[9] = srow[9]
            urow[10] = srow[10]
            urow[11] = srow[11]
            urow[12] = srow[12]
            urow[13] = srow[13]
            urow[14] = srow[14]
            urow[15] = srow[15]
            urow[16] = srow[16]
            urow[17] = srow[17]
            urow[18] = srow[18]
            urow[19] = srow[19]
            urow[20] = srow[20]
            ucursor.updateRow(urow)
            n+=1
    print(urow)

The problem is that it is extremely slow, so I would like to speed it up by stopping the search cursor loop after it finds the result. The values are all unique so after it returns a result, there is no need for the loop to keep on going. That was my thought on speeding it up, but of course I am open to other suggestions, or approaches that could help me achieve my goal.

  • 2
    I think the way to do this is by using a single dictionary comprehension on your search cursor, and then reading values from the dictionary that creates in your update cursor. That way access is almost instant and there is no need to think about breaking out of loops. The technique is covered under "A dictionary based solution" at gis.stackexchange.com/a/182841/115, and probably in a couple of other Q&As on this site. – PolyGeo Sep 1 '16 at 7:58
  • 1
    Also, when you say "the tools in arcmap, such as add field can't do what I want", did you try the Join Field tool? – PolyGeo Sep 1 '16 at 8:10
  • The dictionary seems to bee exactly what I am looking. I have tried Join field but it throws a runtime error so I just took the python approach without trying to fix it. – Cristian Iosub Sep 1 '16 at 9:06
  • What is the error? what you are trying to achieve is fairly simple and a join field would be the perfect tool for this, no need to write python. I would investigate what is causing the run time error before continuing down this road. Maybe the data you have is not as perfect as you think it is and needs some sort of cleaning up? – Hornbydd Sep 1 '16 at 9:13
  • 1
    @CristianIosub If you have a solution, post it here as an answer – Midavalo Sep 1 '16 at 18:19
2

Based on @PolyGeo's answer I used dictionaries which improved the time significantly. I first ran a loop that took all values in the first table and put them in a dictionary and then updated the second table using the dictionary.

value_dict = dict()
    with arcpy.da.SearchCursor(table2, sfield) as scursor:
        for srow in scursor:
            a_id = srow[0]
            b_value = srow[1]
            value_dict[a_id] = b_value
    print("Dictionary created")
    with arcpy.da.UpdateCursor(table1, field2) as ucursor:
        for urow in ucursor:
            urow[1] = value_dict[urow[0]]
            ucursor.updateRow(urow)
            print(urow)
    print("column " + field + " was added to the table")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.