topic Re: Splitting data in an attribute table in Mapping Questions

Splitting data in an attribute table

SimonCrutchley — Tue, 08 Dec 2020 14:23:18 GMT

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.

Re: Splitting data in an attribute table

DanPatterson — Tue, 08 Dec 2020 14:40:03 GMT

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'

Re: Splitting data in an attribute table

JoshuaBixby — Tue, 08 Dec 2020 15:09:15 GMT

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'

Re: Splitting data in an attribute table

SimonCrutchley — Tue, 08 Dec 2020 15:30:55 GMT

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

Re: Splitting data in an attribute table

JoshuaBixby — Tue, 08 Dec 2020 16:47:46 GMT

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

Re: Splitting data in an attribute table

SimonCrutchley — Tue, 08 Dec 2020 16:52:50 GMT

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

Re: Splitting data in an attribute table

JoshuaBixby — Tue, 08 Dec 2020 16:57:38 GMT

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

Re: Splitting data in an attribute table

SimonCrutchley — Wed, 09 Dec 2020 09:55:23 GMT

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

Re: Splitting data in an attribute table

DanPatterson — Wed, 09 Dec 2020 12:05:34 GMT

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.

Re: Splitting data in an attribute table

SimonCrutchley — Wed, 09 Dec 2020 12:42:54 GMT

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. 😊

Re: Splitting data in an attribute table

SimonCrutchley — Wed, 09 Dec 2020 12:44:17 GMT

Thanks for all your help with this.

Re: Splitting data in an attribute table

SimonCrutchley — Tue, 18 May 2021 08:56:32 GMT

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

Re: Splitting data in an attribute table

DanPatterson — Thu, 20 May 2021 15:06:35 GMT

@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.

Re: Splitting data in an attribute table

SimonCrutchley — Thu, 20 May 2021 15:43:13 GMT

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

Re: Splitting data in an attribute table

SimonCrutchley — Wed, 02 Jun 2021 13:24:37 GMT

Hi Dan,

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

Thanks