nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Trouver plus de publications taguées avec

Accepted answers

danretired

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.

All comments

SimonCrutchley

Hi Dan,

I was just wondering whether you'd had any further thoughts about this? Do you need further information from me?

Thanks

SimonCrutchley

Hi Dan,

That's exactly it. As I show above, I currently have the concatenated fields, separated by / e.g. the field currently called 'TYPE' has entries such as 'ENCLOSURE/FIELD/FIELD SYSTEM/SETTLEMENT' and I would like this to become a number of separate new fields TYPE1 = 'ENCLOSURE', TYPE2 = 'FIELD', TYPE3 = 'FIELD SYSTEM' etc

I attach a sample of a file where I did the split manually, to see if the process would produce the data in a form that I could then use in Portal, in a pop-up.

I hope that makes sense.

Thanks

Split_demo.zip

danretired

@SimonCrutchley are you requiring that you use the same idea, but place each parsed value into a separate field?

Maybe you could elaborate more on what the table and inputs should look like.

SimonCrutchley

Hi Dan,

You were very helpful with the above last year, so I'm hoping that you can help again with a similar issue. Having concatenated the list so that it is searchable in a single attribute, I now discover that I need to make hyperlinks to the various entries 🤔. The easiest way to do this seems to be to split them into separate entries and then do a link to each unique entry, as there are far too many different combinations. I assume that this is what the first part of your program did, so hope it's not difficult to then create a new field for each of the separate options e.g. the field TYPE 'ENCLOSURE/FIELD/FIELD SYSTEM/SETTLEMENT' becomes TYPE1 'ENCLOSURE', TYPE2 'FIELD', TYPE3 'FIELD SYSTEM' etc.

I hope you can help.

Cheers

SimonCrutchley

Brilliant! I hadn't realised it sorts alphabetically, which confused me at first, but that's fine. The key fact is that it removes all the annoying duplicates.

Apologies that I misunderstood about all the different bits in the code and which need to be added and which don't.

Thanks so much. 😊

danretired

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.

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

set_o_rama2.jpg

set_o_rama1.jpg

bixb0012

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

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

bixb0012

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

An overview:

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

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

bixb0012

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'

danretired

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'

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.