topic Re: Split records of a field which are separated by a specific character in several columns in Python Questions

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

GeorgiosFot — Tue, 17 Nov 2020 20:40:51 GMT

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.

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

DavidPike — Tue, 17 Nov 2020 20:59:52 GMT

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

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

BlakeTerhune — Wed, 18 Nov 2020 22:00:45 GMT

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

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

DavidPike — Wed, 18 Nov 2020 22:09:34 GMT

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?

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

BlakeTerhune — Wed, 18 Nov 2020 23:46:18 GMT

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)