Splitting data in an attribute table

[SimonCrutchley]

Is it possible to split data within a single field in an attribute table. I have been provided some data where one field currently consists of concatenated data from a table with multiple values separated by "/". There are numerous duplicate elements, which makes the field look messy, so I'd like to remove them. I've had success doing this by exporting into Excel and Word, but it's very long winded, and because of the size of the files very slow, so I wondered if it's possible actually working within GIS.

[DanPatterson]

sure can... field calculator expression using python parser.

Now... how do you want the output formatted?

remove the duplicates?

sort the output?

# ---- split on splitter, remove duplicates and rejoin in sorted order 
def split_o_rama(a, splitter="/"):
    """split stuff, keeping first"""
    out = []
    sp = a.split(splitter)
    for i in sp:
        if i not in out:
            out.append(i)
    return splitter.join([i for i in sorted(out)])
    
# ---- sample data and run
a = "e/a/b/c/a/d/a/b"
split_o_rama(a)
'a/b/c/d/e'

[JoshuaBixby]

Since you seem new to Python, I will offer an alternative so you can see how one of Python's built-in data structures can help in situations like yours.

# ---- split on splitter, remove duplicates using set and rejoin in sorted order 
def set_o_rama(a, splitter="/"):
    """split stuff, keeping only one of each"""
    sp = set(a.split(splitter))
    return splitter.join(sorted(sp))

# ---- sample data and run
a = "e/a/b/c/a/d/a/b"
set_o_rama(a)
'a/b/c/d/e'

[SimonCrutchley]

Hi folks,

Those both look like they will do exactly what I need. Unfortunately, I have no idea how to actually apply them. Do I just copy that text into the Calculated field box or what?

Sorry

[JoshuaBixby]

It is good to start with Calculate Field Python examples—ArcGIS Pro | Documentation. 

An overview:

1. Open Field Calculator
2. Select Python 3 Expression Type:
3. Put the following in Expression Box (change field1 with your field)
   1. set_o_rama(!field1!)
4. Put the function definition in Code Block

[SimonCrutchley]

Thanks. I'll give that a go tomorrow. I should add that I'm working in ArcMap, not ArcGIS Pro, but hopefully that should still work?

Cheers

[JoshuaBixby]

Field Calculator didn't change much between ArcMap and Pro, so you should be able to translate the information over.

[SimonCrutchley]

Thanks for all your help with this.

[SimonCrutchley]

Hi folks,

I tried that, but I'm getting an error message. I attach screenshots of the 'Calculated field' box, and the 'Geoprocessing result'.

I note that in the ArcPro documentation that 'legacy' (which I'm assuming includes ArcMap) uses Python and Python 9.3, not Python 3. Does this make a difference, or have I done something wrong in the code box?

Thanks

[DanPatterson · Accepted solution]

Code block

def set_o_rama(a, splitter="/"):
    """split stuff, keeping only one of each"""
    sp = set(a.split(splitter))
    return splitter.join(sorted(sp))

Expression box

set_o_rama(!YourFieldNameHere!)  # note the ! marks

It is best to select the field name from the dialog's provided list to ensure that the correct format is determined.