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

Question

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.

table sample.jpg

Map Image as Should Be Displayed.jpg

blake.terhune · Answer

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)

DavidPike · Answer

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?

blake.terhune · Answer

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