topic Re: Concatenate Multi-Values from Single Field and Insert to Single Cell in Python Questions

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

EricMahaffey1 — Thu, 28 Jan 2021 22:28:02 GMT

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.

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

JoeBorgione — Thu, 28 Jan 2021 23:23:02 GMT

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.

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

Anonymous User — Fri, 29 Jan 2021 14:05:12 GMT

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.

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

EricMahaffey1 — Fri, 29 Jan 2021 17:46:03 GMT

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

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

EricMahaffey1 — Fri, 29 Jan 2021 20:00:00 GMT

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.

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

JoeBorgione — Fri, 29 Jan 2021 20:11:10 GMT

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.

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

EricMahaffey1 — Fri, 29 Jan 2021 20:22:22 GMT

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?

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

Anonymous User — Fri, 29 Jan 2021 20:56:27 GMT

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.

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

EricMahaffey1 — Fri, 29 Jan 2021 21:05:48 GMT

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"

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

Anonymous User — Fri, 29 Jan 2021 21:27:37 GMT

It would have failed on the insertRow eventually... It failing on the reString tells me that the data in 'MILE_TEXT' field is not "1234 5678" and it is not splitting into two ('1234', '5678').

From the image, it printed 337. Do you have the right cell for the cursor, or are there mixed values like 337 and 333 789? If it is mixed like that, you will need a conditional to skip the split if there is only one.

if ' ' in row[0]: resString = split code else: resString = 'Value 1: {}'.format(row[0]) insertCursor.insertRow((resString))

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

EricMahaffey1 — Fri, 29 Jan 2021 21:34:30 GMT

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.

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

Anonymous User — Fri, 29 Jan 2021 22:26:00 GMT

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]

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

EricMahaffey1 — Fri, 29 Jan 2021 22:13:01 GMT

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.

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

Anonymous User — Fri, 29 Jan 2021 22:18:11 GMT

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.

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

JamesWilcox1970 — Fri, 29 Jan 2021 22:19:03 GMT

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

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

Anonymous User — Fri, 29 Jan 2021 22:21:33 GMT

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

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

EricMahaffey1 — Fri, 29 Jan 2021 22:28:11 GMT

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.

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

Anonymous User — Fri, 29 Jan 2021 22:30:58 GMT

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

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

EricMahaffey1 — Fri, 29 Jan 2021 22:44:07 GMT

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

I also had to use brackets instead of parenthesis around reString

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

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

Anonymous User — Sat, 30 Jan 2021 13:45:02 GMT

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]