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

Concatenate Multi-Values from Single Field and Insert to Single Cell

EricMahaffey1

I'm building a geoprocessing model that iterates through features in one f-class and spatially selects features from another f-class that are within a certain distance of each feature. I want to pull values from one f-class to the other. The model essentially does a select features by location, and from that selection I would like to pull values within a single field, concatenate them together as a string and insert that value into the other f-class field in a single cell. I believe that I need to use some cursors within ArcPy to do this (SearchCursor & InsertCursor) and embed the Python script within the model, but I'm not entirely sure. So basically Table1 FIELD_A has two values "1234" and "5678". I want to take those two values and insert them into a single cell within Table2 FIELD_B so that the value looks something like "Value 1: 1234 to Value 2: 5678". Any assistance would be appreciated.

Find more posts tagged with

Accepted answers

All comments

EricMahaffey1

I finally figured it out, and sharing the code in case anyone runs into a similar situation and needs some sample code to get them started

import arcpy

# Local variables:
River_Section = "C:\<path to your GDB f-class>"
Adjacent_MileMarkers = "C:\<path to your GDB f-class>"

scursor = [row[0] for row in arcpy.da.SearchCursor(Adjacent_MileMarkers, ("MILE_TEXT"))]
print "List Comprehension value is: " + str(scursor)

i=0
ucursor = arcpy.da.UpdateCursor(River_Section, ["MILE_MARKERS"])

for row in ucursor:      
    row[0] = str('Mile Marker:' + scursor[i] + ' to Mile Marker:' + scursor[i+1])
    print row[0]
    ucursor.updateRow(row)

print "Script Complete"

Anonymous User

The best thing to do here is use the **bleep** and see what your variables values are. There is two indexes in that statement. x[1] referring to the value in the 'MILE_TEXT' field, and the List index at the end [0]. If there is nothing returned by that cursor, the x[1] will throw the index out of range. If there is nothing from the list comprehension, then the [0] will throw the index out of range. Hard to say which one is failing.

However, if you are going by the -1, mile why not just subtract one from the current mile, and skip the searchcursor that is getting the current mile - 1? would that give you what you want? [I added that part incase the miles were not sequential and you needed to go by the sequence of the row Id's.]

EricMahaffey1

Jeff,

I've now realized that I needed to switch out the InsertSursor with UpdateCursor because I'm trying to put the values into an existing feature's MILE_MARKERS value. Using your last code sample I made that change along with some other minor tweaks, and got the correct value to populate the cell "Value 1: 336 Value 2: 337". I had to switch from using the OBJECTID field to a MILE integer field in the table in order to capture the values in ascending order. This is so that the value shows a range in mile markers. So the code runs, but then throws an error:

"previousMT = [x[1] for x in arcpy.da.SearchCursor(Adjacent_MileMarkers, ['MILE', 'MILE_TEXT'], wc)][0]
IndexError: list index out of range"

with arcpy.da.SearchCursor(Adjacent_MileMarkers, ['MILE', 'MILE_TEXT']) as S_cursor:
    for row in S_cursor:
        # Use your lowest ID here because that will be your floor
        if row[0] != 1:
            # Create your filter to get the previous value using the current Id - 1
            wc = """MILE = {}""".format(row[0] - 1)
            #wc = """OBJECTID = {}""".format(row[0] - 1)
            # get the previous Mile_Text value by filtering the for Id -1
            previousMT = [x[1] for x in arcpy.da.SearchCursor(Adjacent_MileMarkers, ['MILE', 'MILE_TEXT'], wc)][0]
            resString = 'Value 1: {} Value 2: {}'.format(previousMT, row[1])
            print "Initial resString is: " + (resString)
        else:
            # if the incoming selection is your floor, there will not be any previous 'MILE_TEXT'.
            resString = 'Value 1: {}'.format(row[1])
            print "else resString is: " + (resString)
        # update target FC
        with arcpy.da.UpdateCursor(River_Section, 'MILE_MARKERS') as U_cursor:
            for row in U_cursor:
                U_cursor.updateRow([resString])

I think I need to end it after one pass somehow.

Anonymous User

I think we can sum this up a little, now that we figured out the data structure and worked through the cursors.

This script will accept the selection you pass into it, and uses another cursor to get the previous mile_text by filtering the selections Adjacent_MileMarkers row ID -1. This relies on the previous value needed being the previous row Id in the table and not your previous selections mile_text.

If your selection's Id is 1 (or your lowest), there will not be any previous value to get so that selection should default to inserting just Value 1.

with arcpy.da.SearchCursor(Adjecent_MileMarkers, ['OBJECTID', 'MILE_TEXT']) as cur:
    for row in cur:
        # Use your lowest ID here because that will be your floor
        if row[0] != 1:
            # Create your filter to get the previous value using the current Id - 1
            wc = """OBJECTID = {}""".format(row[0] - 1)
            # get the previous Mile_Text value by filtering the for Id -1
            previousMT = [x[1] for x in arcpy.da.SearchCursor(Adjecent_MileMarkers, ['OBJECTID', 'MILE_TEXT'], wc)][0]
            resString = 'Value 1: {} Value 2: {}'.format(previousMT, row[1])
            print(resString)
        else:
            # if the incoming selection is your floor, there will not be any previous 'MILE_TEXT'.
            resString = 'Value 1: {}'.format(row[1])
        # insert into target FC
        with arcpy.da.InsertCursor(River_Section, 'MILE_MARKERS') as incur:
             incur.insertRow([resString])

will output:

Value 1: 658 Value 2: 659
Value 1: 670 Value 2: 671
Value 1: 652 Value 2: 653
Value 1: 666 Value 2: 667

If its not that sequential and you need the previous selections mile_text value, you'll have to include your feature selections in the script in order to maintain the previous mile_text. for example:

miletext = ''

# insert your selection iteration here
for f in ['iteratable list or something']:
    selFeature = arcpy.SelectLayerByLocation_management('selection stuff based on other stuff and f from the iterator')
    with arcpy.da.SearchCursor(selFeature, 'MILE_TEXT') as cur:
        for row in cur:
            if miletext != '':
                resString = 'Value 1: {} Value 2: {}'.format(miletext, row[0])
                print(resString)
            else:
                resString = 'Value 1: {}'.format(row[0])
            # insert into target FC
            with arcpy.da.InsertCursor(River_Section, 'MILE_MARKERS') as incur:
                 incur.insertRow([resString])
            miletext = row[0]

EricMahaffey1

Yeah I noticed the need for quotes around the field name right before you responded. After inserting them, I now get a TypeError: sequence size must match size of the row when I run it. The field width that I'm inserting to is plenty large so I'm not sure what's causing it.

Anonymous User

yes, yes it should. Fixed (along with the cursor name) Thanks!

JamesWilcox1970

Shouldn't the line with the InsertCursor start with "with"?

Anonymous User

I think you need Mile_markers in quote and give it a different name than the searchcursor. The way you had it would be ok too. Sorry I just grabbed that code from an earlier post.

I just remembered that you are passing the selected feature to this tool so it will only be one row instead of a table of rows. You'll have to change that part of model so the selection will be part of this script if you want to the previous value to be persisted for the next iteration.

EricMahaffey1

Sorry for that I'm not able to explain this very clearly Jeff. You're correct with how the table is structured. When I run the last code sample, I'm back to the syntax error with the second "as" in the script.

Anonymous User

ok, still a little muddy on how your data is structured. If your table looks like:

id Mile_text

1 337

2 338

3 339

you'll need to store the previous value into a variable and recall it on the current

inTable = input from your model, which will be Table1
destTable = input from your model, which will be Table2
miletext = ''

with arcpy.da.SearchCursor(inTable , Field_A) as cur:
    for row in cur:
        if miletext != '':
            resString = 'Value 1: {} Value 2: {}'.format(miletext, row[0])
        else: # for the first value before miletext gets assigned
            resString = 'Value 1: {}'.format(row[0])
        # insert into target FC
        with arcpy.da.InsertCursor(Table2, Field_B) as incur:
             incur.insertRow((resString))
        # set new value to miletext for next concat
        miletext = row[0]

EricMahaffey1

Thanks Jeff. I changed the code per your last suggestion. I'm still getting the "list index out of range". The print results only show one value, but I think it should show two. Which from I remember might be causing the index to be out of range in "reString"

Anonymous User

Insert cursor uses a tuple of values for the insertRow so you will need to wrap that resString string with (), I often forget that aspect of insertcursor's and I see that I missed it in the previous example.

Looking at your image, printing the cursor will just print the object so its not really doing anything. Try this:

      print row[0]  #should be the '1234 5678'?
      resString = 'Value 1: {} Value 2: {}'.format(row[0].split(' ')[0], row[0].split(' ')[1])
      print resString
      insertcursor.insertRow((resString))

If this doesn't work, share the print results with us so we get an idea of the data value/format.

EricMahaffey1

Jeff,

I setup two stand alone f-classes to emulate the selection results from within the model. I figured if I can get the script to function with those, then I can focus on embedding it within the model next. When I first started running the code I was getting an invalid syntax error within

arcpy.da.InsertCursor(Table2, Field_B) as cur:

I moved things around to appear like this:

, but now I'm getting a "IndexError: list index out of range" error. Any ideas?

JoeBorgione

Okay; I think it's a nomenclature issue for me. You have two records in Table1, and the respective values in the Field need to be returned to one field in Table2. I'm not much of a model builder, but in a straight python script I can see how you would do that.

EricMahaffey1

HI Joe. The values that I'm trying to take from Table1 are the results of a spatial query. In this case two separate values ("1234" and "5678") from two cells in the same field of the selected features. I'm trying to insert both of those values into a single cell within Table2 ("Value 1: 1234 to Value 2: 5678"). Table2 already exists and the target field is there. I hope this answers your questions. Thanks.

EricMahaffey1

Thanks for the quick reply Jeff. I'll give this a shot and let you know how it turns out. Appreciate the help!

Anonymous User

Your current process is passing a featureclass with a selected feature to the script. using the selected feature as input, you can use a search cursor to access the features data.

inTable = input from your model, which will be Table1
destTable = input from your model, which will be Table2

with arcpy.da.SearchCursor(inTable , Field_A) as cur:
    for row in cur:
        resString = 'Value 1: {} Value 2: {}'.format(row[0].split(' ')[0], row[0].split(' ')[1])
        # insert into target FC
        arcpy.da.InsertCursor(Table2, Field_B) as cur:
             cur.insertRow(resString)

You will need to set this script up as a script tool, then drag and drop that tool it into your model and wire up the input parameters.

JoeBorgione

Maybe you can clarify what the objective is as well as you data structure. Are you randomly inserting the values from Table1 into Table2 or is there some sort of correlation between the two? Does Table2 already exist (update cursor) or are you truly inserting new records (insert cursor).

You mention that Table1 FieldA has two values " 1234" and "5678" ; if both of those strings of characters are within a single field, that field has one value: "1234 5678". A few more details would really help to provide a solution.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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