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

2818
22
01-28-2021 02:28 PM
EricMahaffey1
Occasional Contributor

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.

0 Kudos
22 Replies
EricMahaffey1
Occasional Contributor

Correct.  The data in the MILE_TEXT field would be 337 in the first cell, then 336 in the second cell.  I'd like to grab these two values then insert them into a single cell within the MILE_MARKERS field within the River_Section table.  So in theory I think there's no need for the split at all now that I look at it. 

0 Kudos
by Anonymous User
Not applicable

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
Occasional Contributor

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.

EricMahaffey1_0-1611958347918.png

 

0 Kudos
by Anonymous User
Not applicable

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
Occasional Contributor

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.

0 Kudos
by Anonymous User
Not applicable

Check if you have double parenthesis : insertRow((resString)).

JamesWilcox1970
New Contributor III

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

by Anonymous User
Not applicable

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

EricMahaffey1
Occasional Contributor

Thanks guys.  That seemed to work.  No errors now, and I'm getting data in the second table.  However, it's producing two rows.  The first row only has one value, the second looks correct with both values

EricMahaffey1_0-1611960135377.png

I also had to use brackets instead of parenthesis around reString

with arcpy.da.InsertCursor(River_Section, "MILE_MARKERS") as iCursor:
iCursor.insertRow([resString])

0 Kudos
by Anonymous User
Not applicable

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]

 

0 Kudos