How do I split values within a field into separate rows using ArcPy in Model Builder?

[JohnEbbers]

Hello,

I am trying to split the values contained in one field table into separate rows. For example, an entry in the field 'Numbers' would contain [12, 14, 18] and I'm trying to split it up so that 12, 14 and 18 are each their own entry in their own rows while maintaining the rest of the data. I am also trying to do this in model builder; I don't know how to use ArcPy or whatever the solution is in Model Builder so if that could be explained as well that would be much appreciated.

If more information is required I will be sure to answer tomorrow morning.

Thanks

[BrendanNewell]

hi @JohnEbbers,
I would recommend using Arcade within the calculate field geoprocessing tool within the Model. You will have to add the calculate tool each time you require to calculate a single field, so in your above example three times. 

You could use the split function as seen in the below expression to split the text string by each comma.

var input = "12,14,18"
var output = Split(input, ",")
return output[0]
//12 would be returned
//return output[1]
//14 would be returned

• https://developers.arcgis.com/arcade/function-reference/text_functions/#splitinputtext-separatortext...
  
  let me know if you need more information here, happy to help!

[DavidSolari]

I'm going to assume their data has variable-length arrays and this will fail at best and obliterate their original data at worst.

I also can't find any built-in tools that will explode records based on a given format so I'm pretty sure this requires a trip to arcpy.

[JohnEbbers]

Hello @BrendanNewell , thanks for you suggestion.

Unfortunately @DavidSolari is correct, my data set has variable-length arrays, in addition to a variable set of arrays that need to be split. I should have been more clear with my question and will edit to make the example fit my situation better.

-John

[JohnEbbers]

EDIT:

The example I gave did not sufficiently represent my situation. Say I had this dataset:

OBJECTID | FIELDX     | FIELDY

1                  | P1, W2     | Cat
2                  | S6             | Dog
3                  | I1, K9, J7 | Fish

The output I am trying to create would be this:

OBJECTID | FIELDX     | FIELDY

1                  | P1             | Cat
2                  | W2            | Cat
3                  | S6             | Dog
4                  | I1               | Fish
5                  | K9             | Fish
6                  | J7              | Fish

[BobBooth1 · Accepted solution]

Have a look at this post:

https://gis.stackexchange.com/questions/486403/duplicating-records-by-using-column-delimiter-in-arcg...

I think it is what you want.

[JohnEbbers]

Hello Bob, 

Thanks! This should work, but for some reason when I run the for loop nothing happens, I've included the relevant screenshots.

John Ebbers

[BobBooth1]

It looks like you are doing this in the Python Window in ArcGIS Pro. It might be better to do it in a Notebook in ArcGIS Pro, to save repeated copy/paste and to have a sense of what is working.

I would add some rows with print statements to get a view into the variables' contents at different places in the code. If the code is running without giving you an error message, the problem is probably a logic error in the code  - it is doing something, just not what you want...

[JohnEbbers · Accepted solution]

Hello,

This is what finally did it, the error I kept on getting was actually that I hadn't signed in a so didn't have the proper license to edit the data. For future readers, this is the code that worked:

It is virtually the same as the one in the link sent by @BobBooth1 but I will mark this as the solution since the code is localized. Here are also screenshots of the tables that were used:

Thanks Bob,

John Ebbers