Split records of a field which are separated by a specific character in several columns

930
4
11-17-2020 12:40 PM
GeorgiosFot
New Contributor

I would like to ask how can I split the entries of a field which are separated by commas, into new columns. Because comma-separated partitions can be too many (maybe 50) it is difficult to do it manually with .split () for each column separately.

0 Kudos
4 Replies
DavidPike
MVP Frequent Contributor

Have you used model builder before? Even if not, this guide seems to be a good walkthrough:

https://resources.esri.ca/getting-technical/delimit-your-data-in-modelbuilder

 

0 Kudos
BlakeTerhune
MVP Regular Contributor

I see that there can be a variable number of values in the list. Will they always be sequential? In other words, if it skips a column, will there be an empty value? Like a,b,c,,e,f

0 Kudos
DavidPike
MVP Frequent Contributor

You're right, I believe this will not respect the order i.e. a field could contain any records depending on order or amount of delimited records.

My go-to would be to script it, do you have any scripting experience?

0 Kudos
BlakeTerhune
MVP Regular Contributor

I haven't tested this so there's likely something wrong, but give it a try and let me know how it goes.

import arcpy

fc = path_to_fc
# Get list of fields, ignoring ObjectID
# Assumes that all other fields are valid for the separated list values to go
fields = [f.name for f in arcpy.ListFields(fc) if f.type != "OID" or f.name != "OBJECTID"]
# Put the field with the sparated values at the end of the list
# so it's easy to identify.
sep_field = fields.pop(fields.index("test"))
fields.append(sep_field)
with arcpy.da.UpdateCursor(fc, fields) as cursor: 
    for row in cursor:
        # Make list of individual values
        # Assumes separators are always a comma (no space).
        sep_values = row[-1].split(",")
        # Make list of columns to receive values
        sep_columns = row[:-1]
        for i, value in enumerate(sep_values):
            # Put each value into a column.
            # Will yield IndexError if there are too many values for the columns.
            sep_columns[i] = value
            # Any additional columns that don't have a new value
            # will retain their original value (if any).
        # Put the original separated list of values
        # back with the new column values.
        row = sep_columns.append(sep_values)
        # Update the row in the table
        cursor.updateRow(row)